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.

 NOTE: Nessus has not performed this check. Please review the benchmark to ensure target compliance.

Solution

At this time, pg_permission 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 postgresql11-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!
 # 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-12/bin:$PATH
 # which pg_config
 /usr/pgsql-12/bin/pg_config
 # make install
 /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/doc/extension'
 /usr/bin/install -c -m 644 .//pg_permissions.control '/usr/pgsql-12/share/extension/'
 /usr/bin/install -c -m 644 .//pg_permissions--*.sql '/usr/pgsql-12/share/extension/'
 /usr/bin/install -c -m 644 .//README.pg_permissions '/usr/pgsql-12/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,i
 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.

 References:

 https://github.com/cybertec-postgresql/pg_permission

See Also

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

Item Details

References: CSCv7|5.1

Plugin: PostgreSQLDB

Control ID: 05bf2bab9c43ee440e7a901f8f951e343b5422fb02384b9d059b6d772f3fb8e9