Message-ID: <ad228f4cc2909c0ef4da59f6e5025267880f1c81.camel@cybertec.at>
Subject: Re: No docs for interaction of ON DELETE/UPDATE and permissions
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: David Glasser <glasser@apollographql.com>,
	pgsql-docs@lists.postgresql.org
Date: Tue, 25 Feb 2020 10:54:52 +0100
In-Reply-To: 
 <CAOz3Odt1+vuFZvKoHuOT6N1nOAgJdhY7HpF5zsxUe18_B2qB=w@mail.gmail.com>
References: 
	<CAOz3Odt1+vuFZvKoHuOT6N1nOAgJdhY7HpF5zsxUe18_B2qB=w@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.32.5 (3.32.5-1.fc30) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

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