Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit0823d06

Browse files
committed
Introduce SYSTEM_USER
SYSTEM_USER is a reserved keyword of the SQL specification that,roughly described, is aimed at reporting some information about thesystem user who has connected to the database server. It may includeimplementation-specific information about the means by the userconnected, like an authentication method.This commit implements SYSTEM_USER as of auth_method:identity, where"auth_method" is a keyword about the authentication method used to loginto the server (like peer, md5, scram-sha-256, gss, etc.) and"identity" is the authentication identity as introduced by 9afffcb (peersets authn to the OS user name, gss to the user principal, etc.). Thisformat has been suggested by Tom Lane.Note that thanks tod951052, SYSTEM_USER is available to parallelworkers.Bump catalog version.Author: Bertrand DrouvotReviewed-by: Jacob Champion, Joe Conway, Álvaro Herrera, Michael PaquierDiscussion:https://postgr.es/m/7e692b8c-0b11-45db-1cad-3afc5b57409f@amazon.com
1 parent5ac9e86 commit0823d06

File tree

14 files changed

+177
-7
lines changed

14 files changed

+177
-7
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -22623,6 +22623,25 @@ SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
2262322623
</para></entry>
2262422624
</row>
2262522625

22626+
<row>
22627+
<entry role="func_table_entry"><para role="func_signature">
22628+
<indexterm>
22629+
<primary>system_user</primary>
22630+
</indexterm>
22631+
<function>system_user</function>
22632+
<returnvalue>text</returnvalue>
22633+
</para>
22634+
<para>
22635+
Returns the authentication method and the identity (if any) that the
22636+
user presented during the authentication cycle before they were
22637+
assigned a database role. It is represented as
22638+
<literal>auth_method:identity</literal> or
22639+
<literal>NULL</literal> if the user has not been authenticated (for
22640+
example if <link linkend="auth-trust">Trust authentication</link> has
22641+
been used).
22642+
</para></entry>
22643+
</row>
22644+
2262622645
<row>
2262722646
<entry role="func_table_entry"><para role="func_signature">
2262822647
<indexterm>

‎src/backend/access/transam/parallel.c

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1496,6 +1496,14 @@ ParallelWorkerMain(Datum main_arg)
14961496
false);
14971497
RestoreClientConnectionInfo(clientconninfospace);
14981498

1499+
/*
1500+
* Initialize SystemUser now that MyClientConnectionInfo is restored.
1501+
* Also ensure that auth_method is actually valid, aka authn_id is not NULL.
1502+
*/
1503+
if (MyClientConnectionInfo.authn_id)
1504+
InitializeSystemUser(MyClientConnectionInfo.authn_id,
1505+
hba_authname(MyClientConnectionInfo.auth_method));
1506+
14991507
/* Attach to the leader's serializable transaction, if SERIALIZABLE. */
15001508
AttachSerializableXact(fps->serializable_xact_handle);
15011509

‎src/backend/parser/gram.y

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -743,7 +743,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
743743
SERIALIZABLE SERVER SESSION SESSION_USER SET SETS SETOF SHARE SHOW
744744
SIMILAR SIMPLE SKIP SMALLINT SNAPSHOT SOME SQL_P STABLE STANDALONE_P
745745
START STATEMENT STATISTICS STDIN STDOUT STORAGE STORED STRICT_P STRIP_P
746-
SUBSCRIPTION SUBSTRING SUPPORT SYMMETRIC SYSID SYSTEM_P
746+
SUBSCRIPTION SUBSTRING SUPPORT SYMMETRIC SYSID SYSTEM_P SYSTEM_USER
747747

