Is there a way to disable updates/deletes but still allow triggers to perform them?
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:
- What are the privileges required to execute a trigger function in PostgreSQL 8.4?