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

Greetings,

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Greg Stark <stark(at)mit(dot)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.
>
> > 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.
>
> 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