patternsqlModerate
Can a non-owner of a table be allowed to disable the table's triggers?
Viewed 0 times
ownercantheallowednondisabletriggerstable
Problem
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?
Solution
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:
Then grant the necessary privilege to your user to execute the function:
Now
The trick is in
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.Code Snippets
CREATE OR REPLACE FUNCTION disable_this_trigger()
RETURNS void
LANGUAGE SQL
AS $$
ALTER TABLE something DISABLE TRIGGER this_trigger;
$$ SECURITY DEFINER;GRANT EXECUTE ON FUNCTION disable_this_trigger() TO alice;Context
StackExchange Database Administrators Q#230278, answer score: 10
Revisions (0)
No revisions yet.