How to use password file for Postgresql authentication

[nu-roger]

Hi,

We'd like to use password to authenticate postgresql and as the document suggests:

It is usually recommended not to set the password directly in this configuration parameter. Use a password file instead.

We followed the instruction to create the .pgpass file and we can see psql can connect to the database without asking the password. However, Nominatim server returns error like this when accessing the status endpoint:

ERROR: Database connection failed

I can see the detailed error message is like:
Failed to establish database connection:SQLSTATE[08006] [7] fe_sendauth: no password supplied.

What we did are:

• Update the NOMINATIM_DATABASE_DSN as: pgsql:dbname=nominatim;user=nominatim
• Set the authentication from peer to md5 for local access in /var/lib/pgsql/13/data/pg_hba.conf.
• Create .pgpass file under nominatim user home folder and give it the permission as "0600"

So I am wondering what is missing to configure the nominatim server to use the password file for database authentication?

Thanks,
Roger

[lonvia]

After updating the NOMINATIM_DATABASE_DSN you need to run nominatim refresh --website. Have you done that? If yes, inspect website/status.php to confirm that there is really the correct DSN in there.

Also be aware that you have to create the .pgpass file for the user who is making the connection to the database. If you are running Nominatim through a server, this would be usually 'www-data'. You might have to work with a PGPASSFILE environment variable. But we are getting now deep into the realms of system administration which goes a bit too far for this discussion forum.

Side note: the API should usually not be connecting as the DB user nominatim but as the read-only website user. If you haven't modified the configuration this would be www-data.

[nu-roger]

Changing the user name to apache (as we are on centos) fixed the problem. Thanks and appreciate your help!