Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario
| От | Bryn Llewellyn |
|---|---|
| Тема | Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario |
| Дата | |
| Msg-id | 615A1F2A-EB13-43D8-A97C-6344DD9C892F@yugabyte.com обсуждение исходный текст |
| Ответ на | Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Ответы |
Aw: Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario
(Karsten Hilbert <Karsten.Hilbert@gmx.net>)
|
| Список | pgsql-general |
david.g.johnston@gmail.com wrote:
You said "simply returning NULL for all triggers seems suspect.". Yes, it would be! I took my approach from "The usual idiom in DELETE triggers is to return OLD." in the "Overview of Trigger Behavior" section. And, indeed, I do say "return old" in the code of each of my eight triggers. I'm embarrassed to say that I simply did a typo in my email account.
…you should spend some time making a smaller code example that still shows the desired behavior but can be easily read and executed by others. In particular, your description of simply returning NULL for all triggers seems suspect. If only two of the eight triggers show the problem then the example only needs two triggers to show the presence of the unexpected current_role and to get clarity why it is that. All the stuff that is working as expected is just noise; that is the stuff that can be summed up with words on a first pass.
As the behavior you are pointing out has nothing to do with pl/pgsql specifically, but rather the runtime environment of triggers in the server, it is not surprising the lack of discussion of this topic in that part of the documentation.
You said "simply returning NULL for all triggers seems suspect.". Yes, it would be! I took my approach from "The usual idiom in DELETE triggers is to return OLD." in the "Overview of Trigger Behavior" section. And, indeed, I do say "return old" in the code of each of my eight triggers. I'm embarrassed to say that I simply did a typo in my email account.
I'll be happy to make a smaller example. It will, however, need to create users, a database, schemas, and some number of triggers. Because the triggers, their functions, and everything else about them follow a pattern, I can use "format()" and dynamic SQL to generate them. I'll still need those three "security definer" procedures to make the table changes that I explained. And the code to call these procedures to implement the test. So the result won't be exactly small. But, while I'm generating the triggers, I may just as well generate all eight. After all, how would I know which of the eight to skip while I don't know the intended rules for the current_role?
Am I missing something about how this list is meant to work? Email attachments don't make it to the archive for posts to this list (https://www.postgresql.org/list/pgsql-general/). Is there a reliable place where I can post a code .zip so that readers of the list can download it? I asked ages ago about a GitHub-style scheme for filing and tracking PG issues. but I was told that this email list, and its cousins, is the system that you (all) prefer.
Meanwhile, I'd hoped that what I said would prompt a simple statement of what rules are intended. You implied that it's elsewhere in the doc than what I thought would be the place to look. A cross-reference to that section, from the "Triggers on Data Changes" section (and other sections like "CREATE TRIGGER") would help.
Could you please point me to where the statement of rules that I'm seeking is made? Or, failing that, simply tell me what the intended rules are?
Should I read your "All the stuff that is working as expected is just noise; that is the stuff that can be summed up with words on a first pass." to mean that the intended role is that the current_role in a "security invoker" trigger function is the role that does the trigger-firing DML on the table?
B.t.w., here's what I had tried before sending the email that started this thread. (I didn't want to bore you all with this long story.)
I looked in the "CREATE TRIGGER" section. I found this:
« 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. »
I suppose that this covers the case where a pre-existing trigger function has a different owner than will the to-be-created trigger that wants to use it.
This rule is certainly in the general space where the answer to my question might be found. But this doc extract doesn't answer it explicitly. However, it does seem to imply that the invoking role for a trigger function will be the owner of the trigger that uses it. And in my example, this was the table owner. (In other words, the Oracle Database rule.) But this is not what I see in six out of the eight of my tests.
There are no hits on the "CREATE TRIGGER" page for "current_role" or "current_user" (with or without the underscore). Neither are there any hits if I say "effective user" or "effective role". I don't know what else to try.
I looked in the "CREATE FUNCTION" section. It does, of course, explain the "security invoker" and "security definer" notions. But doesn't say anything there to inform my question. (There are only two hits for the word "trigger" on the page. And neither is relevant for my question.)
I tried "Overview of Trigger Behavior" (https://www.postgresql.org/docs/current/trigger-definition.html). But again found nothing.
Of course, I tried Google, and the PG doc's own search, for all the wordings that I could dream up along the lines of « PostgreSQL current role when security invoker trigger function fires ». But I got nothing helpful.
В списке pgsql-general по дате отправления:
Предыдущее
От: "David G. Johnston"Дата:
Сообщение: Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario
Следующее
От: Laura SmithДата:
Сообщение: Modelling a web CMS in Postgres ... a little advice needed