Role and grants

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.

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

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

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

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&gt;
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

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

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

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&gt;
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

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