Enhancements
- PUBLIC is a first-class grantee: it appears in all
*_permissionsviews and can be used inpermission_target. permission_diffs()is scoped to managed roles (those inpermission_targetplus the roles that inherit from them) and markedSTABLE. 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_targettable,default_acl_permissionsview anddefault_acl_diffs()function to manageALTER DEFAULT PRIVILEGES. permission_targetnow rejects empty permission lists,COLUMNtargets without a column name, duplicate targets and unknown roles.MAINTAINis 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
sourcecolumn that explains why a privilege is held:owner,direct,PUBLICorrole:<name>. - New
permission_fix_sql()returns theGRANTandREVOKEstatements that would resolve every difference, andpermission_apply()executes them (dry run by default). - New
permission_diff_summaryview 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.3update 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_targetanddefault_acl_targetare no longer writable byPUBLIC. The desired state is a compliance baseline, so only the extension owner and roles that are explicitly granted write access may change it.SELECTstays granted toPUBLICso thatpermission_diffs()anddefault_acl_diffs()keep working for everybody.
Behaviour changes
permission_diffs()no longer reports privileges of roles that do not appear inpermission_targetand do not inherit from a role that does. Useall_permissionsfor an unfiltered audit.- The permission views gained a
sourcecolumn at the end. Queries that useSELECT *will see one more column. - PostgreSQL 15 or later is required. Earlier releases are no longer supported, because the unique constraint on
permission_targetusesNULLS NOT DISTINCT.
Tests
- The regression suite is split into
sample,upgrade,public,inheritance,constraints,default_acl,usabilityandrole_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.