pg_permissions

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

Overview

Packages

Source

Install

Make sure PGDG repo available:

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

Install this extension with pig:

pig install pg_permissions;		# install via package name, for the active PG version

pig install pg_permissions -v 18;   # install for PG 18
pig install pg_permissions -v 17;   # install for PG 17
pig install pg_permissions -v 16;   # install for PG 16
pig install pg_permissions -v 15;   # install for PG 15
pig install pg_permissions -v 14;   # install for PG 14

Create this extension with:

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).