On 28.07.2018 21:41, Tom Lane wrote:I noticed today that \dp does not distinguish empty acl fields
(meaning nobody has any privileges) from null acl fields
(which mean default privileges, typically not empty).
This confusing behavior exists not only for \dp command.
Consider schemas and \dn+ command:
postgres=# create schema s authorization u;
CREATE SCHEMA
postgres=# \dn+ s
List of schemas
Name | Owner | Access privileges | Description
------+-------+-------------------+-------------
s | u | |
(1 row)
postgres=# \c - u
You are now connected to database "postgres" as user "u".
postgres=> create table s.t(id int);
CREATE TABLE
postgres=> revoke all on schema s from u;
REVOKE
postgres=> \dn+ s
List of schemas
Name | Owner | Access privileges | Description
------+-------+-------------------+-------------
s | u | |
(1 row)
postgres=> create table s.t2(id int);
ERROR: permission denied for schema s
LINE 1: create table s.t2(id int);
One idea is to replace a null ACL value with the actual effective
permissions, which we could get from the acldefault() function.
As for me, this is a right option.
Very hard to describe (I am engaged in the development of training courses) why after GRANT command
we see two records in acl column, but after CREATE TABLE - no records.
Phrases like "for historical reasons" are not very convincing:
postgres=# create table t (id int);
CREATE TABLE
postgres=# \dp t
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+-------------------+-------------------+----------
public | t | table | | |
(1 row)
postgres=# grant select on t to u;
GRANT
postgres=# \dp t
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+---------------------------+-------------------+----------
public | t | table | postgres=arwdDxt/postgres+| |
| | | u=r/postgres | |
-----
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company