Detections
Analytics
4.5 Use pg_permission extension to audit object permissions
Information
Using a PostgreSQL extension called pg_permissions it is possible to declare which DB users should have which permissions on a given object and generate a report showing compliance/deviation.Rationale:
Auditing permissions in a PostgreSQL database can be intimidating given the default manner in which permissions are presented. The pg_permissions extension greatly simplifies this presentation and allows the user to declare what permissions should exist and then report on differences from that ideal.
Solution
At this time, pg_permissions is not packaged by the PGDG packaging team. As such, download the latest from the extension's site, compile it, and then install it:# whoami
root
# dnf -y install postgresql13-devel
[snip]
Installed:
clang-10.0.1-1.module_el8.3.0+467+cb298d5b.x86_64
clang-devel-10.0.1-1.module_el8.3.0+467+cb298d5b.x86_64
clang-libs-10.0.1-1.module_el8.3.0+467+cb298d5b.x86_64
clang-tools-extra-10.0.1-1.module_el8.3.0+467+cb298d5b.x86_64
cmake-filesystem-3.11.4-7.el8.x86_64
compiler-rt-10.0.1-1.module_el8.3.0+467+cb298d5b.x86_64
emacs-filesystem-1:26.1-5.el8.noarch
gcc-c++-8.3.1-5.1.el8.x86_64
libicu-devel-60.3-2.el8_1.x86_64
libomp-10.0.1-1.module_el8.3.0+467+cb298d5b.x86_64
libomp-devel-10.0.1-1.module_el8.3.0+467+cb298d5b.x86_64
libstdc++-devel-8.3.1-5.1.el8.x86_64
llvm-10.0.1-3.module_el8.3.0+467+cb298d5b.x86_64
llvm-devel-10.0.1-3.module_el8.3.0+467+cb298d5b.x86_64
llvm-libs-10.0.1-3.module_el8.3.0+467+cb298d5b.x86_64
postgresql13-devel-13.1-1PGDG.rhel8.x86_64
Complete!
# curl -L -o pg_permission_1.1.tgz https://github.com/cybertec-postgresql/pg_permission/archive/REL_1_1.tar.gz
# tar xf pg_permission_1.1.tgz
# cd pg_permission-REL_1_1/
# which pg_config
/usr/bin/which: no pg_config in (various paths here)
# export PATH=/usr/pgsql-13/bin:$PATH
# which pg_config
/usr/pgsql-13/bin/pg_config
# make install
/usr/bin/mkdir -p '/usr/pgsql-13/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-13/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-13/doc/extension'
/usr/bin/install -c -m 644 .//pg_permissions.control '/usr/pgsql-13/share/extension/'
/usr/bin/install -c -m 644 .//pg_permissions--*.sql '/usr/pgsql-13/share/extension/'
/usr/bin/install -c -m 644 .//README.pg_permissions '/usr/pgsql-13/doc/extension/'
# su - postgres
bash-4.2$ whoami
postgres
bash-4.2$ psql -c 'create extension pg_permissions;'
CREATE EXTENSION
Now you need to add entries to permission_target that correspond to your desired permissions.
Let's assume we have a schema appschema, and appuser should have SELECT, UPDATE, DELETE, and INSERT permissions on all tables and views in that schema:
postgres=# INSERT INTO public.permission_target
postgres-# (id, role_name, permissions,
postgres(# object_type, schema_name)
postgres-# VALUES
postgres-# (1, 'appuser', '{SELECT,INSERT,UPDATE,DELETE}',
postgres(# 'TABLE', 'appschema');
INSERT 0 1
postgres=# INSERT INTO public.permission_target
postgres-# (id, role_name, permissions,
postgres(# object_type, schema_name)
postgres-# VALUES
postgres-# (2, 'appuser', '{SELECT,INSERT,UPDATE,DELETE}',
postgres(# 'VIEW', 'appschema');
INSERT 0 1
Of course, the user will need the USAGE privilege on the schema:
postgres=# INSERT INTO public.permission_target
postgres-# (id, role_name, permissions,
postgres(# object_type, schema_name)
postgres-# VALUES
postgres-# (3, 'appuser', '{USAGE}',
postgres(# 'SCHEMA', 'appschema');
INSERT 0 1
The user also needs USAGE privileges on the appseq sequence in that schema:
postgres=# INSERT INTO public.permission_target
postgres-# (id, role_name, permissions,
postgres(# object_type, schema_name, object_name)
postgres-# VALUES
postgres-# (4, 'appuser', '{USAGE}',
postgres(# 'SEQUENCE', 'appschema', 'appseq');
INSERT 0 1
Now we can review which permissions are missing and which additional permissions are granted:
postgres=# SELECT * FROM public.permission_diffs();
missing | role_name | object_type | schema_name | object_name | column_name | permission
---------+-----------+-------------+-------------+-------------+-------------+------------
f | laurenz | VIEW | appschema | appview | | SELECT
t | appuser | TABLE | appschema | apptable | | DELETE
(2 rows)
That means that appuser is missing (missing is TRUE) the DELETE privilege on appschema.apptable which should be GRANTed, while user laurenz has the additional SELECT privilege on appschema.appview (missing is FALSE).
To review the actual permissions on an object, we can use the _permissions views:
postgres=# SELECT * FROM schema_permissions
postgres-# WHERE role_name = 'appuser' AND schema_name = 'appschema' AND granted IS TRUE;
object_type | role_name | schema_name | object_name | column_name | permissions | granted
-------------+-----------+-------------+-------------+-------------+-------------+---------
SCHEMA | appuser | appschema | | | USAGE | t
(1 row)
For more details and examples, visit the online documentation.
See Also
Item Details
Audit Name: CIS PostgreSQL 13 DB v1.0.0
References: CSCv7|5.1
Plugin: PostgreSQLDB
Control ID: eb91fcaa7fe3ac39821b55aef4d83fb0bfe59f44527a742aafc9b48cc483e6f8