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

Hi  Hackers,

We are trying to implement AAD(Azure AD) support in PostgreSQL and it
can be achieved with support of the OAuth method. To support AAD on
top of OAuth in a generic fashion (i.e for all other OAuth providers),
we are proposing this patch. It basically exposes two new hooks (one
for error reporting and one for OAuth provider specific token
validation) and passing OAuth bearer token to backend. It also adds
support for client credentials flow of OAuth additional to device code
flow which Jacob has proposed.

The changes for each component are summarized below.

1.     Provider-specific extension:
        Each OAuth provider implements their own token validator as an
extension. Extension registers an OAuth provider hook which is matched
to a line in the HBA file.

2.     Add support to pass on the OAuth bearer token. In this
obtaining the bearer token is left to 3rd party application or user.

        ./psql -U <username> -d 'dbname=postgres
oauth_client_id=<client_id> oauth_bearer_token=<token>

3.     HBA: An additional param ‘provider’ is added for the oauth method.
        Defining "oauth" as method + passing provider, issuer endpoint
and expected audience

        * * * * oauth   provider=<token validation extension>
issuer=.... scope=....

4.     Engine Backend:
        Support for generic OAUTHBEARER type, requesting client to
provide token and passing to token for provider-specific extension.

5.     Engine Frontend: Two-tiered approach.
           a)      libpq transparently passes on the token received
from 3rd party client as is to the backend.
           b)      libpq optionally compiled for the clients which
explicitly need libpq to orchestrate OAuth communication with the
issuer (it depends heavily on 3rd party library iddawc as Jacob
already pointed out. The library seems to be supporting all the OAuth
flows.)

Please let us know your thoughts as the proposed method supports
different OAuth flows with the use of provider specific hooks. We
think that the proposal would be useful for various OAuth providers.

Thanks,
Mahendrakar.


On Tue, 20 Sept 2022 at 10:18, Jacob Champion <pchampion@vmware.com> wrote:
>
> On Tue, 2021-06-22 at 23:22 +0000, Jacob Champion wrote:
> > On Fri, 2021-06-18 at 11:31 +0300, Heikki Linnakangas wrote:
> > >
> > > A few small things caught my eye in the backend oauth_exchange function:
> > >
> > > > +       /* Handle the client's initial message. */
> > > > +       p = strdup(input);
> > >
> > > this strdup() should be pstrdup().
> >
> > Thanks, I'll fix that in the next re-roll.
> >
> > > In the same function, there are a bunch of reports like this:
> > >
> > > >                    ereport(ERROR,
> > > > +                          (errcode(ERRCODE_PROTOCOL_VIOLATION),
> > > > +                           errmsg("malformed OAUTHBEARER message"),
> > > > +                           errdetail("Comma expected, but found character \"%s\".",
> > > > +                                     sanitize_char(*p))));
> > >
> > > I don't think the double quotes are needed here, because sanitize_char
> > > will return quotes if it's a single character. So it would end up
> > > looking like this: ... found character "'x'".
> >
> > I'll fix this too. Thanks!
>
> v2, attached, incorporates Heikki's suggested fixes and also rebases on
> top of latest HEAD, which had the SASL refactoring changes committed
> last month.
>
> The biggest change from the last patchset is 0001, an attempt at
> enabling jsonapi in the frontend without the use of palloc(), based on
> suggestions by Michael and Tom from last commitfest. I've also made
> some improvements to the pytest suite. No major changes to the OAuth
> implementation yet.
>
> --Jacob