Thread: Transaction control in SECURITY DEFINER procedures

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?

Regards, Chris Sterritt

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