From: Tom Lane <tgl@sss.pgh.pa.us>
To: Greg Stark <stark@mit.edu>
cc: Jacob Champion <pchampion@vmware.com>,
        "sfrost@snowman.net" <sfrost@snowman.net>,
        "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: Proposal: Save user's original authenticated identity for logging
In-reply-to: 
 <CAM-w4HPaWxXZd7ZZ=DmLUDa-R_Dp=m7CbqFnH393zuY2uirWsQ@mail.gmail.com>
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>
Comments: In-reply-to Greg Stark <stark@mit.edu>
	message dated "Sun, 31 Jan 2021 10:17:33 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3085245.1612108406.1@sss.pgh.pa.us>
Date: Sun, 31 Jan 2021 10:53:26 -0500
Message-ID: <3085246.1612108406@sss.pgh.pa.us>
Precedence: bulk

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.

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

			regards, tom lane