Re: Tracking role modification timestamps in pg_authid / pg_roles

Hi everyone,

On Fri, 21 Aug 2026 at 22:29, Gabriele Bartolini <
gabriele(dot)bartolini(at)enterprisedb(dot)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