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

On Mon, Sep 26, 2022 at 6:39 PM Andrey Chudnovsky
<achudnovskij@gmail.com> wrote:
> For the providing token directly, that would be primarily used for
> scenarios where the same party controls both the server and the client
> side wrapper.
> I.e. The client knows how to get a token for a particular principal
> and doesn't need any additional information other than human readable
> messages.
> Please clarify the scenarios where you see this falling apart.

The most concrete example I can see is with the OAUTHBEARER error
response. If you want to eventually handle differing scopes per role,
or different error statuses (which the proof-of-concept currently
hardcodes as `invalid_token`), then the client can't assume it knows
what the server is going to say there. I think that's true even if you
control both sides and are hardcoding the provider.

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 can provide an example in the cloud world. We (Azure) as well as
> other providers offer ways to obtain OAUTH tokens for
> Service-to-Service communication at IAAS / PAAS level.
> on Azure "Managed Identity" feature integrated in Compute VM allows a
> client to make a local http call to get a token. VM itself manages the
> certificate livecycle, as well as implements the corresponding OAUTH
> flow.
> This capability is used by both our 1st party PAAS offerings, as well
> as 3rd party services deploying on VMs or managed K8S clusters.
> Here, the client doesn't need libpq assistance in obtaining the token.

Cool. To me that's the strongest argument yet for directly providing
tokens to libpq.

> My optimistic plan here would be to implement several core OAUTH flows
> in libpq core which would be generic enough to support major
> enterprise OAUTH providers:
> 1. Client Credentials flow (Client_id + Client_secret) for backend applications.
> 2. Authorization Code Flow with PKCE and/or Device code flow for GUI
> applications.

As long as it's clear to DBAs when to use which flow (because existing
documentation for that is hit-and-miss), I think it's reasonable to
eventually support multiple flows. Personally my preference would be
to start with one or two core flows, and expand outward once we're
sure that we do those perfectly. Otherwise the explosion of knobs and
buttons might be overwhelming, both to users and devs.

Related to the question of flows is the client implementation library.
I've mentioned that I don't think iddawc is production-ready. As far
as I'm aware, there is only one certified OpenID relying party written
in C, and that's... an Apache server plugin. That leaves us either
choosing an untested library, scouring the web for a "tested" library
(and hoping we're right in our assessment), or implementing our own
(which is going to tamp down enthusiasm for supporting many flows,
though that has its own set of benefits). If you know of any reliable
implementations with a C API, please let me know.

> (2.) above would require a protocol between libpq and upstream clients
> to exchange several messages.
> Your patch includes a way for libpq to deliver to the client a message
> about the next authentication steps, so planned to build on top of
> that.

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.

> A little about scenarios, we look at.
> What we're trying to achieve here is an easy integration path for
> multiple players in the ecosystem:
> - Managed PaaS Postgres providers (both us and multi-cloud solutions)
> - SaaS providers deploying postgres on IaaS/PaaS providers' clouds
> - Tools - pg_admin, psql and other ones.
> - BI, ETL, Federation and other scenarios where postgres is used as
> the data source.
>
> If we can offer a provider agnostic solution for Backend <=> libpq <=>
> Upstreal client path, we can have all players above build support for
> OAUTH credentials, managed by the cloud provider of their choice.

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.

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.

> For us, that would mean:
> - Better administrator experience with pg_admin / psql handling of the
> AAD (Azure Active Directory) authentication flows.
> - Path for integration solutions using Postgres to build AAD
> authentication in their management experience.
> - Ability to use AAD identity provider for any Postgres deployments
> other than our 1st party PaaS offering.
> - Ability to offer github as the identity provider for PaaS Postgres offering.

GitHub is unfortunately a bit tricky, unless they've started
supporting OpenID recently?

> Other players in the ecosystem above would be able to get the same benefits.
>
> Does that make sense and possible without provider specific libpq plugin?

If the players involved implement the flows and follow the specs, yes.
That's a big "if", unfortunately. I think GitHub and Google are two
major players who are currently doing things their own way.

> I just referred to the ability to compile libpq without extra
> dependencies to save some kilobytes.
> Not sure if OAUTH is widely used in those cases. It involves overhead
> anyway, and requires the device to talk to an additional party (OAUTH
> provider).
> Likely Cert authentication is easier.
> If needed, it can get libpq with full OAUTH support and use a client
> code. But I didn't think about this scenario.

Makes sense. Thanks!

--Jacob