Thread: pgsql: Add some information about authenticated identity via log_connec
Add some information about authenticated identity via log_connections
The "authenticated identity" is the string used by an authentication
method to identify a particular user. In many common cases, this is the
same as the PostgreSQL username, but for some third-party authentication
methods, the identifier in use may be shortened or otherwise translated
(e.g. through pg_ident user mappings) before the server stores it.
To help administrators see who has actually interacted with the system,
this commit adds the capability to store the original identity when
authentication succeeds within the backend's Port, and generates a log
entry when log_connections is enabled. The log entries generated look
something like this (where a local user named "foouser" is connecting to
the database as the database user called "admin"):
LOG: connection received: host=[local]
LOG: connection authenticated: identity="foouser" method=peer (/data/pg_hba.conf:88)
LOG: connection authorized: user=admin database=postgres application_name=psql
Port->authn_id is set according to the authentication method:
bsd: the PostgreSQL username (aka the local username)
cert: the client's Subject DN
gss: the user principal
ident: the remote username
ldap: the final bind DN
pam: the PostgreSQL username (aka PAM username)
password (and all pw-challenge methods): the PostgreSQL username
peer: the peer's pw_name
radius: the PostgreSQL username (aka the RADIUS username)
sspi: either the down-level (SAM-compatible) logon name, if
compat_realm=1, or the User Principal Name if compat_realm=0
The trust auth method does not set an authenticated identity. Neither
does clientcert=verify-full.
Port->authn_id could be used for other purposes, like a superuser-only
extra column in pg_stat_activity, but this is left as future work.
PostgresNode::connect_{ok,fails}() have been modified to let tests check
the backend log files for required or prohibited patterns, using the
new log_like and log_unlike parameters. This uses a method based on a
truncation of the existing server log file, like issues_sql_like().
Tests are added to the ldap, kerberos, authentication and SSL test
suites.
Author: Jacob Champion
Reviewed-by: Stephen Frost, Magnus Hagander, Tom Lane, Michael Paquier
Discussion: https://postgr.es/m/c55788dd1773c521c862e8e0dddb367df51222be.camel@vmware.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/9afffcb833d3c5e59a328a2af674fac7e7334fc1
Modified Files
--------------
doc/src/sgml/config.sgml | 3 +-
src/backend/libpq/auth.c | 136 ++++++++++++++++++++++++++++--
src/backend/libpq/hba.c | 24 ++++++
src/include/libpq/hba.h | 1 +
src/include/libpq/libpq-be.h | 13 +++
src/test/authentication/t/001_password.pl | 59 +++++++++----
src/test/kerberos/t/001_auth.pl | 79 +++++++++++------
src/test/ldap/t/001_auth.pl | 52 +++++++++---
src/test/perl/PostgresNode.pm | 77 +++++++++++++++++
src/test/ssl/t/001_ssltests.pl | 36 +++++---
src/test/ssl/t/002_scram.pl | 10 ++-
11 files changed, 416 insertions(+), 74 deletions(-)
Re: pgsql: Add some information about authenticated identity via log_connec
From
Michael Paquier
Date:
On Wed, Apr 07, 2021 at 01:17:34AM +0000, Michael Paquier wrote: > Add some information about authenticated identity via log_connections > > The "authenticated identity" is the string used by an authentication > method to identify a particular user. In many common cases, this is the > same as the PostgreSQL username, but for some third-party authentication > methods, the identifier in use may be shortened or otherwise translated > (e.g. through pg_ident user mappings) before the server stores it. So, fairywren, that is able to run the SSL tests on Windows, has been complaining here: https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=fairywren&dt=2021-04-07 03%3A04%3A44 The issue happens for 4 tests that check if some logs are not generated in the backend after an authentication failure, but the origin of the issue is that the truncation of the log file used to prevent a check of some previous connection's logs does not work properly, as fairywren shows a full-fledged log file in its output. The method used by connect_ok() and connect_fails() is the same as issues_sql_like() in PostgresNode.pm, so it seems to me that there is a pending issue with the latter, no? I think that this could lead to some false positives in tests expecting some general SQL query patterns, with a risk of overlap. A safe solution to this stuff is just to rotate the log file once before restarting the server, like in the attached. I would do that just on Windows to not slow down other systems. Thoughts? -- Michael
Attachment
Michael Paquier <michael@paquier.xyz> writes:
> On Wed, Apr 07, 2021 at 01:17:34AM +0000, Michael Paquier wrote:
>> Add some information about authenticated identity via log_connections
> So, fairywren, that is able to run the SSL tests on Windows, has been
> complaining here:
prairiedog is also not happy, apparently for a different reason.
Let me know if you need help debugging that one.
regards, tom lane
Re: pgsql: Add some information about authenticated identity via log_connec
From
Jacob Champion
Date:
On Wed, 2021-04-07 at 10:27 -0400, Tom Lane wrote: > Michael Paquier <michael@paquier.xyz> writes: > > On Wed, Apr 07, 2021 at 01:17:34AM +0000, Michael Paquier wrote: > > > Add some information about authenticated identity via log_connections > > So, fairywren, that is able to run the SSL tests on Windows, has been > > complaining here: > > prairiedog is also not happy, apparently for a different reason. > Let me know if you need help debugging that one. That one's failing because older OpenSSL doesn't support channel binding, and the new test I wrote forgot to check to make sure channel binding was supported... sorry. But the test doesn't truly *need* channel binding anyway; it just needs to check the interaction between SCRAM and verify-full, to ensure that the correct authn_id is set. Patch attached, tested locally with OpenSSL 1.0.1 and 1.1.1. --Jacob
Attachment
Jacob Champion <pchampion@vmware.com> writes:
> On Wed, 2021-04-07 at 10:27 -0400, Tom Lane wrote:
>> prairiedog is also not happy, apparently for a different reason.
> That one's failing because older OpenSSL doesn't support channel
> binding, and the new test I wrote forgot to check to make sure channel
> binding was supported... sorry.
> But the test doesn't truly *need* channel binding anyway; it just needs
> to check the interaction between SCRAM and verify-full, to ensure that
> the correct authn_id is set. Patch attached, tested locally with
> OpenSSL 1.0.1 and 1.1.1.
Sounds reasonable, pushed. (I didn't actually verify it on prairiedog,
because that would have taken a couple hours :-(. We can revisit if
that animal fails to go green.)
regards, tom lane
Re: pgsql: Add some information about authenticated identity via log_connec
From
Michael Paquier
Date:
On Wed, Apr 07, 2021 at 12:51:57PM -0400, Tom Lane wrote: > Sounds reasonable, pushed. (I didn't actually verify it on prairiedog, > because that would have taken a couple hours :-(. We can revisit if > that animal fails to go green.) Thanks to both of you. prairiedog is the only animal running the SSL tests with 1.0.1, it seems. What has been applied should be enough. I have applied the fix for fairywren now, to close the loop. -- Michael
Attachment
Re: pgsql: Add some information about authenticated identity via log_connec
From
Michael Paquier
Date:
On Thu, Apr 08, 2021 at 07:37:56AM +0900, Michael Paquier wrote: > I have applied the fix for fairywren now, to close the loop. I have been keeping an eye on fairywren since yesterday to see if this failure is completely taken care of, but it has not reported yet. Andrew, is this animal alive? -- Michael
Attachment
Re: pgsql: Add some information about authenticated identity via log_connec
From
Michael Paquier
Date:
On Fri, Apr 09, 2021 at 10:12:51AM +0900, Michael Paquier wrote: > I have been keeping an eye on fairywren since yesterday to see if this > failure is completely taken care of, but it has not reported yet. > Andrew, is this animal alive? FYI, fairywren has reported back and is now green: https://buildfarm.postgresql.org/cgi-bin/show_history.pl?nm=fairywren&br=HEAD The runtime of the test has gone up a bit, from 27s to 30s. So c7578fa has been able to fix the problem at hand. This also means that we had better do something similar for issues_sql_like in PostgresNode.pm. I'll start a new thread about that. -- Michael