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

On Mon, Dec 5, 2022 at 4:15 PM Andrey Chudnovsky <achudnovskij@gmail.com> wrote:
> I think we can focus on the roles and responsibilities of the components first.
> Details of the patch can be elaborated. Like "flow type code" is a
> mistake on our side, and we will use the term "grant_type" which is
> defined by OIDC spec. As well as details of usage of refresh_token.

(For the record, whether we call it "flow type" or "grant type"
doesn't address my concern.)

> Basically Yes. We propose an increase of the server side hook responsibility.
> From just validating the token, to also return the provider root URL
> and required audience. And possibly provide more metadata in the
> future.

I think it's okay to have the extension and HBA collaborate to provide
discovery information. Your proposal goes further than that, though,
and makes the server aware of the chosen client flow. That appears to
be an architectural violation: why does an OAuth resource server need
to know the client flow at all?

> Which is in our opinion aligned with SASL protocol, where the server
> side is responsible for telling the client auth requirements based on
> the requested role in the startup packet.

You've proposed an alternative SASL mechanism. There's nothing wrong
with that, per se, but I think it should be clear why we've chosen
something nonstandard.

> Our understanding is that in the original patch that information came
> purely from hba, and we propose extension being able to control that
> metadata.
> As we see extension as being owned by the identity provider, compared
> to HBA which is owned by the server administrator or cloud provider.

That seems reasonable, considering how tightly coupled the Issuer and
the token validation process are.

> 2. Server Owners / PAAS providers (On premise admins, Cloud providers,
> multi-cloud PAAS providers).
>    - Install extensions and configure HBA to allow clients to
> authenticate with the identity providers of their choice.

(For a future conversation: they need to set up authorization, too,
with custom scopes or some other magic. It's not enough to check who
the token belongs to; even if Postgres is just using the verified
email from OpenID as an authenticator, you have to also know that the
user authorized the token -- and therefore the client -- to access
Postgres on their behalf.)

> 3. Client Application Developers (Data Wis, integration tools,
> PgAdmin, monitoring tools, e.t.c.)
>    - Independent from specific Identity providers or server providers.
> Write one code for all identity providers.

Ideally, yes, but that only works if all identity providers implement
the same flows in compatible ways. We're already seeing instances
where that's not the case and we'll necessarily have to deal with that
up front.

>    - Rely on application deployment owners to configure which OIDC
> provider to use across client and server setups.
> 4. Application Deployment Owners (End customers setting up applications)
>    - The only actor actually aware of which identity provider to use.
> Configures the stack based on the Identity and PostgreSQL deployments
> they have.

(I have doubts that the roles will be as decoupled in practice as you
have described them, but I'd rather defer that for now.)

> The critical piece of the vision is (3.) above is applications
> agnostic of the identity providers. Those applications rely on
> properly configured servers and rich driver logic (libpq,
> com.postgresql, npgsql) to allow their application to popup auth
> windows or do service-to-service authentication with any provider. In
> our view that would significantly democratize the deployment of OAUTH
> authentication in the community.

That seems to be restating the goal of OAuth and OIDC. Can you explain
how the incompatible change allows you to accomplish this better than
standard implementations?

> In order to allow this separation, we propose:
> 1. HBA + Extension is the single source of truth of Provider root URL
> + Required Audience for each role. If some backfill for missing OIDC
> discovery is needed, the provider-specific extension would be
> providing it.
> 2. Client Application knows which grant_type to use in which scenario.
> But can be coded without knowledge of a specific provider. So can't
> provide discovery details.
> 3. Driver (libpq, others) - coordinate the authentication flow based
> on client grant_type and identity provider metadata to allow client
> applications to use any flow with any provider in a unified way.
>
> Yes, this would require a little more complicated flow between
> components than in your original patch.

Why? I claim that standard OAUTHBEARER can handle all of that. What
does your proposed architecture (the third diagram) enable that my
proposed hook (the second diagram) doesn't?

> And yes, more complexity comes
> with more opportunity to make bugs.
> However, I see PG Server and Libpq as the places which can have more
> complexity. For the purpose of making work for the community
> participants easier and simplify adoption.
>
> Does this make sense to you?

Some of it, but it hasn't really addressed the questions from my last mail.

Thanks,
--Jacob