github cybertec-postgresql/pg_permissions REL_1_6
pg_permissions 1.6

one hour ago

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.

Don't miss a new pg_permissions release

NewReleases is sending notifications on new releases.