Tracking role modification timestamps in pg_authid / pg_roles
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253504psql -h localhost -U postgresBuilt from patchset v1 (message #1), August 23, 2026 at 03:26 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253504_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253504_1 && git checkout t253504_1Patchset v1 (message #1) is on t253504_1
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
2026年8月21日(金) 12:08 Gabriele Bartolini <gabriele.bartolini@enterprisedb.com>:
(...)
Happy to hear whether this is a direction the community would be open to at all
Just my personal opinion, but at a glance it seems useful to have, for
the reasons you mention.
and whether rollastupdated is a reasonable name and column placement, before I take it further.
IMO "last" is implicit anyway, "rolupdated" conveys the same meaning
and is easier to read.
Regards
Ian Barwick
Hi,
On 2026-08-21 13:07:42 +1000, Gabriele Bartolini wrote:
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.
My question is why this is needed for pg_authid and not any of the other
catalogs? Adding one-off code for different catalogs one-by-one would ... not
be likely to result in good code.
Greetings,
Andres Freund
On 2026-08-21 Fr 12:40 AM, Andres Freund wrote:
Hi,
On 2026-08-21 13:07:42 +1000, Gabriele Bartolini wrote:
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.My question is why this is needed for pg_authid and not any of the other
catalogs? Adding one-off code for different catalogs one-by-one would ... not
be likely to result in good code.
Is the suggestion to track modification times for all catalog objects?
I'm not objecting, but that does seem like a pretty substantial change.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
Hi,
On August 21, 2026 7:05:15 AM EDT, Andrew Dunstan <andrew@dunslane.net> wrote:
On 2026-08-21 Fr 12:40 AM, Andres Freund wrote:
Hi,
On 2026-08-21 13:07:42 +1000, Gabriele Bartolini wrote:
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.My question is why this is needed for pg_authid and not any of the other
catalogs? Adding one-off code for different catalogs one-by-one would ... not
be likely to result in good code.Is the suggestion to track modification times for all catalog objects? I'm not objecting, but that does seem like a pretty substantial change.
I'm not suggesting to do anything. I want to know why pg_authid, given Gabrielle's logic does apply just as much to many other object types. I would bet a fair amount of money that if we add it for authid there will be a lot of patches for other objects. So it seems bogus to just discuss doing this for one catalog in isolation.
Andres
--
Sent from my phone. Please excuse my brevity.
Hi Andres,
On Fri, 21 Aug 2026 at 21:32, Andres Freund <andres@anarazel.de> wrote:
I'm not suggesting to do anything. I want to know why pg_authid, given
Gabrielle's logic does apply just as much to many other object types. I
would bet a fair amount of money that if we add it for authid there will be
a lot of patches for other objects. So it seems bogus to just discuss doing
this for one catalog in isolation.
Fair, and I should have drawn the line explicitly in the first mail rather
than leaving it to be inferred. Also, to Andrew's question: no, not all
catalog objects. The boundary I would propose is global objects, concretely
roles, databases and, if necessary, tablespaces.
On the expectation of a lot of follow-up patches, I think the boundary can
be based on event triggers: they already provide a way to get this
information for other object types, and they deliberately do not fire for
shared objects, which is what leaves global objects without an answer.
Happy to put together a PoC covering the three if that scope sounds
reasonable.
Thanks,
Gabriele
--
Gabriele Bartolini
VP, Chief Architect, Kubernetes
enterprisedb.com / Melbourne, Australia