Allow an 'auth_query' so passwords for pools can be loaded from database servers

[magec]

Is your feature request related to a problem? Please describe.

We currently use PgBouncer with md5 password method using auth_query so we do not need to change its configuration when password changes etc. Also it allows us not to have to set passwords in three places, application -> DB connection pooler -> Database server.

It would be nice to have a feature like PgBouncer/Odyssey have that allows md5 authentication using passwords stored on server backends by means of a query to the target server. This way we only have to tell PgCat one password, the one used to connect to the server for looking up the password. The rest of passwords are obtained from the DB.

Describe the solution you'd like
I find Pgbouncer neat.

In the case of PGCat It would be something like:

---

auth_query string

Enable remote user authentication.

Whenever a new client connection is opened and MD5 auth is used, use 'auth_query' against target server (using auth_user and auth_password) to obtain user password.

auth_query "SELECT usename, passwd FROM pg_shadow WHERE usename=$1"
auth_user ""
auth_password ""

This is usually done using a function so you can use an unprivileged user that have access to just this table (see this)

Disabled by default.

---

Also, to ease deployment in containerized environments It would be nice to be able to overwrite auth_query_password using an environment variable like (PGCAT_AUTH_QUERY_PASSWORD) , this way, if also admin password can be overridden by an Env var, config file will be password-less which improves security and simplifies deployment in containerized environments.

NOTE: I currently have some bandwidth to implement this.

[levkk]

Postgres 14 switched to SCRAM, will this still work?

[magec]

MD5 is still available (and widely used), not an expert on the new scram method, will investigate a bit further.

[magec]

Uhmm, from pgbouncer docu:

SCRAM secrets can only be used for logging into a server if the client authentication also uses SCRAM, the PgBouncer database definition does not specify a user name, and the SCRAM secrets are identical in PgBouncer and the PostgreSQL server (same salt and iterations, not merely the same password). This is due to an inherent security property of SCRAM: The stored SCRAM secret cannot by itself be used for deriving login credentials.

The authentication file can be written by hand, but it's also useful to generate it from some other list of users and passwords. See ./etc/mkauth.py for a sample script to generate the authentication file from the pg_shadow system table. Alternatively, use auth_query instead of auth_file to avoid having to maintain a separate authentication file.

It seems that username definition should be drop from pool config, well also the password which is the point of this. I don't see why not this could also be used for scram. Will dig deeper tomorrow, when I have some time.

[magec]

Well, the whole scram thing can't be easily implemented given the way the pool works. For "pass-through" authentication to work in scram, PgCat should talk to clients for opening new server connections and currently this is decoupled, server connections are started by PgCat on demand with the client not intervening at all.

I have a working proof of concept for MD5, mainly because knowing server hash (by using auth_query) you can construct the salted hash without even knowing the password, but for scram this is not possible, which is the whole point of the protocol in the end.

So, even though we could "easily" set up Client -> PgCat scram auth using server hashes obtained by the auth_query method, we still need the client to open server connections. I see some 'possible' solutions:

• A "clean" solution could be using existing client connections to establish new server connections, but I don't see any way of requesting a 're-auth' to clients in Postgres protocol. If such thing could be generated, we could establish new server connections by requesting auth messages to the client that wants to use a server connection.

• Use new client connections to open server connections and keep them opened. This is suboptimal, because we will not be pooling until we reach the pool_size.

[levkk]

We could try to do what we do for our config validation: fetch all information at boot and store it in a HashMap or similar. When a user attempts to login, we compare their md5 hash with what we fetched from pg_shadow on boot. It won't be real time, e.g. when a new user is added, this mapping has to be refetched, but that can be implemented as part of RELOAD.

[magec]

Yep, that would do. Still, are you ok on implementing only cleartext/md5 with auth_query.

[levkk]

Yeah sure, it's a great feature to have, and as you said there are many installations still using md5. Maybe we'll be able to figure out SCRAM later too.