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