Trigger function permissions

From: Keith Fiske <keith(at)omniti(dot)com>
To: PGSQL Mailing List <pgsql-general(at)postgresql(dot)org>
Subject: Trigger function permissions
Date: 2014-06-06 17:13:44
Message-ID: CAG1_KcAHRGBfV22v5pK0dOCkmEF40Wj+S_2JOCOCnQXhuY6K7g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
http://www.keithf4.com

Browse pgsql-general by date

  From Date Subject
Next Message Alan Hodgson 2014-06-06 18:00:36 Re: bytea Issue - Reg
Previous Message Bosco Rama 2014-06-06 16:12:32 Re: How to select rows for which column has empty array ?