Postgresql unix domain socket with password authentication

Question

It looks like postgresql supports either of the below

1. Use tcp (i.e. localhost:5432) with password authentication
2. 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.

Answer 1

1. Use tcp (i.e. localhost:5432) with password authentication
2. Use Unix domain scoket (i.e /var/run/postgresql/.s.PGSQL.5432 ) with peer/trust authentication

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:

# "local" is for Unix domain socket connections only
local   all             all                                     peer

To request a password, replace it with

# "local" is for Unix domain socket connections only
local   all             all                                     md5

(or the more modern scram-sha-256 instead of md5 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:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

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.

Answer 2

I am not familiar with php-fpm, but usually in Postgres, if you leave out the host in the connection string, but specify the username, then it connects as the specified user. At least that's the documented behaviour e.g. in psql.

So that solves

if one app is compromised, it can read data of the other app too.

You can e.g. set up one schema for each app, have one user for each app, and only allow the user access to the corresponding schema.

You have to make sure in php-fm that the choice of user cannot be influenced by someone with www-data permissions.

It doesn't give you passwords, however. (But passwords stored in scripts are not safer, anyhow...)