Creating a database role in Postgresql, which is only intended for writing data in certain database tables. The tables, however, are associated with trigger functions, which additionally require SELECT/INSERT permissions on the rest of the database.
I don't know whether restricting user role permissions to INSERT only statements on certain database tables (and then executing related triggers as an owner, sudoer) would make any improvement on the overall security of the system (or would introduce additional security risks) compared to just permitting broader read/write access on the entire database (executing the triggers as current user.) It would be desirable, that a user writing to the database would not be able to read any data. (I'm new to DBMS anyway)
MS-SQL supports WITH EXECUTE AS OWNER OPTION in CREATE TRIGGER statement Permissions on Trigger Problem, just thinking if there is some workaround in Postgres.
1 Answer 1
If you want the trigger function to run with permissions different from the ones of the user who runs the INSERT, define the trigger function as SECURITY DEFINER. Then it will run with the permissions of the function owner.