We spend a lot of time fiddling individually with pg_service/pgpass files. We have good doc about it, but automation is better than doc. Can we please deploy /etc/postgres/**/pg_service.conf (and the matching pgpass) files everywhere via puppet, standardizing database names listed on the wiki for everyone: https://intranet.softwareheritage.org/wiki/Databases ?
(this would supersede T1234, which was just a tiny teeny instance of this request)
After checking the postgresql docs for pg_service.conf (https://www.postgresql.org/docs/13/libpq-pgservice.html) and the allowed settings there (https://www.postgresql.org/docs/13/libpq-connect.html#LIBPQ-PARAMKEYWORDS), as well as checking the behavior of libpq on debian systems, I think we can improve and generalize what was shipped with T1234 by:
Once that's done I think we can enable this on all hosts without ill effects.
If we make sure that "trust" authentication for the guest user is properly configured for all database hosts (in pg_hba.conf), we can even avoid the password entry in pg_service.conf.
This allows overriding the default service entry for different credentials using psql 'service=swh-storage user=xxx' (which would prompt for a password / use the user's .pgpass file). If we set password in the service file, we would need users to do psql 'service=swh-storage user=xxx password=', which looks a bit silly.