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

>>> Libpq passing toked directly from an upstream client is useful in other scenarios:
>>> 1. Enterprise clients, built with .Net / Java and using provider-specific authentication libraries, like MSAL for
AAD.Those can also support more advanced provider-specific token acquisition flows.
 

> I can see that providing a token directly would help you work around
> limitations in libpq's "standard" OAuth flows, whether we use iddawc or
> not. And it's cheap in terms of implementation. But I have a feeling it
> would fall apart rapidly with error cases, where the server is giving
> libpq information via the OAUTHBEARER mechanism, but libpq can only
> communicate to your wrapper through human-readable error messages on stderr.

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.

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.

> This seems like clear motivation for client-side SASL plugins (which
> were also discussed on Samay's proposal thread). That's a lot more
> expensive to implement in libpq, but if it were hypothetically
> available, wouldn't you rather your provider-specific code be able to
> speak OAUTHBEARER directly with the server?

I generally agree that pluggable auth layers in libpq could be
beneficial. However, as you pointed out in Samay's thread, that would
require a new distribution model for libpq / clients to optionally
include provider-specific logic.

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.

(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.

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.

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.

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?

-------------------------
On resource constrained scenarios.
> I want to dig into this much more; resource-constrained systems are near
> and dear to me. I can see two cases here:

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.

On Fri, Sep 23, 2022 at 3:39 PM Jacob Champion <jchampion@timescale.com> wrote:
>
> On Fri, Mar 25, 2022 at 5:00 PM Jacob Champion <pchampion@vmware.com> wrote:
> > v4 rebases over the latest version of the pluggable auth patchset
> > (included as 0001-4). Note that there's a recent conflict as
> > of d4781d887; use an older commit as the base (or wait for the other
> > thread to be updated).
>
> Here's a newly rebased v5. (They're all zipped now, which I probably
> should have done a while back, sorry.)
>
> - As before, 0001-4 are the pluggable auth set; they've now diverged
> from the official version over on the other thread [1].
> - I'm not sure that 0005 is still completely coherent after the
> rebase, given the recent changes to jsonapi.c. But for now, the tests
> are green, and that should be enough to keep the conversation going.
> - 0008 will hopefully be obsoleted when the SYSTEM_USER proposal [2] lands.
>
> Thanks,
> --Jacob
>
> [1] https://www.postgresql.org/message-id/CAJxrbyxgFzfqby%2BVRCkeAhJnwVZE50%2BZLPx0JT2TDg9LbZtkCg%40mail.gmail.com
> [2] https://www.postgresql.org/message-id/flat/7e692b8c-0b11-45db-1cad-3afc5b57409f@amazon.com