Re: [oauth] SASL mechanisms - Mailing list pgsql-hackers

On Mon, Nov 24, 2025 at 10:54 AM Nico Williams <nico@cryptonector.com> wrote:
> OAuth comes with batteries not included, unlike Kerberos.

Yes. :/

> > OAuth validators can also be Postgres extensions, so this is at least
> > technically feasible to retrieve, though I'm not yet understanding why
> > you need set_config() functionality. And the sslinfo extension should
> > be able to give you the SANs (though whether they're in a form that's
> > easy to use without too much trouble, I don't know).
>
> Have you see how PostgREST does it?  I want it done kinda like that.

I could see us eventually pulling out the user's claims (whether from
Kerberos or OAuth, or maybe generically mapped from an identity) into
a central API. That way validators wouldn't have to reinvent the wheel
each time.

> > That should be in pg_stat_gssapi.
>
> I'd like it to be something more environmental, like the `TG_*` things
> that trigger functions get.  Having to JOIN a table for a single item
> like this is annoying.  What I've done in some cases is to make the
> client principal name an actual ROLE name (using a trigger to create it
> if need be), which then lets me use `current_user` to get at it in SQL
> code.

As long as you're just looking for the "single" principal that the
database considers to represent the user, we have system_user now.
It's not complex enough to handle multi-factor situations, though,
like when you have both an OAuth token and a client cert. For that you
still need sslinfo.

authn_id itself (which is the internal implementation of system_user)
could be viewed as the base of a more general claims system, I
suppose.

> We use an OAuth claim to indicate the user's Kerberos principal name,
> since we started out as a Kerberos shop and need a way to transition
> that doesn't break old things.  But we don't use `sub` for that.  So at
> minimum we'd need a way to tell PG which claim has the Kerberos
> principal name and then `pg_hba`/`pg_ident` can do the rest.

This is what the validator API lets you do today. It's just not done
in SQL (unless of course you write a validator that lets you write
SQL).

> > libpq will let you plug in your own code. psql doesn't (yet).
>
> :(

I'm trying to take a small step towards that in PG19, if you don't
mind overriding link paths. Generic client-side plugins are probably
too ambitious to turn around in a couple of months; I need a solid
proposal for that and I don't have one yet.

(I don't want this to be a CVE factory. There are a bunch of security
conventions around the server modules that users have gotten used to,
but if I inject those conventions into libpq they will affect a bunch
of people who have never had to think about this before.)

> Resource indicators are nice, but we use single-use FQDNs, so `aud`
> (audience) is enough to constrain the token to a whole PG DB in most
> cases.  That said, `resource=postgresql://...` would be fantastic,
> falling back on just `aud=FQDN` if the STS doesn't support `resource`.

Yeah, though we'd probably need to outright prohibit query strings in
our resource implementation (postgres://host/db?host=other-host looks
like a nightmare to me). In any case, I think we're going to need to
wait for better authorization server support for that sort of thing
before it goes into our builtin flow.

> > As for working with an STS in general, I think we're going to need
> > Token Exchange ourselves for postgres_fdw at some point. And possibly
> > pgbouncer?
>
> Almost certainly.
>
> I recommend open-coding it rather than relying on some third party
> library.

We are well-versed in that by now :)

> Imagine that we had set-only session-level `set_config()`s, and/or ones
> that require privilege.  Then authen. mechanisms can set a bunch to
> describe the credential used.  And then there could be a "session begin
> trigger"-like function that the DB owner could specify to the rest of
> whatever they want done, up to and including [optionally] `SET SESSION
> ROLE`.

If anyone else is reading along, I'd be interested to see what kind of
appetite there is for a generic mechanism like this? It sounds like a
decent idea to me, but I'm not sure how big the audience for it would
be.

--Jacob