Re: [GENERAL] Role and grants

2016-11-09 Thread Adrian Klaver 

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.kla...@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.kla...@aklaver.com



--
Adrian Klaver
adrian.kla...@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

Re: [GENERAL] Role and grants

2016-11-09 Thread John R Pierce 

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

Re: [GENERAL] Role and grants

2016-11-09 Thread Fran ... 

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.kla...@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.kla...@aklaver.com

Re: [GENERAL] Role and grants

2016-11-09 Thread Adrian Klaver 

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.kla...@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

Re: [GENERAL] Role and grants

2016-11-09 Thread Melvin Davidson 
On Wed, Nov 9, 2016 at 2:05 PM, 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
> 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   can access.
Just replace  with the actual username you want.

SELECT *
  FROM information_schema.table_privileges
 WHERE grantee = ''
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.

[GENERAL] Role and grants

2016-11-09 Thread Fran ... 
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