Just want to make sure I'm understanding the permissions needed for trigger functions as well as making sure this is what the developers intended before I go assuming things will always work this way. Also as a sanity check for myself that I'm not missing something obvious.
I have an extension (
https://github.com/omniti-labs/mimeo) that does logical replication. The setup functions for the trigger-based DML replication automatically create the queue table, trigger function & trigger on the source database. I'm working on fixing a bug where the correct permissions weren't being given and in my testing for a fix found that just simply giving the trigger function SECURITY DEFINER fixed all the problems and I don't have to even bother looking up which roles currently have write permissions on the source table to set them on the queue table and trigger function.
I understand how SECURITY DEFINER solves the issue of the function writing to the queue table (function and queue table owner are the same). But I would've thought that any roles with write privileges to the table would've needed to be given EXECUTE permissions on the trigger function. I thought maybe the trigger function was being called as the owner of the table, but apparently even the owner of the table doesn't need these execute permissions. Reading through the docs on triggers, I didn't see anything mentioned about how this is expected to work. Examples are in the gist link below. You can see the owner has no explicit permissions to the trigger function and inserts still work even after revoking PUBLIC.
https://gist.github.com/keithf4/83c5c6516e2726609675
