Trigger based audit does not work on pg_dist_placement

[upmanish]

Trigger based audit enabled on Citus metadata table using superuser do not create any audit records.

create or replace function pg_dist_placement_audit_info() returns trigger as $pg_dist_placement_audit$ begin if (TG_OP = 'DELETE') THEN insert into pg_dist_placement_audit SELECT 'D', now(), user, OLD.*; elsif (TG_OP = 'UPDATE') THEN insert into pg_dist_placement_audit SELECT 'U_OLD', now(), user, OLD.*; insert into citus.pg_dist_placement_audit SELECT 'U_NEW', now(), user, NEW.*; elsif (TG_OP = 'INSERT') THEN insert into pg_dist_placement_audit SELECT 'I', now(), user, NEW.*; end if; return null; end; $pg_dist_placement_audit$ language plpgsql;

create trigger pg_dist_placement_audit_trigger after insert or update or delete on pg_dist_placement for each row execute procedure pg_dist_placement_audit_info();

Audit table “pg_dist_placement_audit” is never populated with audit records when there is a DML in the pg_dist_placement table following creating/dropping of a distributed table. However, this works perfectly fine while manually performing DML operations in “pg_dist_placement” table.

[onderkalaci]

Audit table “pg_dist_placement_audit” is never populated with audit records when there is a DML in the pg_dist_placement table following creating/dropping of a distributed table.

Citus uses low-level C functions to modify the metadata tables. And, these low level functions do not fire the triggers.

And, this is the same for Postgres' catalog tables. But, I think the UX is better, PG throws error immediately:

CREATE TRIGGER t_if_modified_trg FTER INSERT OR UPDATE OR DELETE ON pg_class 
FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func();
ERROR:  permission denied: "pg_class" is a system catalog

Even if you relax this via allow_system_table_mods = true, the trigger is still not fired.

Citus should probably enforce the same/similar checks for its catalog tables

[velioglu]

We can add a new metadata table and save debug_query_string from each Citus function updating metadata. So, we can track such queries and use the new metadata table for operational purposes.