Allow passwordless 'peer' auth where matches, but require password for other users

Question

I have what I think is a simple Postgres authentication requirement, but I can't figure out the pg_hba.conf incantation to make it work.

I have a database user (role) named dbuser which is the owner of a password-protected database adb. There is a corresponding Unix user named dbuser. The Unix user dbuser should be able to connect to adb without a password, all other Unix users should be able to connect by using the dbuser role and providing the database password. I.e.

sudo -u dbuser psql -U dbuser adb

should not require a database password, but

sudo -u any_other_user psql -U dbuser adb

should require the database password.

The pg_hba.conf entry:

local adb dbuser peer

works for the first case, but fails for the second (psql: FATAL: Peer authentication failed for user "dbuser"). I understand why, but can't figure out the right rules to do what I want. Unix socket connections (local) only. Postgres 9.2 if it makes a difference.

Answer 1

What you want isn't possible with PostgreSQL's current authentication scheme. When peer authentication fails, PostgreSQL won't fall through to the next entry in pg_hba.conf. Only the first entry is used, and if it fails, the connection is rejected. The peer mode does not offer the option of falling back to password authentication if the user ID doesn't match.

So you can't do it. Sorry. The closest you can come is using local (unix socket) connections with peer auth and requiring other clients to connect over TCP/IP (host) sockets with md5 auth. Using a different protocol lets you pick a different auth method for each.

I recently proposed a change to authentication behaviour on pgsql-hackers that might permit what you want in a later version, if I can convince people it's a good idea. Basically, the peer mode would take an md5_fallback option that'd allow it to request a password from the client if it failed peer authentication.