Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux