Re: [PoC] Federated Authn/z with OAUTHBEARER - Mailing list pgsql-hackers

Jacob Champion <jacob.champion@enterprisedb.com> wrote:

> On Mon, Sep 30, 2024 at 6:38 AM Antonin Houska <ah@cybertec.at> wrote:
> >
> > Are you sure you can legitimately acquire the bearer token containing my email
> > address?
>
> Yes. In general that's how OpenID-based "Sign in with <Service>"
> works. All those third-party services are running around with tokens
> that identify you, but unless they've asked for more abilities and
> you've granted them the associated scopes, identifying you is all they
> can do.
>
> > I think the email address returned by the /userinfo endpoint is one
> > of the standard claims [1]. Thus by returning the particular value of "email"
> > from the endpoint the identity provider asserts that the token owner does have
> > this address.
>
> We agree that /userinfo gives authentication claims for the end user.
> It's just insufficient for our use case.
>
> For example, there are enterprise applications out there that will ask
> for read access to your Google Calendar. If you're willing to grant
> that, then you probably won't mind if those applications also know
> your email address, but you probably do mind if they're suddenly able
> to access your production databases just because you gave them your
> email.
>
> Put another way: if you log into Postgres using OAuth, and your
> provider doesn't show you a big message saying "this application is
> about to access *your* prod database using *your* identity; do you
> want to allow that?", then your DBA has deployed a really dangerous
> configuration. That's a critical protection feature you get from your
> OAuth provider. Otherwise, what's stopping somebody else from setting
> up their own malicious service to farm access tokens? All they'd have
> to do is ask for your email.

Perhaps I understand now. I use getmail [2] to retrieve email messages from my
Google account. What made me confused is that the getmail application,
although installed on my workstation (and thus the bearer token it eventually
gets contains my email address), it's "someone else" (in particular the
"Relying Party") from the perspective of the OpenID protocol. And the same
applies to "psql" in the context of your patch.

Thus, in addition to the email, we'd need special claims which authorize the
RPs to access the database and only the database. Does this sound correct?

> > (I'm learning this stuff on-the-fly, so there might be something naive in my
> > comments.)
>
> No worries! Please keep the questions coming; this OAuth architecture
> is unintuitive, and I need to be able to defend it.

I'd like to play with the code a bit and provide some review before or during
the next CF. That will probably generate some more questions.

>
> [1] https://postgr.es/m/CACrwV54_euYe%2Bv7bcLrxnje-JuM%3DKRX5azOcmmrXJ5qrffVZfg%40mail.gmail.com

[2] https://github.com/getmail6/getmail6/

--
Antonin Houska
Web: https://www.cybertec-postgresql.com