On Tue, Jul 7, 2020 at 12:13:42PM +0100, Chris Sterritt wrote: > The documentation for CREATE PROCEDURE informs us "A SECURITY DEFINER procedure > cannot execute transaction control statements (for example, COMMIT and ROLLBACK > , depending on the language)." > > Can anyone let me know why this is so and are there any plans to remove this > restriction in future releases? I have a reproducible case: CREATE OR REPLACE PROCEDURE transcheck () AS $$ BEGIN PERFORM 1; COMMIT; END; $$ LANGUAGE plpgsql; CALL transcheck (); ALTER PROCEDURE transcheck SECURITY DEFINER; CALL transcheck (); --> ERROR: invalid transaction termination --> CONTEXT: PL/pgSQL function transcheck() line 4 at COMMIT and this is the reason: commit 3884072329 Author: Peter Eisentraut <peter_e@xxxxxxx> Date: Wed Jul 4 09:26:19 2018 +0200 Prohibit transaction commands in security definer procedures Starting and aborting transactions in security definer procedures doesn't work. StartTransaction() insists that the security context stack is empty, so this would currently cause a crash, and AbortTransaction() resets it. This could be made to work by reorganizing the code, but right now we just prohibit it. Reported-by: amul sul <sulamul@xxxxxxxxx> Discussion: https://www.postgresql.org/message-id/flat/CAAJ_b96Gupt_LFL7uNyy3c50-wbhA68NUjiK5%3DrF6_w%3Dpq_T%3DQ%40mail.gmail.com so, yes, it is possible, but no one has implemented it. This is the first complaint I have heard about this. -- Bruce Momjian <bruce@xxxxxxxxxx> https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
