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 <[email protected]>
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 <[email protected]> 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 <[email protected]> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
