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:
[root@centos7 ~]# whoami
root
[root@centos7 ~]# yum -y install postgresql11-devel
Loaded plugins: fastestmirror, priorities
Loading mirror speeds from cached hostfile
* base: centos.mirrors.tds.net
* extras: mirror.team-cymru.com
* updates: mirrors.gigenet.com
1425 packages excluded due to repository priority protections
Resolving Dependencies
--> Running transaction check
---> Package postgresql11-devel.x86_64 0:11.3-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================
Package Arch Version Repository Size
=============================================================================

Installing:
postgresql11-devel x86_64 11.3-1PGDG.rhel7 pgdg11 2.0 M

Transaction Summary
=============================================================================
Install 1 Package

Total download size: 2.0 M
Installed size: 9.3 M
Downloading packages:
postgresql11-devel-11.3-1PGDG.rhel7.x86_64.rpm | 2.0 MB 00:00:02
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : postgresql11-devel-11.3-1PGDG.rhel7.x86_64 1/1
Verifying : postgresql11-devel-11.3-1PGDG.rhel7.x86_64 1/1

Installed:
postgresql11-devel.x86_64 0:11.3-1PGDG.rhel7

Complete!

[root@centos7 ~]# yum install centos-release-scl
Loaded plugins: fastestmirror, priorities
Loading mirror speeds from cached hostfile
* base: centos.mirrors.tds.net
* extras: mirror.team-cymru.com
* updates: mirrors.cmich.edu
1425 packages excluded due to repository priority protections
Resolving Dependencies
--> Running transaction check
---> Package centos-release-scl.noarch 0:2-3.el7.centos will be installed
--> Processing Dependency: centos-release-scl-rh for package: centos-release-scl-2-3.el7.centos.noarch
--> Running transaction check
---> Package centos-release-scl-rh.noarch 0:2-3.el7.centos will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================
Package Arch Version Repository Size
=============================================================================
Installing:
centos-release-scl noarch 2-3.el7.centos extras 12 k
Installing for dependencies:
centos-release-scl-rh noarch 2-3.el7.centos extras 12 k

Transaction Summary
=============================================================================Install 1 Package (+1 Dependent package)

Total download size: 24 k
Installed size: 39 k
Is this ok [y/d/N]: y
Downloading packages:
(1/2): centos-release-scl-rh-2-3.el7.centos.noarch.rpm | 12 kB 00:00:00
(2/2): centos-release-scl-2-3.el7.centos.noarch.rpm | 12 kB 00:00:00
-----------------------------------------------------------------------------Total 144 kB/s | 24 kB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : centos-release-scl-rh-2-3.el7.centos.noarch 1/2
Installing : centos-release-scl-2-3.el7.centos.noarch 2/2
Verifying : centos-release-scl-2-3.el7.centos.noarch 1/2
Verifying : centos-release-scl-rh-2-3.el7.centos.noarch 2/2

Installed:
centos-release-scl.noarch 0:2-3.el7.centos

Dependency Installed:
centos-release-scl-rh.noarch 0:2-3.el7.centos

Complete!

