Hi hackers,
The SYSTEM_USER is a sql reserved word as mentioned in [1] and is currently not implemented.
Please find attached a patch proposal to make use of the SYSTEM_USER so that it returns the authenticated identity (if any) (aka authn_id in the Port struct).
Indeed in some circumstances, the authenticated identity is not the SESSION_USER and then the information is lost from the connection point of view (it could still be retrieved thanks to commit 9afffcb833 and log_connections set to on).
Example 1, using the gss authentification.
Say we have this entry in pg_hba.conf:
host all all 0.0.0.0/0 gss map=mygssmap
and the related mapping in pg_ident.conf
mygssmap /^(.*@.*)\.LOCAL$ mary
Then, connecting with a valid Kerberos Ticket that contains “bertrand@BDTFOREST.LOCAL” as the default principal that way: psql -U mary -h myhostname -d postgres,
we will get:
postgres=> select current_user, session_user;
current_user | session_user
--------------+--------------
mary | mary
(1 row)
While the SYSTEM_USER would produce the Kerberos principal:
postgres=> select system_user;
system_user
--------------------------
bertrand@BDTFOREST.LOCAL
(1 row)
Example 2, using the peer authentification.
Say we have this entry in pg_hba.conf:
local all john peer map=mypeermap
and the related mapping in pg_ident.conf
mypeermap postgres john
Then connected localy as the system user postgres and connecting to the database that way: psql -U john -d postgres, we will get:
postgres=> select current_user, session_user;
current_user | session_user
--------------+--------------
john | john
(1 row)
While the SYSTEM_USER would produce the system user that requested the connection:
postgres=> select system_user;
system_user
-------------
postgres
(1 row)
Thanks to those examples we have seen some situations where the information related to the authenticated identity has been lost from the connection point of view (means not visible in the current_session or in the session_user).
The purpose of this patch is to make it visible through the SYSTEM_USER sql reserved word.
Remarks:
- In case port->authn_id is NULL then the patch is returning the SESSION_USER for the SYSTEM_USER. Perhaps it should return NULL instead.
- There is another thread [2] to expose port->authn_id to extensions and triggers thanks to a new API. This thread [2] leads to discussions about providing this information to the parallel workers too. While the new MyClientConnectionInfo being discussed in [2] could be useful to hold the client information that needs to be shared between the backend and any parallel workers, it does not seem to be needed in the case port->authn_id is exposed through SYSTEM_USER (like it is not for CURRENT_USER and SESSION_USER).
I will add this patch to the next commitfest.
I look forward to your feedback.
Bertrand
[1]: https://www.postgresql.org/docs/current/sql-keywords-appendix.html
[2]: https://www.postgresql.org/message-id/flat/793d990837ae5c06a558d58d62de9378ab525d83.camel%40vmware.com