pg_permissions

Overview

Latest PGDG RPM/catalog version is 1.4.1; PGDG DEB remains on 1.4.

Version

Install

You can install pg_permissions directly. First, make sure the PGDG repository is added and enabled:

pig repo add pgdg -u          # Add PGDG repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install pg_permissions;          # Install for current active PG version

pig ext install -y pg_permissions -v 18  # PG 18
pig ext install -y pg_permissions -v 17  # PG 17
pig ext install -y pg_permissions -v 16  # PG 16
pig ext install -y pg_permissions -v 15  # PG 15
pig ext install -y pg_permissions -v 14  # PG 14

dnf install -y pg_permissions_18       # PG 18
dnf install -y pg_permissions_17       # PG 17
dnf install -y pg_permissions_16       # PG 16
dnf install -y pg_permissions_15       # PG 15
dnf install -y pg_permissions_14       # PG 14

apt install -y postgresql-18-pg-permissions   # PG 18
apt install -y postgresql-17-pg-permissions   # PG 17
apt install -y postgresql-16-pg-permissions   # PG 16
apt install -y postgresql-15-pg-permissions   # PG 15
apt install -y postgresql-14-pg-permissions   # PG 14

Create Extension:

CREATE EXTENSION pg_permissions;

Usage

pg_permissions: view object permissions and compare them with the desired state

pg_permissions lets you review actual permissions on database objects and compare them against a desired permission state.

Define Desired Permissions

Insert entries into permission_target to describe what permissions should exist:

INSERT INTO permission_target (role_name, permissions, object_type, schema_name)
VALUES ('appuser', '{SELECT,INSERT,UPDATE,DELETE}', 'TABLE', 'appschema');

INSERT INTO permission_target (role_name, permissions, object_type, schema_name)
VALUES ('appuser', '{USAGE}', 'SCHEMA', 'appschema');

INSERT INTO permission_target (role_name, permissions, object_type, schema_name, object_name)
VALUES ('appuser', '{USAGE}', 'SEQUENCE', 'appschema', 'appseq');

Set object_name or column_name to NULL to apply to all objects of that type in the schema.

Find Permission Differences

SELECT * FROM permission_diffs();

Returns rows where missing = TRUE (permission should exist but doesn’t) or missing = FALSE (extra permission that shouldn’t exist).

Review Actual Permissions

Available views (all with the same column structure):

• database_permissions – permissions on the current database
• schema_permissions – permissions on schemas
• table_permissions – permissions on tables
• view_permissions – permissions on views
• column_permissions – permissions on table/view columns
• function_permissions – permissions on functions
• sequence_permissions – permissions on sequences
• all_permissions – UNION of all above

SELECT * FROM table_permissions WHERE role_name = 'appuser' AND schema_name = 'appschema';

Grant/Revoke via Views

The granted column of the permission views is updatable – updating it executes the appropriate GRANT or REVOKE command.

Note: superusers are not shown in the views (they automatically have all permissions).