Tracking role modification timestamps in pg_authid / pg_roles

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