Re: DELETE CASCADE - Mailing list pgsql-hackers
| From | Isaac Morland |
|---|---|
| Subject | Re: DELETE CASCADE |
| Date | |
| Msg-id | CAMsGm5dqU1fTgG16f69BwOUVmsB62DYXAYXLHWvjZ6zy2bAibA@mail.gmail.com Whole thread Raw |
| In response to | Re: DELETE CASCADE (David Christensen <david.christensen@crunchydata.com>) |
| List | pgsql-hackers |
On Thu, 3 Jun 2021 at 18:08, David Christensen <david.christensen@crunchydata.com> wrote:
On Thu, Jun 3, 2021 at 4:15 PM Isaac Morland <isaac.morland@gmail.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.
pgsql-hackers by date: