Обсуждение: No docs for interaction of ON DELETE/UPDATE and permissions
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.
--dave
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
On Tue, Feb 25, 2020 at 1:54 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote: > 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. I had found that too, but it doesn't look like that page talks about permissions at all. It seems like the CREATE TABLE docs are the "reference" for referential actions (pun not intended). Should I add it to both places perhaps? Should docs patches be sent to this list or to pgsql-hackers? https://wiki.postgresql.org/wiki/Submitting_a_Patch is not clear. --dave
On Tue, 2020-02-25 at 10:30 -0800, David Glasser wrote: > > 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. > > I had found that too, but it doesn't look like that page talks about > permissions at all. It seems like the CREATE TABLE docs are the > "reference" for referential actions (pun not intended). Should I add > it to both places perhaps? No, it should be in the documentation only once. > Should docs patches be sent to this list or to pgsql-hackers? > https://wiki.postgresql.org/wiki/Submitting_a_Patch is not clear. It does not matter much, but if you want to add it to the commitfest, that might only integrate well with the -hackers list (though I am not sure about that). Yours, Laurenz Albe