From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephen Frost <sfrost@snowman.net>
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
In-reply-to: <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>
 <20210201170605.GJ27507@tamriel.snowman.net>
Comments: In-reply-to Stephen Frost <sfrost@snowman.net>
	message dated "Mon, 01 Feb 2021 12:06:05 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3344749.1612200729.1@sss.pgh.pa.us>
Date: Mon, 01 Feb 2021 12:32:09 -0500
Message-ID: <3344750.1612200729@sss.pgh.pa.us>
Precedence: bulk

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.

> 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.

That's still got the same issue though: where does the role get any
permissions from?

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.

			regards, tom lane