Date: Mon, 27 Feb 2023 15:31:04 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Andrey Chudnovsky <achudnovskij@gmail.com>
Cc: Jacob Champion <jchampion@timescale.com>,
	mahendrakar s <mahendrakarforpg@gmail.com>, hlinnaka@iki.fi,
	Michael Paquier <michael@paquier.xyz>,
	Pg Hackers <pgsql-hackers@postgresql.org>, smilingsamay@gmail.com
Subject: Re: [PoC] Federated Authn/z with OAUTHBEARER
Message-ID: <Y/0TCNMJgvAvHOev@tamriel.snowman.net>
References: <CAAWbhmiPjeVmZuB47BDZEgJD5kNXkxibtzxDtX9wysJKxLNnOw@mail.gmail.com>
 <CABkiuWrzxZLAcHexs9TS2rJ6A_snC4vnxyhY0BTr-ymD8gJDmw@mail.gmail.com>
 <CACrwV56UZQv4Mtm7=DTvvKP74UMMZgattBNQz6R6szm_v6xcag@mail.gmail.com>
 <a798eaae-4687-3687-d464-6d482816328e@timescale.com>
 <CACrwV55-j43wfub9Qwau02kZ4rksZw0gqNdPasj1rTNr8UC8Dg@mail.gmail.com>
 <CABkiuWo4fJQ7dhqgYLtJh41kpCkT6iXOO8Eym3Rdh5tx2RJCJw@mail.gmail.com>
 <Y/P1uITaEcLGGB0Z@tamriel.snowman.net>
 <CAAWbhmh+6q4t3P+wDmS=JuHBpcgF-VM2cXNft8XV02yk-cHCpQ@mail.gmail.com>
 <Y/e02yVzCxa313nV@tamriel.snowman.net>
 <CACrwV54oLzPO8ruh12_mHUH6GJgSvLCqtkDdgntpOincyTjv1g@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="Y4bymvWZsWcEBCM8"
Content-Disposition: inline
In-Reply-To: <CACrwV54oLzPO8ruh12_mHUH6GJgSvLCqtkDdgntpOincyTjv1g@mail.gmail.com>
User-Agent: Mutt/2.1.4 (2021-12-11)
Archived-At: <https://www.postgresql.org/message-id/Y/0TCNMJgvAvHOev%40tamriel.snowman.net>
Precedence: bulk


--Y4bymvWZsWcEBCM8
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Andrey Chudnovsky (achudnovskij@gmail.com) wrote:
> > This really doesn't feel like a great area to try and do hooks or
> > similar in, not the least because that approach has been tried and tried
> > again (PAM, GSSAPI, SASL would all be examples..) and frankly none of
> > them has turned out great (which is why we can't just tell people "well,
> > install the pam_oauth2 and watch everything work!") and this strikes me
> > as trying to do that yet again but worse as it's not even a dedicated
> > project trying to solve the problem but more like a side project.
>=20
> In this case it's not intended to be an open-ended hook, but rather an
> implementation of a specific rfc (rfc-7628) which defines a
> client-server communication for the authentication flow.
> The rfc itself does leave a lot of flexibility on specific parts of
> the implementation. Which do require hooks:

Color me skeptical on an RFC that requires hooks.

> (1.) Server side hook to validate the token, which is specific to the
> OAUTH provider.
> (2.) Client side hook to request the client to obtain the token.

Perhaps I'm missing it... but weren't these handled with what the
original patch that Jacob had was doing?

> On (1.), we would need a hook for the OAUTH provider extension to do
> validation. We can though do some basic check that the credential is
> indeed a JWT token signed by the requested issuer.
>=20
> Specifically (2.) is where we can provide a layer in libpq to simplify
> the integration. i.e. implement some OAUTH flows.
> Though we would need some flexibility for the clients to bring their own =
token:
> For example there are cases where the credential to obtain the token
> is stored in a separate secure location and the token is returned from
> a separate service or pushed from a more secure environment.

In those cases... we could, if we wanted, simply implement the code to
actually pull the token, no?  We don't *have* to have a hook here for
this, we could just make it work.

> > another new "generic" set of hooks/APIs that will just cause DBAs and
> > our users headaches trying to make work.
> As I mentioned above, it's an rfc implementation, rather than our inventi=
on.

While I only took a quick look, I didn't see anything in that RFC that
explicitly says that hooks or a plugin or a library or such is required
to meet the RFC.  Sure, there are places which say that the
implementation is specific to a particular server or client but that's
not the same thing.

> When it comes to DBAs and the users.
> Builtin libpq implementations which allows psql and pgadmin to
> seamlessly connect should suffice those needs.
> While extensibility would allow the ecosystem to be open for OAUTH
> providers, SAAS developers, PAAS providers and other institutional
> players.

Each to end up writing their own code to do largely the same thing
without the benefit of the larger community to be able to review and
ensure that it's done properly?

That doesn't sound like a great approach to me.

Thanks,

Stephen

--Y4bymvWZsWcEBCM8
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEwf6gbxKhD863zrx/7WyKOINHZFUFAmP9EwUACgkQ7WyKOINH
ZFXUew//eQ7/UnjepErXC/CoLtFEhlI1NwQfJR/edT6H6BBfmwQi1IkhsPmPzliL
zALOQJKWXrAG59rRJWPpT+99WiPGx3Q0HOfcbGbPF5YbUsoXsP+gCwduoK7o91qV
PWnnSXVxjP44wkxSx+lTJhJvtm/VBNwyjslFeDCn4t0lz//93e9DfJdNOv0C9SuQ
Sl/t48lZX+EKe1HV9o0846RbRDtLRbqMuM4ZI5vYEk4WnqgxYczF0vm4i9KjtmCh
DPZJUD1nMxEWqch1N7pS+bJwAy8nYUUcWuRJTAlANgmd40Ku1pCf/ZP+12lNM0VH
9zLqyb1VJOiau5U2lvFDfFKUFOxDR8LJTpQKaQPBAjh2cxf+VustYzlltF/9D2V2
pbyYJiDYwsEFxxIHXQW4yUuF8dAfESOxuD+AD7oDchMzmfer/SIY0PUgBjwf1t0f
hpT7KEhuG+NA4D/kgXqjN5AU1bAA2BnoOf+N5FanoUKXtgj/fEVC+ZI1XhJYk9vR
eqFa1tUy6nyPrHuUMulV1FwA6LiMy79QyP/UQeuUHrY0X2a3IFNukGx+JUCEZIMC
Euc2IcTUYzxWLy/z7b+CXIgpSCDDPbvF34Uvgaxo3uuVZrVSbthtuOiAwjZ9jc3R
BbEYC2nc/jRiUZMVYWYSkGzBHboD22eLcykTl3/EHj1u4jnlbbI=
=9tL/
-----END PGP SIGNATURE-----

--Y4bymvWZsWcEBCM8--