Re: [oauth] SASL mechanisms

On Fri, Nov 21, 2025 at 9:24 PM Nico Williams <nico(at)cryptonector(dot)com> wrote:
> I've not looked in detail yet, but I got the impression that the user
> has to fetch the token on their own and provide it to the PG client --
> if so that is monumentally unfriendly by comparison to, e.g., Kerberos.

You provide a client ID and libpq runs a Device Authorization flow.
It's not machine-friendly (you need custom code for that) and our lack
of token caching in PG18 is certainly not user-friendly, but we don't
completely throw you to the wolves. No one is copy-pasting tokens
around.

> As for mTLS, I'd love to use it, but I need to be able to support PKINIT
> SANs and/or rfc822Name SANs and/or UPN SANs. Apps that support client
> certificates invariably do so very poorly. Ideally there would be
> set_config-like (but not settable in SQL w/o privs) parameters with all
> the details of:
>
> - the client certificate (if used)
> - all the claims of the OAuth token (if used)

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

> - the Kerberos principal name (if used)

That should be in pg_stat_gssapi.

> and ideally then I could use something SECURITY DEFINER functions to SET
> SESSION ROLE to assume the desired role (having first "logged in" to a
> role with no grants). This would let me manage pg_hba/pg_ident mappings
> how I need to rather than how PG offers.

I mean, nothing here _looks_ impossible at first read, with PG18. Easy
for me to say that if I don't have to write the code, I guess. What
you describe isn't the out-of-the-box feature set, but I think it's
possible.

> Basically I have a setup where we have credentials for authenticating to
> an STS to get a token with the desired audience. I need the client to
> do that: make a token request to the STS. The credential can be what I
> like to call a "token granting token", or a Kerberos credential (using
> Negotiate), or a KSAT, or a GSAT. I'm willing to provide a library to
> fetch the token (I have one that implements all those options), but psql
> (and libpq) has to support using it.

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

That said, OAUTHBEARER doesn't give you either a resource indicator
(i.e. RFC 8707) or an audience ID, so implementing Token Exchange is
maybe not very straightforward yet. At least not without out-of-band
knowledge. But I'm not well-versed in RFC 8693, so I may be clueless
there.

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?

> All I need
> is for the server to map a `sub` claim (or some other claim I specify in
> a config file) to a ROLE, pg_hba.conf/pg_ident.conf style (or better),

A validator can do that today [1]. Hopefully "better," since a
validator may optionally map token claims to a role with custom logic.
But you have to write C for that, or find a reputable validator that
does it for you. We don't have a validator implementation in core.

Note that the validator documentation uses "authorization" to refer to
the ident mapping itself, not SQL-level GRANTs. In other words, "is
this user identity authorized to assume this role?"

> Fine-grained access control via OAuth is a) not really a practical thing
> in general, b) decidedly not practical for a high-performance RDBMS. I
> recommend not bothering to try.

We're in violent agreement. :D That's basically what I was trying to
say with my email, but I clearly failed.

Thanks,
--Jacob

[1] https://www.postgresql.org/docs/18/oauth-validator-design.html#OAUTH-VALIDATOR-DESIGN-USERMAP-DELEGATION