Date: Mon, 1 Feb 2021 12:43:59 -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: <20210201174359.GK27507@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>
 <20210201170605.GJ27507@tamriel.snowman.net>
 <3344750.1612200729@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="bsuamqX0IQa0YO0e"
Content-Disposition: inline
In-Reply-To: <3344750.1612200729@sss.pgh.pa.us>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


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

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Stephen Frost <sfrost@snowman.net> writes:
> > * Tom Lane (tgl@sss.pgh.pa.us) wrote:
> >> 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.
>=20
> > 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.
>=20
> That's still got the same issue though: where does the role get any
> permissions from?
>=20
> I suppose you could say "allow auto-creation of new roles and make them
> members of group X", where X holds the permissions that "everybody"
> should have.  But I'm not sure how much that buys compared to just
> letting everyone log in as X.

Right, pg_ldap_sync already supports making new roles a member of
another role in PG such as a group role, we'd want to do something
similar.  Also- once the role exists, then permissions could be assigned
directly as well, of course, which would be the advantage of a
background worker that's keeping the set of roles in sync, as the role
would be created at nearly the same time in both the authentication
system itself (eg: AD) and in PG.  That kind of integration exists in
other products and would go a long way to making PG easier to use and
administer.

Thanks,

Stephen

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

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

iQIcBAEBCgAGBQJgGD3fAAoJEO1sijiDR2RVlcIP/2Kh9U8tGAkvWud4DAU1NIKp
JV1szXcduK+Icw7kBxpQkN2nuWvXUXxe01HzrVULsSZ+0sSF0Xiq5Bu+5lZV41ib
IWf5oDa3ItCjS3pToZr52iEPw06PEhRGOmL2tLiI8Bloh/vsgUJpRhkDNa9c1Mlo
v0II53rJx6AayXTpANbSGUMPLmkuSNBxDEKMsxhh5nidkKx0B0bj5UHtlrc3pMbm
W8YZptXR5HefjbJ+jjnosh5Ur0M5PayGblQZiczTmbYt6o8uvqbmieKmP3h20tKs
Ubyj8AVT3qYYF16e1SHxigZsDPMcUPRBxHwi7WMl4nF3ZwxXWUIVy0C/rXdW7BY6
pmY5stXCHZ1o5aDyUjG5Vvolx8ve5p9kLv1IQ8h2SjJmguSnzLZn6/M15OaRdn+h
cNnbIPf7HyF9yhKxUDvkOm4uDDyoxSEhsUKB8GCkYfr9S7ew2b33e8qvtXpiMZWg
yBMv19feoC/mXHhidqjF1pK2fzt2rKIM9ZB57sOB4yz/YDA7Gj163ILPBYyJ4p8Q
EYbCZ2i3h94b3K4Dnpe+lzEDTbijoavBSo65tvy8DnGzNzbSDo0aqJqIhwNRfu0h
RZCfGR8BugwJ/mFGUoNuxbVZbm7NnKOJZryyD0llpl+6CMH5PoxBy94NCukUDtkw
Eu8TjOtXVy9zYb//vraC
=aYx0
-----END PGP SIGNATURE-----

--bsuamqX0IQa0YO0e--