Disable system triggers with regular user account
I ran into a challenge where a regular user account wanted to disable system triggers for some reason, possibly to run
ALTER TABLE tbl DISABLE TRIGGER ALL; before an import of data to preserve any structure they're importing without being affected by constraints.
Here is a similar issue described in one of Wikimedia's KB articles.
But since it was difficult to contact the vendor of this application, making them insert two statements in their transaction was an uncertain proposal.
So here is a workaround that you can do outside of the transaction. Before the transaction you run a function that disables all triggers, after you toggle them back on again.
The functions are created by the superuser and with security definer, meaning they will run as the superuser. Remember to create them on the correct DB.
# create or replace function enable_triggers(_tblname regclass) returns void language plpgsql as $func$ begin execute format('alter table %s enable trigger all', _tblname); end $func$ security definer; # create or replace function disable_triggers(_tblname regclass) returns void language plpgsql as $func$ begin execute format('alter table %s disable trigger all', _tblname); end $func$ security definer;
Your user must then be granted
EXECUTE privileges to run the functions.
grant execute on function enable_triggers to my_user; grant execute on function disable_triggers to my_user;
The user can then do this to run the functions before and after the transaction is executed.
select disable_triggers('my_tablename'); # Do your transaction here select enable_triggers('my_tablename');
Verify that triggers are disabled
To verify that the triggers were in fact disabled, without running the import job, I executed this query.
# SELECT pg_namespace.nspname, pg_class.relname, pg_trigger.tgname, pg_trigger.tgenabled, pg_trigger.tgrelid FROM pg_trigger JOIN pg_class ON pg_trigger.tgrelid = pg_class.oid JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace where relname='my_tablename';
- How a regular user can disable system triggers using a security definer function
- How to accept a table name as function argument in Plpgsql in a safe way
- Alternative method to disable triggers for a session
- How to see if triggers are enabled