Re: Transaction control in SECURITY DEFINER procedures

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(at)gmx(dot)net>
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(at)gmail(dot)com> 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(at)momjian(dot)us> https://momjian.us
EnterpriseDB https://enterprisedb.com

The usefulness of a cup is in its emptiness, Bruce Lee