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.

Functions

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;

Grant statement

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;

Run function

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';

Disable system triggers with regular user account

Functions

Grant statement

Run function

Verify that triggers are disabled

See also