Re: DELETE CASCADE

On Fri, 4 Jun 2021 at 16:24, David Christensen <
david(dot)christensen(at)crunchydata(dot)com> wrote:

> On Fri, Jun 4, 2021 at 2:53 PM Peter Eisentraut <
> peter(dot)eisentraut(at)enterprisedb(dot)com> wrote:
>
>> On 03.06.21 23:47, David G. Johnston wrote:
>> > This behavior should require the same permissions as actually creating
>> > an ON DELETE CASCADE FK on the cascaded-to tables. i.e., Table Owner
>> > role membership (the requirement for FK permissions can be assumed by
>> > the presence of the existing FK constraint and being the table's owner).
>>
>> You can create foreign keys if you have the REFERENCES privilege on the
>> primary key table. That's something this patch doesn't observe
>> correctly: Normally, the owner of the foreign key table decides the
>> cascade action, but with this patch, it's the primary key table owner.
>>
>
> So what are the necessary and sufficient conditions to check at this
> point? The constraint already exists, so what permissions would we need to
> check against which table(s) in order to grant this action?
>

I apologize if I am deeply confused, but say I have this:

CREATE TABLE parent (
pid int primary key,
parent_data text
);

CREATE TABLE child (
pid int REFERENCES parent,
cid int,
PRIMARY KEY (pid, cid),
child_data text
);

It's easy to imagine needing to write:

DELETE FROM child WHERE ...
DELETE FROM parent WHERE ...

... where the WHERE clauses both work out to the same pid values. It would
be nice to be able to say:

DELETE CASCADE FROM parent WHERE ...

... and just skip writing the first DELETE entirely. And what do I mean by
"DELETE CASCADE" if not "delete the referencing rows from child"? So to me
I think I should require DELETE permission on child (and parent) in order
to execute this DELETE CASCADE. I definitely shouldn't require any
DDL-related permissions (table owner, REFERENCES, …) because I'm not doing
DDL - just data changes. Sure, it may be implemented by temporarily
treating the foreign key references differently, but conceptually I'm just
deleting from multiple tables in one command.

I will say I would prefer this syntax:

DELETE FROM parent WHERE ... CASCADE TO child;

(or "CASCADE TO ALL TABLES" or some such if I want that)

I don't like the idea of saying "CASCADE" and getting a bunch of tables I
didn't intend (or which didn't exist when the query was written).