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 v7 (message #7), August 27, 2026 at 05:06 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_7 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_7 && git checkout t253504_7Patchset v7 (message #7) is on t253504_7
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
Hi everyone,
On Fri, 21 Aug 2026 at 22:29, Gabriele Bartolini <
gabriele.bartolini@enterprisedb.com> wrote:
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.
As promised, I have created an initial proof of concept that adds a
last-updated timestamp to roles, databases, and tablespaces, labelled
`rolupdated`, `datupdated`, and `spcupdated`, respectively. I have attached
the difference file (diff), and the pull request (PR) is also available on
my GitHub fork of Postgres: https://github.com/gbartolini/postgres/pull/3.
The timestamp is updated as follows:
- CreateRole(), AlterRole(), RenameRole() and AlterRoleSet() for ALTER ROLE
... SET/RESET
- createdb(), AlterDatabase(), AlterDatabaseSet(), AlterDatabaseOwner(),
AlterDatabaseRefreshColl(), movedb() and RenameDatabase()
- CreateTableSpace(), RenameTableSpace(), AlterTableSpaceOptions() and, via
the generic AlterObjectOwner_internal() path, ALTER TABLESPACE ... OWNER TO
Please provide your feedback. I plan to conduct more tests inside
Kubernetes and will need to build the container image once I receive
confirmation that this is the correct approach.
Thanks,
Gabriele
--
Gabriele Bartolini
VP, Chief Architect, Kubernetes
enterprisedb.com / Melbourne, Australia
Gabriele Bartolini <gabriele.bartolini@enterprisedb.com> writes:
As promised, I have created an initial proof of concept that adds a
last-updated timestamp to roles, databases, and tablespaces, labelled
`rolupdated`, `datupdated`, and `spcupdated`, respectively.
I don't think that limiting this idea to global objects does one single
thing to address the problems with the concept, such as what are the
semantics for dump/restore. We've discussed modification timestamps
for database objects many times in the past, and rejected them every
single time. What exactly will be different this time?
regards, tom lane
Hi Tom,
I think it's worth addressing these architectural points directly rather
than continuing to narrow the patch's scope in response.
On Thu, 27 Aug 2026 at 15:07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I don't think that limiting this idea to global objects does one single
thing to address the problems with the concept, such as what are the
semantics for dump/restore.
On dump/restore: pg_dump and pg_dumpall are unaffected by this patch and,
structurally, could not carry this column either way. dumpRoles() in
pg_dumpall.c selects an explicit column list matching the attributes
settable via CREATE/ALTER ROLE, and there is no such clause for this
timestamp, so it was never a candidate for inclusion. A restored cluster,
therefore, gets the restoration time on every role, since pg_dumpall
replays CREATE ROLE then ALTER ROLE rather than copying the tuple, and
pg_upgrade goes through the same path. I'm not proposing to change that or
extend the grammar to carry it through. The semantics I have in mind are
strictly "the timestamp of the last CREATE or ALTER command executed
against this object on this server": instance-local, live-cluster state,
not object history. Under that definition, restamping on restore is
correct, not a tolerated limitation. A role that comes back through a
dump/restore is, from that server's point of view, a freshly written state,
and there is nothing to preserve.
We've discussed modification timestamps for database objects many times in
the past, and rejected them every single time. What exactly will be
different this time?
On "what's different this time": I went looking rather than asking you to
dig it up for me. I found a 2016 thread on pgsql-general proposing
relcreated on pg_class, where your answer was almost word-for-word what you
just told me: "it sounds trivial, until you start thinking about
backup/restore/replication situations, and then you realize that the
required semantics are far from clear. In practice, audit logs ... are a
far better solution." (
/messages/by-id/27666.1461177383@sss.pgh.pa.us
)
I also found a 2009 hackers thread proposing created/altered columns on
pg_proc and pg_class, where you leaned toward Josh Berkus's alternative of
a generic side table keyed by classid/objectid instead of per-catalog
columns, with pruning stale entries left as the open problem (
/messages/by-id/24270.1239663969@sss.pgh.pa.us
If either is among the threads you had in mind, I'd rather engage with them
directly than restate my own case blind.
Two things stand out specifically from the 2016 thread. First, your answer
there didn't turn on whether event triggers cover the object: pg_class
already supports them, and you pointed to audit logging instead, not to
event triggers. That's a real problem for the argument I was leaning on,
that this proposal only touches objects that event triggers can't see,
since it suggests the objection to a plain column isn't about coverage gaps
at all. Second, if audit logging is the standard answer, I don't think it
actually serves the declarative-reconciliation use case this patch targets,
and I'd rather say that plainly than assume it's covered. An audit log is
an append-only history that must be enabled, retained, and parsed. What a
reconciler needs is a single indexed value it can compare against a cached
one in a single SELECT, with the same shape as an HTTP ETag or a Kubernetes
resourceVersion. That's a cheaper problem than "what happened here", and I
don't think an audit log answers it any more cheaply than an event trigger
plus a side table does, which my first mail already conceded works, just at
the cost of extra machinery.
So, is the objection that a plain catalog column is never the right shape
for this class of problem, independent of what the alternative costs? If
so, I'd like to hear that plainly, since it means withdrawing this rather
than continuing to adjust its scope.
Thanks,
Gabriele
--
Gabriele Bartolini
VP, Chief Architect, Kubernetes
enterprisedb.com / Melbourne, Australia