On Fri, 2022-02-25 at 16:28 +0000, Jacob Champion wrote: > Ha, opr_sanity caught my use of cstring. I'll work on a fix later > today.
Fixed in v3. --Jacob
From 2fde60a6bc3739f1894c8c264120e4fa0f04df64 Mon Sep 17 00:00:00 2001 From: Jacob Champion <pchamp...@vmware.com> Date: Mon, 14 Feb 2022 08:10:53 -0800 Subject: [PATCH v3] 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, session_authn_id(), to expose the field to triggers that may want to make use of it. --- src/backend/utils/adt/name.c | 12 +++++++++++- src/include/catalog/catversion.h | 2 +- src/include/catalog/pg_proc.dat | 3 +++ src/test/authentication/t/001_password.pl | 11 +++++++++++ 4 files changed, 26 insertions(+), 2 deletions(-) diff --git a/src/backend/utils/adt/name.c b/src/backend/utils/adt/name.c index e8bba3670c..b892d25c29 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, 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 +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/catversion.h b/src/include/catalog/catversion.h index 1addb568ef..14194afe1c 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202202221 +#define CATALOG_VERSION_NO 202202251 #endif diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 7f1ee97f55..3ddcbae55e 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 => 'session_authn_id', provolatile => 's', prorettype => 'text', + proargtypes => '', prosrc => '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..2aa28ed547 100644 --- a/src/test/authentication/t/001_password.pl +++ b/src/test/authentication/t/001_password.pl @@ -82,6 +82,11 @@ 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 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 +96,12 @@ test_role($node, 'md5_role', 'password', 0, log_like => [qr/connection authenticated: identity="md5_role" method=password/]); +$res = $node->safe_psql('postgres', + "SELECT 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( -- 2.25.1