4.7 Ensure the set_user extension is installed

Information

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

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.

Solution

At the time this benchmark is being written, set_user is not available as a package in the PGDG repository. As such, we will build it from source:

# whoami
root
# dnf -y install postgresql12-devel
Last metadata expiration check: 1:28:07 ago on Mon 28 Oct 2019 11:23:30 AM EDT.
Dependencies resolved.
[snip]
Installed:
postgresql12-devel-12.0-1PGDG.rhel8.x86_64 libicu-devel-60.2-7.el8.x86_64

Complete!

# dnf -y install epel-release && dnf --enablerepo=* --disablerepo=base-debuginfo --disablerepo=c8-media-* --disablerepo=pgdg13* install -y llvm-devel clang-devel ccache
Last metadata expiration check: 0:00:32 ago on Tue 29 Oct 2019 09:29:18 AM EDT.
Dependencies resolved.
[snip]
Installed:
epel-release-8-5.el8.noarch

Complete!
Last metadata expiration check: 0:00:37 ago on Tue 29 Oct 2019 09:29:18 AM EDT.
Dependencies resolved.
[snip]
Installed:
clang-devel-7.0.1-1.module_el8.0.0+12+30b38a9a.x86_64 llvm-devel-7.0.1-3.module_el8.0.0+176+9dc62ab1.x86_64
ccache-3.7.4-1.epel8.playground.x86_64 compiler-rt-7.0.1-1.module_el8.0.0+12+30b38a9a.x86_64
libomp-7.0.1-1.module_el8.0.0+12+30b38a9a.x86_64 clang-7.0.1-1.module_el8.0.0+12+30b38a9a.x86_64
clang-libs-7.0.1-1.module_el8.0.0+12+30b38a9a.x86_64 clang-tools-extra-7.0.1-1.module_el8.0.0+12+30b38a9a.x86_64
cmake-filesystem-3.11.4-3.el8.x86_64 gcc-c++-8.2.1-3.5.el8.x86_64
libstdc++-devel-8.2.1-3.5.el8.x86_64 llvm-7.0.1-3.module_el8.0.0+176+9dc62ab1.x86_64
llvm-libs-7.0.1-3.module_el8.0.0+176+9dc62ab1.x86_64 libatomic-8.2.1-3.5.el8.x86_64

Complete!

$ curl -L https://codeload.github.com/pgaudit/set_user/tar.gz/REL1_6_2 > set_user-1.6.2.tgz
$ tar xf set_user-1.6.2.tgz
$ cd set_user-REL1_6_2
$ export PATH=/usr/pgsql-12/bin:$PATH
$ make USE_PGXS=1 install
/usr/lib64/ccache/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2 -I. -I./ -I/usr/pgsql-12/include/server -I/usr/pgsql-12/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -flto=thin -emit-llvm -c -o set_user.bc set_user.c
/usr/bin/mkdir -p '/usr/pgsql-12/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-12/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-12/lib'
/usr/bin/install -c -m 644 'set_user.h' /usr/pgsql-12/include
/usr/bin/install -c -m 644 .//set_user.control '/usr/pgsql-12/share/extension/'
/usr/bin/install -c -m 644 .//set_user--1.6.sql .//set_user--1.5--1.6.sql .//set_user--1.4--1.5.sql .//set_user--1.1--1.4.sql .//set_user--1.0--1.1.sql '/usr/pgsql-12/share/extension/'
/usr/bin/install -c -m 755 set_user.so '/usr/pgsql-12/lib/'
/usr/bin/mkdir -p '/usr/pgsql-12/lib/bitcode/set_user'
/usr/bin/mkdir -p '/usr/pgsql-12/lib/bitcode'/set_user/
/usr/bin/install -c -m 644 set_user.bc '/usr/pgsql-12/lib/bitcode'/set_user/./
cd '/usr/pgsql-12/lib/bitcode' && /usr/bin/llvm-lto -thinlto -thinlto-action=thinlink -o set_user.index.bc set_user/set_user.bc
$

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

