Date: Mon, 1 Feb 2021 12:06:05 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Greg Stark <stark@mit.edu>, Jacob Champion <pchampion@vmware.com>,
	"pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: Proposal: Save user's original authenticated identity for logging
Message-ID: <20210201170605.GJ27507@tamriel.snowman.net>
References: <c55788dd1773c521c862e8e0dddb367df51222be.camel@vmware.com>
 <20210129220101.GE27507@tamriel.snowman.net>
 <2829030.1611959442@sss.pgh.pa.us>
 <c65fd6360d9a174dcf98b1e2e7d02ca654e0ca85.camel@vmware.com>
 <2832058.1611963634@sss.pgh.pa.us>
 <CAM-w4HPaWxXZd7ZZ=DmLUDa-R_Dp=m7CbqFnH393zuY2uirWsQ@mail.gmail.com>
 <3085246.1612108406@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="aZ0tWAY5A1AeJEdp"
Content-Disposition: inline
In-Reply-To: <3085246.1612108406@sss.pgh.pa.us>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


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

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Greg Stark <stark@mit.edu> writes:
> > I wonder if there isn't room to handle this the other way around. To
> > configure Postgres to not need a CREATE ROLE for every role but
> > delegate the user management to the external authentication service.
>=20
> > So Postgres would consider the actual role to be the one kerberos said
> > it was even if that role didn't exist in pg_role. Presumably you would
> > want to delegate to a corresponding authorization system as well so if
> > the role was absent from pg_role (or more likely fit some pattern)
> > Postgres would ignore pg_role and consult the authorization system
> > configured like AD or whatever people use with Kerberos these days.
>=20
> This doesn't sound particularly workable: how would you manage
> inside-the-database permissions?  Kerberos isn't going to know
> what "view foo" is, let alone know whether you should be allowed
> to read or write it.  So ISTM there has to be a role to hold
> those permissions.  Certainly, you could allow multiple external
> identities to share a role ... but that works today.

Agreed- we would need something in the database to tie it to and I don't
see it making much sense to try to invent something else for that when
that's what roles are.  What's been discussed before and would certainly
be nice, however, would be a way to have roles automatically created.
There's pg_ldap_sync for that today but it'd be nice to have something
built-in and which happens at connection/authentication time, or maybe a
background worker that connects to an ldap server and listens for
changes and creates appropriate roles when they're created.  Considering
we've got the LDAP code already, that'd be a really nice capability.

Thanks,

Stephen

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJgGDT9AAoJEO1sijiDR2RVc30P+wSdBabuLtH6DQWN67t4O4fg
+v7Y2nPlbAAIpbq65tcAEGmFKRcLHlYt3jOVQv3An1rwwV8jNXBt81ZCtZqI6v5I
ydrTMTRpQn2awe/Swz+Vhm1/jtNUFSigUMTLgK+RMrwAPFKWBGySVnjzynMomnzA
FD4kLFRR3pCGibqO04oOWer1EHcCk6psG+cuvc1ASRulOBn2yrRSoAmmPkNTvs5p
X8jQdXwfu+OBaHuWESL5/aIvHbC0YyLtT+fDj0mTq1M8eE7mnsuKnlktfXwABi1g
8gjx53sDQllRcRhF0WwP77gA6Rgp07kUG03c6PXaaAlRCWu/vRBE8D9+JtGRVgwX
s3nTLIN1b9Shcm1YoIuw+cPrEA+D9Nrkp+mij61kQizn0tmucyF6SVjS2I8Gd/oJ
wMuv+uA/28mrqs0mQNfwRTzy71SG+wzufL8irxyHlLxzjZrhJicBMFvXguGq6IDd
xhXTdu9e0O77xF8Zc65r+KGD6YAjah8kelUA3V0zjvAm93XE929wqFHOFPNZMy4R
ptVfmaZWgYblxGdN6rf+HGcB3Q6IsGSU7mTDc1+3k7ikh5w4rj074OJYYS54QAGV
8fQb11IgTkSLb8LNxXb1k39H7wNepvO48aUFqKmCRmGUXaoWnl5rwhlDpL+pWUXh
EN6bGUyOS37NpbKes799
=prg2
-----END PGP SIGNATURE-----

--aZ0tWAY5A1AeJEdp--