On Fri, Nov 21, 2025 at 03:46:12PM -0800, Jacob Champion wrote: > On Fri, Nov 21, 2025 at 3:15 PM Nico Williams <[email protected]> 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 --
