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

On Mon, 2021-02-01 at 18:44 +0100, Magnus Hagander wrote:
> 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.

Yep. I think there are at least three separate things:

1) third-party authentication ("tell me who this user is"), which I
think Postgres currently has a fairly good handle on;

2) third-party authorization ("tell me what roles this user can
assume"), which Postgres doesn't do, unless you have a script
automatically update pg_ident -- and even then you can't do it for
every authentication type; and

3) third-party role administration ("tell me what roles should exist in
the database, and what permissions they have"), which currently exists
in a limited handful of third-party tools.

Many users will want all three of these questions to be answered by the
same system, which is fine, but for more advanced use cases I think
it'd be really useful if you could answer them fully independently.

For really gigantic deployments, the overhead of hundreds of Postgres
instances randomly pinging a central server just to see if there have
been any new users can be a concern. Having a solid system for
authorization could potentially decrease the need for a role auto-
creation system, and reduce the number of moving parts. If you have a
small number of core roles (relative to the number of users), it might
not be as important to constantly keep role lists up to date, so long
as the central authority can tell you which of your existing roles a
user is authorized to become.

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

Agreed. As long as it's possible for multiple user identities to assume
the same role, storing the original authenticated identity is still
important, regardless of how you administer the roles themselves.

--Jacob