Detections
Analytics

4.5 Use pg_permission extension to audit object permissions

Warning! Audit Deprecated

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

View Next Audit Version

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

https://workbench.cisecurity.org/files/3170

Item Details

References: CSCv7|5.1

Plugin: PostgreSQLDB

Control ID: eb91fcaa7fe3ac39821b55aef4d83fb0bfe59f44527a742aafc9b48cc483e6f8