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?