Can I enable both peer and md5 authentication for the same PostgreSQL local user in pg_hba.conf?

Question

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?

Answer 1

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.