DELETE CASCADE
Hi -hackers,
Presented for discussion is a POC for a DELETE CASCADE functionality,
which will allow you one-shot usage of treating existing NO ACTION and
RESTRICT FK constraints as if they were originally defined as CASCADE
constraints. I can't tell you how many times this functionality would have
been useful in the field, and despite the expected answer of "define your
constraints right in the first place", this is not always an option, nor is
the ability to change that easily (or create new constraints that need to
revalidate against big tables) always the best option.
That said, I'm happy to quibble about the specific approach to be taken;
I've written this based on the most straightforward way I could come up
with to accomplish this, but if there are better directions to take to get
the equivalent functionality I'm happy to discuss.
From the commit message:
Proof of concept of allowing a DELETE statement to override formal FK's
handling from RESTRICT/NO
ACTION and treat as CASCADE instead.
Syntax is "DELETE CASCADE ..." instead of "DELETE ... CASCADE" due to
unresolvable bison conflicts.
Sample session:
postgres=# create table foo (id serial primary key, val text);
CREATE TABLE
postgres=# create table bar (id serial primary key, foo_id int references
foo(id), val text);
CREATE TABLE
postgres=# insert into foo (val) values ('a'),('b'),('c');
INSERT 0 3
postgres=# insert into bar (foo_id, val) values
(1,'d'),(1,'e'),(2,'f'),(2,'g');
INSERT 0 4
postgres=# select * from foo;
id | val
----+-----
1 | a
2 | b
3 | c
(3 rows)
postgres=# select * from bar;
id | foo_id | val
----+--------+-----
1 | 1 | d
2 | 1 | e
3 | 2 | f
4 | 2 | g
(4 rows)
postgres=# delete from foo where id = 1;
ERROR: update or delete on table "foo" violates foreign key constraint
"bar_foo_id_fkey" on table "bar"
DETAIL: Key (id)=(1) is still referenced from table "bar".
postgres=# delete cascade from foo where id = 1;
DELETE 1
postgres=# select * from foo;
id | val
----+-----
2 | b
3 | c
(2 rows)
postgres=# select * from bar;
id | foo_id | val
----+--------+-----
3 | 2 | f
4 | 2 | g
(2 rows)
Best,
David
Attachments:
0001-Add-support-for-DELETE-CASCADE.patchapplication/octet-stream; name=0001-Add-support-for-DELETE-CASCADE.patchDownload+143-13
On Thu, 3 Jun 2021 at 16:49, David Christensen <
david.christensen@crunchydata.com> wrote:
Hi -hackers,
Presented for discussion is a POC for a DELETE CASCADE functionality,
which will allow you one-shot usage of treating existing NO ACTION and
RESTRICT FK constraints as if they were originally defined as CASCADE
constraints. I can't tell you how many times this functionality would have
been useful in the field, and despite the expected answer of "define your
constraints right in the first place", this is not always an option, nor is
the ability to change that easily (or create new constraints that need to
revalidate against big tables) always the best option.
I would sometimes find this convenient. There are circumstances where I
don't want every DELETE to blunder all over the database deleting stuff,
but certain specific DELETEs should take care of the referencing tables.
An additional syntax to say "CASCADE TO table1, table2" would be safer and
sometimes useful in the case where I know I want to cascade to specific
other tables but not all (and in particular not to ones I didn't think of
when I wrote the query); I might almost suggest omitting the cascade to all
syntax (or maybe have a separate syntax, literally "CASCADE TO ALL TABLES"
or some such).
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.
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.
On Thu, Jun 3, 2021 at 1:49 PM David Christensen <
david.christensen@crunchydata.com> wrote:
Presented for discussion is a POC for a DELETE CASCADE functionality,
which will allow you one-shot usage of treating existing NO ACTION and
RESTRICT FK constraints as if they were originally defined as CASCADE
constraints.
ON DELETE NO ACTION constraints become ON DELETE CASCADE constraints - ON
DELETE SET NULL constraints are ignored, and not possible to emulate via
this feature.
I can't tell you how many times this functionality would have been
useful in the field, and despite the expected answer of "define your
constraints right in the first place", this is not always an option, nor is
the ability to change that easily (or create new constraints that need to
revalidate against big tables) always the best option.
Once...but I agreed.
That said, I'm happy to quibble about the specific approach to be taken;
I've written this based on the most straightforward way I could come up
with to accomplish this, but if there are better directions to take to get
the equivalent functionality I'm happy to discuss.
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).
Having the defined FK behaviors be more readily changeable, while not
mitigating this need, is IMO a more important feature to implement. If
there is a reason that cannot be implemented (besides no one has bothered
to take the time) then I would consider that reason to also apply to
prevent implementing this work-around.
David J.
On Thu, Jun 3, 2021 at 4:48 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:
On Thu, Jun 3, 2021 at 1:49 PM David Christensen <
david.christensen@crunchydata.com> wrote:Presented for discussion is a POC for a DELETE CASCADE functionality,
which will allow you one-shot usage of treating existing NO ACTION and
RESTRICT FK constraints as if they were originally defined as CASCADE
constraints.ON DELETE NO ACTION constraints become ON DELETE CASCADE constraints - ON
DELETE SET NULL constraints are ignored, and not possible to emulate via
this feature.
I have not tested this part per se (which clearly I need to expand the
existing test suite), but my reasoning here was that ON DELETE SET
NULL/DEFAULT would still be applied with their defined behaviors (being
that we're still calling the underlying RI triggers using SPI) with the
same results; the intent of this feature is just to suppress the RESTRICT
action and cascade the DELETE to all tables (on down the chain) which would
normally block this, without having to manually figure all the dependencies
which can be inferred by the database itself.
I can't tell you how many times this functionality would have been
useful in the field, and despite the expected answer of "define your
constraints right in the first place", this is not always an option, nor is
the ability to change that easily (or create new constraints that need to
revalidate against big tables) always the best option.Once...but I agreed.
Heh.
That said, I'm happy to quibble about the specific approach to be taken;
I've written this based on the most straightforward way I could come up
with to accomplish this, but if there are better directions to take to get
the equivalent functionality I'm happy to discuss.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).
I'm not sure if this would be overly prohibitive or not, but if you're the
table owner this should just work, like you point out. I think this
restriction could be fine for the common case, and if there was a way to
hint if/when this failed to cascade as to the actual reason for the failure
I'm fine with that part too. (I was assuming that DELETE permission on the
underlying tables + existence of FK would be enough in practice, but we
could definitely tighten that up.)
Having the defined FK behaviors be more readily changeable, while not
mitigating this need, is IMO a more important feature to implement. If
there is a reason that cannot be implemented (besides no one has bothered
to take the time) then I would consider that reason to also apply to
prevent implementing this work-around.
Agreed that this would be a nice feature to have too; noone wants to break
FK consistency to change things or require a rescan of a valid constraint.
On Thu, Jun 3, 2021 at 4:15 PM Isaac Morland <isaac.morland@gmail.com>
wrote:
On Thu, 3 Jun 2021 at 16:49, David Christensen <
david.christensen@crunchydata.com> wrote:Hi -hackers,
Presented for discussion is a POC for a DELETE CASCADE functionality,
which will allow you one-shot usage of treating existing NO ACTION and
RESTRICT FK constraints as if they were originally defined as CASCADE
constraints. I can't tell you how many times this functionality would have
been useful in the field, and despite the expected answer of "define your
constraints right in the first place", this is not always an option, nor is
the ability to change that easily (or create new constraints that need to
revalidate against big tables) always the best option.I would sometimes find this convenient. There are circumstances where I
don't want every DELETE to blunder all over the database deleting stuff,
but certain specific DELETEs should take care of the referencing tables.An additional syntax to say "CASCADE TO table1, table2" would be safer and
sometimes useful in the case where I know I want to cascade to specific
other tables but not all (and in particular not to ones I didn't think of
when I wrote the query); I might almost suggest omitting the cascade to all
syntax (or maybe have a separate syntax, literally "CASCADE TO ALL TABLES"
or some such).
I'm not fond of the syntax requirements for the explicitness here, plus it
seems like it would complicate the functionality of the patch (which
currently is able to just slightly refactor the RI triggers to account for
a single state variable, rather than do anything smarter than that). I do
understand the desire/need for visibility into what would be affected with
an offhand statement.
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... :-))
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?
David
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... :-))
Enclosed is a basic test script and the corresponding output run through
`psql -e` (will adapt into part of the regression test, but wanted to get
this out there). TL;DR; DELETE CASCADE behaves exactly as if said
constraint were defined as a ON DELETE CASCADE FK constraint wrt DELETE
permission behavior. I do agree in this case, that it makes sense to throw
an error if we're trying to bypass the RESTRICT behavior and we are not
part of the table owner role (and since this would be called/checked
recursively for each table involved in the graph I think we can count on it
reporting the appropriate error message in this case).
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.
On Thu, 3 Jun 2021 at 18:25, David Christensen <
david.christensen@crunchydata.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... :-))Enclosed is a basic test script and the corresponding output run through
`psql -e` (will adapt into part of the regression test, but wanted to get
this out there). TL;DR; DELETE CASCADE behaves exactly as if said
constraint were defined as a ON DELETE CASCADE FK constraint wrt DELETE
permission behavior. I do agree in this case, that it makes sense to throw
an error if we're trying to bypass the RESTRICT behavior and we are not
part of the table owner role (and since this would be called/checked
recursively for each table involved in the graph I think we can count on it
reporting the appropriate error message in this case).
Surely you mean if we don't have DELETE permission on the referencing
table? I don't see why we need to be a member of the table owner role.
On Thu, Jun 3, 2021 at 3:29 PM Isaac Morland <isaac.morland@gmail.com>
wrote:
Surely you mean if we don't have DELETE permission on the referencing
table? I don't see why we need to be a member of the table owner role.
I would reverse the question - why does this feature need to allow the more
broad DELETE permission instead of just limiting it to the table owner? The
latter matches the required permission for the existing cascade feature
that this is extending.
David J.
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.
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?
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).
On Fri, Jun 4, 2021 at 3:40 PM Isaac Morland <isaac.morland@gmail.com>
wrote:
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 ...
This is entirely the use case and the motivation.
... 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.
This is the part where I'm also running into some conceptual roadblocks
between what is an implementation issue based on the current behavior of
CASCADE triggers, and what makes sense in terms of a POLA perspective. In
part, I am having this discussion to flesh out this part of the problem.
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).
A soft -1 from me here, though I understand the rationale here; you would
be unable to manually delete these records with the existing constraints if
there were a `grandchild` table without first removing those records too.
(Maybe some method of previewing which relations/FKs would be involved here
would be a suitable compromise, but I have no idea what that would look
like or how it would work.) (Maybe just NOTICE: DELETE CASCADES to ... for
each table, and people should know to wrap in a transaction if they don't
know what will happen.)
David
On 04.06.21 22:24, David Christensen wrote:
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 think you would need DELETE privilege on all affected tables.
On 03.06.21 22:49, David Christensen wrote:
Presented for discussion is a POC for a DELETE CASCADE functionality,
which will allow you one-shot usage of treating existing NO ACTION and
RESTRICT FK constraints as if they were originally defined as CASCADE
constraints. I can't tell you how many times this functionality would
have been useful in the field, and despite the expected answer of
"define your constraints right in the first place", this is not always
an option, nor is the ability to change that easily (or create new
constraints that need to revalidate against big tables) always the best
option.
I think, if we think this is useful, the other way around would also be
useful: Override a foreign key defined as ON DELETE CASCADE to behave as
RESTRICT for a particular command.
On Jun 5, 2021, at 2:30 AM, Peter Eisentraut <peter.eisentraut@enterprisedb.com> wrote:
On 03.06.21 22:49, David Christensen wrote:
Presented for discussion is a POC for a DELETE CASCADE functionality, which will allow you one-shot usage of treating existing NO ACTION and RESTRICT FK constraints as if they were originally defined as CASCADE constraints. I can't tell you how many times this functionality would have been useful in the field, and despite the expected answer of "define your constraints right in the first place", this is not always an option, nor is the ability to change that easily (or create new constraints that need to revalidate against big tables) always the best option.
I think, if we think this is useful, the other way around would also be useful: Override a foreign key defined as ON DELETE CASCADE to behave as RESTRICT for a particular command.
I am not opposed to this, but I am struggling to come up with a use case. Where would this be useful?
David
On Sat, 5 Jun 2021 at 03:30, Peter Eisentraut <
peter.eisentraut@enterprisedb.com> wrote:
On 03.06.21 22:49, David Christensen wrote:
Presented for discussion is a POC for a DELETE CASCADE functionality,
which will allow you one-shot usage of treating existing NO ACTION and
RESTRICT FK constraints as if they were originally defined as CASCADE
constraints. I can't tell you how many times this functionality would
have been useful in the field, and despite the expected answer of
"define your constraints right in the first place", this is not always
an option, nor is the ability to change that easily (or create new
constraints that need to revalidate against big tables) always the best
option.I think, if we think this is useful, the other way around would also be
useful: Override a foreign key defined as ON DELETE CASCADE to behave as
RESTRICT for a particular command.
This is not as obviously useful as the other, but might conceivably still
have applications.
We would need to be very careful about permissions. This is a substitute
for checking whether there are any matching rows in the referring tables
and throwing an error manually in that case. My immediate reaction is that
this should require SELECT permission on the referring tables. Or to be
more precise, SELECT permission on the foreign key columns in the referring
tables.
On Jun 5, 2021, at 2:29 AM, Peter Eisentraut <peter.eisentraut@enterprisedb.com> wrote:
On 04.06.21 22:24, David Christensen wrote:
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 think you would need DELETE privilege on all affected tables.
So basically where we are dispatching to the CASCADE guts, first check session user’s DELETE permission and throw the normal permissions error if they can’t delete?
On 05.06.21 14:21, David Christensen wrote:
On Jun 5, 2021, at 2:30 AM, Peter Eisentraut <peter.eisentraut@enterprisedb.com> wrote:
On 03.06.21 22:49, David Christensen wrote:
Presented for discussion is a POC for a DELETE CASCADE functionality, which will allow you one-shot usage of treating existing NO ACTION and RESTRICT FK constraints as if they were originally defined as CASCADE constraints. I can't tell you how many times this functionality would have been useful in the field, and despite the expected answer of "define your constraints right in the first place", this is not always an option, nor is the ability to change that easily (or create new constraints that need to revalidate against big tables) always the best option.
I think, if we think this is useful, the other way around would also be useful: Override a foreign key defined as ON DELETE CASCADE to behave as RESTRICT for a particular command.
I am not opposed to this, but I am struggling to come up with a use case. Where would this be useful?
If you suspect a primary key row is no longer used, you want to delete
it, but don't want to accidentally delete it if it's still used.
I sense more complicated concurrency and permission issues, however.
On 05.06.21 14:25, David Christensen wrote:
On Jun 5, 2021, at 2:29 AM, Peter Eisentraut <peter.eisentraut@enterprisedb.com> wrote:
On 04.06.21 22:24, David Christensen wrote:
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 think you would need DELETE privilege on all affected tables.
So basically where we are dispatching to the CASCADE guts, first check session user’s DELETE permission and throw the normal permissions error if they can’t delete?
Actually, you also need appropriate SELECT permissions that correspond
to the WHERE clause of the DELETE statement.
