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

On Tue, Nov 25, 2025 at 09:19:29AM -0800, Jacob Champion wrote:
> On Mon, Nov 24, 2025 at 10:54 AM Nico Williams <nico@cryptonector.com> wrote:
> > OAuth comes with batteries not included, unlike Kerberos.
>
> Yes. :/

It's very sad and annoying.  Mangement wants off Kerberos, but the
amount of work to do for that is enormous.  I'm sure the folks at
Microsoft have it much worse because they really need a GSS-API (SSPI)
mechanism that can do key exchange _and_ authentication.

> > 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.

But I want it _now_ not eventually :)

(And... I don't have time to contribute this, plus I've tried to
contribute to PG before and got my patches into two commitfests, but the
amount of energy needed to contribute to PG is too high.  My
contribution was for `ALWAYS DEFERRED` for constraints so that users
cannot make constraints `IMMEDIATE` that were intended to be deferred.)

> > I'd like it to be something more environmental, like the `TG_*` things
> > [...]
>
> As long as you're just looking for the "single" principal that the
> database considers to represent the user, we have system_user now.

TIL.  Since I map to ROLEs using pg_ident, I hadn't needed to know this.
But I can see this being very useful.

> 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.

Sure.  For client certs we _really_ need SANs.  Almost every app that
supports client certs only supports using the CN from the cert's name --
this is terrible.

This gets me right back to wanting discrete environmental elements for
each part of the client's credential(s):

 - jwt_claim_<claim> (value of <claim> in JWT)

 - gss_mechanism (string? containing OID?)
 - gss_client_principal (string)

   (If MIT Kerberos and/or Heimdal were good at implementinff RFC 6680
    then we could have claim-like things [think Kerberos authorization
    data elements] here as well.)

 - client_cert (blob containing DER-encoding of cert)
 - client_anchor (name of root CA the client cert chained to)
 - client_cert_dn_<attribute> (value of that attribute of the DN [they can be multi-valued though!])
 - client_cert_rfc822Name (rfc822Name SAN [one can have more than one])
 - client_cert_PKINIT (PKINIT SAN [one can have more than one])
 - client_cert_UPN (UPN SAN [one can have more than one])
 - etc.

> > 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.

Authorization servers are external.  You don't need to wait for them.

> > I recommend open-coding it rather than relying on some third party
> > library.
>
> We are well-versed in that by now :)

Sad.  C is such a terrible language and ecosystem.

> > 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.

Please folks speak up for this!  :)

(This ties into the parallel thread on RESETing the session role.)

Nico
--