748748
TABLE TABLES TABLESAMPLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN
749749
TIES TIME TIMESTAMP TO TRAILING TRANSACTION TRANSFORM
@@ -15239,6 +15239,13 @@ func_expr_common_subexpr:
1523915239
{
1524015240
$$ = makeSQLValueFunction(SVFOP_SESSION_USER, -1,@1);
1524115241
}
15242+
|SYSTEM_USER
15243+
{
15244+
$$ = (Node *) makeFuncCall(SystemFuncName("system_user"),
15245+
NIL,
15246+
COERCE_SQL_SYNTAX,
15247+
@1);
15248+
}
1524215249
|USER
1524315250
{
1524415251
$$ = makeSQLValueFunction(SVFOP_USER, -1,@1);
@@ -17120,6 +17127,7 @@ reserved_keyword:
1712017127
| SESSION_USER
1712117128
| SOME
1712217129
| SYMMETRIC
17130+
| SYSTEM_USER
1712317131
| TABLE
1712417132
| THEN
1712517133
| TO
@@ -17500,6 +17508,7 @@ bare_label_keyword:
1750017508
| SYMMETRIC
1750117509
| SYSID
1750217510
| SYSTEM_P
17511+
| SYSTEM_USER
1750317512
| TABLE
1750417513
| TABLES
1750517514
| TABLESAMPLE

‎src/backend/utils/adt/ruleutils.c

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -10317,6 +10317,10 @@ get_func_sql_syntax(FuncExpr *expr, deparse_context *context)
1031710317
appendStringInfoChar(buf,')');
1031810318
return true;
1031910319

10320+
caseF_SYSTEM_USER:
10321+
appendStringInfoString(buf,"SYSTEM_USER");
10322+
return true;
10323+
1032010324
caseF_XMLEXISTS:
1032110325
/* XMLEXISTS ... extra parens because args are c_expr */
1032210326
appendStringInfoString(buf,"XMLEXISTS((");

‎src/backend/utils/init/miscinit.c

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -477,6 +477,7 @@ static OidAuthenticatedUserId = InvalidOid;
477477
staticOidSessionUserId=InvalidOid;
478478
staticOidOuterUserId=InvalidOid;
479479
staticOidCurrentUserId=InvalidOid;
480+
staticconstchar*SystemUser=NULL;
480481

481482
/* We also have to remember the superuser state of some of these levels */
482483
staticboolAuthenticatedUserIsSuperuser= false;
@@ -548,6 +549,16 @@ SetSessionUserId(Oid userid, bool is_superuser)
548549
CurrentUserId=userid;
549550
}
550551

552+
/*
553+
* Return the system user representing the authenticated identity.
554+
* It is defined in InitializeSystemUser() as auth_method:authn_id.
555+
*/
556+
constchar*
557+
GetSystemUser(void)
558+
{
559+
returnSystemUser;
560+
}
561+
551562
/*
552563
* GetAuthenticatedUserId - get the authenticated user ID
553564
*/
@@ -818,6 +829,45 @@ InitializeSessionUserIdStandalone(void)
818829
SetSessionUserId(BOOTSTRAP_SUPERUSERID, true);
819830
}
820831

832+
/*
833+
* Initialize the system user.
834+
*
835+
* This is built as auth_method:authn_id.
836+
*/
837+
void
838+
InitializeSystemUser(constchar*authn_id,constchar*auth_method)
839+
{
840+
char*system_user;
841+
842+
/* call only once */
843+
Assert(SystemUser==NULL);
844+
845+
/*
846+
* InitializeSystemUser should be called only when authn_id is not NULL,
847+
* meaning that auth_method is valid.
848+
*/
849+
Assert(authn_id!=NULL);
850+
851+
system_user=psprintf("%s:%s",auth_method,authn_id);
852+
853+
/* Store SystemUser in long-lived storage */
854+
SystemUser=MemoryContextStrdup(TopMemoryContext,system_user);
855+
pfree(system_user);
856+
}
857+
858+
/*
859+
* SQL-function SYSTEM_USER
860+
*/
861+
Datum
862+
system_user(PG_FUNCTION_ARGS)
863+
{
864+
constchar*sysuser=GetSystemUser();
865+
866+
if (sysuser)
867+
PG_RETURN_DATUM(CStringGetTextDatum(sysuser));
868+
else
869+
PG_RETURN_NULL();
870+
}
821871

822872
/*
823873
* Change session auth ID while running

‎src/backend/utils/init/postinit.c

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -904,6 +904,10 @@ InitPostgres(const char *in_dbname, Oid dboid,
904904
Assert(MyProcPort!=NULL);
905905
PerformAuthentication(MyProcPort);
906906
InitializeSessionUserId(username,useroid);
907+
/* ensure that auth_method is actually valid, aka authn_id is not NULL */
908+
if (MyClientConnectionInfo.authn_id)
909+
InitializeSystemUser(MyClientConnectionInfo.authn_id,
910+
hba_authname(MyClientConnectionInfo.auth_method));
907911
am_superuser=superuser();
908912
}
909913

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/*yyyymmddN */
60-
#defineCATALOG_VERSION_NO202209261
60+
#defineCATALOG_VERSION_NO202209291
6161

6262
#endif

‎src/include/catalog/pg_proc.dat

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1508,6 +1508,9 @@
15081508
{ oid => '746', descr => 'session user name',
15091509
proname => 'session_user', provolatile => 's', prorettype => 'name',
15101510
proargtypes => '', prosrc => 'session_user' },
1511+
{ oid => '9977', descr => 'system user name',
1512+
proname => 'system_user', provolatile => 's', prorettype => 'text',
1513+
proargtypes => '', prosrc => 'system_user' },
15111514

