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

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