Re: [GENERAL] Access privileges /yyyy -- role that granted thisprivilege. - 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