Clear text auth passthrough

[bendem]

Is your feature request related to a problem? Please describe.
We use external auth to connect to postgres (think ldap, pam, etc.), when connecting, the password is sent in clear text and passed through to the external authentication provider so we need the actual password and not a hash (especially not md5). auth_query cannot work with this kind of authentication since no one involved here has access to the password. Obviously, this only secure if the connections between the client, pgcat and the postgres server are all encrypted.

Describe the solution you'd like
Support actual auth passthrough, don't try to validate the password, just try to open a connection to postgres with what you received and if that worked, the password was correct.

Describe alternatives you've considered
We could connect pgcat directly to the auth provider, but that's much more complex to implement imo.

Additional context
Adjacent to #255 which is also auth related.

[levkk]

Hi, thanks for submitting the issue.

One issue I'm seeing with this implementation is:

just try to open a connection to postgres with what you received and if that worked

This does not scale beyond a simple low traffic configuration. Opening Postgres connections is expensive, and doing so for every client that connects to the pooler will defeat the point of the pooler. That's why poolers implement their own auth.

We can try and cache auth perhaps, that's what auth_query does, although it does only work with md5 at the moment. We can maybe hash the plaintext password we receive ourselves and compare it with what's in the server? Although Postgres 14+ switched to SCRAM, so that auth method won't work anymore.

Ideas welcome!

[bendem]

We can try and cache auth perhaps, that's what auth_query does, although it does only work with md5 at the moment. We can maybe hash the plaintext password we receive ourselves and compare it with what's in the server? Although Postgres 14+ switched to SCRAM, so that auth method won't work anymore.

Being a pooler, pgcat is supposed to open connections less often than clients, which means when the client opens it either has a connection open, or it has the credentials to pass through.

To validate the password of the client, if a connection is not available in the pool, one is opened and if successful, the credentials were valid and are then cached in memory (hashed using any algorithm available). If a connection is available in the pool, the password is checked against the hash cached in memory.

SCRAM is a "Password Authentication" method, separate from external authentication systems, as such, the server will probably not propose it in the authentication request message if it is configured to do LDAP/RADIUS/PAM/etc.