Re: [oauth] SASL mechanisms

On Mon, Nov 24, 2025 at 09:33:01AM -0800, Jacob Champion wrote:
> 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.

Speaking of which, I should probably write up the file-cache I built at
$WORK for tokens. My scheme is much simpler than the MIT Kerberos /
Heimdal ticket "ccache" schemes.

OAuth comes with batteries not included, unlike Kerberos. We've had to
write a bunch of token-fetching libraries for non-browser apps. And
token caching? Pfft, no one ever thinks about that till it's a problem.
These are things that Kerberos had more than 30 years ago. It's a bit
infuriating. At least it's easy to open-code fetching (and caching)
tokens.

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

(PostgREST uses `set_config()`, but also it doesn't allow arbitrary SQL
to be sent, so your SQL tiggers and views can trust those
`set_config()`s. But that won't do for PG because no authorization is
needed to call `set_config()`.)

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

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

These are things that are needed to make using OAuth painless.

(And Kerberos, and PKIX. Ideally things like `pg_hba`/`pg_ident` could
be SQL-coded rather than having to rely on built-in functionaly only.
And if it was trivial to extract "authorization data" from kerberos (it
does support that, which is the same sort of thing as "claims" in OAuth
land), then we could treat all these things in a fairly unified way.

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.

> > [...]. I need the client to
> > do that: make a token request to the STS. [...]
>
> 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.

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

> 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. Reasons: portability, control, etc. Open-coding this is
pretty straightforward (I've built one in C that does async I/O in a
CPS-style, letting the caller use their own event loop, but it's not
open source, and it doesn't do WIN32; point is it's possible and not too
hard).

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

Hmmm, I don't mind writing one in C, but there is a better way based on
how PostgREST does it (see above).

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

> 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?"

Got it.

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

Gotcha, phew! :)

Thanks for the details,

Nico
--