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).