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

First, My message from corp email wasn't displayed in the thread,
That is what Jacob replied to, let me post it here for context:

> We can support both passing the token from an upstream client and libpq implementing OAUTH2 protocol to obtain one.
>
> Libpq implementing OAUTHBEARER is needed for community/3rd party tools to have user-friendly authentication
experience:
>
> 1. For community client tools, like pg_admin, psql etc.
>   Example experience: pg_admin would be able to open a popup dialog to authenticate customers and keep refresh tokens
toavoid asking the user frequently.
 
> 2. For 3rd party connectors supporting generic OAUTH with any provider. Useful for datawiz clients, like Tableau or
ETLtools. Those can support both user and client OAUTH flows.
 
>
> Libpq passing toked directly from an upstream client is useful in other scenarios:
> 1. Enterprise clients, built with .Net / Java and using provider-specific authentication libraries, like MSAL for
AAD.Those can also support more advanced provider-specific token acquisition flows.
 
> 2. Resource-tight (like IoT) clients. Those can be compiled without the optional libpq flag not including the iddawc
orother dependency.
 

-----------------------------------------------------------------------------------------------------
On this:

> What I don't understand is how the OAUTHBEARER mechanism helps you in
> this case. You're short-circuiting the negotiation where the server
> tells the client what provider to use and what scopes to request, and
> instead you're saying "here's a secret string, just take it and
> validate it with magic."
>
> I realize the ability to pass an opaque token may be useful, but from
> the server's perspective, I don't see what differentiates it from the
> password auth method plus a custom authenticator plugin. Why pay for
> the additional complexity of OAUTHBEARER if you're not going to use
> it?

Yes, passing a token as a new auth method won't make much sense in
isolation. However:
1. Since OAUTHBEARER is supported in the ecosystem, passing a token as
a way to authenticate with OAUTHBEARER is more consistent (IMO), then
passing it as a password.
2. Validation on the backend side doesn't depend on whether the token
is obtained by libpq or transparently passed by the upstream client.
3. Single OAUTH auth method on the server side for both scenarios,
would allow both enterprise clients with their own Token acquisition
and community clients using libpq flows to connect as the same PG
users/roles.

On Wed, Sep 21, 2022 at 8:36 PM Jacob Champion <jchampion@timescale.com> wrote:
>
> On Wed, Sep 21, 2022 at 3:10 PM Andrey Chudnovskiy
> <Andrey.Chudnovskiy@microsoft.com> wrote:
> > We can support both passing the token from an upstream client and libpq implementing OAUTH2 protocol to obtaining
one.
>
> Right, I agree that we could potentially do both.
>
> > Libpq passing toked directly from an upstream client is useful in other scenarios:
> > 1. Enterprise clients, built with .Net / Java and using provider-specific authentication libraries, like MSAL for
AAD.Those can also support more advance provider-specific token acquisition flows.
 
> > 2. Resource-tight (like IoT) clients. Those can be compiled without optional libpq flag not including the iddawc or
otherdependency.
 
>
> What I don't understand is how the OAUTHBEARER mechanism helps you in
> this case. You're short-circuiting the negotiation where the server
> tells the client what provider to use and what scopes to request, and
> instead you're saying "here's a secret string, just take it and
> validate it with magic."
>
> I realize the ability to pass an opaque token may be useful, but from
> the server's perspective, I don't see what differentiates it from the
> password auth method plus a custom authenticator plugin. Why pay for
> the additional complexity of OAUTHBEARER if you're not going to use
> it?
>
> --Jacob
>
>
>
>