Add some information about authenticated identity via log_connections

Enterprise / PostgreSQL - Michael Paquier [] - 7 April 2021 01:16 UTC

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

9afffcb833 Add some information about authenticated identity via log_connections
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/ | 59 +++++++++----
src/test/kerberos/t/ | 79 +++++++++++------
src/test/ldap/t/ | 52 +++++++++---
src/test/perl/ | 77 +++++++++++++++++
src/test/ssl/t/ | 36 +++++---
src/test/ssl/t/ | 10 ++-
11 files changed, 416 insertions(+), 74 deletions(-)


  • Share