Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
menu search
person
Welcome To Ask or Share your Answers For Others

Categories

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
727 views
Welcome To Ask or Share your Answers For Others

1 Answer

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:


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
...