Re: DELETE CASCADE

On Thu, 3 Jun 2021 at 18:25, David Christensen <
david(dot)christensen(at)crunchydata(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... :-))
>>
>
> 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.