On Thu, Aug 11, 2022 at 8:41 PM Bryn Llewellyn <b...@yugabyte.com> 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>>
