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
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 478a216dbb..07bb333a3b 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -23660,6 +23660,20 @@ 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>system_user</primary>
+ </indexterm>
+ <function>system_user</function>
+ <returnvalue>name</returnvalue>
+ </para>
+ <para>
+ Returns the identity (if any) that the user presented during the
+ authentication cycle, before they were assigned a database role.
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
diff --git a/src/backend/executor/execExprInterp.c
b/src/backend/executor/execExprInterp.c
index e44ad68cda..63cf3d8bb7 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -2543,6 +2543,11 @@ ExecEvalSQLValueFunction(ExprState *state, ExprEvalStep
*op)
*op->resvalue = session_user(fcinfo);
*op->resnull = fcinfo->isnull;
break;
+ case SVFOP_SYSTEM_USER:
+ InitFunctionCallInfoData(*fcinfo, NULL, 0, InvalidOid,
NULL, NULL);
+ *op->resvalue = system_user(fcinfo);
+ *op->resnull = fcinfo->isnull;
+ break;
case SVFOP_CURRENT_CATALOG:
InitFunctionCallInfoData(*fcinfo, NULL, 0, InvalidOid,
NULL, NULL);
*op->resvalue = current_database(fcinfo);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 969c9c158f..29dcc77724 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -842,7 +842,7 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
SEQUENCE SEQUENCES SERIALIZABLE SERVER SESSION SESSION_USER SET SETS
SETOF
SHARE SHOW SIMILAR SIMPLE SKIP SMALLINT SNAPSHOT SOME SQL_P STABLE
STANDALONE_P
START STATEMENT STATISTICS STDIN STDOUT STORAGE STORED STRICT_P STRING
STRIP_P
- SUBSCRIPTION SUBSTRING SUPPORT SYMMETRIC SYSID SYSTEM_P
+ SUBSCRIPTION SUBSTRING SUPPORT SYMMETRIC SYSID SYSTEM_P SYSTEM_USER
TABLE TABLES TABLESAMPLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN
TIES TIME TIMESTAMP TO TRAILING TRANSACTION TRANSFORM
@@ -15457,6 +15457,10 @@ func_expr_common_subexpr:
{
$$ =
makeSQLValueFunction(SVFOP_SESSION_USER, -1, @1);
}
+ | SYSTEM_USER
+ {
+ $$ =
makeSQLValueFunction(SVFOP_SYSTEM_USER, -1, @1);
+ }
| USER
{
$$ = makeSQLValueFunction(SVFOP_USER,
-1, @1);
@@ -18173,6 +18177,7 @@ reserved_keyword:
| SESSION_USER
| SOME
| SYMMETRIC
+ | SYSTEM_USER
| TABLE
| THEN
| TO
@@ -18578,6 +18583,7 @@ bare_label_keyword:
| SYMMETRIC
| SYSID
| SYSTEM_P
+ | SYSTEM_USER
| TABLE
| TABLES
| TABLESAMPLE
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 0dc2fc472e..8256cc177d 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -2299,6 +2299,7 @@ transformSQLValueFunction(ParseState *pstate,
SQLValueFunction *svf)
case SVFOP_CURRENT_USER:
case SVFOP_USER:
case SVFOP_SESSION_USER:
+ case SVFOP_SYSTEM_USER:
case SVFOP_CURRENT_CATALOG:
case SVFOP_CURRENT_SCHEMA:
svf->type = NAMEOID;
diff --git a/src/backend/parser/parse_target.c
b/src/backend/parser/parse_target.c
index 2a1d44b813..8ad50e31d2 100644
--- a/src/backend/parser/parse_target.c
+++ b/src/backend/parser/parse_target.c
@@ -1917,6 +1917,9 @@ FigureColnameInternal(Node *node, char **name)
case SVFOP_SESSION_USER:
*name = "session_user";
return 2;
+ case SVFOP_SYSTEM_USER:
+ *name = "system_user";
+ return 2;
case SVFOP_CURRENT_CATALOG:
*name = "current_catalog";
return 2;
diff --git a/src/backend/utils/adt/name.c b/src/backend/utils/adt/name.c
index e8bba3670c..f8e69fb7ec 100644
--- a/src/backend/utils/adt/name.c
+++ b/src/backend/utils/adt/name.c
@@ -257,7 +257,7 @@ namestrcmp(Name name, const char *str)
/*
- * SQL-functions CURRENT_USER, SESSION_USER
+ * SQL-functions CURRENT_USER, SESSION_USER, SYSTEM_USER
*/
Datum
current_user(PG_FUNCTION_ARGS)
@@ -271,6 +271,16 @@ session_user(PG_FUNCTION_ARGS)
PG_RETURN_DATUM(DirectFunctionCall1(namein,
CStringGetDatum(GetUserNameFromId(GetSessionUserId(), false))));
}
+Datum
+system_user(PG_FUNCTION_ARGS)
+{
+ const char *sysuser = GetSystemUser();
+
+ if (sysuser)
+ PG_RETURN_DATUM(DirectFunctionCall1(namein,
CStringGetDatum(GetSystemUser())));
+ else
+ return session_user(fcinfo);
+}
/*
* SQL-functions CURRENT_SCHEMA, CURRENT_SCHEMAS
diff --git a/src/backend/utils/adt/ruleutils.c
b/src/backend/utils/adt/ruleutils.c
index c3937a60fd..ffdd818f1e 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -9319,6 +9319,9 @@ get_rule_expr(Node *node, deparse_context *context,
case SVFOP_SESSION_USER:
appendStringInfoString(buf,
"SESSION_USER");
break;
+ case SVFOP_SYSTEM_USER:
+ appendStringInfoString(buf,
"SYSTEM_USER");
+ break;
case SVFOP_CURRENT_CATALOG:
appendStringInfoString(buf,
"CURRENT_CATALOG");
break;
diff --git a/src/backend/utils/init/miscinit.c
b/src/backend/utils/init/miscinit.c
index b25bd0e583..4984da23a7 100644
--- a/src/backend/utils/init/miscinit.c
+++ b/src/backend/utils/init/miscinit.c
@@ -473,6 +473,7 @@ static Oid AuthenticatedUserId = InvalidOid;
static Oid SessionUserId = InvalidOid;
static Oid OuterUserId = InvalidOid;
static Oid CurrentUserId = InvalidOid;
+static const char *SystemUser = NULL;
/* We also have to remember the superuser state of some of these levels */
static bool AuthenticatedUserIsSuperuser = false;
@@ -544,6 +545,16 @@ SetSessionUserId(Oid userid, bool is_superuser)
CurrentUserId = userid;
}
+/*
+ * Return the system user (the authenticated identity), which may be different
+ * from the session user.
+ */
+const char *
+GetSystemUser(void)
+{
+ return SystemUser;
+}
+
/*
* GetAuthenticatedUserId - get the authenticated user ID
*/
@@ -814,6 +825,19 @@ InitializeSessionUserIdStandalone(void)
SetSessionUserId(BOOTSTRAP_SUPERUSERID, true);
}
+/*
+ * Initialize the system user during normal backend startup.
+ */
+void
+InitializeSystemUser(const char *system_user)
+{
+ /* call only once */
+ Assert(SystemUser == NULL);
+
+ /* If system_user is not NULL, store it in long-lived storage */
+ if (system_user)
+ SystemUser = MemoryContextStrdup(TopMemoryContext, system_user);
+}
/*
* Change session auth ID while running
diff --git a/src/backend/utils/init/postinit.c
b/src/backend/utils/init/postinit.c
index 6b9082604f..a3199a17ea 100644
--- a/src/backend/utils/init/postinit.c
+++ b/src/backend/utils/init/postinit.c
@@ -835,6 +835,7 @@ InitPostgres(const char *in_dbname, Oid dboid, const char
*username,
Assert(MyProcPort != NULL);
PerformAuthentication(MyProcPort);
InitializeSessionUserId(username, useroid);
+ InitializeSystemUser(MyProcPort->authn_id);
am_superuser = superuser();
}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 87aa571a33..4406396055 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 => '786', descr => 'system user name',
+ proname => 'system_user', provolatile => 's', prorettype => 'name',
+ proargtypes => '', prosrc => 'system_user' },
{ oid => '744',
proname => 'array_eq', prorettype => 'bool',
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 0af130fbc5..de173c1387 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -364,6 +364,8 @@ extern void InitializeSessionUserIdStandalone(void);
extern void SetSessionAuthorization(Oid userid, bool is_superuser);
extern Oid GetCurrentRoleId(void);
extern void SetCurrentRoleId(Oid roleid, bool is_superuser);
+extern void InitializeSystemUser(const char *system_user);
+extern const char* GetSystemUser(void);
/* in utils/misc/superuser.c */
extern bool superuser(void); /* current user is superuser */
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 51505eee85..c23b0f2507 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -1186,6 +1186,7 @@ typedef enum SQLValueFunctionOp
SVFOP_CURRENT_USER,
SVFOP_USER,
SVFOP_SESSION_USER,
+ SVFOP_SYSTEM_USER,
SVFOP_CURRENT_CATALOG,
SVFOP_CURRENT_SCHEMA
} SQLValueFunctionOp;
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index ae35f03251..e309988b26 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -434,6 +434,7 @@ PG_KEYWORD("support", SUPPORT, UNRESERVED_KEYWORD,
BARE_LABEL)
PG_KEYWORD("symmetric", SYMMETRIC, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("sysid", SYSID, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("system", SYSTEM_P, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("system_user", SYSTEM_USER, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("table", TABLE, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("tables", TABLES, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("tablesample", TABLESAMPLE, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
diff --git a/src/test/kerberos/t/001_auth.pl b/src/test/kerberos/t/001_auth.pl
index 62e0542639..2f6e6e4d38 100644
--- a/src/test/kerberos/t/001_auth.pl
+++ b/src/test/kerberos/t/001_auth.pl
@@ -4,8 +4,8 @@
# Sets up a KDC and then runs a variety of tests to make sure that the
# GSSAPI/Kerberos authentication and encryption are working properly,
# that the options in pg_hba.conf and pg_ident.conf are handled correctly,
-# and that the server-side pg_stat_gssapi view reports what we expect to
-# see for each test.
+# that the server-side pg_stat_gssapi view reports what we expect to
+# see for each test and that SYSTEM_USER returns what we expect to see.
#
# Since this requires setting up a full KDC, it doesn't make much sense
# to have multiple test scripts (since they'd have to also create their
@@ -307,6 +307,15 @@ test_query(
'gssencmode=require',
'sending 100K lines works');
+# Test that SYSTEM_USER works.
+test_query(
+ $node,
+ 'test1',
+ 'SELECT CURRENT_USER,SYSTEM_USER;',
+ qr/^test1|test1\@$realm$/s,
+ 'gssencmode=require',
+ 'testing system_user');
+
unlink($node->data_dir . '/pg_hba.conf');
$node->append_conf('pg_hba.conf',
qq{hostgssenc all all $hostaddr/32 gss map=mymap});
