Re: DELETE CASCADE

On Thu, 3 Jun 2021 at 18:08, David Christensen <
david(dot)christensen(at)crunchydata(dot)com> wrote:

> On Thu, Jun 3, 2021 at 4:15 PM Isaac Morland <isaac(dot)morland(at)gmail(dot)com>
> wrote:
>
>>
>> What happens if I don't have delete permission on the referencing table?
>> When a foreign key reference delete cascades, I can cause records to
>> disappear from a referencing table even if I don't have delete permission
>> on that table. This feels like it's just supposed to be a convenience that
>> replaces multiple DELETE invocations but one way or the other we need to be
>> clear on the behaviour.
>>
>
> Did you test this and find a failure? Because it is literally using all of
> the same RI proc code/permissions as defined I would expect that it would
> just abort the transaction. (I am working on expanding the test suite for
> this feature to allow for test cases like this, so keep 'em coming... :-))
>

I haven't run your patch. I'm just asking because it's a question about
exactly how the behaviour works that needs to be clearly and intentionally
decided (and documented). I think aborting the transaction with a
permission denied error on the referencing table is probably the right
behaviour: it's what you would get if you issued an equivalent delete on
the referencing table explicitly. I think of your patch as being a
convenience to avoid having to write a separate DELETE for each referencing
table. So based on what you say, it sounds like you've already covered this
issue.

Sidebar: isn't this inconsistent with trigger behaviour in general? When I
>> say "ON DELETE CASCADE" what I mean and what I get are the same: whenever
>> the referenced row is deleted, the referencing row also disappears,
>> regardless of the identity or permissions of the role running the actual
>> DELETE. But any manually implemented trigger runs as the caller; I cannot
>> make the database do something when a table update occurs; I can only make
>> the role doing the table update perform some additional actions.
>>
>
> Have you found a failure? Because all this is doing is effectively
> calling the guts of the cascade RI routines, so no differences should
> occur. If not, I'm not quite clear on your objection; can you clarify?
>

Sorry, my sidebar is only tangentially related. In another thread we had a
discussion about triggers, which it turns out execute as the role running
the command, not as the owner of the table. For many triggers it doesn't
matter, but for many things I can think of that I would want to do with
triggers it will only work if the trigger executes as the owner of the
table (or trigger, hypothetically…); and there are several common cases
where it makes way more sense to execute as the owner (e.g., triggers to
maintain a log table; it doesn't make sense to have to grant permissions on
the log table to roles with permissions on the main table, and also allows
spurious log entries to be made). But here it seems that cascaded actions
do execute as a role that is not dependent on who is running the command.

In short, I probably should have left off the sidebar. It's not an issue
with your patch.