Thread: Transaction Management
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/12/plpgsql-transactions.html Description: Hello, We are looking to use transaction management and have run into a limitation when the procedure is security definer. There doesn't appear to be any reference to this in the https://www.postgresql.org/docs/13/plpgsql-transactions.html so we cannot see when the limitation is lifted. Are you able to clarify the situation with this between PG11, 12 and 13? We are hoping the limitation is lifted as we cannot see what the reason for the limitation is. Thanks, James
On Tue, Jul 7, 2020 at 10:46:59AM +0000, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/12/plpgsql-transactions.html > Description: > > Hello, > > We are looking to use transaction management and have run into a limitation > when the procedure is security definer. There doesn't appear to be any > reference to this in the > https://www.postgresql.org/docs/13/plpgsql-transactions.html so we cannot > see when the limitation is lifted. > > Are you able to clarify the situation with this between PG11, 12 and 13? We > are hoping the limitation is lifted as we cannot see what the reason for the > limitation is. You are going to need to be more specific if you want help, and this doesn't seem like a documentation problem either. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee
Hi Bruce,
Thanks for taking a look at this.
Thanks for taking a look at this.
This is really about making a limitation obvious. In other parts of the documentation, like table partitioning, limitations are really spelled out, good solid health warnings for those uninitiated. For transactions we have found that procedures cannot handle commit and rollbacks when marked as security definer, we found this out through trial and error (we don't have a clue as to why the limitation is in there and are trying to figure it out). The only reference we found in the documentation was on https://www.postgresql.org/docs/11/sql-createprocedure.html with the line "A
SECURITY DEFINER
procedure cannot execute transaction control statements (for example, COMMIT
and ROLLBACK
, depending on the language)." We only spotted this when digging as to find any information as to why we received this error.My reasoning of raising the point is it would have been really helpful for us if the limitation was listed on the versions for https://www.postgresql.org/docs/13/plpgsql-transactions.html so we didn't sleepwalk into a limitation and if documented there we can see when the limitation is raised (we can't figure out what the problem is with security definer as it works so well for our use case).
Thanks,
James
On Tue, 14 Jul 2020 at 21:23, Bruce Momjian <bruce@momjian.us> wrote:
On Tue, Jul 7, 2020 at 10:46:59AM +0000, PG Doc comments form wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/12/plpgsql-transactions.html
> Description:
>
> Hello,
>
> We are looking to use transaction management and have run into a limitation
> when the procedure is security definer. There doesn't appear to be any
> reference to this in the
> https://www.postgresql.org/docs/13/plpgsql-transactions.html so we cannot
> see when the limitation is lifted.
>
> Are you able to clarify the situation with this between PG11, 12 and 13? We
> are hoping the limitation is lifted as we cannot see what the reason for the
> limitation is.
You are going to need to be more specific if you want help, and this
doesn't seem like a documentation problem either.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
James King
COO & Co-Founder
E: W: A: | Bentima House, 168-172 Old Street EC1V 9BP |
On Wed, Jul 15, 2020 at 10:52:04AM +0100, James King wrote: > Hi Bruce, > > Thanks for taking a look at this. > > This is really about making a limitation obvious. In other parts of the > documentation, like table partitioning, limitations are really spelled out, > good solid health warnings for those uninitiated. For transactions we have > found that procedures cannot handle commit and rollbacks when marked as > security definer, we found this out through trial and error (we don't have a > clue as to why the limitation is in there and are trying to figure it out). > The only reference we found in the documentation was on https:// > www.postgresql.org/docs/11/sql-createprocedure.html with the line "A SECURITY > DEFINER procedure cannot execute transaction control statements (for example, > COMMIT and ROLLBACK, depending on the language)." We only spotted this when > digging as to find any information as to why we received this error. > > My reasoning of raising the point is it would have been really helpful for us > if the limitation was listed on the versions for https://www.postgresql.org/ > docs/13/plpgsql-transactions.html so we didn't sleepwalk into a limitation and > if documented there we can see when the limitation is raised (we can't figure > out what the problem is with security definer as it works so well for our use > case). Well, we try to put warnings at the places where it is most relevant, and I assume the CREATE PROCEDURE is the most relevent place to have it, and we do. Putting it in other places can get tiresome on the reader. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EnterpriseDB https://enterprisedb.com The usefulness of a cup is in its emptiness, Bruce Lee