On Mon, Nov 24, 2025 at 10:54 AM Nico Williams <[email protected]> wrote: > OAuth comes with batteries not included, unlike Kerberos.
Yes. :/ > > 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. 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. > > 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. As long as you're just looking for the "single" principal that the database considers to represent the user, we have system_user now. 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. authn_id itself (which is the internal implementation of system_user) could be viewed as the base of a more general claims system, I suppose. > 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. This is what the validator API lets you do today. It's just not done in SQL (unless of course you write a validator that lets you write SQL). > > libpq will let you plug in your own code. psql doesn't (yet). > > :( I'm trying to take a small step towards that in PG19, if you don't mind overriding link paths. Generic client-side plugins are probably too ambitious to turn around in a couple of months; I need a solid proposal for that and I don't have one yet. (I don't want this to be a CVE factory. There are a bunch of security conventions around the server modules that users have gotten used to, but if I inject those conventions into libpq they will affect a bunch of people who have never had to think about this before.) > 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. > > 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. We are well-versed in that by now :) > 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. --Jacob
