Ciao everyone, I'd like to propose adding a small column to pg_authid (rollastupdated timestamptz), that records when a role was last created or altered, and get feedback on the idea before polishing it for a commitfest (a working prototype is attached).
Why? Tools that manage PostgreSQL roles declaratively all reconcile in the same way: given a desired set of roles and attributes, make the live cluster match it, repeatedly and idempotently. That covers Kubernetes operators, Ansible playbooks and Terraform providers alike. The mechanism differs, but the problem doesn't. Full disclosure on my main interest: I maintain CloudNativePG, a Cloud Native Computing Foundation (CNCF) Project. However, the solution benefits any configuration manager that addresses PostgreSQL roles. Today, there is no cheap way to ask, "Has this role changed since I last looked at it?" The two available options are: - Re-issue ALTER ROLE unconditionally on every pass (idempotent but never free) - Diff every attribute client-side by querying pg_authid/pg_roles and comparing field-by-field against desired state. This works, but every tool ends up reimplementing the same comparison logic, and it still cannot tell whether somebody else altered the role between two passes. A single last-modified timestamp collapses both into a cheap "SELECT rollastupdated FROM pg_roles WHERE rolname = ANY(...)" up front, compared against a locally cached value, and issuing ALTER ROLE only for the roles that actually moved. This is a change-detection pattern similar to an HTTP ETag, rsync's mtime, or a Kubernetes resourceVersion. Notably, it is the one thing you cannot currently build for roles from the outside. Why not existing workarounds? DDL event triggers, or pgaudit, can record role changes, but both mean installing and maintaining server-side objects or an extension, running code on every DDL statement cluster-wide, plus a separate table or log to hold the history and prune it. That is a lot of moving parts for a question as small as "Did this role change since I last looked?". A dedicated column needs none of it: always on, no dependency on optional cluster settings, and one extra column in a SELECT that these tools already issue against pg_roles. Design: - New column: pg_authid.rollastupdated timestamptz, nullable, added after rolvaliduntil. - Set to now() (via GetCurrentTimestamp()) by CreateRole(). - Set to now() by AlterRole() whenever it runs. - Set to now() by RenameRole() (ALTER ROLE ... RENAME TO). - Set to now() by AlterRoleSet() (ALTER ROLE ... SET/RESET), since role-level GUC overrides are role state too, even though they live in pg_db_role_setting rather than pg_authid itself. - Exposed as rollastupdated in pg_roles, and as lastupdated in pg_shadow/pg_user for consistency with those views' naming. - NULL only for roles created during initdb that have never been altered. Semantics: "time of the last CREATE/ALTER ROLE command executed against this role", deliberately not "time a value last actually changed". The timestamp advances whenever such a command completes successfully, even if it was a no-op (e.g. re-setting an attribute to the value it already had). I have been considering the following aspects: - Doing better than that is not possible for passwords: pg_be_scram_build_secret() re-salts on every call, so an identical password is indistinguishable from a changed one. - The value is not preserved by pg_dumpall or pg_upgrade, which replay CREATE ROLE / ALTER ROLE against the new cluster, so restored roles get the time of the restore. - ALTER ROLE <self> SET is already allowed for unprivileged roles, so that path now writes pg_authid where only pg_db_role_setting was touched before. If that is unwelcome, the narrower option is to drop the ALTER ROLE ... SET case altogether. - Out of scope for now: GRANT/REVOKE of role membership, and psql's \du. The attached prototype (available also at https://github.com/gbartolini/postgres/pull/2) covers the catalog column and bootstrap data, CreateRole/AlterRole/RenameRole/AlterRoleSet, pg_roles/pg_shadow/pg_user, documentation, and a regression test. It applies cleanly on the master branch, and passes the new test as well as the existing role-related suites. Happy to hear whether this is a direction the community would be open to at all, and whether rollastupdated is a reasonable name and column placement, before I take it further. Thanks, Gabriele -- Gabriele Bartolini VP, Chief Architect, Kubernetes enterprisedb.com / Melbourne, Australia
0001-Track-role-modification-timestamp-in-pg_authid.rolla.patch
Description: Binary data
