Re: Proposal: Save user's original authenticated identity for logging - Mailing list pgsql-hackers

On Sat, Jan 30, 2021 at 12:21 AM Jacob Champion <pchampion@vmware.com> wrote:
>
> On Fri, 2021-01-29 at 17:01 -0500, Stephen Frost wrote:
> > > - 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..?
>
> For simple binds, this gives you almost nothing. For bind+search,
> logging the actual bind DN is still important, in my opinion, since the
> mechanism for determining it is more opaque (and may change over time).

Yeah, that's definitely a piece of information that can be hard to get at today.


> (There's also the fact that I think pg_ident mapping for LDAP would be
> just as useful as it is for GSS or certs. That's for a different
> conversation.)

Specifically for search+bind, I would assume?


> > I'm also just generally not thrilled with
> > putting much effort into LDAP as it's a demonstrably insecure
> > authentication mechanism.
>
> Because Postgres has to proxy the password? Or is there something else?

Stephen is on a bit of a crusade against ldap :) Mostly for good
reasons of course. A large amount of those who choose ldap also have a
kerberos system (because, say, active directory) and the pick ldap
only because they think it's good, not because it is...

But yes, I think the enforced cleartext password proxying is at the
core of the problem. LDAP also encourages the idea of centralized
password-reuse, which is not exactly a great thing for security.

That said, I don't think either of those are reasons not to improve on
LDAP. It can certainly be a reason for somebody not to want to spend
their own time on it, but there's no reason it should prevent
improvements.


> > > 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.
>
> Agreed in principle... Is the CSV format configurable? Forcing it into
> CSV logs by default seems like it'd be a hard sell, especially for
> people not using pg_ident.

For CVS, all columns are always included, and that's a feature -- it
makes it predictable.

To make it optional it would have to be a configuration parameter that
turns the field into an empty one. but it should still be there.


> > 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.
>
> As long as the identity can be easily logged and reviewed by DBAs, I'm
> happy.

Yeah, per my previous mail, I think this is a better way - make it
part of log_connections. But it would be good to find a way that we
can log it the same way for all of them -- rather than slightly
different ways depending on authentication method.

With that I think it would also be useful to have it available in the
system as well -- either as a column in pg_stat_activity or maybe just
as a function like pg_get_authenticated_identity() since it might be
something that's interesting to a smallish subset of users (but very
interesting to those).

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