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@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
--> Processing Dependency: libicu-devel for package: postgresql11-devel-11.3-1PGDG.rhel7.x86_64
--> Running transaction check
---> Package libicu-devel.x86_64 0:50.1.2-17.el7 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
Installing for dependencies:
libicu-devel x86_64 50.1.2-17.el7 base 702 k

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

Total download size: 2.7 M
Installed size: 13 M
Downloading packages:
(1/2): libicu-devel-50.1.2-17.el7.x86_64.rpm | 702 kB 00:00:00
(2/2): postgresql11-devel-11.3-1PGDG.rhel7.x86_64.rpm | 2.0 MB 00:00:01
-----------------------------------------------------------------------------
Total 1.9 MB/s | 2.7 MB 00:00:01
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : libicu-devel-50.1.2-17.el7.x86_64 1/2
Installing : postgresql11-devel-11.3-1PGDG.rhel7.x86_64 2/2
Verifying : postgresql11-devel-11.3-1PGDG.rhel7.x86_64 1/2
Verifying : libicu-devel-50.1.2-17.el7.x86_64 2/2

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

Dependency Installed:
libicu-devel.x86_64 0:50.1.2-17.el7

Complete!
[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-11/bin:$PATH
[root@instance-1 ~]# which pg_config
/usr/pgsql-11/bin/pg_config
[root@instance-1 ~]# make install
/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/doc/extension'
/usr/bin/install -c -m 644 .//pg_permissions.control '/usr/pgsql-11/share/extension/'
/usr/bin/install -c -m 644 .//pg_permissions--*.sql '/usr/pgsql-11/share/extension/'
/usr/bin/install -c -m 644 .//README.pg_permissions '/usr/pgsql-11/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' 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.

Detections

Analytics

4.5 Use pg_permission extension to audit object permissions

Information

Solution

See Also

Item Details