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

From: Jean-Michel Scheiwiler <jm(dot)scheiwiler(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Access privileges /yyyy -- role that granted this privilege.
Date: 2017-02-17 13:44:50
Message-ID: CAGbCck8tCc7=Sx3Qh7x9uMwVXOiXaPjLc6fWpqiew-TbgaR9Zg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

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

Can you help me figure this out ?

Thank you in advance

Jean-Michel Scheiwiler

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Murtuza Zabuawala 2017-02-17 13:55:03 Re: Load multiple CSV file in Postgres using COPY
Previous Message Achilleas Mantzios 2017-02-17 13:20:14 Re: Streaming Replication Without Downtime