Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

david.g.johnston@xxxxxxxxx wrote:

You are correct that the behavior here is not explicitly documented [where] I would expect to find it.

My expectation is that the trigger owner is the context in which the trigger function is executed. Table owners can grant the ability to other roles to create triggers on the owned tables should the trigger owner and table owner require different roles. Triggers are like views in this regard.

I don't expect cascade update/cascade delete to be a factor here at all, other than making the process easier to perform. This extends from the prior expectation.

I expect [all this] not because I can point to a place where all that is said.

I would for sure expect deviations to be mentioned, and would find explicit documentation to be reasonable to add if someone pushes forward such a change.

Good. We're converging. Thanks, David. I think that this  is a fair summary:

1. The PG doc very likely has no clear statement, anywhere, of the rules that govern the behavior in the class of trigger scenarios under discussion.

2. An expectation of what the rules are has emerged:

> the invoking role for a trigger's function is the role[*] that owns the trigger.

Thereafter, the privilege domain in which the function executes is governed by the ordinary, separable, rules about "security definer" versus "security invoker".

I mentioned this from the "CREATE TRIGGER" section:

> https://www.postgresql.org/docs/current/sql-createtrigger.html#SQL-CREATETRIGGER-NOTES

> To create or replace a trigger on a table, the user must have the TRIGGER privilege on the table. The user must also have EXECUTE privilege on the trigger function.

It gives a strong hint that David's expectations are sound.

3. The outcomes with the eight triggers that I tested show straight buggy behavior in six cases. Moreover, because David said "don't expect cascade… to [matter]", the outcomes in the other two cases might show correct behavior only by accident.

It seems to me, therefore, that a carefully constructed, "single click", reproducible testcase is needed. I have this on my laptop. But, of course, I need to refine it a bit and review it thoroughly. It ended up in several .sql scripts called by a master script. This naturally implies a .zip file as the delivery vehicle.

Nobody has told me how an outsider like me can deliver such a .zip file, together with its typographically nuanced external documentation, to readers of plsql-general. So this is what I'll do:

I'll create a placeholder GitHub issue in "yugabyte/yugabyte-db" and send you the URL. Anybody can access this repo, read the account of the issues, and download an attached .zip of a testcase. I'll mention in my account that the behavior that I observe in YugabyteDB reproduces exactly in PG 14.4, that the YugabyteDB issue is filed for tracking purposes, and that I'll update the account with more information from the PG folks in due course.

Please bear with me. It might be a few days before I'm able to send you all the promised URL.

____________________

[*] The PG doc favors using "role" in sentences like this rather than "user"—even though a role might have child rows in an indefinitely deep hierarchy. I trust that this point is fully separable from what matters in the present triggers scenario.