Re: No docs for interaction of ON DELETE/UPDATE and permissions

On Mon, 2020-02-24 at 14:45 -0800, David Glasser wrote:
> I was planning to add a new table TA only writable by user A, with a foreign key reference with
> ON DELETE CASCADE to another table TB only writable by user B.  My hope was that user B would
> still be allowed to delete rows in TB and cause cascading deletions in table TA, and some manual
> investigation makes it seem like that is the implemented semantics.
> 
> However, I could not find a direct answer to my question in the docs. I looked primarily at
> https://www.postgresql.org/docs/current/sql-grant.html and
https://www.postgresql.org/docs/current/sql-createtable.html.
 
> There are references to permissions required to create references, but not to the permissions
> (not, apparently) required to indirectly write to a table via referential actions.
> 
> Am I correct that (a) no permissions are needed here and (b) this is undocumented and (c) it would
> be helpful to document this, probably in the CREATE TABLE docs?  Happy to write a patch if so.

I would say that it is not documented outside the source, and I think it
wouldn't harm to document that.  I had to dig in the source myself the
first time I encountered that.

I am not sure if CREATE TABLE is the perfect place; another place that
would offer ifself is
https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK ,
where foreign key constraints are explained.

Yours,
Laurenz Albe