$ whoami
root
$ vi ~postgres/12/data/postgresql.conf
$ load set_user libs before anything else
shared_preload_libraries = 'set_user, other_libs'
$ systemctl restart postgresql-12
$ systemctl status postgresql-12|grep 'ago$'
Active: active (running) since [timestamp]; 1s ago

And now, we can install the extension with SQL:

postgres=# select * from pg_available_extensions where name = 'set_user';
name | default_version | installed_version | comment
---------+-----------------+-------------------+-----------------------------
set_user | 1.6 | | 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 | 1.6 | 1.6 | 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
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
psql (11.3)

Make sure there are no other roles that are superuser's and can still login:

postgres=# SELECT rolname FROM pg_authid WHERE rolsuper and rolcanlogin;
rolname
---------
(0 rows)

Verify there are no unprivileged roles that can login directly that are granted a superuser role even if it is multiple layers removed:

postgres=# DROP VIEW IF EXISTS roletree;
NOTICE: view 'roletree' does not exist, skipping
DROP VIEW
postgres=# CREATE OR REPLACE VIEW roletree AS
postgres-# WITH RECURSIVE
postgres-# roltree AS (
postgres(# SELECT u.rolname AS rolname,
postgres(# u.oid AS roloid,
postgres(# u.rolcanlogin,
postgres(# u.rolsuper,
postgres(# '{}'::name[] AS rolparents,
postgres(# NULL::oid AS parent_roloid,
postgres(# NULL::name AS parent_rolname
postgres(# FROM pg_catalog.pg_authid u
postgres(# LEFT JOIN pg_catalog.pg_auth_members m on u.oid = m.member
postgres(# LEFT JOIN pg_catalog.pg_authid g on m.roleid = g.oid
postgres(# WHERE g.oid IS NULL
postgres(# UNION ALL
postgres(# SELECT u.rolname AS rolname,
postgres(# u.oid AS roloid,
postgres(# u.rolcanlogin,
postgres(# u.rolsuper,
postgres(# t.rolparents || g.rolname AS rolparents,
postgres(# g.oid AS parent_roloid,
postgres(# g.rolname AS parent_rolname
postgres(# FROM pg_catalog.pg_authid u
postgres(# JOIN pg_catalog.pg_auth_members m on u.oid = m.member
postgres(# JOIN pg_catalog.pg_authid g on m.roleid = g.oid
postgres(# JOIN roltree t on t.roloid = g.oid
postgres(# )
postgres-# SELECT
postgres-# r.rolname,
postgres-# r.roloid,
postgres-# r.rolcanlogin,
postgres-# r.rolsuper,
postgres-# r.rolparents
postgres-# FROM roltree r
postgres-# ORDER BY 1;
CREATE VIEW
postgres=# SELECT
postgres-# ro.rolname,
postgres-# ro.roloid,
postgres-# ro.rolcanlogin,
postgres-# ro.rolsuper,
postgres-# ro.rolparents
postgres-# FROM roletree ro
postgres-# WHERE (ro.rolcanlogin AND ro.rolsuper)
postgres-# OR
postgres-# (
postgres(# ro.rolcanlogin AND EXISTS
postgres(# (
postgres(# SELECT TRUE FROM roletree ri
postgres(# WHERE ri.rolname = ANY (ro.rolparents)
postgres(# AND ri.rolsuper
postgres(# )
postgres(# );
rolname | roloid | rolcanlogin | rolsuper | rolparents
---------+--------+-------------+----------+------------
(0 rows)

If any roles are identified by this query, use REVOKE to correct.

Impact:

Much like the venerable sudo does for the OS, set_user manages superuser access for PostgreSQL. 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. You may find some tools need different options, reconfigured, or even abandoned to compensate for this.

References:

https://github.com/pgaudit/set_user

Detections

Analytics

4.7 Ensure the set_user extension is installed

Warning! Audit Deprecated

Information

Solution

See Also

Item Details