Postgres Pro
Downloads
Home   >   mailing lists

Thread: Question about granting permissions

Question about granting permissions

From
Matvey Teplov
Date:
Hi,

Sorry to bother with the stupid question guys - I'm new to the Postgres.
I'm having issue allowing user to access the database - the user is
not allowed to access the data. I do the following:
1)   grant all on database testdb table mytable to trinity;

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |  Collation  |    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
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres
                                                             :
postgres=CTc/postgres
                                                             :
trinity=CTc/postgres
(4 rows)

But when I login (psql -d testdb -U trinity) as trinity and check it,
it doesn't work.

testdb=> select * from mytable;
ERROR:  permission denied for relation mytable
testdb=> \dp
                            Access privileges
 Schema |  Name   | Type  | Access privileges | Column access privileges
--------+---------+-------+-------------------+--------------------------
 public | mytable | table |                   |
(1 row)

There is also entry in the log:
2012-08-26 13:06:01 CEST testdb trinity ERROR:  permission denied for
relation mytable
2012-08-26 13:06:01 CEST testdb trinity STATEMENT:  select * from mytable;


Can someone explain what do I do wrong? Thank you in advance!

Cheers

Re: Question about granting permissions

From
Dmitriy Igrishin
Date:
Hey Matvey,

2012/8/26 Matvey Teplov <matvey.teplov@gmail.com>
Hi,

Sorry to bother with the stupid question guys - I'm new to the Postgres.
I'm having issue allowing user to access the database - the user is
not allowed to access the data. I do the following:
1)   grant all on database testdb table mytable to trinity;
By the command above you're granting privileges on the database
object itself, i.e. connect, create schemas, creating the temprorary
tables.

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |  Collation  |    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
 testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres
                                                             :
postgres=CTc/postgres
                                                             :
trinity=CTc/postgres
(4 rows)

But when I login (psql -d testdb -U trinity) as trinity and check it,
it doesn't work.

testdb=> select * from mytable;
ERROR:  permission denied for relation mytable
testdb=> \dp
                            Access privileges
 Schema |  Name   | Type  | Access privileges | Column access privileges
--------+---------+-------+-------------------+--------------------------
 public | mytable | table |                   |
(1 row)

There is also entry in the log:
2012-08-26 13:06:01 CEST testdb trinity ERROR:  permission denied for
relation mytable
2012-08-26 13:06:01 CEST testdb trinity STATEMENT:  select * from mytable;


Can someone explain what do I do wrong? Thank you in advance!
You need to grant on the another database object (table):
GRANT SELECT ON mytable TO trinity.

Please, see
http://www.postgresql.org/docs/9.2/static/sql-grant.html

--
// Dmitriy.


Re: Question about granting permissions

From
Guillaume Lelarge
Date:
On Sun, 2012-08-26 at 13:10 +0200, Matvey Teplov wrote:
> Hi,
>
> Sorry to bother with the stupid question guys - I'm new to the Postgres.
> I'm having issue allowing user to access the database - the user is
> not allowed to access the data. I do the following:
> 1)   grant all on database testdb table mytable to trinity;
>
> postgres=# \l
>                                   List of databases
>    Name    |  Owner   | Encoding |  Collation  |    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
>  testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres
>                                                              :
> postgres=CTc/postgres
>                                                              :
> trinity=CTc/postgres
> (4 rows)
>
> But when I login (psql -d testdb -U trinity) as trinity and check it,
> it doesn't work.
>
> testdb=> select * from mytable;
> ERROR:  permission denied for relation mytable
> testdb=> \dp
>                             Access privileges
>  Schema |  Name   | Type  | Access privileges | Column access privileges
> --------+---------+-------+-------------------+--------------------------
>  public | mytable | table |                   |
> (1 row)
>
> There is also entry in the log:
> 2012-08-26 13:06:01 CEST testdb trinity ERROR:  permission denied for
> relation mytable
> 2012-08-26 13:06:01 CEST testdb trinity STATEMENT:  select * from mytable;
>
>
> Can someone explain what do I do wrong? Thank you in advance!
>

Giving all permissions to your user on a database doesn't mean he has
all permissions on every object inside this database. It simply means he
has permission to connect, and create object in this database.

So, if your user isn't the owner of this table, he cannot read it.


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

Re: Question about granting permissions

From
Matvey Teplov
Date:
Thank guys! Got it.

On Sun, Aug 26, 2012 at 1:37 PM, Guillaume Lelarge
<guillaume@lelarge.info> wrote:
> On Sun, 2012-08-26 at 13:10 +0200, Matvey Teplov wrote:
>> Hi,
>>
>> Sorry to bother with the stupid question guys - I'm new to the Postgres.
>> I'm having issue allowing user to access the database - the user is
>> not allowed to access the data. I do the following:
>> 1)   grant all on database testdb table mytable to trinity;
>>
>> postgres=# \l
>>                                   List of databases
>>    Name    |  Owner   | Encoding |  Collation  |    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
>>  testdb    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres
>>                                                              :
>> postgres=CTc/postgres
>>                                                              :
>> trinity=CTc/postgres
>> (4 rows)
>>
>> But when I login (psql -d testdb -U trinity) as trinity and check it,
>> it doesn't work.
>>
>> testdb=> select * from mytable;
>> ERROR:  permission denied for relation mytable
>> testdb=> \dp
>>                             Access privileges
>>  Schema |  Name   | Type  | Access privileges | Column access privileges
>> --------+---------+-------+-------------------+--------------------------
>>  public | mytable | table |                   |
>> (1 row)
>>
>> There is also entry in the log:
>> 2012-08-26 13:06:01 CEST testdb trinity ERROR:  permission denied for
>> relation mytable
>> 2012-08-26 13:06:01 CEST testdb trinity STATEMENT:  select * from mytable;
>>
>>
>> Can someone explain what do I do wrong? Thank you in advance!
>>
>
> Giving all permissions to your user on a database doesn't mean he has
> all permissions on every object inside this database. It simply means he
> has permission to connect, and create object in this database.
>
> So, if your user isn't the owner of this table, he cannot read it.
>
>
> --
> Guillaume
> http://blog.guillaume.lelarge.info
> http://www.dalibo.com
>