On Fri, 2022-02-25 at 01:15 +0800, Julien Rouhaud wrote:
> On Thu, Feb 24, 2022 at 04:50:59PM +0000, Jacob Champion wrote:
> > On Thu, 2022-02-24 at 20:39 +0900, Michael Paquier wrote:
> > > I don't quite see the additional value that this API brings as
> > > MyProcPort is directly accessible, and contrib modules like
> > > postgres_fdw and sslinfo just use that to find the data of the current
> > > backend.
> >
> > Right -- I just didn't know if third-party modules were actually able
> > to rely on the internal layout of struct Port. Is that guaranteed to
> > remain constant for a major release line? If so, this new API is
> > superfluous.
>
> Yes, third-party can rely on Port layout. We don't break ABI between minor
> release. In some occasions we can add additional fields at the end of a
> struct, but nothing more.
That simplifies things. PFA a smaller v2; if pgaudit can't make use of
libpq-be.h for some reason, then I guess we can tailor a fix to that
use case.
> > > I could still see a use case for that at a more global level with
> > > beentrys, but it looked like there was not much interest the last time
> > > I dropped this idea.
> >
> > I agree that this would be useful to have in the stats. From my outside
> > perspective, it seems like it's difficult to get strings of arbitrary
> > length in there; is that accurate?
>
> Yes, as it's all in shared memory. The only workaround is using something
> like
> track_activity_query_size, but it's not great.
Yeah... I was following a similar track with the initial work last
year, but I dropped it when the cost of implementation started to grow
considerably. At the time, though, it looked like some overhauls to the
stats framework were being pursued? I should read up on that thread.
--Jacob
From 92679a2109be5ba4d81bf58e8fb091c2d0020828 Mon Sep 17 00:00:00 2001
From: Jacob Champion <pchamp...@vmware.com>
Date: Mon, 14 Feb 2022 08:10:53 -0800
Subject: [PATCH v2] 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..ff0e2829bb 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_CSTRING(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..ca52e6889c 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 202202231
#endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 7f1ee97f55..b76d357bee 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 => 'cstring',
+ 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
