Re: [PoC] Federated Authn/z with OAUTHBEARER

That being said, the Diagram 2 would look like this with our proposal:
+----------------------+ +----------+
| +-------+ | Postgres |
| PQconnect ->| | |
|
| | | |
+-----------+
| | | -------------- Empty Token ------------> | >
| |
| | libpq | <----- Error(w\ Root URL + Audience ) -- | <
| Pre-Auth |
| +------+ | |
| Hook |
| +- < | Hook | | |
+-----------+
| | +------+ | | |
| v | | |
|
| [get token]| | |
|
| | | | |
|
| + | | |
+-----------+
| PQconnect > | | -------------- Access Token -----------> | >
| Validator |
| | | <---- Authorization Success/Failure ---- | <
| Hook |
| | | |
+-----------+
| +-------+ |
| +----------------------+
+----------+

With the application taking care of all Token acquisition logic. While
the server-side hook is participating in the pre-authentication reply.

That is definitely a required scenario for the long term and the
easiest to implement in the client core.
And if we can do at least that flow in PG16 it will be a strong
foundation to provide more support for specific grants in libpq going
forward.

Does the diagram above look good to you? We can then start cleaning up
the patch to get that in first.

Thanks!
Andrey.

On Wed, Dec 7, 2022 at 3:22 PM Andrey Chudnovsky <achudnovskij(at)gmail(dot)com> wrote:
>
> > 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?
>
> Ok. It may have left there from intermediate iterations. We did
> consider making extension drive the flow for specific grant_type, but
> decided against that idea. For the same reason you point to.
> Is it correct that your main concern about use of grant_type was that
> it's propagated to the server? Then yes, we will remove sending it to
> the server.
>
> > 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.
>
> Yes, based on our analysis OIDC spec is detailed enough, that
> providers implementing that one, can be supported with generic code in
> libpq / client.
> Github specifically won't fit there though. Microsoft Azure AD,
> Google, Okta (including Auth0) will.
> Theoretically discovery documents can be returned from the extension
> (server-side) which is provider specific. Though we didn't plan to
> prioritize that.
>
> > 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?
>
> Do you refer to passing grant_type to the server? Which we will get
> rid of in the next iteration. Or other incompatible changes as well?
>
> > 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?
>
> The hook proposed on the 2nd diagram effectively delegates all Oauth
> flows implementations to the client.
> We propose libpq takes care of pulling OpenId discovery and coordination.
> Which is effectively Diagram 1 + more flows + server hook providing
> root url/audience.
>
> Created the diagrams with all components for 3 flows:
> 1. Authorization code grant (Clients with Browser access):
> +----------------------+ +----------+
> | +-------+ |
> |
> | PQconnect | | |
> |
> | [auth_code] | | |
> +-----------+
> | -> | | -------------- Empty Token ------------> | >
> | |
> | | libpq | <----- Error(w\ Root URL + Audience ) -- | <
> | Pre-Auth |
> | | | |
> | Hook |
> | | | |
> +-----------+
> | | | +--------------+ | |
> | | | -------[GET]---------> | OIDC | | Postgres |
> | +------+ | <--Provider Metadata-- | Discovery | | |
> | +- < | Hook | < | +--------------+ |
> |
> | | +------+ | |
> |
> | v | | |
> |
> | [get auth | | |
> |
> | code] | | |
> |
> |<user action>| | |
> |
> | | | | |
> |
> | + | | |
> |
> | PQconnect > | +--------+ +--------------+ |
> |
> | | | iddawc | <-- [ Auth code ]-> | Issuer/ | | |
> | | | | <-- Access Token -- | Authz Server | | |
> | | +--------+ +--------------+ | |
> | | | |
> +-----------+
> | | | -------------- Access Token -----------> | >
> | Validator |
> | | | <---- Authorization Success/Failure ---- | <
> | Hook |
> | +------+ | |
> +-----------+
> | +-< | Hook | | |
> |
> | v +------+ | |
> |
> |[store +-------+ |
> |
> | refresh_token] +----------+
> +----------------------+
>
> 2. Device code grant
> +----------------------+ +----------+
> | +-------+ |
> |
> | PQconnect | | |
> |
> | [auth_code] | | |
> +-----------+
> | -> | | -------------- Empty Token ------------> | >
> | |
> | | libpq | <----- Error(w\ Root URL + Audience ) -- | <
> | Pre-Auth |
> | | | |
> | Hook |
> | | | |
> +-----------+
> | | | +--------------+ | |
> | | | -------[GET]---------> | OIDC | | Postgres |
> | +------+ | <--Provider Metadata-- | Discovery | | |
> | +- < | Hook | < | +--------------+ |
> |
> | | +------+ | |
> |
> | v | | |
> |
> | [device | +---------+ +--------------+ |
> |
> | code] | | iddawc | | Issuer/ | |
> |
> |<user action>| | | --[ Device code ]-> | Authz Server | |
> |
> | | |<polling>| --[ Device code ]-> | | |
> |
> | | | | --[ Device code ]-> | | |
> |
> | | | | | | | |
> | | | | <-- Access Token -- | | | |
> | | +---------+ +--------------+ | |
> | | | |
> +-----------+
> | | | -------------- Access Token -----------> | >
> | Validator |
> | | | <---- Authorization Success/Failure ---- | <
> | Hook |
> | +------+ | |
> +-----------+
> | +-< | Hook | | |
> |
> | v +------+ | |
> |
> |[store +-------+ |
> |
> | refresh_token] +----------+
> +----------------------+
>
> 3. Non-interactive flows (Client Secret / Refresh_Token)
> +----------------------+ +----------+
> | +-------+ |
> |
> | PQconnect | | |
> |
> | [grant_type]| | | |
> | -> | | |
> +-----------+
> | | | -------------- Empty Token ------------> | >
> | |
> | | libpq | <----- Error(w\ Root URL + Audience ) -- | <
> | Pre-Auth |
> | | | |
> | Hook |
> | | | |
> +-----------+
> | | | +--------------+ | |
> | | | -------[GET]---------> | OIDC | | Postgres |
> | | | <--Provider Metadata-- | Discovery | | |
> | | | +--------------+ |
> |
> | | | |
> |
> | | +--------+ +--------------+ |
> |
> | | | iddawc | <-- [ Secret ]----> | Issuer/ | | |
> | | | | <-- Access Token -- | Authz Server | | |
> | | +--------+ +--------------+ | |
> | | | |
> +-----------+
> | | | -------------- Access Token -----------> | >
> | Validator |
> | | | <---- Authorization Success/Failure ---- | <
> | Hook |
> | | | |
> +-----------+
> | +-------+ +----------+
> +----------------------+
>
> I think what was the most confusing in our latest patch is that
> flow_type was passed to the server.
> We are not proposing this going forward.
>
> > (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.)
>
> My understanding is that metadata in the tokens is provider specific,
> so server side hook would be the right place to handle that.
> Plus I can envision for some providers it can make sense to make a
> remote call to pull some information.
>
> The way we implement Azure AD auth today in PAAS PostgreSQL offering:
> - Server administrator uses special extension functions to create
> Azure AD enabled PostgreSQL roles.
> - PostgreSQL extension maps Roles to unique identity Ids (UID) in the Directory.
> - Connection flow: If the token is valid and Role => UID mapping
> matches, we authenticate as the Role.
> - Then its native PostgreSQL role based access control takes care of privileges.
>
> This is the same for both User- and System-to-system authorization.
> Though I assume different providers may treat user- and system-
> identities differently. So their extension would handle that.
>
> Thanks!
> Andrey.
>
> On Wed, Dec 7, 2022 at 11:06 AM Jacob Champion <jchampion(at)timescale(dot)com> wrote:
> >
> > On Mon, Dec 5, 2022 at 4:15 PM Andrey Chudnovsky <achudnovskij(at)gmail(dot)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