Re: Usability fail with psql's \dp command
| От | Pavel Luzanov |
|---|---|
| Тема | Re: Usability fail with psql's \dp command |
| Дата | |
| Msg-id | 54a8c784-a526-9efd-67ce-43e14379eae1@postgrespro.ru обсуждение исходный текст |
| Ответ на | Usability fail with psql's \dp command (Tom Lane <tgl@sss.pgh.pa.us>) |
| Ответы |
Re: Usability fail with psql's \dp command
|
| Список | pgsql-hackers |
On 28.07.2018 21:41, Tom Lane wrote:
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);
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 | |
This confusing behavior exists not only for \dp command.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).
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);
As for me, this is a right option.One idea is to replace a null ACL value with the actual effective permissions, which we could get from the acldefault() function.
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
В списке pgsql-hackers по дате отправления: