Re: Failed assertion due to procedure created with SECURITY DEFINER option

On Fri, Jun 29, 2018 at 5:26 PM Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:
>
> On 6/29/18 13:07, amul sul wrote:
> > This happens because of in fmgr_security_definer() function we are
> > changing  global variable SecurityRestrictionContext and in the
> > StartTransaction() insisting it should be zero, which is the problem.
>
> Hmm, what is the reason for this insistation?
>
> We could work around this for now by prohibiting transaction commands in
> security definer procedures, similar to what we do in procedures with
> GUC settings attached.
>

I am not sure that I have understood this, apologies. Do you mean by
the following case:

postgres=# CREATE PROCEDURE transaction_test1() LANGUAGE plpgsql
SECURITY DEFINER SET work_mem to '16MB'
AS $$ BEGIN
          COMMIT;
 END $$;
CREATE PROCEDURE

postgres=# CALL transaction_test1();
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function transaction_test1() line 2 at COMMIT

Thanks.

Regards,
Amul