Re: DELETE CASCADE
| От | Isaac Morland |
|---|---|
| Тема | Re: DELETE CASCADE |
| Дата | |
| Msg-id | CAMsGm5fUY67qGU8J=BUxuokx1wUQ3O9UZ5sFHn12kGLeEgOjZg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: DELETE CASCADE (David Christensen <david.christensen@crunchydata.com>) |
| Ответы |
Re: DELETE CASCADE
(David Christensen <david.christensen@crunchydata.com>)
|
| Список | pgsql-hackers |
On Fri, 4 Jun 2021 at 16:24, David Christensen <david.christensen@crunchydata.com> wrote:
On Fri, Jun 4, 2021 at 2:53 PM Peter Eisentraut <peter.eisentraut@enterprisedb.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).
В списке pgsql-hackers по дате отправления: