Keep Track of Who Created a Table and When...

When a table is created in YSQL, the catalog does not store information about the user who created it or the time of creation.

				
					yugabyte=# CREATE TABLE some_table(c1 INT);
CREATE TABLE

yugabyte=# \x
Expanded display is on.

yugabyte=# SELECT * FROM pg_tables WHERE tablename = 'some_table';
-[ RECORD 1 ]-----------
schemaname  | public
tablename   | some_table
tableowner  | yugabyte
tablespace  |
hasindexes  | f
hasrules    | f
hastriggers | f
rowsecurity | f

yugabyte=# SELECT * FROM pg_class WHERE relname = 'some_table';
-[ RECORD 1 ]-------+-----------
oid                 | 16408
relname             | some_table
relnamespace        | 2200
reltype             | 16410
reloftype           | 0
relowner            | 13514
relam               | 2
relfilenode         | 16408
reltablespace       | 0
relpages            | 0
reltuples           | -1
relallvisible       | 0
reltoastrelid       | 0
relhasindex         | f
relisshared         | f
relpersistence      | p
relkind             | r
relnatts            | 1
relchecks           | 0
relhasrules         | f
relhastriggers      | f
relhassubclass      | f
relrowsecurity      | f
relforcerowsecurity | f
relispopulated      | t
relreplident        | c
relispartition      | f
relrewrite          | 0
relfrozenxid        | 0
relminmxid          | 0
relacl              |
reloptions          |
relpartbound        |

That information is available in the PostgreSQL logs, but it becomes difficult to locate after a few days, weeks, or months—right when a manager or auditor inevitably asks the DBA, “Who created table X, and when was it created?”

To be prepared for that tough question, you can start tracking table creation information now using an event trigger!

Example:

First, create a table to store the audit information:

				
					CREATE TABLE audit_table_creation (
  id BIGSERIAL PRIMARY KEY,
  schema_name TEXT NOT NULL,
  table_name TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT now(),
  created_by TEXT NOT NULL
);

Next, create a database function to capture the table creation info:

				
					CREATE OR REPLACE FUNCTION log_table_creation()
RETURNS EVENT_TRIGGER AS $$
DECLARE
  obj RECORD;
  table_name TEXT;
  schema_name TEXT;
BEGIN
  FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
    IF obj.command_tag = 'CREATE TABLE' THEN
      schema_name := split_part(obj.object_identity, '.', 1);
      table_name := split_part(obj.object_identity, '.', 2);
      INSERT INTO audit_table_creation (schema_name, table_name, created_by)
        VALUES (schema_name, table_name, user);
    END IF;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

Finally, create an event trigger that fires for all DDL events, but filters specifically for CREATE TABLE:

				
					CREATE EVENT TRIGGER capture_table_creation ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE FUNCTION log_table_creation();

Now, whenever a user creates a table, the user, along with the date and time of creation, is recorded:

				
					yugabyte=# CREATE USER amey WITH superuser;
CREATE ROLE

yugabyte=# CREATE USER alan WITH superuser;
CREATE ROLE

yugabyte=# \! ysqlsh -h 127.0.0.1 -U alan -c "CREATE TABLE alan_table(c1 INT);"
CREATE TABLE

yugabyte=# SELECT * FROM audit_table_creation ORDER BY id;
 id  | schema_name | table_name |         created_at         | created_by
-----+-------------+------------+----------------------------+------------
   1 | public      | amey_table | 2025-03-13 02:29:49.606464 | amey
 101 | public      | alan_table | 2025-03-13 02:30:00.668995 | alan
(2 rows)

Have Fun!

Keep Track of Who Created a Table and When…