Re: Access privileges /yyyy -- role that granted this privilege.

Ok thank you (and sorry i didn't read this line of documentation)

2017-02-17 15:18 GMT+01:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:

> 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(dot)klaver(at)aklaver(dot)com
>