On Thu, Aug 11, 2022 at 8:41 PM Bryn Llewellyn <bryn@xxxxxxxxxxxx> wrote:
The conventions that this list's archive imposes (only plain text, quoted content indicated with successively deep chevron-style marks, explicit URLs twice as long as your arm, and baked-in hard line breaks at about a dozen words) makes comprehension quite hard—and structuring an account well-nigh impossible.
It encourages brevity so we consider it a feature ;) Usually, but not here, the complaint is that it seems to do that too well...
David J.
p.s. just for testing I've included the original email as both zip and text here.
My code example ended up quite big—so I'll show it to you all only if you ask. But it's easy to describe. My script does this:
1. It creates three non-superuser roles: "data", "code", and "client".
2. It creates a text-book masters-and-details table pair with owner "data".
Each table has the obvious PK column. The "details" table has the obvious FK column. And each table has a payload column "v". The FK constraint is defined thus:
constraint details_fk foreign key(mk)
references masters(mk)
match full
on delete cascade
on update restrict
initially deferred
3. It creates a DELETE trigger at each timing point on each table.
That's eight in all: [before|after] * [row|statement] * [masters|details].
The trigger functions have the same names as the triggers. And each name pair reflects the three degrees of freedom—for example "before_statement_masters".
Each trigger function simply does a "raise info" to report its name, the return value from "current_role", (and, in the "row" case, the value of "old.v"). And then it does "return null".
The trigger functions are owned by "data" and are explicitly marked "security invoker". (The results are the same without this marking—as expected.)
(In my real use case, the trigger functions are marked "security definer". But I did this test in order to understand the rules.)
"data" grants appropriate privileges to "code" to let its functions succeed.
4. It creates three "security definer" procedures with owner "code"
"cr_m_and_ds()" inserts specified "details" rows and then their specified "masters" row. (That's why the FK constraint is "initially deferred". The use-case from which this test is derived needs this.)
"del_m()" deletes a specified "masters" row—which implies the cascade-delete of its details.
"del_ds()" deletes all the "details" rows for a specified master.
"code" grants "execute" on these to "client". ("client " owns no objects.)
5. "client" invokes "code.cr_m_and_ds()".
It's called to insert a single "masters" row with "masters.v" set to 'Mary' and a single "details" row with "details.v" set to 'shampoo'.
6. "client" invokes "code.del_m()" on 'Mary'.
Here's what I see:
before_statement_masters: [code]
before_row_masters: [code] Mary
before_statement_details: [data]
before_row_details: [data] shampoo
after_row_masters: [code] Mary
after_statement_masters: [code]
after_row_details: [code] shampoo
after_statement_details: [code]
(I stripped the noisy "psql:0.sql:32: INFO:" preamble for each output line by hand.)
I was surprised that the value from "current_role" is *not* the table owner, "data", in all cases. (This is how triggers behave in Oracle database.) Rather, it's mainly (but not always) "code". I could be persuaded that, in the cascade-delete case, the invoking role is the owner of the "masters" table rather than the role, "code" that performs the "delete" from "masters"—but that would maybe be a stretch. Anyway, if this is the intention, why is it like this only for the "before" timing points for the triggers on "details"?
7. Starting with the same 'Mary'-'shampoo' pair, client invokes "code.del_ds()" on 'Mary'
With the same set-up, and using this instead of "del_m()", this is the result:
before_statement_details: [code]
before_row_details: [code] shampoo
after_row_details: [code] shampoo
after_statement_details: [code]
Here the value for "current_role" from each trigger is the same. These results are in line with the common case in the first test.
I read the section "Triggers on Data Changes" (https://www.postgresql.org/docs/current/plpgsql-trigger.html#PLPGSQL-DML-TRIGGER). But there's no hit on the page for any of "security", "invoker", or "definer". And I couldn't find wording without these terms that addresses what I describe here.
<<attachment: example.zip>>
