> Become a superuser again and commit: > > RESET ROLE; > > COMMIT; > NOTICE: current_user = postgres > > > So a deferred constraint trigger does not run with the same security context > as an immediate trigger. This is somewhat nasty in combination with > SECURITY DEFINER functions: if that function performs an operation, and that > operation triggers a deferred trigger, that trigger will run in the wrong > security context. > > This behavior looks buggy to me. What do you think? > I cannot imagine that it is a security problem, though.
This looks to me like another reason that triggers should run as the trigger owner. Which role owns the trigger won’t change as a result of constraints being deferred or not, or any role setting done during the transaction, including that relating to security definer functions.
Right now triggers can’t do anything that those who can INSERT/UPDATE/DELETE (i.e., cause the trigger to fire) can’t do, which in particular breaks the almost canonical example of using triggers to log changes — I can’t do it without also allowing users to make spurious log entries.
Also if I cause a trigger to fire, I’ve just given the trigger owner the opportunity to run arbitrary code as me.
I just realized one problem with running a deferred constraint trigger as the triggering role: that role might have been dropped by the time the trigger executes. But then we could still error out.
This problem is also fixed by running triggers as their owners: there should be a dependency between an object and its owner. So the trigger-executing role can’t be dropped without dropping the trigger.