Re: Proposal: Save user's original authenticated identity for logging

On Mon, Feb 1, 2021 at 6:32 PM 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.
>
> > 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.

What people would *really* want I think is "alow auto-creation of new
roles, and then look up which other roles they should be members of
using ldap" (or "using this script over here" for a more flexible
approach). Which is of course a whole different thing to do in the
process of authentication.

The main thing you'd gain by auto-creating users rather than just
letting them log in is the ability to know exactly which user did
something, and view who it really is through pg_stat_activity. Adding
the "original auth id" as a field or available method would provide
that information in the mapped user case -- making the difference even
smaller. It's really the auto-membership that's the killer feature of
that one, I think.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/