Is there a way to disable updates/deletes but stil

Question

Basically, I want to be able to use the REVOKE command to disable UPDATE and DELETE, but I still want the triggers on a table to update my rows.

My triggers perform on newly inserted rows, and update a specific field. So I still want this behaviour, but wouldn't they be disabled with REVOKE or with a RULE. (I saw an SO post)

Is there a way to keep using the UPDATE/INSERT commands in TRIGGERS but disabling the rest?

Answer 1

Yes, this is possible.

Triggers are run with the privileges of the trigger function, defaulting to SECURITY INVOKER which means, the trigger function is effectively executed with the privileges of the current_user, in your case the one inserting rows.

If the current user does not have the required privileges for the tables your trigger function operates on, your original operation in the underlying table will error out.

However, you can use SECURITY DEFINER for the trigger function to have this function run with the privileges of the OWNER of the function.

If you have a superuser own the trigger function, it can do everything - which would be a possible security hazard. Consider the instructions in the manual about Writing SECURITY DEFINER Functions Safely.

But it's wiser to make a plain role with just the necessary privileges OWNER of the trigger function. You can even just create a "daemon" role without login, acting as privilege bundle for such operations. You would then grant only the needed privileges (on schemas, tables, sequences ...) to this daemon role. For more sophisticated designs you should bundle privileges in "group roles" (again, no login) and grant these group roles to roles that need it (to the daemon role in this example), effectively making them "member of the group". I do that a lot.

Also consider this related question on dba.SE concerning the privileges on the function itself.