Re: [oauth] SASL mechanisms

On Fri, Nov 21, 2025 at 03:46:12PM -0800, Jacob Champion wrote:
> On Fri, Nov 21, 2025 at 3:15 PM Nico Williams <nico(at)cryptonector(dot)com> wrote:
> > For apps like PG I'm much more interested in real OAuth support. But
> > that's because I use PG in a corporate environment where we use
> > Kerberos, PKIX, and OAuth for authentication.
>
> Let us know what you think of PG18's OAuth support. We don't have
> token binding (whether to the sender or to the channel), but I think
> I'd rather put support behind something like an OAUTHDPOP-PLUS than
> add bindings to OAUTHBEARER. (Though I still can't figure out whether
> mTLS-constrained tokens are dead or not.)

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.

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)
- the Kerberos principal name (if used)
- ...

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.

Heck, one could see using SECURITY DEFINER functions to implement many
client authentication mechanisms (possibly ussing pggggg-crypto, or
other extensions) and do SET SESSION ROLE to complete successful logins.

> > In particular I want the _client_ to be configurable to be smart enough
> > as to how to fetch the darned OAuth rock the server wants.
>
> libpq can be told to use a custom flow. psql, though, not yet... Only
> the device flow for the utilities at the moment.

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.

> > I'm much
> > more interested in OAuth for authentication than I am in OAuth for
> > authorization -- GRANTs and RLS (and/or VIEWs that JOIN authz tables)
> > are plenty good enough for authz in PG.
>
> I think there might eventually be some interest in the latter, based
> on some conversations I've had in the past few months, but I'm not
> planning to work on that any time soon. (I think users would expect
> central authz changes to take effect immediately, which is not going
> to happen.)

What do you mean? GRANTs and RLS (and ...) all work today. 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),
just as with Kerberos. Yes, subject identifying claims are optional,
but for corporate network use it's all I need and all I want.

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.

Nico
--