Detections
Analytics

4.6 Ensure the set_user extension is installed

Warning! Audit Deprecated

This audit has been deprecated and will be removed in a future update.

View Next Audit Version

Information

PostgreSQL access to the superuser database role must be controlled and audited to prevent unauthorized access.

Note: Prior to performing this audit you must create a roletree view. Here are the procedures to create this view:

postgres=#

DROP VIEW IF EXISTS roletree;

CREATE OR REPLACE VIEW roletree AS

WITH RECURSIVE

roltree AS (

 SELECT u.rolname AS rolname,

 u.oid AS roloid,

 u.rolcanlogin,

 u.rolsuper,

 '{}'::name[] AS rolparents,

 NULL::oid AS parent_roloid,

 NULL::name AS parent_rolname

 FROM pg_catalog.pg_authid u

 LEFT JOIN pg_catalog.pg_auth_members m on u.oid = m.member

 LEFT JOIN pg_catalog.pg_authid g on m.roleid = g.oid

 WHERE g.oid IS NULL

 UNION ALL

 SELECT u.rolname AS rolname,

 u.oid AS roloid,

 u.rolcanlogin,

 u.rolsuper,

 t.rolparents || g.rolname AS rolparents,

 g.oid AS parent_roloid,

 g.rolname AS parent_rolname

 FROM pg_catalog.pg_authid u

 JOIN pg_catalog.pg_auth_members m on u.oid = m.member

 JOIN pg_catalog.pg_authid g on m.roleid = g.oid

 JOIN roltree t on t.roloid = g.oid

)

SELECT

 r.rolname,

 r.roloid,

 r.rolcanlogin,

 r.rolsuper,

 r.rolparents

FROM roltree r

ORDER BY 1;

Rationale:

Even when reducing and limiting the access to the superuser role as described earlier in this benchmark, it is still difficult to determine who accessed the superuser role and what actions were taken using that role. As such, it is ideal to prevent anyone from logging in as the superuser and forcing them to escalate their role. This model is used at the OS level by the use of sudo and should be emulated in the database. The set_user extension allows for this setup.

Impact:

Much like the venerable sudo does for the OS, set_user manages superuser access for PostgreSQL. The complete configuration of set_user is documented at the extension's website and should be reviewed to ensure the logging entries that your organization cares about are properly configured.

Note that some external tools assume they can connect as the postgres user by default and this is no longer true when set_user is deployed. You may find some tools need different options, reconfigured, or even abandoned to compensate for this.

Solution

We will install the set_user extension:

# whoami
root
# dnf -y install set_user_16
[snip]
Installed:
 set_user_16-4.0.1-2.rhel9.1.x86_64

Complete!

Now that set_user is installed, we need to tell PostgreSQL to load its library:

# whoami
root
# vi ~postgres/16/data/postgresql.conf

Find the shared_preload_libraries entry, and add 'set_user' to it (preserving any existing entries):

shared_preload_libraries = 'set_user'

OR

shared_preload_libraries = 'set_user,pgaudit,somethingelse'

Restart the PostgreSQL server for changes to take effect:

# systemctl restart postgresql-16
# systemctl status postgresql-16|grep 'ago$'
 Active: active (running) since [timestamp]; 1s ago

And now, we can install the extension with SQL:

# su - postgres
# psql
postgres=# select * from pg_available_extensions where name = 'set_user';
 name | default_version | installed_version | comment
---------+-----------------+-------------------+-----------------------------
set_user | 4.0.1 | | similar to SET ROLE but with
 | | | added logging
(1 row)

postgres=# create extension set_user;
CREATE EXTENSION
postgres=# select * from pg_available_extensions where name = 'set_user';
 name | default_version | installed_version | comment
---------+-----------------+-------------------+-----------------------------
set_user | 4.0.1 | 4.0.1 | similar to SET ROLE but with
 | | | added logging
(1 row)

Now, we use GRANT to configure each DBA role to allow it to use the set_user functions. In the example below, we will configure my db user doug. (You would do this for each DBA's normal user role.)

postgres=# grant execute on function set_user(text) to doug;
GRANT
postgres=# grant execute on function set_user_u(text) to doug;
GRANT

Connect to PostgreSQL as yourself and verify it works as expected:

# whoami
psql
# psql -U doug -d postgres -h 127.0.0.1
postgres=> select set_user('postgres');
ERROR: switching to superuser not allowed
HINT: Use 'set_user_u' to escalate.
postgres=> select set_user_u('postgres');
 set_user_u
------------
 OK
(1 row)
postgres=# select current_user, session_user;
 current_user | session_user
--------------+--------------
 postgres | doug
(1 row)
postgres=# select reset_user();
 reset_user
------------
 OK
(1 row)
postgres=> select current_user, session_user;
 current_user | session_user
--------------+--------------
 doug | doug
(1 row)

Once all DBA's normal user accounts have been GRANTed permission, revoke the ability to login as the postgres (superuser) user:

postgres=# ALTER USER postgres NOLOGIN;
ALTER ROLE

Which results in:

$ psql
psql: FATAL: role 'postgres' is not permitted to log in
$ psql -U doug -d postgres -h 127.0.0.1
psql (16.0)

Revoke SUPERUSER and/or LOGIN from any other roles that were previously identified:

postgres=# ALTER USER usera NOSUPERUSER; -- revoke superuser
ALTER ROLE
postgres=# ALTER USER usera NOLOGIN; -- revoke login
ALTER ROLE
postgres=# ALTER USER usera NOSUPERUSER NOLOGIN; -- revoke both at once
ALTER ROLE

Note that we show dropping the privileges both individually and as one. Pick an appropriate version based on your application/business needs.

Remove any escalated privileges on users granted indirectly that were previously identified using the roletree view:

postgres=# REVOKE name_of_granting_role FROM bob; -- an example only
REVOKE ROLE

See Also

https://workbench.cisecurity.org/benchmarks/14977

Item Details

References: CSCv7|4.3

Plugin: PostgreSQLDB

Control ID: 043b0044cf6e985d0000e2a4efd9d4b4e6ab3208fd49d492928aaacd55fd65d4