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

[Laurenz Albe]

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

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

[David Glasser]

On Tue, Feb 25, 2020 at 1:54 AM Laurenz Albe  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

Better documentation for row_number() combined with set returning functions

[Erik Tews]

Hi

The current documentation of Postgresql sounds like row_number() over
() can be used to number the rows returned by postgres. However, that
doesn't work when the query also uses set returning functions such as
json_array_elements. In this case, row_number() will be the same for
every element of the set.

I suggest to add a paragraph to the description of row_number() that
states that this is the behaviour and maybe also reference the "with
ordinality" feature that can be used instead.

Erik


signature.asc
Description: This is a digitally signed message part