Feature idea: Support for GSS/Kerberos

[ktosiek]

I have a PostgreSQL cluster in a Kerberos (Active Directory) environment. I'd like to use pgbouncer in that environment while keeping the Kerberos authentication.
I think this could be achieved by:

• implementing the gss authentication method
• using Constrained Delegation / Kerberos S4UProxy to pass the client credentials to the PostgreSQL cluster. This would be transparent to clients.

Problems I can see:

• GSS API is synchronous, and I don't think there are any guarantees about thread safety. Are there any components like this already used? Would I need to keep GSS calls on a separate thread?

[ktosiek]

I've started working on this, but there's an architectural decision I'm not sure about: what to do with the "borrowed" user's credential?

Just storing it on the PgPool won't work - I need a fresh credential to authenticate to the server, but I can't just blindly put it there each time a client connects because I might need to use the same client credential in multiple calls to GSS. Also, I can't copy it to PgSocket - there's no way to copy the token I'm getting from GSS. Maybe some kind of garbage collection might work here? I have a scheme in mind, but if there's anything similar already in pgbouncer I'd love a hint.

[petere]

I can't comment on the technical details, but GSS/Kerberos support in PgBouncer would be welcome.

[Godwottery]

I second the fact that it would be welcome. I would be able to help with testing.

[ktosiek]

How would you use such feature? Would you use it with constrained (S4U)/unconstrained delegation? Or would you use a dedicated user on the backend?

[Godwottery]

Without fully understanding the question: I would like pgBouncer to pass along the authentication information that the user has, so that the user can authenticate via pgBouncer using their domain credentials. I would like to have only a single source of user-info, which is the AD-server, and then have everything authenticate against this. I think that would be constrained/unconstrained delegation.

[aiobofh]

@ktosiek how are things going?

My thinking is that every connected user should have a more permanent connection to the database through the bouncer (a pool of connections), which is set-up on the first GSSAPI login to the pgbouncer (delegation/token forwarding), until it times out or something. And the users should in turn be able to connect and disconnect as they please to the pgbouncer from their clients with their Kerberos tokens. Right? I'm very curious on your progress and findings! :D

[ktosiek]

@aiobofh I've stopped working on this for now.

Your thinking is correct for first connection to the backend db, but pgbouncer might need to connect as the same user without getting a new connection on the front-end.

I think I was too attached to the idea of always using a client-provided token, when in fact we could just use S4U2Self when what we have expires.

[aiobofh]

S4U2Self might work in some scenarios, but not all. If you have an AD or or AD-like solution with an LDAP solution that can provide the metadata required it will fly, i guess. But for example: When it comes to Kerberos-only centralized user database - I don't know, but I guess it does not provide S4U2 out of the box? Then one has to rely on token-forwarding. It's still better than nothing :)

[Sheridan]

Is there any news, comrades?

[ktosiek]

I'm afraid there's none.

If you want to work on this I can help with code review and some basics, but I just don't use pgbouncer for users (which was my main use case for Kerberos).

[aiobofh]

A more simplistic variant would be to have GSSAPI on the listening side of PgBouncer, and forcing SET SESSION AUTHORIZATION 'username' before making further actions through the superuser connection already setup to the PostgreSQL server. Not super nice, since the user at some point is privilege escalated... (in the "normal" installation scenario where PgBouncer logs in as a superuser).

I'm not sure if gsstunnel would help, since we would still need to inject the SQL code for switching to the user somehow - if the goal is to maintain user identity for data access (which i kind of assume).

[aiobofh]

OK, guys... No promises, but I started working on this, and will give it a go... But I've been quite sick for a month. I hope to get some progress on it now as i get better... I'm not sure to what degree I will be able make true SSO happen all the way to PostgreSQL. The simplest/generic solution i can come up with is the one i stated earlier. Letting the sessions switch users in the PostgreSQL session (with some risk for a slight privilege escalation since it requires that PGBouncer is connected to the PostgreSQL-server with a super-user), rather than forwarding the authentication once more - since that would require dropping connections, and I guess we don't want that.

At least I will try to get a GSSAPI authentication to the PGBouncer-server somehow from any libpq-implementation, with reuse of PostgreSQL Kerberos configurations and stuff like that. I'm still studying the problem.

If someone familiar with the code in question (mainly client.c) wants to share any heads up on caveats, or get involved, please let me know. Where is the best place to discuss real-time? IRC?

I already realized it will not be as simple as I thought since many framework-things available in PostgreSQL will not be able in PGBouncer, so augmenting code from the one to the other will not be the way to go.

I have written a few other GSSAPI-things earlier (Minor tweaks for the Dovecot IMAP-server, older NFS-server versions, and a patch to CyrusSASL for token delegation) and my own small applications where I wanted single-sign-on. So I think it will be possible - once I get my head around where in the handshake procedure I should shoe-horn the stuff in :D (probably in several places)

[petere]

Take a look at the SCRAM patch, which is the most recent instance of a new authentication method being added.

Also study the PAM code, the way it runs authentication in a separate thread, which is probably something you will have to do as well.

[aiobofh]

@petere Thanks, mate :) I already took your suggested approach. Moving along nicely - I'm not sure I will support 100% of the GSSAPI features, but - I'm hopeful and a subset would be a good start :)

[aiobofh]

So.. I've made pull-request #441 and it seems OK, however no tests on the specific feature. I don't know how to set-up kerberized services on the CI stuff in GitHub to do the black-box tests required, i guess it would be possible somehow.

[petere]

The Kerberos test suite in the PostgreSQL source tree (src/test/kerberos/) might give some ideas.

[aiobofh]

@petere yepp. Like PostgreSQL tests... PGBouncer too would need a full MIT Kerberos installation the CI environment, with a configured host and service, also with some users and stuff to integrate well. I currently have no time to make this happen. :(

[AndrewJackson2020]

+1 for this idea. This is a very important feature in enterprise environments and would be a huge advantage for pgbouncer as no other postgres connection pooler offers this to my knowledge.