Can a non-owner of a table be allowed to disable the table’s triggers?

Posted on

Question :

I would like for one user who is not a table owner, give the permissions to turn off and turn on the triggers. Can it be done?

Answer :

As the owner of the table, you can create a function (or, if you are on Postgres 11 or newer, a procedure) which disables the trigger of your choice like:

CREATE OR REPLACE FUNCTION disable_this_trigger() 
AS $$
ALTER TABLE something DISABLE TRIGGER this_trigger;

Then grant the necessary privilege to your user to execute the function:

GRANT EXECUTE ON FUNCTION disable_this_trigger() TO alice;

Now alice will be able to disable that one trigger. You can either extend this function with a switch (on/off, for example) to make enabling possible, or create a similar function that does the opposite of this one.

The trick is in SECURITY DEFINER. It makes the function running with the privileges of the user that defines (creates) the function. As said above, this has to be the owner of the table, because only it (and superusers) can disable the triggers on it.

Leave a Reply

Your email address will not be published. Required fields are marked *