Re: [PoC] Federated Authn/z with OAUTHBEARER

Thanks for the feedback,

> Having skimmed back through this thread again, I still feel that the
> direction that was originally being taken (actually support something in
> libpq and the backend, be it with libiddawc or something else or even
> our own code, and not just throw hooks in various places) makes a lot
> more sense and is a lot closer to how Kerberos and client-side certs and
> even LDAP auth work today.  That also seems like a much better answer
> for our users when it comes to new authentication methods than having
> extensions and making libpq developers have to write their own custom
> code, not to mention that we'd still need to implement something in psql
> to provide such a hook if we are to have psql actually usefully exercise
> this, no?

libpq implementation is the long term plan. However, our intention is
to start with the protocol implementation which allows us to build on
top of.

While device code is the right solution for psql, having that as the
only one can result in incentive to use it in the cases it's not
intended to.
Reasonably good implementation should support all of the following:
(1.) authorization code with pkce (for GUI applications)
(2.) device code (for console user logins)
(3.) client secret
(4.) some support for client certificate flow

(1.) and (4.) require more work to get implemented, though necessary
for encouraging the most secure grant types.
As we didn't have those pieces, we're proposing starting with the
protocol, which can be used by the ecosystem to build token flow
implementations.
Then add the libpq support for individual grant types.

We originally looked at starting with bare bone protocol for PG16 and
adding libpq support in PG17.
That plan won't happen, though still splitting the work into separate
stages would make more sense in my opinion.

Several questions to follow up:
(a.) Would you support committing the protocol first? or you see libpq
implementation for grants as the prerequisite to consider the auth
type?
(b.) As of today, the server side core does not validate that the
token is actually a valid jwt token. Instead relies on the extensions
to do the validation.
Do you think server core should do the basic validation before passing
to extensions to prevent the auth type being used for anything other
than OAUTH flows?

Tests are the plan for the commit-ready implementation.

Thanks!
Andrey.

On Tue, Feb 21, 2023 at 2:24 PM Jacob Champion <jchampion@timescale.com> wrote:
>
> On Mon, Feb 20, 2023 at 2:35 PM Stephen Frost <sfrost@snowman.net> wrote:
> > Having skimmed back through this thread again, I still feel that the
> > direction that was originally being taken (actually support something in
> > libpq and the backend, be it with libiddawc or something else or even
> > our own code, and not just throw hooks in various places) makes a lot
> > more sense and is a lot closer to how Kerberos and client-side certs and
> > even LDAP auth work today.
>
> Cool, that helps focus the effort. Thanks!
>
> > That also seems like a much better answer
> > for our users when it comes to new authentication methods than having
> > extensions and making libpq developers have to write their own custom
> > code, not to mention that we'd still need to implement something in psql
> > to provide such a hook if we are to have psql actually usefully exercise
> > this, no?
>
> I don't mind letting clients implement their own flows... as long as
> it's optional. So even if we did use a hook in the end, I agree that
> we've got to exercise it ourselves.
>
> > In the Kerberos test suite we have today, we actually bring up a proper
> > Kerberos server, set things up, and then test end-to-end installing a
> > keytab for the server, getting a TGT, getting a service ticket, testing
> > authentication and encryption, etc.  Looking around, it seems like the
> > equivilant would perhaps be to use Glewlwyd and libiddawc or libcurl and
> > our own code to really be able to test this and show that it works and
> > that we're doing it correctly, and to let us know if we break something.
>
> The original patchset includes a test server in Python -- a major
> advantage being that you can test the client and server independently
> of each other, since the implementation is so asymmetric. Additionally
> testing against something like Glewlwyd would be a great way to stack
> coverage. (If we *only* test against a packaged server, though, it'll
> be harder to test our stuff in the presence of malfunctions and other
> corner cases.)
>
> Thanks,
> --Jacob