Postgresql – enable both peer and md5 authentication for the same PostgreSQL local user in pg_hba.conf

configurationpostgresql

I would like to let a PostgreSQL (super)user be authenticated with either the "peer" or "md5" authentication methods, preferably using local Unix domain sockets in both cases. The problem is that the order of the entries in pg_hba.conf matters, and the first match will be applied.

What I have:

# TYPE    DATABASE   USER      ADDRESS   METHOD
  local   all        harold              peer
  local   all        all                 md5

What I would like (invalid syntax):

# TYPE    DATABASE   USER      ADDRESS   METHOD
  local   all        harold              peer-or-md5
  local   all        all                 md5

Here are some more details about the environment we'd like to use this in:

On this particular server, all databases are relatively small (<10MB of data each) and purely read-only. They are dropped and re-created every night by the harold superuser. harold is also a system account, so "peer" works nicely for passwordless cronjobs etc. Then there is a web interface accessing the databases: this uses md5 authentication (system user is the webserver, pg username, password, and db name are supplied by the visitors). Now I'd like to let the harold user log in over the web interface as well. This fails because of the "peer" setting for harold.

The current workaround is to let the webinterface connect over TCP, using 

# TYPE    DATABASE   USER      ADDRESS        METHOD
  host    all        all       127.0.0.1/32   md5
  host    all        all       ::1/128        md5

in addition to the local entries in the first example. This works, but I'd rather use local sockets here too – for performance and because of a gut feeling that it seems like the right thing to do.

Is this possible? Are there any better workarounds for us?

Best Answer

According to a similar StackOverflow question, the answer is no, you can't enable two types of authentication for the same type, database, user, and address.

The StackOverflow question recommends using a .pgpass file in the user's home directory and using md5 authentication, but I'm not sure that would necessarily work for cron jobs.

Related Topic