On Tue, 2022-03-01 at 08:35 -0500, Stephen Frost wrote:
> * Michael Paquier (mich...@paquier.xyz) wrote:
> >
> > Ayway, this function needs to be documented. I think that you should
> > just add that in "Session Information Functions" in func.sgml, same
> > area as current_user(). The last time we talked about the authn ID,
> > one thing we discussed about was how to describe that in a good way to
> > the user, which is why the section of log_connections was reworked a
> > bit. And we don't have yet any references to what an authenticated
> > identity is in the docs.
>
> Agreed that it should be documented and that location seems reasonable
> to me.
Added a first draft in v5, alongside the perltidy fixups mentioned by
Michael.
> > There is no need to update catversion.h in the patch, committers
> > usually take care of that and that's an area of the code that
> > conflicts a lot.
>
> Yeah, best to let committers handle catversion bumps.
Heh, that was added for my benefit -- I was tired of forgetting to
initdb after switching dev branches -- but I've dropped it from the
patch and will just carry that diff locally.
Thanks,
--Jacob
From fd6e8a5b09b7facbecc8e38ef1f8a3d2cef866d4 Mon Sep 17 00:00:00 2001
From: Jacob Champion <pchamp...@vmware.com>
Date: Mon, 14 Feb 2022 08:10:53 -0800
Subject: [PATCH v5] Add API to retrieve authn_id from SQL
The authn_id field in MyProcPort is currently only accessible to the
backend itself. Add a SQL function, pg_session_authn_id(), to expose
the field to triggers that may want to make use of it.
---
doc/src/sgml/func.sgml | 26 +++++++++++++++++++++++
src/backend/utils/adt/name.c | 12 ++++++++++-
src/include/catalog/pg_proc.dat | 3 +++
src/test/authentication/t/001_password.pl | 11 ++++++++++
src/test/ssl/t/001_ssltests.pl | 7 ++++++
5 files changed, 58 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index df3cd5987b..654b96e677 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -22280,6 +22280,32 @@ SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_session_authn_id</primary>
+ </indexterm>
+ <function>pg_session_authn_id</function> ()
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Returns the authenticated identity for the current connection, or
+ <literal>NULL</literal> if the user has not been authenticated.
+ </para>
+ <para>
+ The authenticated identity is an immutable identifier for the user
+ presented during the connection handshake; the exact format depends on
+ the authentication method in use. (For example, when using the
+ <literal>scram-sha-256</literal> auth method, the authenticated identity
+ is simply the username. When using the <literal>cert</literal> auth
+ method, the authenticated identity is the Distinguished Name of the
+ client certificate.) Even for auth methods which use the username as
+ the authenticated identity, this function differs from
+ <literal>session_user</literal> in that its return value cannot be
+ changed after login.
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
diff --git a/src/backend/utils/adt/name.c b/src/backend/utils/adt/name.c
index e8bba3670c..662a7943ed 100644
--- a/src/backend/utils/adt/name.c
+++ b/src/backend/utils/adt/name.c
@@ -23,6 +23,7 @@
#include "catalog/namespace.h"
#include "catalog/pg_collation.h"
#include "catalog/pg_type.h"
+#include "libpq/libpq-be.h"
#include "libpq/pqformat.h"
#include "mb/pg_wchar.h"
#include "miscadmin.h"
@@ -257,7 +258,7 @@ namestrcmp(Name name, const char *str)
/*
- * SQL-functions CURRENT_USER, SESSION_USER
+ * SQL-functions CURRENT_USER, SESSION_USER, PG_SESSION_AUTHN_ID
*/
Datum
current_user(PG_FUNCTION_ARGS)
@@ -271,6 +272,15 @@ session_user(PG_FUNCTION_ARGS)
PG_RETURN_DATUM(DirectFunctionCall1(namein, CStringGetDatum(GetUserNameFromId(GetSessionUserId(), false))));
}
+Datum
+pg_session_authn_id(PG_FUNCTION_ARGS)
+{
+ if (!MyProcPort || !MyProcPort->authn_id)
+ PG_RETURN_NULL();
+
+ PG_RETURN_TEXT_P(cstring_to_text(MyProcPort->authn_id));
+}
+
/*
* SQL-functions CURRENT_SCHEMA, CURRENT_SCHEMAS
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index bf88858171..3afd171224 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1508,6 +1508,9 @@
{ oid => '746', descr => 'session user name',
proname => 'session_user', provolatile => 's', prorettype => 'name',
proargtypes => '', prosrc => 'session_user' },
+{ oid => '9774', descr => 'session authenticated identity',
+ proname => 'pg_session_authn_id', provolatile => 's', proparallel => 'r',
+ prorettype => 'text', proargtypes => '', prosrc => 'pg_session_authn_id' },
{ oid => '744',
proname => 'array_eq', prorettype => 'bool',
diff --git a/src/test/authentication/t/001_password.pl b/src/test/authentication/t/001_password.pl
index 3e3079c824..f0bdeda52d 100644
--- a/src/test/authentication/t/001_password.pl
+++ b/src/test/authentication/t/001_password.pl
@@ -82,6 +82,10 @@ test_role($node, 'scram_role', 'trust', 0,
test_role($node, 'md5_role', 'trust', 0,
log_unlike => [qr/connection authenticated:/]);
+my $res =
+ $node->safe_psql('postgres', "SELECT pg_session_authn_id() IS NULL;");
+is($res, 't', "users with trust authentication have NULL authn_id");
+
# For plain "password" method, all users should also be able to connect.
reset_pg_hba($node, 'password');
test_role($node, 'scram_role', 'password', 0,
@@ -91,6 +95,13 @@ test_role($node, 'md5_role', 'password', 0,
log_like =>
[qr/connection authenticated: identity="md5_role" method=password/]);
+$res = $node->safe_psql(
+ 'postgres',
+ "SELECT pg_session_authn_id();",
+ connstr => "user=md5_role");
+is($res, 'md5_role',
+ "users with md5 authentication have authn_id matching role name");
+
# For "scram-sha-256" method, user "scram_role" should be able to connect.
reset_pg_hba($node, 'scram-sha-256');
test_role(
diff --git a/src/test/ssl/t/001_ssltests.pl b/src/test/ssl/t/001_ssltests.pl
index 5c5b16fbe7..79ef7b46f1 100644
--- a/src/test/ssl/t/001_ssltests.pl
+++ b/src/test/ssl/t/001_ssltests.pl
@@ -443,6 +443,13 @@ $node->connect_ok(
qr/connection authenticated: identity="CN=ssltestuser-dn,OU=Testing,OU=Engineering,O=PGDG" method=cert/
],);
+# Sanity-check pg_session_authn_id() for long ID strings
+my $res = $node->safe_psql('postgres',
+ "SELECT pg_session_authn_id();",
+ connstr => "$dn_connstr user=ssltestuser sslcert=ssl/client-dn.crt sslkey=$key{'client-dn.key'}",
+);
+is($res, "CN=ssltestuser-dn,OU=Testing,OU=Engineering,O=PGDG", "users with cert authentication have entire DN as authn_id");
+
# same thing but with a regex
$dn_connstr = "$common_connstr dbname=certdb_dn_re";
--
2.25.1
