set_user 3.0 still use superuser_whitelist parameter instead of superuser_allowlist #56
Comments
|
Thanks for the report @Jamespsql. Do you mind sharing the configuration you had and the expected functionality/what you saw? The example above does not have any |
|
This is a new project that need security compliance, so we use pgaudit and set_user, any dba individual IDs need to escalate to superuser and everything by dba need audit. Postgres13.4 , pgaudit 1.5, set_user 3.0, all rpms got downloaded from download.postgresql.org. pgaudit works as expected, but when I test set_user, following pgaudit/set_user: PostgreSQL extension allowing privilege escalation with enhanced logging and control (github.com)<https://github.com/pgaudit/set_user>.
1. Install rpm and create extension
=# \dx
List of installed extensions
Name | Version | Schema | Description
…--------------------+---------+------------+------------------------------------------------------------
-----------------------------------
…
pgaudit | 1.5 | public | provides auditing functionality
set_user | 3.0 | public | similar to SET ROLE but with added logging
(14 rows)
shared_preload_libraries = 'orafce,pgaudit,pg_cron,pg_stat_statements,pg_prewarm,set_user'
#set_user
#set_user.superuser_whitelist = '+dba'
set_user.superuser_allowlist = '+dba'
set_user.block_log_statement=on
#set_user.nosuperuser_target_whitelist = ''
set_user.nosuperuser_target_allowlist = ''
1. Restart postgres and start test
t=# create user dbatest password ‘testest’;
CREATE ROLE
\c jamet dbatest localhost
Password for user dbatest:
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
You are now connected to database "jamet" as user "dbatest" on host "localhost" (address "127.0.0.1") at port "5432".
jamet=> select current_user,session_user;
current_user | session_user
--------------+--------------
dbatest | dbatest
(1 row)
t=> select set_user_u('postgres');
ERROR: permission denied for function set_user_u
switch to postgres user to grant permission
t=# GRANT EXECUTE ON FUNCTION set_user_u(text) TO dbatest;
GRANT
jamet=> select set_user_u('postgres');
set_user_u
------------
OK
(1 row)
jamet=# select current_user,session_user;
current_user | session_user
--------------+--------------
postgres | dbatest
(1 row)
--here it’s not expected, because ‘dbatest’ user does NOT have ‘dba’ role, per the parameter set_user.superuser_allowlist = '+dba'.
1. Comment out set_user.superuser_allowlist = '+dba' and use
set_user.superuser_whitelist = '+dba'
#set_user.superuser_allowlist = '+dba'
Restart postgres
jamet=> select current_user,session_user;
current_user | session_user
--------------+--------------
dbatest | dbatest
(1 row)
jamet=> select set_user_u('postgres');
ERROR: switching to superuser not allowed
HINT: Add current user to set_user.superuser_allowlist.
Open a new session with postgres user and grant ‘dba’ role to user ‘dbatest’
jamet=# grant dba to dbatest;
GRANT ROLE
jamet=> select set_user_u('postgres');
set_user_u
------------
OK
(1 row)
jamet=# select current_user,session_user;
current_user | session_user
--------------+--------------
postgres | dbatest
(1 row)
--until here, it works as expected, only users who belong to dba role can switch to superuser, with old set_user.superuser_whitelist instead of set_user.superuser_allowlist suggested in the readme.
Regards,
James
From: Mike Palmiotto ***@***.***>
Sent: Wednesday, December 15, 2021 6:49 AM
To: pgaudit/set_user ***@***.***>
Cc: James Pang (chaolpan) ***@***.***>; Mention ***@***.***>
Subject: Re: [pgaudit/set_user] set_user 3.0 still use superuser_whitelist parameter instead of superuser_allowlist (Issue #56)
Thanks for the report @Jamespsql<https://github.com/Jamespsql>. Do you mind sharing the configuration you had and the expected functionality/what you saw? The example above does not have any target_allowlist variables defined (they're all commented out).
—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub<#56 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AW3VFRCV3ZNOY6GLDCIAXC3UQ7CWXANCNFSM5JYJ56PQ>.
Triage notifications on the go with GitHub Mobile for iOS<https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android<https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.
|
|
Thanks for the detailed response! I've gone ahead and tested this out locally on my 12.9 instance, since it's what I have up at the moment. Your test scenario worked as expected for the I'll try to reproduce with your exact setup when I have some time, but in the meantime, I just want to verify some assumptions:
I appreciate your patience. We'll get this sorted out as soon as possible. |
|
Everytime , I changed parameters in postgresql.conf , I did pg_ctl stop/start, that will restart all processes including postgres.
1. grep set_user /pgdata/postgresql.conf
#shared_preload_libraries = 'orafce,pgaudit,pg_cron,pg_stat_statements,pg_prewarm,set_user'
shared_preload_libraries = 'orafce,pgaudit,pg_cron,pg_stat_statements,pg_prewarm,set_user'
#set_user
#set_user.superuser_whitelist = '+dba'
set_user.superuser_allowlist = '+dba'
set_user.block_log_statement=on
#set_user.nosuperuser_target_whitelist = ''
set_user.nosuperuser_target_allowlist = ''
1. pg_ctl stop, pg_ctl_start
postgres 44652 1 0 03:00 ? 00:00:00 /usr/pgsql-13/bin/postgres --- I see postgres got restarted
postgres 44653 44652 0 03:00 ? 00:00:00 postgres: logger
postgres 44660 44652 0 03:00 ? 00:00:00 postgres: checkpointer
postgres 44661 44652 0 03:00 ? 00:00:00 postgres: background writer
postgres 44662 44652 0 03:00 ? 00:00:00 postgres: walwriter
postgres 44663 44652 0 03:00 ? 00:00:00 postgres: autovacuum launcher
postgres 44664 44652 0 03:00 ? 00:00:00 postgres: archiver
postgres 44665 44652 0 03:00 ? 00:00:00 postgres: stats collector
postgres 44666 44652 0 03:00 ? 00:00:00 postgres: autoprewarm master
postgres 44667 44652 0 03:00 ? 00:00:00 postgres: pg_cron launcher
postgres 44668 44652 0 03:00 ? 00:00:00 postgres: logical replication launcher
t=# \c jamet dbatest localhost
Password for user dbatest:
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
You are now connected to database "jamet" as user "dbatest" on host "localhost" (address "127.0.0.1") at port "5432".
jamet=> select current_user,session_user;
current_user | session_user
…--------------+--------------
dbatest | dbatest
(1 row)
jamet=> select set_user_u('postgres');
set_user_u
------------
OK
(1 row)
jamet=# \du dbatest
List of roles
Role name | Attributes | Member of
-----------+------------+-----------
dbatest | | {}
jamet=# select name,setting from pg_settings where name like 'set_user%';
name | setting
---------------------------------------+---------
set_user.block_alter_system | on
set_user.block_copy_program | on
set_user.block_log_statement | on
set_user.nosuperuser_target_allowlist | *
set_user.superuser_allowlist | *
set_user.superuser_audit_tag | AUDIT
(6 rows)
--it’s not expected here , since I set set_user.superuser_allowlist=’+dba’, and not sure why pg_settings show ‘*’;
Again , comment out set_user.superuser_allowlist=’+dba’ use set_user.superuser_whitelist=’+dba’ , pg_ctl stop, pg_ctl start , it works again. There is nothing in postgresqlauto.conf .
Regards,
James
From: Mike Palmiotto ***@***.***>
Sent: Wednesday, December 15, 2021 10:57 AM
To: pgaudit/set_user ***@***.***>
Cc: James Pang (chaolpan) ***@***.***>; Mention ***@***.***>
Subject: Re: [pgaudit/set_user] set_user 3.0 still use superuser_whitelist parameter instead of superuser_allowlist (Issue #56)
Thanks for the detailed response! I've gone ahead and tested this out locally on my 12.9 instance, since it's what I have up at the moment. Your test scenario worked as expected for the allowlist variable.
I'll try to reproduce with your exact setup when I have some time, but in the meantime, I just want to verify some assumptions:
1. The behavior you're describing could result from an unset allowlist/whitelist after installing set_user. Could you add a SHOW set_user.superuser_allowlist to your test thread prior to the initial attempt to escalate?
2. Is it possible you did not issue a sighup (or the restart did not actually restart postgres) prior to the initial escalation attempt?
3. Is it possible you have some settings in postgresql.auto.conf?
4. What does the output of \du show? (just for grins)
I appreciate your patience. We'll get this sorted out as soon as possible.
—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub<#56 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AW3VFRDPZENHO7SEIE6D6W3UQ77Y7ANCNFSM5JYJ56PQ>.
Triage notifications on the go with GitHub Mobile for iOS<https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android<https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.
|
|
**Hello, I am having a similar issue with set_user 3,0 running postgresql 14.4 on Windows 2019, When set_user.superuser_allowlist is used in postgresql.conf it does not load on startup of the database and defaults to "*". When using set_user.superuser_whitelist instead in postgresql.conf, it loads correctly on database startup and also when using pg_reload_conf(); See TEST CASE 2 Test case 1 Database instance started or rebounced. select * from pg_settings where name ~'set_user'; set_user.superuser_allowlist is set to "*" even if postgresql.conf is set to +ftier3. show set_user.superuser_allowlist; show set_user.superuser_whitelist; When reloading instance pg_reload_conf() it used set_user.superuser_allowlist correctly in postgresql.conf select pg_reload_conf(); select * from pg_settings where name ~'set_user'; show set_user.superuser_allowlist; show set_user.superuser_whitelist; When specifying set_user.superuser_allowlist in postgresql.conf, the parameter is only used when the Test case 2 select * from pg_settings where name ~'set_user'; show set_user.superuser_allowlist; show set_user.superuser_whitelist; When specifying set_user.superuser_whitelist in postgresql.conf, the parameter is always used Regards, |
|
Sorry for slow replies. Considering the pain that this deprecation has caused, and the lack of support (as far as I'm aware) for deprecation of extension GUCs, I'm considering reverting all of the deprecation changes and just making the @Yaco312 @Jamespsql do you see this as an acceptable solution? |
|
Hello Mike, Yes that's fine with me. @mpalmi, would be the timeline for this fix? Thanks! |
|
That’s ok .
James
From: Mike Palmiotto ***@***.***>
Sent: Thursday, August 4, 2022 8:45 PM
To: pgaudit/set_user ***@***.***>
Cc: James Pang (chaolpan) ***@***.***>; Mention ***@***.***>
Subject: Re: [pgaudit/set_user] set_user 3.0 still use superuser_whitelist parameter instead of superuser_allowlist (Issue #56)
Sorry for slow replies. Considering the pain that this deprecation has caused, and the lack of support (as far as I'm aware) for deprecation of extension GUCs, I'm considering reverting all of the deprecation changes and just making the allowlist the only GUC.
@Yaco312<https://github.com/Yaco312> @Jamespsql<https://github.com/Jamespsql> do you see this as an acceptable solution?
—
Reply to this email directly, view it on GitHub<#56 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AW3VFRCC63WUPQ3NOCIIHQTVXO3OBANCNFSM5JYJ56PQ>.
You are receiving this because you were mentioned.Message ID: ***@***.******@***.***>>
|
It's set_user 3.0, we want to leverage set_user to escalate to superuser for individual DBAs IDs. , frollow the readme , shoulde use set_user.superuser_allowlist, but per my test, set_user.superuser_whitelist doest not work, still need to use old version's parameter
set_user.superuser_whitelist.
set_user.superuser_whitelist = '+dbadmin'
#set_user.superuser_allowlist = '+dbadmin'
set_user.block_log_statement=on
set_user.nosuperuser_target_whitelist = ''
#set_user.nosuperuser_target_allowlist = ''
The text was updated successfully, but these errors were encountered: