PostgreSQL multiple authentication methods

Question

How can I set up multiple authentication methods for the same host/database/user rule? I want to be able to log in to my postgres user using both sudo -u postgres psql -U postgres (without having to enter a PostgreSQL password) and psql -U postgres --password. Something like the following in pg_hba.conf:

local   all             postgres                        md5 local   all             postgres                        peer 

I can only get one method or the other working at the same time.

Thanks.

(I am using PostgreSQL 9.1).

Answer 1

Nope. Only one auth method is supported for any given configuration.

I'd love it if Pg could support fall-back authentication, where if an ident check fails it allows md5 auth instead. It doesn't support this at the moment, though, and I suspect (I haven't verified) that a protocol change would be required to support it.

What you can do is store the password in a $HOME/.pgpass file for the postgres system user. Give it mode 0600 so it's only readable by the postgres user and by root, both of whom can get direct access to the database files and configuration anyway. That way you get easy admin and md5 auth. On some systems you may have to set and create a home directory for the postgres user before you can do this. See getent passwd postgres to see if if the postgres user has a homedir and if so, where it is.

(UPDATE: used to read $HOME/.psqlrc - which is useful, but .pgpass is suitable for password storage)