Is there a way to disable updates/deletes but still allow triggers to perform them?

by

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:

More Related Contents:

  1. INSERT with dynamic table name in trigger function
  2. Passing user id to PostgreSQL triggers
  3. Permission denied for relation
  4. How do I get a column with consecutive, increasing numbers, without having any numbers missing?
  5. Grant all on a specific schema in the db to a group role in PostgreSQL
  6. Refresh a materialized view automatically using a rule or notify
  7. Postgresql insert trigger to set value
  8. ERROR: unterminated quoted string at or near
  9. PostgreSQL – set a default cell value according to another cell value
  10. How do I temporarily disable triggers in PostgreSQL?
  11. psql: FATAL: Ident authentication failed for user “postgres”
  12. Use multiple conflict_target in ON CONFLICT clause
  13. PostgreSQL -must appear in the GROUP BY clause or be used in an aggregate function
  14. How to find table creation time?
  15. What is the format for the PostgreSQL connection string / URL?
  16. Go and IN clause in Postgres
  17. Where does PostgreSQL store the database?
  18. How to add “on delete cascade” constraints?
  19. Is there a way to disable function overloading in Postgres
  20. Can I make a plpgsql function return an integer without using a variable?
  21. How can I start PostgreSQL on Windows?
  22. PostgreSQL – max number of parameters in “IN” clause?
  23. SQLAlchemy, Psycopg2 and Postgresql COPY
  24. How to find out if an upsert was an update with PostgreSQL 9.5+ UPSERT?
  25. How to reset sequence in postgres and fill id column with new data?
  26. Differentiate inserted and updated rows in UPSERT using system columns
  27. Postgres ERROR: could not open file for reading: Permission denied
  28. Custom ORDER BY Explanation
  29. How can I send some http request from postgresql function or trigger
  30. Postgres default sort by id – worldship

Leave a Comment