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>
Subject: Re: [PoC] Federated Authn/z with OAUTHBEARER
In-reply-to: <CAOYmi+=MFyrjDps-YNtem3=Gr3mUsgZ49m7bfMCgr1TDjHL58g@mail.gmail.com>
References: <d1b467a78e0e36ed85a09adf979d04cf124a9d4b.camel@vmware.com>
 <e44faf68-6538-41b9-84d2-b259294e5c28@eisentraut.org> <CAOYmi+myshCL_yaWQiu54Kj5in93D5nPyw7yXj2jZnDKi73SHQ@mail.gmail.com>
 <f9e49a45-ed19-412f-8bd4-f956e7a35e66@eisentraut.org> <CAOYmi+nHJgPAYHRUhvh=9rfqnkphRHObKjbpjYyZWi9jkNic3w@mail.gmail.com>
 <fa1c769b-42aa-46c2-ba0c-aa21f3eeb0a1@eisentraut.org> <CAOYmi+=pg=W5L1h=3MEP_EB24jaBu2FyATrLXqQHGe7cpuvwyg@mail.gmail.com>
 <3603e9bb-9a69-4761-869b-f5b1f204b840@eisentraut.org> <e4b1456c-318d-472c-8713-dcfac1ad6a29@eisentraut.org>
 <CAOYmi+n_Pu5X=zC-MhAmW_zV=rXH8Zzz1gXHdjbLf7YeGNDWBw@mail.gmail.com>
 <c7046d57-978d-4ad8-b130-070f38909ea1@eisentraut.org> <CAOYmi+n9qa3+fM0u5+=9vvr+TirM54Y8NMDUpPBn-8xLGHcFgw@mail.gmail.com>
 <CAOYmi+nwexxyLWxFFV1_owEY4DOOQL2dM31L98pNvg_8M7zAig@mail.gmail.com>
 <a12b74b7-9ef9-4720-8f73-5922b9122316@eisentraut.org> <CAOYmi+k6oSACjoFvCUPipeLJBOrH=OgaeY9BeN1Mpsjna8taZg@mail.gmail.com>
 <390f9699-6275-4e1e-a600-11452bee0576@eisentraut.org> <67b7e52e-0d6e-4eb5-9318-fdcfa4e7ea51@eisentraut.org>
 <20A6A78C-D5B1-4BA3-8CD0-99AF18A04B48@yesql.se> <CAOYmi+m9uPWsqz_ZrbMPos7YDrwZqu7WgjiGG3q54oUa8XuX_g@mail.gmail.com>
 <4812.1727459899@antos> <CAOYmi+=MFyrjDps-YNtem3=Gr3mUsgZ49m7bfMCgr1TDjHL58g@mail.gmail.com>
Comments: In-reply-to Jacob Champion <jacob.champion@enterprisedb.com>
   message dated "Fri, 27 Sep 2024 13:45:45 -0700."
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Date: Mon, 30 Sep 2024 15:38:41 +0200
Message-ID: <4503.1727703521@antos>
Archived-At: <https://www.postgresql.org/message-id/4503.1727703521%40antos>
Precedence: bulk

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

> On Fri, Sep 27, 2024 at 10:58=E2=80=AFAM Antonin Houska <ah@cybertec.at> =
wrote:
> > Have you considered sending the token for validation to the server, lik=
e this
> >
> > curl -X GET "https://www.googleapis.com/oauth2/v3/userinfo" -H "Authori=
zation: Bearer $TOKEN"
>=20
> 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.
>=20
> 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.

This statement alone sounds as if you missed *authentication*, but you seem=
 to
admit above that the /userinfo endpoint provides it ("tells you who the end
user is"). I agree that it does. My understanding is that this endpoint, as
well as the concept of "claims" and "scopes", is introduced by OpenID, which
is an *authentication* framework, although it's built on top of OAuth.

Regarding *authorization*, I agree that the bearer token may not contain
enough information to determine whether the owner of the token is allowed to
access the database. However, I consider database a special kind of
"application", which can handle authorization on its own. In this case, the
authorization can be controlled by (not) assigning the user the LOGIN
attribute, as well as by (not) granting it privileges on particular database
objects. In short, I think that *authentication* is all we need.

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

Are you sure you can legitimately acquire the bearer token containing my em=
ail
address? I think the email address returned by the /userinfo endpoint is one
of the standard claims [1]. Thus by returning the particular value of "emai=
l"
from the endpoint the identity provider asserts that the token owner does h=
ave
this address. (And that, if "email_verified" claim is "true", it spent some
effort to verify that the email address is controlled by that user.)

> Now, the token introspection endpoint I mentioned upthread

Can you please point me to the particular message?

> 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 ma=
kes
> 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.
>=20
> 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.)

Another question, assuming the token verification is resolved somehow:
wouldn't it be sufficient for the initial implementation if the client could
pass the bearer token to libpq in the connection string?

Obviously, one use case is than an application / web server which needs the
token to authenticate the user could eventually pass the token to the datab=
ase
server. Thus, if users could authenticate to the database using their
individual ids, it would no longer be necessary to store a separate userid /
password for the application in a configuration file.

Also, if libpq accepted the bearer token via the connection string, it would
be possible to implement the authorization as a separate front-end applicat=
ion
(e.g. pg_oauth_login) rather than adding more complexity to libpq itself.

(I'm learning this stuff on-the-fly, so there might be something naive in my
comments.)

[1] https://openid.net/specs/openid-connect-core-1_0.html#StandardClaims

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