Re: Usability fail with psql's \dp command

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