pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jacob Champion <pchampion@vmware.com>
To: sfrost@snowman.net <sfrost@snowman.net>
To: michael@paquier.xyz <michael@paquier.xyz>
Cc: rjuju123@gmail.com <rjuju123@gmail.com>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Cc: andres@anarazel.de <andres@anarazel.de>
Subject: Re: [PATCH] Expose port->authn_id to extensions and triggers
Date: Tue, 1 Mar 2022 22:03:20 +0000
Message-ID: <9f9cde9f5f9b9ffc63a2c45c735e4129f19c8a42.camel@vmware.com> (raw)
In-Reply-To: <20220301133527.GS10577@tamriel.snowman.net>
References: <2e28b12b450f247d5c0994207030c862263e0297.camel@vmware.com>
	<20220224171538.jbf2cxzmhiwuinp4@jrouhaud>
	<a4d9baa0717ca924331cb1dd062788bd73bd14a4.camel@vmware.com>
	<d15d02c92a3e4bdb7cc7b96b0abfe97f213e4445.camel@vmware.com>
	<cdbcf8653e56340f73438e647ea5a26f021627ed.camel@vmware.com>
	<20220225212349.gf76klktl4dmm5ah@alap3.anarazel.de>
	<Yhm9BUjrCItExUJu@paquier.xyz>
	<0cead4e58257beebb96f162507151271ff2f7b54.camel@vmware.com>
	<20220228210036.GN10577@tamriel.snowman.net>
	<Yh3Czv8xFdUzFFfZ@paquier.xyz>
	<20220301133527.GS10577@tamriel.snowman.net>

On Tue, 2022-03-01 at 08:35 -0500, Stephen Frost wrote:
> * Michael Paquier (michael@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


Attachments:

v5-0001-Add-API-to-retrieve-authn_id-from-SQL.patch (text/x-patch, 5.9K) download From fd6e8a5b09b7facbecc8e38ef1f8a3d2cef866d4 Mon Sep 17 00:00:00 2001 From: Jacob Champion <pchampion@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

view thread (89+ messages)  latest in thread

Message-ID: <9f9cde9f5f9b9ffc63a2c45c735e4129f19c8a42.camel@vmware.com>
Permalink:  ../9f9cde9f5f9b9ffc63a2c45c735e4129f19c8a42.camel@vmware.com/
Also on:    postgresql.org/message-id/9f9cde9f5f9b9ffc63a2c45c735e4129f19c8a42.camel@vmware.com

 ·  · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-hackers@postgresql.org
  Cc: pchampion@vmware.com, sfrost@snowman.net, michael@paquier.xyz, rjuju123@gmail.com, andres@anarazel.de
  Subject: Re: [PATCH] Expose port->authn_id to extensions and triggers
  In-Reply-To: <9f9cde9f5f9b9ffc63a2c45c735e4129f19c8a42.camel@vmware.com>
  References: <2e28b12b450f247d5c0994207030c862263e0297.camel@vmware.com> <20220224171538.jbf2cxzmhiwuinp4@jrouhaud> <a4d9baa0717ca924331cb1dd062788bd73bd14a4.camel@vmware.com> <d15d02c92a3e4bdb7cc7b96b0abfe97f213e4445.camel@vmware.com> <cdbcf8653e56340f73438e647ea5a26f021627ed.camel@vmware.com> <20220225212349.gf76klktl4dmm5ah@alap3.anarazel.de> <Yhm9BUjrCItExUJu@paquier.xyz> <0cead4e58257beebb96f162507151271ff2f7b54.camel@vmware.com> <20220228210036.GN10577@tamriel.snowman.net> <Yh3Czv8xFdUzFFfZ@paquier.xyz> <20220301133527.GS10577@tamriel.snowman.net> <9f9cde9f5f9b9ffc63a2c45c735e4129f19c8a42.camel@vmware.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox