Horton

Re: [PoC] Federated Authn/z with OAUTHBEARER

Jacob Champion <jacob.champion@enterprisedb.com>

View thread
From: Jacob Champion <jacob.champion@enterprisedb.com>
To: Antonin Houska <ah@cybertec.at>
Cc: Daniel Gustafsson <daniel@yesql.se>, Peter Eisentraut <peter@eisentraut.org>, PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2024-09-27T20:45:45Z
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

On Fri, Sep 27, 2024 at 10:58 AM Antonin Houska <ah@cybertec.at> wrote:
> Have you considered sending the token for validation to the server, like this
>
> curl -X GET "https://www.googleapis.com/oauth2/v3/userinfo" -H "Authorization: Bearer $TOKEN"

In short, no, but I'm glad you asked. I think it's going to be a
common request, and I need to get better at explaining why it's not
safe, so we can document it clearly. Or else someone can point out
that I'm misunderstanding, which honestly would make all this much
easier and less complicated. I would love to be able to do it that
way.

We cannot, for the same reason libpq must send the server an access
token instead of an ID token. The /userinfo endpoint tells you who the
end user is, but it doesn't tell you whether the Bearer is actually
allowed to access the database. That difference is critical: it's
entirely possible for an end user to be authorized to access the
database, *and yet* the Bearer token may not actually carry that
authorization on their behalf. (In fact, the user may have actively
refused to give the Bearer that permission.) That's why people are so
pedantic about saying that OAuth is an authorization framework and not
an authentication framework.

To illustrate, think about all the third-party web services out there
that ask you to Sign In with Google. They ask Google for permission to
access your personal ID, and Google asks you if you're okay with that,
and you either allow or deny it. Now imagine that I ran one of those
services, and I decided to become evil. I could take my legitimately
acquired Bearer token -- which should only give me permission to query
your Google ID -- and send it to a Postgres database you're authorized
to access.

The server is supposed to introspect it, say, "hey, this token doesn't
give the bearer access to the database at all," and shut everything
down. For extra credit, the server could notice that the client ID
tied to the access token isn't even one that it recognizes! But if all
the server does is ask Google, "what's the email address associated
with this token's end user?", then it's about to make some very bad
decisions. The email address it gets back doesn't belong to Jacob the
Evil Bearer; it belongs to you.

Now, the token introspection endpoint I mentioned upthread should give
us the required information (scopes, etc.). But Google doesn't
implement that one. In fact they don't seem to have implemented custom
scopes at all in the years since I started work on this feature, which
makes me think that people are probably not going to be able to safely
log into Postgres using Google tokens. Hopefully there's some feature
buried somewhere that I haven't seen.

Let me know if that makes sense. (And again: I'd love to be proven
wrong. It would improve the reach of the feature considerably if I
am.)

Thanks,
--Jacob