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

Greetings,

* Magnus Hagander (magnus(at)hagander(dot)net) wrote:
> On Sat, Jan 30, 2021 at 12:21 AM Jacob Champion <pchampion(at)vmware(dot)com> wrote:
> > > 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?

Yes.

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

This is certainly one area of frustration, but even if Kerberos isn't
available, it doesn't make it a good idea to use LDAP.

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

Right- passing around a user's password in the clear (or even through an
encrypted tunnel) has been strongly discouraged for a very long time,
for very good reason. LDAP does double-down on that by being a
centralized password, meaning that someone's entire identity (for all
the services that share that LDAP system, at least) are compromised if
any one system in the environment is.

Ideally, we'd have a 'PasswordAuthentication' option which would
disallow cleartext passwords, as has been discussed elsewhere, which
would make things like ldap and pam auth methods disallowed.

> 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 realize that this isn't a popular opinion, but I'd much rather we
actively move in the direction of deprecating auth methods which use
cleartext passwords. The one auth method we have that works that way
and isn't terrible is radius, though it also isn't great since the pin
doesn't change and would be compromised, not to mention that it likely
depends on the specific system as to if an attacker might be able to use
the exact same code provided to log into other systems if done fast
enough.

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

Yeah, we've been around this before and, as I recall anyway, there was
actually a prior patch proposed to add this information to the CSV log.
There is the question about if it's valuable enough to repeat on every
line or not. These days, I think I lean in the same direction as the
majority on this thread that it's sufficient to log as part of the
connection authorized message.

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

+1.

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

We've been trending in the direction of having separate functions/views
for the different types of auth, as the specific information you'd want
varies (SSL has a different set than GSS, for example). Maybe it makes
sense to have the one string that's used to match against in pg_ident
included in pg_stat_activity also but I'm not completely sure- after
all, there's a reason we have the separate views. Also, if we do add
it, I would think we'd have it under the same check as the other
sensitive pg_stat_activity fields and not be superuser-only.

Thanks,

Stephen