5

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?

8

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() 
RETURNS void 
LANGUAGE SQL 
AS $$
ALTER TABLE something DISABLE TRIGGER this_trigger;
$$ SECURITY DEFINER;

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.