Re: [GENERAL] Access privileges /yyyy -- role that granted this privilege. - Mailing list pgsql-general

On 02/17/2017 05:44 AM, Jean-Michel Scheiwiler wrote:

Hello,

I delve into access privileges and I have a problem (or a
miscomprehension) when i type \l, \dn+ or \dp with the /yyyy "role that
granted this privilege"  part.

( https://www.postgresql.org/docs/current/static/sql-grant.html )

\l for instance

[postgres:~]$psql
psql (9.6.2)
Type "help" for help.

postgres=# create role superman login superuser;
CREATE ROLE
postgres=# create role user01 login ;
CREATE ROLE
postgres=# create role user02 login ;
CREATE ROLE
postgres=# create database db001;
CREATE DATABASE
postgres=# \l db001
                              List of databases
 Name  |  Owner   | Encoding |   Collate   |    Ctype    | Access
privileges
-------+----------+----------+-------------+-------------+-------------------
 db001 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(1 row)

postgres=# grant connect on database db001 to user01 ;
GRANT
postgres=# \l db001
                                List of databases
 Name  |  Owner   | Encoding |   Collate   |    Ctype    |   Access
privileges
-------+----------+----------+-------------+-------------+-----------------------
 db001 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres
        +
       |          |          |             |             |
postgres=CTc/postgres+
       |          |          |             |             | user01=c/postgres
(1 row)

postgres=# \q
[postgres:~]$psql -U superman postgres
psql (9.6.2)
Type "help" for help.

postgres=# grant connect on database db001 to user02;
GRANT
postgres=# \l db001
                                List of databases
 Name  |  Owner   | Encoding |   Collate   |    Ctype    |   Access
privileges
-------+----------+----------+-------------+-------------+-----------------------
 db001 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres
        +
       |          |          |             |             |
postgres=CTc/postgres+
       |          |          |             |             |
user01=c/postgres    +
       |          |          |             |             | user02=c/postgres
(1 row)


I thought i would get   user02=c/superman but instead i
get user02=c/postgres => I don't get the "role that granted this
privilege" but i get the owner of the database.

As a practical matter it does not matter as postgres and superman are both superusers, still for an explanation of why it happens:

https://www.postgresql.org/docs/9.6/static/sql-grant.html

"If a superuser chooses to issue a GRANT or REVOKE command, the command is performed as though it were issued by the owner of the affected object. In particular, privileges granted via such a command will appear to have been granted by the object owner. (For role membership, the membership appears to have been granted by the containing role itself.)"

The problem is the same with schemas or tables access privileges.

Can you help me figure this out ?

What are you trying to achieve?

Thank you in advance

Jean-Michel Scheiwiler

--
Adrian Klaver
adrian.klaver@aklaver.com