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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2017-02-17 15:18 GMT+01:00 Adrian Klaver <adrian.klaver@aklaver.com>: