1

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.

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.

  • Thanks for the explanation. I have been using a combination of local and host, it just seemed ugly and I was looking for a cleaner solution. I like the idea of md5_fallback, that would seem to cover a lot of currently impossible use cases. – Mike Blackwell Dec 11 '14 at 13:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.