You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
This commit was created on GitHub.com and signed with GitHub’s verified signature.
Enhancements
PUBLIC is a first-class grantee: it appears in all *_permissions views and can be used in permission_target.
permission_diffs() is scoped to managed roles (those in permission_target plus the roles that inherit from them) and marked STABLE. Its cost now follows the size of the desired state instead of objects × roles × privileges — about 10 times faster on a 1000-table, 30-role database.
A privilege held through role membership is no longer reported as a surplus grant.
New default_acl_target table, default_acl_permissions view and default_acl_diffs() function to manage ALTER DEFAULT PRIVILEGES.
permission_target now rejects empty permission lists, COLUMN targets without a column name, duplicate targets and unknown roles.
MAINTAIN is rejected on servers older than 17 instead of being silently ignored.
New permission_target_snapshot() adopts the current grants as the desired state, so a baseline can be created in one call.
The permission views have a new source column that explains why a privilege is held: owner, direct, PUBLIC or role:<name>.
New permission_fix_sql() returns the GRANT and REVOKE statements that would resolve every difference, and permission_apply() executes them (dry run by default).
New permission_diff_summary view collapses a large diff into one row per role, object type, schema and privilege.
Constraint violations now name the broken rule instead of reporting a generic check-constraint failure.
New role_drop() reassigns a role's objects to a successor, revokes every privilege granted to it and drops the role, in one transaction. role_dependencies() reports what still blocks the drop.
Bugfixes
Add the missing 1.2 -> 1.3 update script. A database created with version 1.2 could not be upgraded at all, because there was no update path from 1.2 to 1.3.
permission_target and default_acl_target are no longer writable by PUBLIC. The desired state is a compliance baseline, so only the extension owner and roles that are explicitly granted write access may change it. SELECT stays granted to PUBLIC so that permission_diffs() and default_acl_diffs() keep working for everybody.
Behaviour changes
permission_diffs() no longer reports privileges of roles that do not appear in permission_target and do not inherit from a role that does. Use all_permissions for an unfiltered audit.
The permission views gained a source column at the end. Queries that use SELECT * will see one more column.
PostgreSQL 15 or later is required. Earlier releases are no longer supported, because the unique constraint on permission_target uses NULLS NOT DISTINCT.
Tests
The regression suite is split into sample, upgrade, public, inheritance, constraints, default_acl, usability and role_drop.
The upgrade chain from 1.0 to 1.6, including the 1.2 → 1.3 step and the 1.5 → 1.6 migration of permission_target, is covered.
The suite is run against PostgreSQL 15 through 19.