7

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?

Community
  • 1
  • 1
Paco
  • 4,520
  • 3
  • 29
  • 53
  • If your trigger updates the rows, that are being inserted - it is a sign of a bad design. Can you show as some of your triggers or tell, what do you do with them? – Ihor Romanchenko Jul 26 '13 at 17:29
  • If you need to `update a specific field` of an inserted record - you can just assign the desired value to `NEW.specific_field`. You do not need a separate update. – Ihor Romanchenko Jul 26 '13 at 17:31
  • I didn't design it, but I don't know if I would have done anything better to be honest. I haven't looked at the triggers just yet. Maybe I could use this, I don't know. I'll let you know. Thanks – Paco Jul 26 '13 at 19:01

1 Answers1

19

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.

Consider this related answer on dba.SE concerning the privileges on the function itself:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks, that looks really good. The explanation you provided is great. I'll need to check that when I'll have a chance. Thanks again :) – Paco Jul 26 '13 at 18:59