Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario - Mailing list pgsql-general
| From | Bryn Llewellyn |
|---|---|
| Subject | Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario |
| Date | |
| Msg-id | BE1F5B0B-EBA1-4A9E-9B61-762E3397EF4E@yugabyte.com Whole thread Raw |
| In response to | Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario (Bryn Llewellyn <bryn@yugabyte.com>) |
| Responses |
Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario
|
| List | pgsql-general |
bryn@yugabyte.com wrote:david.g.johnston@gmail.com 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.
Good. We're converging. Thanks, David… 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.
I've submitted the promised GitHub issue. It has an attached .zip of a self-contained, one-touch testcase. Here it is:
Everything points to a bug in that, no matter what the intended behavior is, the same rules should apply in all scenarios—but they don't. Further, I agree with David's:
I don't expect cascade update/cascade delete to be a factor here at all.
My testcase tries a gazillion variations (including what happens with a function-based constraint and when DML to one table "cascades" to another because of trigger action). After all, in the presence of one bug where something unexpected happens in one scenario, who knows what unexpected outcomes might be seen in other comparable scenarios?
Here's my summary of my findings, copied from the issues report:
«
It very much does seems that the intended rule is this:
- The value that current_role will report in a security invoker trigger function for a DML trigger is the role that does the DML to the table that fires the trigger.
It seems, too, that this testcase has revealed a long-standing bug—present at least as far back as PostgreSQL Version 11:
- The value that current_role will report in a security invoker trigger function for a DML trigger on a "details" table will be the owner of that table when its rows are cascade-deleted as a consequence of deleting its parent "masters" row. However, this buggy outcome is seen only for "before delete" triggers, both at “statement" level and at "row" level.
- The bug has an obvious downstream consequence: any operation on other tables that are done by such a trigger function that sees the wrong current_role will be executed by that wrong role—and so on down the line.
»
W.r.t. David's
My expectation is that the trigger owner is the context in which the trigger function is executed.
This can't be right because a trigger doesn't have an owner. You can see this from the "pg_trigger" table. It has its own "oid" and a "tgrelid". But it has no FK to "pg_roles" or to "pg_namespace”. In other words, a trigger isn't a first-class schema object. Rather, from the user's P.o.V., it’s uniquely identified by the table to which it's attached. In this sense, it's like a constraint (and especially like one that's based on a function). Each of these two, trigger and function-based-constraint, is a device that associates a "call-back" function with a table so that the function is invoked in response to DML to the table.
The "pg_constraint" table, too, has no FK to "pg_roles". Mysteriously, though, it does have a "connamespace" column. Presumably this is a denormalization such that its value is always equal to "relnamespace" in "pg_class" for the table to which the constraint is attached..
It seems to me, therefore, that the role that creates the trigger is out of the picture once the trigger has been created. (There's no analogy here for a constraint because there's no "grant alter" on a table to correspond to "grant trigger" on a table.)
It seems, too, that the owner of the trigger function (and of the constraint function) is out of the picture at run-time (when these are "security invoker") for determining the value that "current_role" in such a function will report.
In other words, and as I see it, there are only two candidate answers: the role that does the DML that causes the function to be invoked; and the role that owns the table—DML to which causes the function to be invoked.
In most cases, current_role here shows who does the DML. But in those two rare corner cases that my testcase identified ("before statement" and "before row" for delete that's a consequence of cascade delete from a master) current_role shows the details table owner. (Then there's the knock-on. But this seems to be ordinarily expected and not, therefore, to require any special discussion.)
What do you all think?
pgsql-general by date: