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.

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:
[root@instance-1 ~]# whoami
root [root@instance-1 ~]# yum -y install postgresql10-devel
[snip]
Running transaction
Installing : libicu-devel-50.1.2-17.el7.x86_64 1/2
Installing : postgresql10-devel-10.7-1PGDG.rhel7.x86_64 2/2
Verifying : postgresql10-devel-10.7-1PGDG.rhel7.x86_64 1/2
Verifying : libicu-devel-50.1.2-17.el7.x86_64 2/2

Installed:
postgresql10-devel.x86_64 0:10.7-1PGDG.rhel7

Dependency Installed:
libicu-devel.x86_64 0:50.1.2-17.el7
[root@instance-1 ~]# curl -L -o pg_permission_1.1.tgz https://github.com/cybertec-postgresql/pg_permission/archive/REL_1_1.tar.gz
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 142 0 142 0 0 581 0 --:--:-- --:--:-- --:--:-- 579
0 0 0 9437 0 0 24799 0 --:--:-- --:--:-- --:--:-- 24799
[root@instance-1 ~]# tar xf pg_permission_1.1.tgz
[root@instance-1 ~]# cd pg_permission-REL_1_1/
[root@instance-1 ~]# which pg_config
/usr/bin/which: no pg_config in (/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin)
[root@instance-1 ~]# export PATH=/usr/pgsql-10/bin:$PATH
[root@instance-1 ~]# which pg_config
/usr/pgsql-10/bin/pg_config
[root@instance-1 ~]# make install
/usr/bin/mkdir -p '/usr/pgsql-10/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-10/share/extension'
/usr/bin/mkdir -p '/usr/pgsql-10/doc/extension'
/usr/bin/install -c -m 644 .//pg_permissions.control '/usr/pgsql-10/share/extension/'
/usr/bin/install -c -m 644 .//pg_permissions--*.sql '/usr/pgsql-10/share/extension/'
/usr/bin/install -c -m 644 .//README.pg_permissions '/usr/pgsql-10/doc/extension/'
[root@instance-1 ~]# 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';

object_type | role_name | schema_name | object_name | column_name | permissions | granted
-------------+-----------+-------------+-------------+-------------+-------------+---------
SCHEMA | appuser | appschema | | | USAGE | t
SCHEMA | appuser | appschema | | | CREATE | f
(2 rows)
For more details and examples, visit the online documentation.

Detections

Analytics

4.5 Use pg_permission extension to audit object permissions

Information

Solution

See Also

Item Details