Re: [PoC] Federated Authn/z with OAUTHBEARER

Hi,


We validated on  libpq handling OAuth natively with different flows
with different OIDC certified providers.

Flows: Device Code, Client Credentials and Refresh Token.
Providers: Microsoft, Google and Okta.
Also validated with OAuth provider Github.

We propose using OpenID Connect (OIDC) as the protocol, instead of
OAuth, as it is:
- Discovery mechanism to bridge the differences and provide metadata.
- Stricter protocol and certification process to reliably identify
which providers can be supported.
- OIDC is designed for authentication, while the main purpose of OAUTH is to
authorize applications on behalf of the user.

Github is not OIDC certified, so won’t be supported with this proposal.
However, it may be supported in the future through the ability for the
extension to provide custom discovery document content.

OpenID configuration has a well-known discovery mechanism
for the provider configuration URI which is
defined in OpenID Connect. It allows libpq to fetch
metadata about provider (i.e endpoints, supported grants, response types, etc).

In the attached patch (based on V2 patch in the thread and does not
contain Samay's changes):
- Provider can configure issuer url and scope through the options hook.)
- Server passes on an open discovery url and scope to libpq.
- Libpq handles OAuth flow based on the flow_type sent in the
connection string [1].
- Added callbacks to notify a structure to client tools if OAuth flow
requires user interaction.
- Pg backend uses hooks to validate bearer token.

Note that authentication code flow with PKCE for GUI clients is not
implemented yet.

Proposed next steps:
- Broaden discussion to reach agreement on the approach.
- Implement libpq changes without iddawc
- Prototype GUI flow with pgAdmin

Thanks,
Mahendrakar.

[1]:
connection string for refresh token flow:
./psql -U <user> -d 'dbname=postgres oauth_client_id=<client_id>
oauth_flow_type=<flowtype>  oauth_refresh_token=<refresh token>'

On Mon, 3 Oct 2022 at 23:34, Andrey Chudnovsky <achudnovskij@gmail.com> wrote:
>
> > I think we can probably prototype a callback hook for approach (1)
> > pretty quickly. (2) is a lot more work and investigation, but it's
> > work that I'm interested in doing (when I get the time). I think there
> > are other very good reasons to consider a third-party SASL library,
> > and some good lessons to be learned, even if the community decides not
> > to go down that road.
>
> Makes sense. We will work on (1.) and do some check if there are any
> blockers for a shared solution to support github and google.
>
> On Fri, Sep 30, 2022 at 1:45 PM Jacob Champion <jchampion@timescale.com> wrote:
> >
> > On Fri, Sep 30, 2022 at 7:47 AM Andrey Chudnovsky
> > <achudnovskij@gmail.com> wrote:
> > > > How should we communicate those pieces to a custom client when it's
> > > > passing a token directly? The easiest way I can see is for the custom
> > > > client to speak the OAUTHBEARER protocol directly (e.g. SASL plugin).
> > > > If you had to parse the libpq error message, I don't think that'd be
> > > > particularly maintainable.
> > >
> > > I agree that parsing the message is not a sustainable way.
> > > Could you provide more details on the SASL plugin approach you propose?
> > >
> > > Specifically, is this basically a set of extension hooks for the client side?
> > > With the need for the client to be compiled with the plugins based on
> > > the set of providers it needs.
> >
> > That's a good question. I can see two broad approaches, with maybe
> > some ability to combine them into a hybrid:
> >
> > 1. If there turns out to be serious interest in having libpq itself
> > handle OAuth natively (with all of the web-facing code that implies,
> > and all of the questions still left to answer), then we might be able
> > to provide a "token hook" in the same way that we currently provide a
> > passphrase hook for OpenSSL keys. By default, libpq would use its
> > internal machinery to take the provider details, navigate its builtin
> > flow, and return the Bearer token. If you wanted to override that
> > behavior as a client, you could replace the builtin flow with your
> > own, by registering a set of callbacks.
> >
> > 2. Alternatively, OAuth support could be provided via a mechanism
> > plugin for some third-party SASL library (GNU libgsasl, Cyrus
> > libsasl2). We could provide an OAuth plugin in contrib that handles
> > the default flow. Other providers could publish their alternative
> > plugins to completely replace the OAUTHBEARER mechanism handling.
> >
> > Approach (2) would make for some duplicated effort since every
> > provider has to write code to speak the OAUTHBEARER protocol. It might
> > simplify provider-specific distribution, since (at least for Cyrus) I
> > think you could build a single plugin that supports both the client
> > and server side. But it would be a lot easier to unknowingly (or
> > knowingly) break the spec, since you'd control both the client and
> > server sides. There would be less incentive to interoperate.
> >
> > Finally, we could potentially take pieces from both, by having an
> > official OAuth mechanism plugin that provides a client-side hook to
> > override the flow. I have no idea if the benefits would offset the
> > costs of a plugin-for-a-plugin style architecture. And providers would
> > still be free to ignore it and just provide a full mechanism plugin
> > anyway.
> >
> > > > Well... I don't quite understand why we'd go to the trouble of
> > > > providing a provider-agnostic communication solution only to have
> > > > everyone write their own provider-specific client support. Unless
> > > > you're saying Microsoft would provide an officially blessed plugin for
> > > > the *server* side only, and Google would provide one of their own, and
> > > > so on.
> > >
> > > Yes, via extensions. Identity providers can open source extensions to
> > > use their auth services outside of first party PaaS offerings.
> > > For 3rd party Postgres PaaS or on premise deployments.
> >
> > Sounds reasonable.
> >
> > > > The server side authorization is the only place where I think it makes
> > > > sense to specialize by default. libpq should remain agnostic, with the
> > > > understanding that we'll need to make hard decisions when a major
> > > > provider decides not to follow a spec.
> > >
> > > Completely agree with agnostic libpq. Though needs validation with
> > > several major providers to know if this is possible.
> >
> > Agreed.
> >
> > > > Specifically it delivers that message to an end user. If you want a
> > > > generic machine client to be able to use that, then we'll need to talk
> > > > about how.
> > >
> > > Yes, that's what needs to be decided.
> > > In both Device code and Authorization code scenarios, libpq and the
> > > client would need to exchange a couple of pieces of metadata.
> > > Plus, after success, the client should be able to access a refresh token for further use.
> > >
> > > Can we implement a generic protocol like for this between libpq and the clients?
> >
> > I think we can probably prototype a callback hook for approach (1)
> > pretty quickly. (2) is a lot more work and investigation, but it's
> > work that I'm interested in doing (when I get the time). I think there
> > are other very good reasons to consider a third-party SASL library,
> > and some good lessons to be learned, even if the community decides not
> > to go down that road.
> >
> > Thanks,
> > --Jacob