15121515
{ oid => '744',
15131516
proname => 'array_eq', prorettype => 'bool',

‎src/include/miscadmin.h

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -357,6 +357,9 @@ extern void InitializeSessionUserIdStandalone(void);
357357
externvoidSetSessionAuthorization(Oiduserid,boolis_superuser);
358358
externOidGetCurrentRoleId(void);
359359
externvoidSetCurrentRoleId(Oidroleid,boolis_superuser);
360+
externvoidInitializeSystemUser(constchar*authn_id,
361+
constchar*auth_method);
362+
externconstchar*GetSystemUser(void);
360363

361364
/* in utils/misc/superuser.c */
362365
externboolsuperuser(void);/* current user is superuser */

‎src/include/parser/kwlist.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -409,6 +409,7 @@ PG_KEYWORD("support", SUPPORT, UNRESERVED_KEYWORD, BARE_LABEL)
409409
PG_KEYWORD("symmetric",SYMMETRIC,RESERVED_KEYWORD,BARE_LABEL)
410410
PG_KEYWORD("sysid",SYSID,UNRESERVED_KEYWORD,BARE_LABEL)
411411
PG_KEYWORD("system",SYSTEM_P,UNRESERVED_KEYWORD,BARE_LABEL)
412+
PG_KEYWORD("system_user",SYSTEM_USER,RESERVED_KEYWORD,BARE_LABEL)
412413
PG_KEYWORD("table",TABLE,RESERVED_KEYWORD,BARE_LABEL)
413414
PG_KEYWORD("tables",TABLES,UNRESERVED_KEYWORD,BARE_LABEL)
414415
PG_KEYWORD("tablesample",TABLESAMPLE,TYPE_FUNC_NAME_KEYWORD,BARE_LABEL)

‎src/test/authentication/t/001_password.pl

Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -72,6 +72,11 @@ sub test_role
7272
$node->safe_psql('postgres',
7373
"SET password_encryption='md5'; CREATE ROLE md5_role LOGIN PASSWORD 'pass';"
7474
);
75+
# Set up a table for tests of SYSTEM_USER.
76+
$node->safe_psql(
77+
'postgres',
78+
"CREATE TABLE sysuser_data (n) AS SELECT NULL FROM generate_series(1, 10);
79+
GRANT ALL ON sysuser_data TO md5_role;");
7580
$ENV{"PGPASSWORD"} ='pass';
7681

7782
# For "trust" method, all users should be able to connect. These users are not
@@ -82,6 +87,24 @@ sub test_role
8287
test_role($node,'md5_role','trust', 0,
8388
log_unlike=> [qr/connection authenticated:/]);
8489

90+
# SYSTEM_USER is null when not authenticated.
91+
my$res =$node->safe_psql('postgres',"SELECT SYSTEM_USER IS NULL;");
92+
is($res,'t',"users with trust authentication use SYSTEM_USER = NULL");
93+
94+
# Test SYSTEM_USER with parallel workers when not authenticated.
95+
$res =$node->safe_psql(
96+
'postgres',qq(
97+
SET min_parallel_table_scan_size TO 0;
98+
SET parallel_setup_cost TO 0;
99+
SET parallel_tuple_cost TO 0;
100+
SET max_parallel_workers_per_gather TO 2;
101+
102+
SELECT bool_and(SYSTEM_USER IS NOT DISTINCT FROM n) FROM sysuser_data;),
103+
connstr=>"user=md5_role");
104+
is($res,'t',
105+
"users with trust authentication use SYSTEM_USER = NULL in parallel workers"
106+
);
107+
85108
# For plain "password" method, all users should also be able to connect.
86109
reset_pg_hba($node,'password');
87110
test_role($node,'scram_role','password', 0,
@@ -120,6 +143,25 @@ sub test_role
120143
log_like=>
121144
[qr/connection authenticated: identity="md5_role" method=md5/]);
122145

146+
# Test SYSTEM_USER <> NULL with parallel workers.
147+
$node->safe_psql(
148+
'postgres',
149+
"TRUNCATE sysuser_data;
150+
INSERT INTO sysuser_data SELECT 'md5:md5_role' FROM generate_series(1, 10);",
151+
connstr=>"user=md5_role");
152+
$res =$node->safe_psql(
153+
'postgres',qq(
154+
SET min_parallel_table_scan_size TO 0;
155+
SET parallel_setup_cost TO 0;
156+
SET parallel_tuple_cost TO 0;
157+
SET max_parallel_workers_per_gather TO 2;
158+
159+
SELECT bool_and(SYSTEM_USER IS NOT DISTINCT FROM n) FROM sysuser_data;),
160+
connstr=>"user=md5_role");
161+
is($res,'t',
162+
"users with md5 authentication use SYSTEM_USER = md5:role in parallel workers"
163+
);
164+
123165
# Tests for channel binding without SSL.
124166
# Using the password authentication method; channel binding can't work
125167
reset_pg_hba($node,'password');

‎src/test/kerberos/t/001_auth.pl

Lines changed: 26 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4,8 +4,8 @@
44
# Sets up a KDC and then runs a variety of tests to make sure that the
55
# GSSAPI/Kerberos authentication and encryption are working properly,
66
# that the options in pg_hba.conf and pg_ident.conf are handled correctly,
7-
#andthat the server-side pg_stat_gssapi view reports what we expect to
8-
# see for each test.
7+
# that the server-side pg_stat_gssapi view reports what we expect to
8+
# see for each test and that SYSTEM_USER returns what we expect to see.
99
#
1010
# Since this requires setting up a full KDC, it doesn't make much sense
1111
# to have multiple test scripts (since they'd have to also create their
@@ -180,6 +180,13 @@ END
180180

181181
$node->safe_psql('postgres','CREATE USER test1;');
182182

183+
# Set up a table for SYSTEM_USER parallel worker testing.
184+
$node->safe_psql('postgres',
185+
"CREATE TABLE ids (id) AS SELECT 'gss:test1\@$realm' FROM generate_series(1, 10);"
186+
);
187+
188+
$node->safe_psql('postgres','GRANT SELECT ON ids TO public;');
189+
183190
note"running tests";
184191

185192
# Test connection success or failure, and if success, that query returns true.
@@ -311,6 +318,23 @@ sub test_query
311318
'gssencmode=require',
312319
'sending 100K lines works');
313320

321+
# Test that SYSTEM_USER works.
322+
test_query($node,'test1','SELECT SYSTEM_USER;',
323+
qr/^gss:test1\@$realm$/s,'gssencmode=require','testing system_user');
324+
325+
# Test that SYSTEM_USER works with parallel workers.
326+
test_query(
327+
$node,
328+
'test1',qq(
329+
SET min_parallel_table_scan_size TO 0;
330+
SET parallel_setup_cost TO 0;
331+
SET parallel_tuple_cost TO 0;
332+
SET max_parallel_workers_per_gather TO 2;
333+
SELECT bool_and(SYSTEM_USER = id) FROM ids;),
334+
qr/^t$/s,
335+
'gssencmode=require',
336+
'testing system_user with parallel workers');
337+
314338
unlink($node->data_dir .'/pg_hba.conf');
315339
$node->append_conf('pg_hba.conf',
316340
qq{hostgssenc all all$hostaddr/32 gss map=mymap});

‎src/test/regress/expected/create_view.out

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1940,7 +1940,8 @@ select
19401940
trim(trailing ' foo ') as rt,
19411941
trim(E'\\000'::bytea from E'\\000Tom\\000'::bytea) as btb,
19421942
trim(leading E'\\000'::bytea from E'\\000Tom\\000'::bytea) as ltb,
1943-
trim(trailing E'\\000'::bytea from E'\\000Tom\\000'::bytea) as rtb;
1943+
trim(trailing E'\\000'::bytea from E'\\000Tom\\000'::bytea) as rtb,
1944+
SYSTEM_USER as su;
19441945
select pg_get_viewdef('tt201v', true);
19451946
pg_get_viewdef
19461947
-----------------------------------------------------------------------------------------------
@@ -1961,7 +1962,8 @@ select pg_get_viewdef('tt201v', true);
19611962
TRIM(TRAILING FROM ' foo '::text) AS rt, +
19621963
TRIM(BOTH '\x00'::bytea FROM '\x00546f6d00'::bytea) AS btb, +
19631964
TRIM(LEADING '\x00'::bytea FROM '\x00546f6d00'::bytea) AS ltb, +
1964-
TRIM(TRAILING '\x00'::bytea FROM '\x00546f6d00'::bytea) AS rtb;
1965+
TRIM(TRAILING '\x00'::bytea FROM '\x00546f6d00'::bytea) AS rtb, +
1966+
SYSTEM_USER AS su;
19651967
(1 row)
19661968

19671969
-- corner cases with empty join conditions

‎src/test/regress/sql/create_view.sql

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -721,7 +721,8 @@ select
721721
trim(trailing' foo')as rt,
722722
trim(E'\\000'::byteafrom E'\\000Tom\\000'::bytea)as btb,
723723
trim(leading E'\\000'::byteafrom E'\\000Tom\\000'::bytea)as ltb,
724-
trim(trailing E'\\000'::byteafrom E'\\000Tom\\000'::bytea)as rtb;
724+
trim(trailing E'\\000'::byteafrom E'\\000Tom\\000'::bytea)as rtb,
725+
SYSTEM_USERas su;
725726
select pg_get_viewdef('tt201v', true);
726727

727728
-- corner cases with empty join conditions

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp