Thread

1. PostgreSQL Account and Object Timestamp Logging

   張宸瑋 <kenny020307@gmail.com> — 2025-09-19T02:55:58Z

   Hello everyone,
   
   I would like to ask if there are any built-in features or extensions in
   open-source PostgreSQL—aside from the AUDIT mechanisms—that allow us to
   query or log the creation time of user accounts and database objects.
   
   The reason for this inquiry is that while audit logs do capture these
   events, they may eventually be rotated out or removed due to housekeeping
   policies. Having a dedicated table or view to permanently record the
   creation or modification time of accounts and objects would be very helpful
   for audit and security compliance purposes.
   
   Thank you in advance for your insights!
   

2. Re: PostgreSQL Account and Object Timestamp Logging

   David G. Johnston <david.g.johnston@gmail.com> — 2025-09-19T03:07:19Z

   On Thursday, September 18, 2025, 張宸瑋 <kenny020307@gmail.com> wrote:
   >
   >
   > I would like to ask if there are any built-in features or extensions in
   > open-source PostgreSQL—aside from the AUDIT mechanisms—that allow us to
   > query or log the creation time of user accounts and database objects.
   >
   >
   That information is not captured/retained by a PostgreSQL cluster.  See
   many previous discussions from the mailing list archives.
   
   David J.
   

3. Re: PostgreSQL Account and Object Timestamp Logging

   Dominique Devienne <ddevienne@gmail.com> — 2025-09-19T08:13:47Z

   On Fri, Sep 19, 2025 at 4:56 AM 張宸瑋 <kenny020307@gmail.com> wrote:
   > *log* the creation time of user accounts and database objects.
   
   Yes, indirectly, via event triggers:
   https://www.postgresql.org/docs/current/event-trigger-matrix.html
   
   But that's specific to a DB, does [NOT fire for CREATE USER/ROLE][1],
   only some objects,
   and unfortunately, must be SUPERUSER to install it, DB OWNER is not
   enough (pity...).
   
   [1]: https://www.postgresql.org/docs/current/event-trigger-matrix.html