Horton

Re: [PoC] Federated Authn/z with OAUTHBEARER

Antonin Houska <ah@cybertec.at>

View thread
From: Antonin Houska <ah@cybertec.at>
To: Jacob Champion <jacob.champion@enterprisedb.com>
Cc: Daniel Gustafsson <daniel@yesql.se>, Peter Eisentraut <peter@eisentraut.org>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2024-10-08T10:46:49Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. meson: Fix install-quiet after clean

  2. oauth: Run Autoconf tests with correct compiler flags

  3. Link libpq with libdl if the platform needs that.

  4. Doc: correct spelling of meson switch.

  5. oauth: Correct SSL dependency for libpq-oauth.a

  6. oauth: Fix Autoconf build on macOS

  7. oauth: Move the builtin flow into a separate module

  8. Remove a stray "pgrminclude" annotation

  9. oauth: Simplify copy of PGoauthBearerRequest

  10. oauth: Improve validator docs on interruptibility

  11. oauth: Disallow synchronous DNS in libcurl

  12. oauth: Fix postcondition for set_timer on macOS

  13. oauth: Use IPv4-only issuer in oauth_validator tests

  14. Work around OAuth/EVFILT_TIMER quirk on NetBSD.

  15. oauth: Fix incorrect const markers in struct

  16. Add missing entry to oauth_validator test .gitignore

  17. cirrus: Temporarily fix libcurl link error

  18. Add support for OAUTHBEARER SASL mechanism

  19. libpq: Handle asynchronous actions during SASL

  20. require_auth: prepare for multiple SASL mechanisms

  21. Move PG_MAX_AUTH_TOKEN_LENGTH to libpq/auth.h

  22. Make SASL max message length configurable

  23. jsonapi: fully initialize dummy lexer

  24. common/jsonapi: support libpq as a client

  25. Remove fe_memutils from libpgcommon_shlib

  26. Revert ECPG's use of pnstrdup()

  27. Explicitly require password for SCRAM exchange

  28. Refactor SASL exchange to return tri-state status

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