Thread: Role and grants

Role and grants

From

"Fran ..."

Date:

09 November 2016, 19:05:30

Hi,

I am a new user with PostgreSQL, I came from MySQL and I am experiencing some issues with roles and privileges.

I have created a DB,user and grant privilege on this DB to this user. How could I check what is the privileges/permissions for this user?

Transcript:

postgres=# create database test;

CREATE DATABASE

postgres=# create user test with password 'test';

CREATE ROLE

postgres=# grant all privileges on database test to test;

GRANT

postgres=# \l

List of databases

-----------+----------+----------+-------------+-------------+-----------------------

(5 rows)

With "\l" command It's no clear.

Finally, I don't find some command like "show grants for..." in MySQL.

Regards.

Bryan

Re: Role and grants

From

Melvin Davidson

Date:

09 November 2016, 19:53:53

On Wed, Nov 9, 2016 at 2:05 PM, Fran ... <Bryan691@hotmail.com> wrote:

Hi,

I am a new user with PostgreSQL, I came from MySQL and I am experiencing some issues with roles and privileges.

I have created a DB,user and grant privilege on this DB to this user. How could I check what is the privileges/permissions for this user?

Transcript:

postgres=# create database test;
CREATE DATABASE
postgres=# create user test with password 'test';
CREATE ROLE
postgres=# grant all privileges on database test to test;
GRANT
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres +
| | | | | postgres=CTc/postgres+
| | | | | test=CTc/postgres
test1 | test1 | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/test1 +
| | | | | test1=CTc/test1
(5 rows)

With "\l" command It's no clear.

Finally, I don't find some command like "show grants for..." in MySQL.

Regards.
Bryan

You can use the following query to show what table <USERNAME> can access.
Just replace <USERNAME> with the actual username you want.

SELECT *
FROM information_schema.table_privileges
WHERE grantee = '<USERNAME>'

ORDER BY table_schema,table_name, privilege_type;

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Re: Role and grants

From

Adrian Klaver

Date:

09 November 2016, 20:02:48

On 11/09/2016 11:05 AM, Fran ... wrote:
> Hi,
>
>
> I am a new user with PostgreSQL, I came from MySQL and I am experiencing
> some issues with roles and privileges.
>
>
> I have created a DB,user and grant privilege on this DB to this user.
> How could I check what is the privileges/permissions for this user?
>
>
> Transcript:
>
>
> postgres=# create database test;
> CREATE DATABASE
> postgres=# create user test with password 'test';
> CREATE ROLE
> postgres=# grant all privileges on database test to test;
> GRANT

My guess is the above is not doing what you think it is. See further
comments below for seeing what 'grant all privileges on database' gets you.

> postgres=# \l
>                                   List of databases
>    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access
> privileges
> -----------+----------+----------+-------------+-------------+-----------------------
>  postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
>  template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
> =c/postgres          +
>            |          |          |             |             |
> postgres=CTc/postgres
>  template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
> =c/postgres          +
>            |          |          |             |             |
> postgres=CTc/postgres
>  test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
> =Tc/postgres         +
>            |          |          |             |             |
> postgres=CTc/postgres+
>            |          |          |             |             |
> test=CTc/postgres
>  test1     | test1    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/test1
>            +
>            |          |          |             |             |
> test1=CTc/test1
> (5 rows)
>
> With "\l" command It's no clear.

To understand the abbreviations see here:

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

Look for:

"The entries shown by \dp are interpreted thus:"


>
>
> Finally, I don't find some command like "show grants for..." in MySQL.
>
>
> Regards.
>
> Bryan
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Role and grants

From

"Fran ..."

Date:

09 November 2016, 22:46:19

Thanks for your reply.

I had seen that link from documentation but I do not understand why it prints just "connect,temporary and create":

CTc/test1

I think it should be print "arwdDxt".

With this other command neither I get more information:

postgres=# \du

List of roles

Role name | Attributes | Member of

-----------+------------------------------------------------+-----------

postgres | Superuser, Create role, Create DB, Replication | {}

test1 | | {}

Regards.

De: Adrian Klaver <adrian.klaver@aklaver.com>
Enviado: miércoles, 9 de noviembre de 2016 21:02
Para: Fran ...; pgsql-general@postgresql.org
Asunto: Re: [GENERAL] Role and grants

PostgreSQL: Documentation: 9.5: GRANT

www.postgresql.org

GRANT on Database Objects. This variant of the GRANT command gives specific privileges on a database object to one or more roles. These privileges are added to those ...

