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

Greetings,

* Jacob Champion (pchampion(at)vmware(dot)com) wrote:
> First, the context: recently I've been digging into the use of third-
> party authentication systems with Postgres. One sticking point is the
> need to have a Postgres role corresponding to the third-party user
> identity, which becomes less manageable at scale. I've been trying to
> come up with ways to make that less painful, and to start peeling off
> smaller feature requests.

Yeah, it'd be nice to improve things in this area.

> = Problem =
>
> For auth methods that allow pg_ident mapping, there's a way around the
> one-role-per-user problem, which is to have all users that match some
> pattern map to a single role. For Kerberos, you might specify that all
> user principals under @EXAMPLE.COM are allowed to connect as some
> generic user role, and that everyone matching */admin(at)EXAMPLE(dot)COM is
> additionally allowed to connect as an admin role.
>
> Unfortunately, once you've been assigned a role, Postgres either makes
> the original identity difficult to retrieve, or forgets who you were
> entirely:
>
> - for GSS, the original principal is saved in the Port struct, and you
> need to either pull it out of pg_stat_gssapi, or enable log_connections
> and piece the log line together with later log entries;

This has been improved on of late, but it's been done piece-meal.

> - for LDAP, the bind DN is discarded entirely;

We don't support pg_ident.conf-style entries for LDAP, meaning that the
user provided has to match what we check, so I'm not sure what would be
improved with this change..? I'm also just generally not thrilled with
putting much effort into LDAP as it's a demonstrably insecure
authentication mechanism.

> - for TLS client certs, the DN has to be pulled from pg_stat_ssl or the
> sslinfo extension (and it's truncated to 64 characters, so good luck if
> you have a particularly verbose PKI tree);

Yeah, it'd be nice to improve on this.

> - for peer auth, the username of the peereid is discarded;

Would be good to improve this too.

> = Proposal =
>
> I propose that every auth method should store the string it uses to
> identify a user -- what I'll call an "authenticated identity" -- into
> one central location in Port, after authentication succeeds but before
> any pg_ident authorization occurs. This field can then be exposed in
> log_line_prefix. (It could additionally be exposed through a catalog
> table or SQL function, if that were deemed useful.) This would let a
> DBA more easily audit user activity when using more complicated
> pg_ident setups.

This seems like it would be good to include the CSV format log files
also.

> Would this be generally useful for those of you using pg_ident in
> production? Have I missed something that already provides this
> functionality?

For some auth methods, eg: GSS, we've recently added information into
the authentication method which logs what the authenticated identity
was. The advantage with that approach is that it avoids bloating the
log by only logging that information once upon connection rather than
on every log line... I wonder if we should be focusing on a similar
approach for other pg_ident.conf use-cases instead of having it via
log_line_prefix, as the latter means we'd be logging the same value over
and over again on every log line.

Thanks,

Stephen