[root@centos7 ~]# yum -y install llvm-toolset-7-clang
Loaded plugins: fastestmirror, priorities
Loading mirror speeds from cached hostfile
* base: centos.mirrors.tds.net
* centos-sclo-rh: mirror.genesisadaptive.com
* centos-sclo-sclo: mirror.jaleco.com
* extras: mirror.team-cymru.com
* updates: mirror.jaleco.com
1425 packages excluded due to repository priority protections
Resolving Dependencies
--> Running transaction check
---> Package llvm-toolset-7-clang.x86_64 0:5.0.1-4.el7 will be installed
--> Processing Dependency: llvm-toolset-7-clang-libs(x86-64) = 5.0.1-4.el7 for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64
--> Processing Dependency: llvm-toolset-7-runtime for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64
--> Processing Dependency: libclang.so.5(LLVM_5.0)(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64
--> Processing Dependency: libLLVM-5.0.so(LLVM_5.0)(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64
--> Processing Dependency: libclangToolingCore.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64
--> Processing Dependency: libclangTooling.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64
--> Processing Dependency: libclangStaticAnalyzerFrontend.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64
--> Processing Dependency: libclangSerialization.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64
--> Processing Dependency: libclangRewriteFrontend.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64
--> Processing Dependency: libclangRewrite.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64
--> Processing Dependency: libclangParse.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64
--> Processing Dependency: libclangLex.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64
--> Processing Dependency: libclangIndex.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64
--> Processing Dependency: libclangFrontendTool.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64
--> Processing Dependency: libclangFrontend.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64
--> Processing Dependency: libclangFormat.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64
--> Processing Dependency: libclangDriver.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64
--> Processing Dependency: libclangCodeGen.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64
--> Processing Dependency: libclangBasic.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64
--> Processing Dependency: libclangAST.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64
--> Processing Dependency: libclang.so.5()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64
--> Processing Dependency: libLLVM-5.0.so()(64bit) for package: llvm-toolset-7-clang-5.0.1-4.el7.x86_64
--> Running transaction check
---> Package llvm-toolset-7-clang-libs.x86_64 0:5.0.1-4.el7 will be installed
--> Processing Dependency: llvm-toolset-7-libomp(x86-64) >= 5.0.1 for package: llvm-toolset-7-clang-libs-5.0.1-4.el7.x86_64
--> Processing Dependency: llvm-toolset-7-compiler-rt(x86-64) >= 5.0.1 for package: llvm-toolset-7-clang-libs-5.0.1-4.el7.x86_64
--> Processing Dependency: devtoolset-7-libstdc++-devel for package: llvm-toolset-7-clang-libs-5.0.1-4.el7.x86_64
--> Processing Dependency: devtoolset-7-gcc-c++ for package: llvm-toolset-7-clang-libs-5.0.1-4.el7.x86_64
---> Package llvm-toolset-7-llvm-libs.x86_64 0:5.0.1-8.el7 will be installed
---> Package llvm-toolset-7-runtime.x86_64 0:5.0.1-4.el7 will be installed
--> Processing Dependency: scl-utils for package: llvm-toolset-7-runtime-5.0.1-4.el7.x86_64
--> Running transaction check
---> Package devtoolset-7-gcc-c++.x86_64 0:7.3.1-5.15.el7 will be installed
--> Processing Dependency: devtoolset-7-gcc = 7.3.1-5.15.el7 for package: devtoolset-7-gcc-c++-7.3.1-5.15.el7.x86_64
--> Processing Dependency: devtoolset-7-runtime for package: devtoolset-7-gcc-c++-7.3.1-5.15.el7.x86_64
---> Package devtoolset-7-libstdc++-devel.x86_64 0:7.3.1-5.15.el7 will be installed
---> Package llvm-toolset-7-compiler-rt.x86_64 0:5.0.1-2.el7 will be installed
---> Package llvm-toolset-7-libomp.x86_64 0:5.0.1-2.el7 will be installed
---> Package scl-utils.x86_64 0:20130529-19.el7 will be installed
--> Running transaction check
---> Package devtoolset-7-gcc.x86_64 0:7.3.1-5.15.el7 will be installed
--> Processing Dependency: devtoolset-7-binutils >= 2.22.52.0.1 for package: devtoolset-7-gcc-7.3.1-5.15.el7.x86_64
---> Package devtoolset-7-runtime.x86_64 0:7.1-4.el7 will be installed
--> Processing Dependency: /usr/sbin/semanage for package: devtoolset-7-runtime-7.1-4.el7.x86_64
--> Processing Dependency: /usr/sbin/semanage for package: devtoolset-7-runtime-7.1-4.el7.x86_64
--> Running transaction check
---> Package devtoolset-7-binutils.x86_64 0:2.28-11.el7 will be installed
---> Package policycoreutils-python.x86_64 0:2.5-29.el7_6.1 will be installed
--> Processing Dependency: setools-libs >= 3.3.8-4 for package: policycoreutils-python-2.5-29.el7_6.1.x86_64
--> Processing Dependency: libsemanage-python >= 2.5-14 for package: policycoreutils-python-2.5-29.el7_6.1.x86_64
--> Processing Dependency: audit-libs-python >= 2.1.3-4 for package: policycoreutils-python-2.5-29.el7_6.1.x86_64
--> Processing Dependency: python-IPy for package: policycoreutils-python-2.5-29.el7_6.1.x86_64
--> Processing Dependency: libqpol.so.1(VERS_1.4)(64bit) for package: policycoreutils-python-2.5-29.el7_6.1.x86_64
--> Processing Dependency: libqpol.so.1(VERS_1.2)(64bit) for package: policycoreutils-python-2.5-29.el7_6.1.x86_64
--> Processing Dependency: libcgroup for package: policycoreutils-python-2.5-29.el7_6.1.x86_64
--> Processing Dependency: libapol.so.4(VERS_4.0)(64bit) for package: policycoreutils-python-2.5-29.el7_6.1.x86_64
--> Processing Dependency: checkpolicy for package: policycoreutils-python-2.5-29.el7_6.1.x86_64
--> Processing Dependency: libqpol.so.1()(64bit) for package: policycoreutils-python-2.5-29.el7_6.1.x86_64
--> Processing Dependency: libapol.so.4()(64bit) for package: policycoreutils-python-2.5-29.el7_6.1.x86_64
--> Running transaction check
---> Package audit-libs-python.x86_64 0:2.8.4-4.el7 will be installed
---> Package checkpolicy.x86_64 0:2.5-8.el7 will be installed
---> Package libcgroup.x86_64 0:0.41-20.el7 will be installed
---> Package libsemanage-python.x86_64 0:2.5-14.el7 will be installed
---> Package python-IPy.noarch 0:0.75-6.el7 will be installed
---> Package setools-libs.x86_64 0:3.3.8-4.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================
Package Arch Version Repository Size
=============================================================================
Installing:
llvm-toolset-7-clang x86_64 5.0.1-4.el7 centos-sclo-rh 545 k
Installing for dependencies:
audit-libs-python x86_64 2.8.4-4.el7 base 76 k
checkpolicy x86_64 2.5-8.el7 base 295 k
devtoolset-7-binutils x86_64 2.28-11.el7 centos-sclo-rh 5.3 M
devtoolset-7-gcc x86_64 7.3.1-5.15.el7 centos-sclo-rh 29 M
devtoolset-7-gcc-c++ x86_64 7.3.1-5.15.el7 centos-sclo-rh 11 M
devtoolset-7-libstdc++-devel x86_64 7.3.1-5.15.el7 centos-sclo-rh 2.6 M
devtoolset-7-runtime x86_64 7.1-4.el7 centos-sclo-rh 20 k
libcgroup x86_64 0.41-20.el7 base 66 k
libsemanage-python x86_64 2.5-14.el7 base 113 k
llvm-toolset-7-clang-libs x86_64 5.0.1-4.el7 centos-sclo-rh 13 M
llvm-toolset-7-compiler-rt x86_64 5.0.1-2.el7 centos-sclo-rh 1.7 M
llvm-toolset-7-libomp x86_64 5.0.1-2.el7 centos-sclo-rh 246 k
llvm-toolset-7-llvm-libs x86_64 5.0.1-8.el7 centos-sclo-rh 13 M
llvm-toolset-7-runtime x86_64 5.0.1-4.el7 centos-sclo-rh 1.1 M
policycoreutils-python x86_64 2.5-29.el7_6.1 updates 456 k
python-IPy noarch 0.75-6.el7 base 32 k
scl-utils x86_64 20130529-19.el7 base 24 k
setools-libs x86_64 3.3.8-4.el7 base 620 k

Transaction Summary
=============================================================================Install 1 Package (+18 Dependent packages)

Total download size: 80 M
Installed size: 261 M
Downloading packages:
(1/19): audit-libs-python-2.8.4-4.el7.x86_64.rpm | 76 kB 00:00:00
(2/19): checkpolicy-2.5-8.el7.x86_64.rpm | 295 kB 00:00:00
warning: /var/cache/yum/x86_64/7/centos-sclo-rh/packages/devtoolset-7-binutils-2.28-11.el7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID f2ee9d55: NOKEY
Public key for devtoolset-7-binutils-2.28-11.el7.x86_64.rpm is not installed
(3/19): devtoolset-7-binutils-2.28-11.el7.x86_64.rpm | 5.3 MB 00:00:04
(4/19): devtoolset-7-gcc-c++-7.3.1-5.15.el7.x86_64.rpm | 11 MB 00:00:04
(5/19): devtoolset-7-runtime-7.1-4.el7.x86_64.rpm | 20 kB 00:00:00
(6/19): llvm-toolset-7-clang-5.0.1-4.el7.x86_64.rpm | 545 kB 00:00:00
(7/19): devtoolset-7-libstdc++-devel-7.3.1-5.15.el7.x86_64.rpm | 2.6 MB 00:00:01
(8/19): libsemanage-python-2.5-14.el7.x86_64.rpm | 113 kB 00:00:01
(9/19): libcgroup-0.41-20.el7.x86_64.rpm | 66 kB 00:00:01
(10/19): llvm-toolset-7-compiler-rt-5.0.1-2.el7.x86_64.rpm | 1.7 MB 00:00:00
(11/19): llvm-toolset-7-libomp-5.0.1-2.el7.x86_64.rpm | 246 kB 00:00:00
(12/19): llvm-toolset-7-clang-libs-5.0.1-4.el7.x86_64.rpm | 13 MB 00:00:02
(13/19): llvm-toolset-7-runtime-5.0.1-4.el7.x86_64.rpm | 1.1 MB 00:00:00
(14/19): python-IPy-0.75-6.el7.noarch.rpm | 32 kB 00:00:00
(15/19): scl-utils-20130529-19.el7.x86_64.rpm | 24 kB 00:00:00
(16/19): policycoreutils-python-2.5-29.el7_6.1.x86_64.rpm | 456 kB 00:00:00
(17/19): devtoolset-7-gcc-7.3.1-5.15.el7.x86_64.rpm | 29 MB 00:00:10
(18/19): llvm-toolset-7-llvm-libs-5.0.1-8.el7.x86_64.rpm | 13 MB 00:00:03
(19/19): setools-libs-3.3.8-4.el7.x86_64.rpm | 620 kB 00:00:02
-----------------------------------------------------------------------------
Total 7.4 MB/s | 80 MB 00:00:10
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-SIG-SCLo
Importing GPG key 0xF2EE9D55:
Userid : 'CentOS SoftwareCollections SIG (https://wiki.centos.org/SpecialInterestGroup/SCLo) <[email protected]>'
Fingerprint: c4db d535 b1fb ba14 f8ba 64a8 4eb8 4e71 f2ee 9d55
Package : centos-release-scl-rh-2-3.el7.centos.noarch (@extras)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-SIG-SCLo
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : scl-utils-20130529-19.el7.x86_64 1/19
Installing : llvm-toolset-7-runtime-5.0.1-4.el7.x86_64 2/19
Installing : llvm-toolset-7-llvm-libs-5.0.1-8.el7.x86_64 3/19
Installing : llvm-toolset-7-libomp-5.0.1-2.el7.x86_64 4/19
Installing : llvm-toolset-7-compiler-rt-5.0.1-2.el7.x86_64 5/19
Installing : setools-libs-3.3.8-4.el7.x86_64 6/19
Installing : checkpolicy-2.5-8.el7.x86_64 7/19
Installing : python-IPy-0.75-6.el7.noarch 8/19
Installing : libsemanage-python-2.5-14.el7.x86_64 9/19
Installing : libcgroup-0.41-20.el7.x86_64 10/19
Installing : audit-libs-python-2.8.4-4.el7.x86_64 11/19
Installing : policycoreutils-python-2.5-29.el7_6.1.x86_64 12/19
Installing : devtoolset-7-runtime-7.1-4.el7.x86_64 13/19
Installing : devtoolset-7-libstdc++-devel-7.3.1-5.15.el7.x86_64 14/19
Installing : devtoolset-7-binutils-2.28-11.el7.x86_64 15/19
Installing : devtoolset-7-gcc-7.3.1-5.15.el7.x86_64 16/19
Installing : devtoolset-7-gcc-c++-7.3.1-5.15.el7.x86_64 17/19
Installing : llvm-toolset-7-clang-libs-5.0.1-4.el7.x86_64 18/19
Installing : llvm-toolset-7-clang-5.0.1-4.el7.x86_64 19/19
Verifying : policycoreutils-python-2.5-29.el7_6.1.x86_64 1/19
Verifying : audit-libs-python-2.8.4-4.el7.x86_64 2/19
Verifying : llvm-toolset-7-clang-libs-5.0.1-4.el7.x86_64 3/19
Verifying : devtoolset-7-libstdc++-devel-7.3.1-5.15.el7.x86_64 4/19
Verifying : scl-utils-20130529-19.el7.x86_64 5/19
Verifying : llvm-toolset-7-runtime-5.0.1-4.el7.x86_64 6/19
Verifying : llvm-toolset-7-clang-5.0.1-4.el7.x86_64 7/19
Verifying : libcgroup-0.41-20.el7.x86_64 8/19
Verifying : llvm-toolset-7-libomp-5.0.1-2.el7.x86_64 9/19
Verifying : libsemanage-python-2.5-14.el7.x86_64 10/19
Verifying : devtoolset-7-gcc-7.3.1-5.15.el7.x86_64 11/19
Verifying : devtoolset-7-binutils-2.28-11.el7.x86_64 12/19
Verifying : llvm-toolset-7-llvm-libs-5.0.1-8.el7.x86_64 13/19
Verifying : python-IPy-0.75-6.el7.noarch 14/19
Verifying : devtoolset-7-runtime-7.1-4.el7.x86_64 15/19
Verifying : checkpolicy-2.5-8.el7.x86_64 16/19
Verifying : llvm-toolset-7-compiler-rt-5.0.1-2.el7.x86_64 17/19
Verifying : setools-libs-3.3.8-4.el7.x86_64 18/19
Verifying : devtoolset-7-gcc-c++-7.3.1-5.15.el7.x86_64 19/19

Installed:
llvm-toolset-7-clang.x86_64 0:5.0.1-4.el7

Dependency Installed:
audit-libs-python.x86_64 0:2.8.4-4.el7 checkpolicy.x86_64 0:2.5-8.el7
devtoolset-7-binutils.x86_64 0:2.28-11.el7 devtoolset-7-gcc.x86_64 0:7.3.1-5.15.el7
devtoolset-7-gcc-c++.x86_64 0:7.3.1-5.15.el7 devtoolset-7-libstdc++-devel.x86_64 0:7.3.1-5.15.el7
devtoolset-7-runtime.x86_64 0:7.1-4.el7 libcgroup.x86_64 0:0.41-20.el7
libsemanage-python.x86_64 0:2.5-14.el7 llvm-toolset-7-clang-libs.x86_64 0:5.0.1-4.el7
llvm-toolset-7-compiler-rt.x86_64 0:5.0.1-2.el7 llvm-toolset-7-libomp.x86_64 0:5.0.1-2.el7
llvm-toolset-7-llvm-libs.x86_64 0:5.0.1-8.el7 llvm-toolset-7-runtime.x86_64 0:5.0.1-4.el7
policycoreutils-python.x86_64 0:2.5-29.el7_6.1 python-IPy.noarch 0:0.75-6.el7
scl-utils.x86_64 0:20130529-19.el7 setools-libs.x86_64 0:3.3.8-4.el7

Complete!

[root@centos7 ~]# yum -y install epel-release
Loaded plugins: fastestmirror, priorities
Loading mirror speeds from cached hostfile
* base: centos.mirrors.tds.net
* centos-sclo-rh: mirror.genesisadaptive.com
* centos-sclo-sclo: mirror.atlanticmetro.net
* extras: mirror.team-cymru.com
* updates: mirror.sesp.northwestern.edu
1425 packages excluded due to repository priority protections
Resolving Dependencies
--> Running transaction check
---> Package epel-release.noarch 0:7-11 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================
Package Arch Version Repository Size
=============================================================================
Installing:
epel-release noarch 7-11 extras 15 k

Transaction Summary
=============================================================================
Install 1 Package

Total download size: 15 k
Installed size: 24 k
Downloading packages:
epel-release-7-11.noarch.rpm | 15 kB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : epel-release-7-11.noarch 1/1
Verifying : epel-release-7-11.noarch 1/1

Installed:
epel-release.noarch 0:7-11

Complete!

[root@centos7 ~]# yum -y install llvm5.0
Loaded plugins: fastestmirror, priorities
Loading mirror speeds from cached hostfile
epel/x86_64/metalink | 12 kB 00:00:00
* base: centos.mirrors.tds.net
* centos-sclo-rh: mirror.trouble-free.net
* centos-sclo-sclo: mirror.jaleco.com
* epel: mirror.compevo.com
* extras: mirror.team-cymru.com
* updates: mirrors.gigenet.com
epel | 5.3 kB 00:00:00
(1/3): epel/x86_64/group_gz | 88 kB 00:00:00
(2/3): epel/x86_64/updateinfo | 1.0 MB 00:00:00
(3/3): epel/x86_64/primary_db | 6.7 MB 00:00:01
1447 packages excluded due to repository priority protections
Resolving Dependencies
--> Running transaction check
---> Package llvm5.0.x86_64 0:5.0.1-7.el7 will be installed
--> Processing Dependency: llvm5.0-libs(x86-64) = 5.0.1-7.el7 for package: llvm5.0-5.0.1-7.el7.x86_64
--> Running transaction check
---> Package llvm5.0-libs.x86_64 0:5.0.1-7.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================
Package Arch Version Repository Size
=============================================================================
Installing:
llvm5.0 x86_64 5.0.1-7.el7 epel 2.6 M
Installing for dependencies:
llvm5.0-libs x86_64 5.0.1-7.el7 epel 13 M

Transaction Summary
=============================================================================
Install 1 Package (+1 Dependent package)

Total download size: 16 M
Installed size: 57 M
Downloading packages:
warning: /var/cache/yum/x86_64/7/epel/packages/llvm5.0-5.0.1-7.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID 352c64e5: NOKEY
Public key for llvm5.0-5.0.1-7.el7.x86_64.rpm is not installed
(1/2): llvm5.0-5.0.1-7.el7.x86_64.rpm | 2.6 MB 00:00:00
(2/2): llvm5.0-libs-5.0.1-7.el7.x86_64.rpm | 13 MB 00:00:03
-----------------------------------------------------------------------------
Total 4.4 MB/s | 16 MB 00:00:03
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7
Importing GPG key 0x352C64E5:
Userid : 'Fedora EPEL (7) <[email protected]>'
Fingerprint: 91e9 7d7c 4a5e 96f1 7f3e 888f 6a2f aea2 352c 64e5
Package : epel-release-7-11.noarch (@extras)
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : llvm5.0-libs-5.0.1-7.el7.x86_64 1/2
Installing : llvm5.0-5.0.1-7.el7.x86_64 2/2
Verifying : llvm5.0-5.0.1-7.el7.x86_64 1/2
Verifying : llvm5.0-libs-5.0.1-7.el7.x86_64 2/2

Installed:
llvm5.0.x86_64 0:5.0.1-7.el7

Dependency Installed:
llvm5.0-libs.x86_64 0:5.0.1-7.el7

Complete!

[root@centos7 ~]# yum -y install gcc
Loaded plugins: fastestmirror, langpacks, priorities
Loading mirror speeds from cached hostfile
* base: repos-va.psychz.net
* centos-sclo-rh: repos-va.psychz.net
* centos-sclo-sclo: repos-va.psychz.net
* epel: mirror.cogentco.com
* extras: mirror.vcu.edu
* updates: repos-va.psychz.net
1531 packages excluded due to repository priority protections
Resolving Dependencies
--> Running transaction check
---> Package gcc.x86_64 0:4.8.5-36.el7_6.2 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================
Package Arch Version Repository Size
=============================================================================
Installing:
gcc x86_64 4.8.5-36.el7_6.2 updates 16 M

Transaction Summary
=============================================================================
Install 1 Package

Total download size: 16 M
Installed size: 37 M
Downloading packages:
gcc-4.8.5-36.el7_6.2.x86_64.rpm | 16 MB 00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : gcc-4.8.5-36.el7_6.2.x86_64 1/1
Verifying : gcc-4.8.5-36.el7_6.2.x86_64 1/1

Installed:
gcc.x86_64 0:4.8.5-36.el7_6.2

Complete!

$ curl -L https://codeload.github.com/pgaudit/set_user/tar.gz/REL1_6_2 > set_user-1.6.2.tgz
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 14916 0 14916 0 0 57215 0 --:--:-- --:--:-- --:--:-- 184k

$ tar xf set_user-1.6.2.tgz
$ cd set_user-REL1_6_2
$ export PATH=/usr/pgsql-11/bin:$PATH
[root@centos7 set_user-REL1_6_2]# make USE_PGXS=1 install
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o set_user.o set_user.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -L/usr/pgsql-11/lib -Wl,--as-needed -L/usr/lib64/llvm5.0/lib -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-11/lib',--enable-new-dtags -lm -shared -o set_user.so set_user.o
/opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2 -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/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-11/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-11/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-11/lib'
/usr/bin/install -c -m 644 'set_user.h' /usr/pgsql-11/include
/usr/bin/install -c -m 644 .//set_user.control '/usr/pgsql-11/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-11/share/extension/'
/usr/bin/install -c -m 755 set_user.so '/usr/pgsql-11/lib/'
/usr/bin/mkdir -p '/usr/pgsql-11/lib/bitcode/set_user'
/usr/bin/mkdir -p '/usr/pgsql-11/lib/bitcode'/set_user/
/usr/bin/install -c -m 644 set_user.bc '/usr/pgsql-11/lib/bitcode'/set_user/./
cd '/usr/pgsql-11/lib/bitcode' && /usr/lib64/llvm5.0/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/11/data/postgresql.conf
# load set_user libs before anything else
shared_preload_libraries = 'set_user, other_libs'
[root@centos7 ~]# systemctl restart postgresql-11
[root@centos7 ~]# systemctl status postgresql-11|grep 'ago$'
Active: active (running) since Thu 2019-05-30 15:13:02 EDT; 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.

Detections

Analytics

4.7 Ensure the set_user extension is installed

Information

Solution

See Also

Item Details