Look for:

"The entries shown by \dp are interpreted thus:"

>
>
> Finally, I don't find some command like "show grants for..." in MySQL.
>
>
> Regards.
>
> Bryan
>

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Role and grants

From

John R Pierce

Date:

09 November 2016, 22:52:51

On 11/9/2016 2:46 PM, Fran ... wrote:
>
> I had seen that link from documentation but I do not understand why it
> prints just "connect,temporary and create":
>
> CTc/test1
>
> I think it should be print "arwdDxt".

Connect, Temporary and Create are the only permissions a DATABASE has.

a SCHEMA has Create and Usage permissions.

a TABLE has select, insert, update, delete, truncate, references,
trigger permissions.

> With this other command neither I get more information:
>
> postgres=# \du
>                              List of roles
>  Role name |                   Attributes   | Member of
> -----------+------------------------------------------------+-----------
>  postgres  | Superuser, Create role, Create DB, Replication | {}
>  test1     |    | {}

those are role(user) attributes, which is something quite different than
object permissions.

--
john r pierce, recycling bits in santa cruz

Re: Role and grants

From

Adrian Klaver

Date:

09 November 2016, 23:38:01

On 11/09/2016 02:46 PM, Fran ... wrote:
>
> Thanks for your reply.
>
> I had seen that link from documentation but I do not understand why it
> prints just "connect,temporary and create":
>
> CTc/test1
>
> I think it should be print "arwdDxt".

It is explained in the documentation at the link
(https://www.postgresql.org/docs/9.5/static/sql-grant.html). Look under
the section:

"The possible privileges are:"

Also see John's post.


>
> With this other command neither I get more information:
>
> postgres=# \du
>                              List of roles
>  Role name |                   Attributes                   | Member of
> -----------+------------------------------------------------+-----------
>  postgres  | Superuser, Create role, Create DB, Replication | {}
>  test1     |                                                | {}
>
> Regards.
>
>
> ------------------------------------------------------------------------
> *De:* Adrian Klaver <adrian.klaver@aklaver.com>
> *Enviado:* miércoles, 9 de noviembre de 2016 21:02
> *Para:* Fran ...; pgsql-general@postgresql.org
> *Asunto:* Re: [GENERAL] Role and grants
>
> On 11/09/2016 11:05 AM, Fran ... wrote:
>> Hi,
>>
>>
>> I am a new user with PostgreSQL, I came from MySQL and I am experiencing
>> some issues with roles and privileges.
>>
>>
>> I have created a DB,user and grant privilege on this DB to this user.
>> How could I check what is the privileges/permissions for this user?
>>
>>
>> Transcript:
>>
>>
>> postgres=# create database test;
>> CREATE DATABASE
>> postgres=# create user test with password 'test';
>> CREATE ROLE
>> postgres=# grant all privileges on database test to test;
>> GRANT
>
> My guess is the above is not doing what you think it is. See further
> comments below for seeing what 'grant all privileges on database' gets you.
>
>> postgres=# \l
>>                                   List of databases
>>    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access
>> privileges
>> -----------+----------+----------+-------------+-------------+-----------------------
>>  postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
>>  template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
>> =c/postgres          +
>>            |          |          |             |             |
>> postgres=CTc/postgres
>>  template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
>> =c/postgres          +
>>            |          |          |             |             |
>> postgres=CTc/postgres
>>  test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
>> =Tc/postgres         +
>>            |          |          |             |             |
>> postgres=CTc/postgres+
>>            |          |          |             |             |
>> test=CTc/postgres
>>  test1     | test1    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/test1
>>            +
>>            |          |          |             |             |
>> test1=CTc/test1
>> (5 rows)
>>
>> With "\l" command It's no clear.
>
> To understand the abbreviations see here:
>
> https://www.postgresql.org/docs/9.5/static/sql-grant.html
> PostgreSQL: Documentation: 9.5: GRANT
> <https://www.postgresql.org/docs/9.5/static/sql-grant.html>
> www.postgresql.org
> GRANT on Database Objects. This variant of the GRANT command gives
> specific privileges on a database object to one or more roles. These
> privileges are added to those ...
>
>
>
>
> Look for:
>
> "The entries shown by \dp are interpreted thus:"
>
>
>>
>>
>> Finally, I don't find some command like "show grants for..." in MySQL.
>>
>>
>> Regards.
>>
>> Bryan
>>
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com


--
Adrian Klaver
adrian.klaver@aklaver.com