It looks like postgresql supports either of the below
- Use tcp (i.e. localhost:5432) with password authentication
- Use Unix domain scoket (i.e /var/run/postgresql/.s.PGSQL.5432 ) with peer/trust authentication
Is it possible to have password with unix domain socket?
Background:
I use php-fpm to run multiple apps. I want different apps to have different databases and passwords but they will be run as the same user (www-data). So peer/trust authentication does not good since if one app is compromised, it can read data of the other app too. I cannot use tcp auth too as I run php-fpm service with PrivateNetwork=yes
to make sure the apps can't make outside requests. Also unix domain sockets have better performance than tcp.
Best Answer
That's a typical default configuration, but not a hard-wired behavior.
It's the server-side pg_hba.conf configuration file that tells what authentication method gets used depending on the type and origin of the connection, and the target database and username.
Often there's this line in the first few rules, which trigger the
peer
authentication for Unix local domain sockets:To request a password, replace it with
(or the more modern
scram-sha-256
instead ofmd5
if the server has passwords hashed with SCRAM, available since Postgres 10).Often there's also this more specific rule above in the configuration, for the postgres user:
You may leave it as is or change it too depending on your case. Rules are interpreted in the order of appearance, so the more specific rules go on top.