Page MenuHomeSoftware Heritage

shared pg_service configuration on all hosts
Open, NormalPublic

Description

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)

Event Timeline

zack triaged this task as Normal priority.Wed, Jan 6, 1:39 PM
zack created this task.
zack updated the task description. (Show Details)

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:

  • replacing the .pg_service.conf files shipped in (select) user directories by a system-wide /etc/postgresql-common/pg_service.conf
  • hardcode user=guest / password=guest in the system-wide file for all service entries (users will be able to override the user setting in their own connection string if needed)
  • drop the management of .pgpass files completely

Once that's done I think we can enable this on all hosts without ill effects.

zack renamed this task from shared pg_service configuration of all hosts to shared pg_service configuration on all hosts.Wed, Jan 6, 2:11 PM

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.