Re: [PoC] Federated Authn/z with OAUTHBEARER

More information on the latest patch.

1. We aligned the implementation with the barebone SASL for OAUTH
described here - https://www.rfc-editor.org/rfc/rfc7628
The flow can be explained in the diagram below:

  +----------------------+                                 +----------+
  |             +-------+                                  | Postgres |
  | PQconnect ->|       |                                  |          |
  |             |       |                                  |   +-----------+
  |             |       | ---------- Empty Token---------> | > |           |
  |             | libpq | <-- Error(Discovery + Scope ) -- | < | Pre-Auth  |
  |          +------+   |                                  |   |  Hook     |
  |     +- < | Hook |   |                                  |   +-----------+
  |     |    +------+   |                                  |          |
  |     v       |       |                                  |          |
  |  [get token]|       |                                  |          |
  |     |       |       |                                  |          |
  |     +       |       |                                  |   +-----------+
  | PQconnect > |       | --------- Access Token --------> | > | Validator |
  |             |       | <---------- Auth Result -------- | < |   Hook    |
  |             |       |                                  |   +-----------+
  |             +-------+                                  |          |
  +----------------------+                                 +----------+

2. Removed Device Code implementation in libpq. Several reasons:
   - Reduce scope and focus on the protocol first.
   - Device code implementation uses iddawc dependency. Taking this
dependency is a controversial step which requires broader discussion.
   - Device code implementation without iddaws would significantly
increase the scope of the patch, as libpq needs to poll the token
endpoint, setup different API calls, e.t.c.
   - That flow should canonically only be used for clients which can't
invoke browsers. If it is the only flow to be implemented, it can be
used in the context when it's not expected by the OAUTH protocol.

3. Temporarily removed test suite. We are actively working on aligning
the tests with the latest changes. Will add a patch with tests soon.

We will change the "V3" prefix to make it the next after the previous
iterations.

Thanks!
Andrey.

On Thu, Jan 12, 2023 at 11:08 AM mahendrakar s
<mahendrakarforpg@gmail.com> wrote:
>
> Hi All,
>
> Changes added to Jacob's patch(v2) as per the discussion in the thread.
>
> The changes allow the customer to send the OAUTH BEARER token through psql connection string.
>
> Example:
> psql  -U user@example.com -d 'dbname=postgres oauth_bearer_token=abc'
>
> To configure OAUTH, the pg_hba.conf line look like:
> local   all             all                                     oauth   provider=oauth_provider
issuer="https://example.com"scope="openid email"
 
>
> We also added hook to libpq to pass on the metadata about the issuer.
>
> Thanks,
> Mahendrakar.
>
>
> On Sat, 17 Dec 2022 at 04:48, Jacob Champion <jchampion@timescale.com> wrote:
> >
> > On Mon, Dec 12, 2022 at 9:06 PM Andrey Chudnovsky
> > <achudnovskij@gmail.com> wrote:
> > > If your concern is extension not honoring the DBA configured values:
> > > Would a server-side logic to prefer HBA value over extension-provided
> > > resolve this concern?
> >
> > Yeah. It also seals the role of the extension here as "optional".
> >
> > > We are definitely biased towards the cloud deployment scenarios, where
> > > direct access to .hba files is usually not offered at all.
> > > Let's find the middle ground here.
> >
> > Sure. I don't want to make this difficult in cloud scenarios --
> > obviously I'd like for Timescale Cloud to be able to make use of this
> > too. But if we make this easy for a lone DBA (who doesn't have any
> > institutional power with the providers) to use correctly and securely,
> > then it should follow that the providers who _do_ have power and
> > resources will have an easy time of it as well. The reverse isn't
> > necessarily true. So I'm definitely planning to focus on the DBA case
> > first.
> >
> > > A separate reason for creating this pre-authentication hook is further
> > > extensibility to support more metadata.
> > > Specifically when we add support for OAUTH flows to libpq, server-side
> > > extensions can help bridge the gap between the identity provider
> > > implementation and OAUTH/OIDC specs.
> > > For example, that could allow the Github extension to provide an OIDC
> > > discovery document.
> > >
> > > I definitely see identity providers as institutional actors here which
> > > can be given some power through the extension hooks to customize the
> > > behavior within the framework.
> >
> > We'll probably have to make some compromises in this area, but I think
> > they should be carefully considered exceptions and not a core feature
> > of the mechanism. The gaps you point out are just fragmentation, and
> > adding custom extensions to deal with it leads to further
> > fragmentation instead of providing pressure on providers to just
> > implement the specs. Worst case, we open up new exciting security
> > flaws, and then no one can analyze them independently because no one
> > other than the provider knows how the two sides work together anymore.
> >
> > Don't get me wrong; it would be naive to proceed as if the OAUTHBEARER
> > spec were perfect, because it's clearly not. But if we need to make
> > extensions to it, we can participate in IETF discussions and make our
> > case publicly for review, rather than enshrining MS/GitHub/Google/etc.
> > versions of the RFC and enabling that proliferation as a Postgres core
> > feature.
> >
> > > Obtaining a token is an asynchronous process with a human in the loop.
> > > Not sure if expecting a hook function to return a token synchronously
> > > is the best option here.
> > > Can that be an optional return value of the hook in cases when a token
> > > can be obtained synchronously?
> >
> > I don't think the hook is generally going to be able to return a token
> > synchronously, and I expect the final design to be async-first. As far
> > as I know, this will need to be solved for the builtin flows as well
> > (you don't want a synchronous HTTP call to block your PQconnectPoll
> > architecture), so the hook should be able to make use of whatever
> > solution we land on for that.
> >
> > This is hand-wavy, and I don't expect it to be easy to solve. I just
> > don't think we have to solve it twice.
> >
> > Have a good end to the year!
> > --Jacob