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+m9uPWsqz_ZrbMPos7YDrwZqu7WgjiGG3q54oUa8XuX_g@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>
Comments: In-reply-to Jacob Champion <jacob.champion@enterprisedb.com>
   message dated "Wed, 11 Sep 2024 15:54:18 -0700."
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <4811.1727459899.1@antos>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 27 Sep 2024 19:58:19 +0200
Message-ID: <4812.1727459899@antos>
Archived-At: <https://www.postgresql.org/message-id/4812.1727459899%40antos>
Precedence: bulk

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

> Peter asked me if there were plans to provide a "standard" validator
> module, say as part of contrib. The tricky thing is that Bearer
> validation is issuer-specific, and many providers give you an opaque
> token that you're not supposed to introspect at all.
> =

> We could use token introspection (RFC 7662) for online verification,
> but last I looked at it, no one had actually implemented those
> endpoints. For offline verification, I think the best we could do
> would be to provide a generic JWT Profile (RFC 9068) validator, but
> again I don't know if anyone is actually providing those token formats
> in practice. I'm inclined to push that out into the future.

Have you considered sending the token for validation to the server, like t=
his

curl -X GET "https://www.googleapis.com/oauth2/v3/userinfo" -H "Authorizat=
ion: Bearer $TOKEN"

and getting the userid (e.g. email address) from the response, as describe=
d in
[1]? ISTM that this is what pgadmin4 does - in paricular, see the
get_user_profile() function in web/pgadmin/authenticate/oauth2.py.

[1] https://www.oauth.com/oauth2-servers/signing-in-with-google/verifying-=
the-user-info/

-- =

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