Обсуждение: Strange Grant behavior in postgres 8.3

Version:
PostgreSQL 8.3.5 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real
(Debian 4.3.2-1) 4.3.2

I have a DB "foo" created and owned by postgres.

No I created another role called "bar" and with the user postgres in
the db foo I did:

#> grant all on foo to bar;

when I select from pg_database I can see the correct line

bar=CTc/postgres for the db foo

I login with user bar to foo

$ psql -U bar -h localhost foo

but I cannot select anything from any table.

Now the strange part.

I grant to one table only a all privileges
#> grant all on table nodes to bar

$> \z nodes
 Schema | Name  | Type  |                 Access privileges
--------+-------+-------+---------------------------------------------------
 public | nodes | table | {postgres=arwdxt/postgres,foo=arwdxt/postgres}

and then the user bar could read from all tables.

What am I doing wrong? Or where can I look if I missed something

--
[ Clemens Schwaighofer                      -----=====:::::~ ]
[ IT Engineer/Manager                                        ]
[ E-Graphics Communications, TEQUILA\ Japan IT Group         ]
[                6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ]
[ Tel: +81-(0)3-3545-7703            Fax: +81-(0)3-3545-7343 ]
[ http://www.tequila.jp                                      ]

Advertising Age Global Agency of the Year 2008
Adweek Global Agency of the Year 2008

This e-mail is intended only for the named person or entity to which
it is addressed and contains valuable business information that is
privileged, confidential and/or otherwise protected from disclosure.
Dissemination, distribution or copying of this e-mail or the
information herein by anyone other than the intended recipient, or
an employee or agent responsible for delivering the message to the
intended recipient, is strictly prohibited.  All contents are the
copyright property of TBWA Worldwide, its agencies or a client of
such agencies. If you are not the intended recipient, you are
nevertheless bound to respect the worldwide legal rights of TBWA
Worldwide, its agencies and its clients. We require that unintended
recipients delete the e-mail and destroy all electronic copies in
their system, retaining no copies in any media.If you have received
this e-mail in error, please immediately notify us via e-mail to
disclaimer@tbwaworld.com.  We appreciate your cooperation.

We make no warranties as to the accuracy or completeness of this
e-mail and accept no liability for its content or use.  Any opinions
expressed in this e-mail are those of the author and do not
necessarily reflect the opinions of TBWA Worldwide or any of its
agencies or affiliates.

Schwaighofer Clemens wrote:
> Version:
> PostgreSQL 8.3.5 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real(Debian 4.3.2-1) 4.3.2
> 
> I have a DB "foo" created and owned by postgres.
> 
> No I created another role called "bar" and with the user postgres in
> the db foo I did:
> 
> #> grant all on foo to bar;

That statement is wrong.
It should be:

GRANT ALL ON DATABASE foo TO bar;

> when I select from pg_database I can see the correct line
> 
> bar=CTc/postgres for the db foo
> 
> I login with user bar to foo
> 
> $ psql -U bar -h localhost foo
> 
> but I cannot select anything from any table.

That is ok, because you have no permissions on schemata and tables.

> Now the strange part.
> 
> I grant to one table only a all privileges
> #> grant all on table nodes to bar
> 
> $> \z nodes
>  Schema | Name  | Type  |                 Access privileges
> --------+-------+-------+---------------------------------------------------
>  public | nodes | table | {postgres=arwdxt/postgres,foo=arwdxt/postgres}

That is very strange, because the user in your example is "bar" and not "foo".

> and then the user bar could read from all tables.

That is unlikely.

> What am I doing wrong? Or where can I look if I missed something

It seems that some of your statements or results are different
from what actually happened.

Try to reproduce it with a new clean database, and then copy and
paste what you did and what results you got so that we can reproduce
the behaviour.

Yours,
Laurenz Albe

Sorry for some confusion. I re-created the whole thing again with
fresh users and a fresh database:

(1) Create a new user and a new db, also create a table 'test' inside
with user 'foo'

$> createuser -U postgres -P -E foo
$> createdb -U postgres -O foo -E utf8 foo_test

(2) create a second user

$> createuser -U postgres -P -E bar

just a check for db ownership

=> \l
               List of databases
         Name         |    Owner     | Encoding
----------------------+--------------+----------
 foo_test             | foo          | UTF8

(3) check the database rights (with user postgres)
# select * from pg_database where datname = 'foo_test';
 datname  | datdba | encoding | datistemplate | datallowconn |
datconnlimit | datlastsysoid | datfrozenxid | dattablespace |
datconfig | datacl

----------+--------+----------+---------------+--------------+--------------+---------------+--------------+---------------+-----------+--------
 foo_test |  72427 |        6 | f             | t            |
  -1 |         11510 |          379 |          1663 |           |
(1 row)

(4) login as user bar and get correct error

=> select * from test;
ERROR:  permission denied for relation test;

(5) give user 'bar' full rights to the database 'foo_test' as logged
in with user 'postgres'

# grant all on database foo_test to bar;

(6) check rights again

datname  | datdba | encoding | datistemplate | datallowconn |
datconnlimit | datlastsysoid | datfrozenxid | dattablespace |
datconfig |              datacl

----------+--------+----------+---------------+--------------+--------------+---------------+--------------+---------------+-----------+-----------------------------------
 foo_test |  72427 |        6 | f             | t            |
  -1 |         11510 |          379 |          1663 |           |
{=Tc/foo,foo=CTc/foo,bar=CTc/foo}

(7) login again with 'bar' user

=> select * from test;
ERROR:  permission denied for relation test

still cannot select

rights for the table inside (the same for postgres, foo or bar user):

# \z
 Access privileges for database "foo_test"
 Schema | Name | Type  | Access privileges
--------+------+-------+-------------------
 public | test | table |

So what do I do wrong? Even if I do the GRANT command as user 'foo'
who is the database owner, I still cannot select with the user 'bar'.
It only works if I set GRANT rights for the TABLE itself:

as user 'foo' logged in
=> grant all on table test to bar;

=> \z
 Access privileges for database "foo_test"
 Schema | Name | Type  | Access privileges
--------+------+-------+-------------------
 public | test | table | {foo=arwdxt/foo,bar=arwdxt/foo}

and then login again with user 'bar'

=> select * from test;
 test
------
 foo
(1 row)


I am seriously confused and ask myself what I do wrong

On Tue, Feb 17, 2009 at 21:06, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:
> Schwaighofer Clemens wrote:
>> Version:
>> PostgreSQL 8.3.5 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real(Debian 4.3.2-1) 4.3.2
>>
>> I have a DB "foo" created and owned by postgres.
>>
>> No I created another role called "bar" and with the user postgres in
>> the db foo I did:
>>
>> #> grant all on foo to bar;
>
> That statement is wrong.
> It should be:
>
> GRANT ALL ON DATABASE foo TO bar;
>
>> when I select from pg_database I can see the correct line
>>
>> bar=CTc/postgres for the db foo
>>
>> I login with user bar to foo
>>
>> $ psql -U bar -h localhost foo
>>
>> but I cannot select anything from any table.
>
> That is ok, because you have no permissions on schemata and tables.
>
>> Now the strange part.
>>
>> I grant to one table only a all privileges
>> #> grant all on table nodes to bar
>>
>> $> \z nodes
>>  Schema | Name  | Type  |                 Access privileges
>> --------+-------+-------+---------------------------------------------------
>>  public | nodes | table | {postgres=arwdxt/postgres,foo=arwdxt/postgres}
>
> That is very strange, because the user in your example is "bar" and not "foo".
>
>> and then the user bar could read from all tables.
>
> That is unlikely.
>
>> What am I doing wrong? Or where can I look if I missed something
>
> It seems that some of your statements or results are different
> from what actually happened.
>
> Try to reproduce it with a new clean database, and then copy and
> paste what you did and what results you got so that we can reproduce
> the behaviour.
>
> Yours,
> Laurenz Albe
>



--
[ Clemens Schwaighofer                      -----=====:::::~ ]
[ IT Engineer/Manager                                        ]
[ E-Graphics Communications, TEQUILA\ Japan IT Group         ]
[                6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ]
[ Tel: +81-(0)3-3545-7703            Fax: +81-(0)3-3545-7343 ]
[ http://www.tequila.jp                                      ]

Advertising Age Global Agency of the Year 2008
Adweek Global Agency of the Year 2008

This e-mail is intended only for the named person or entity to which
it is addressed and contains valuable business information that is
privileged, confidential and/or otherwise protected from disclosure.
Dissemination, distribution or copying of this e-mail or the
information herein by anyone other than the intended recipient, or
an employee or agent responsible for delivering the message to the
intended recipient, is strictly prohibited.  All contents are the
copyright property of TBWA Worldwide, its agencies or a client of
such agencies. If you are not the intended recipient, you are
nevertheless bound to respect the worldwide legal rights of TBWA
Worldwide, its agencies and its clients. We require that unintended
recipients delete the e-mail and destroy all electronic copies in
their system, retaining no copies in any media.If you have received
this e-mail in error, please immediately notify us via e-mail to
disclaimer@tbwaworld.com.  We appreciate your cooperation.

We make no warranties as to the accuracy or completeness of this
e-mail and accept no liability for its content or use.  Any opinions
expressed in this e-mail are those of the author and do not
necessarily reflect the opinions of TBWA Worldwide or any of its
agencies or affiliates.

Schwaighofer Clemens wrote:
> So what do I do wrong? Even if I do the GRANT command as user 'foo'
> who is the database owner, I still cannot select with the user 'bar'.
> It only works if I set GRANT rights for the TABLE itself:
>
> as user 'foo' logged in
> => grant all on table test to bar;
>

that is correct.  DATABASE privileges relate to connecting to the
database, permissions to create objects and so forth.


each object in the database has its own access rights.

for typical application use, I create teh database so the primary
application account owns the database, then let that account create all
the tables so it owns those too.

$ sudo -u postgres createuser someuser
$ sudo -u postgres createdb -o someuser somedb

then access this database with that user to create the tables and such

On 02/18/2009 01:15 PM, John R Pierce wrote:
> Schwaighofer Clemens wrote:
>> So what do I do wrong? Even if I do the GRANT command as user 'foo'
>> who is the database owner, I still cannot select with the user 'bar'.
>> It only works if I set GRANT rights for the TABLE itself:
>>
>> as user 'foo' logged in
>> => grant all on table test to bar;
>>
>
> that is correct.  DATABASE privileges relate to connecting to the
> database, permissions to create objects and so forth.
>
>
> each object in the database has its own access rights.
>
> for typical application use, I create teh database so the primary
> application account owns the database, then let that account create all
> the tables so it owns those too.
>
> $ sudo -u postgres createuser someuser
> $ sudo -u postgres createdb -o someuser somedb
>
> then access this database with that user to create the tables and such

I see, normally I always create a user that owns the DB, so I don't have
those problems.

But yesterday I run in some issues with table ownership and thought if I
just give the user all rights for the DB, he should have all rights to
the tables too.

The other problem is, that there is no "grant all on table db.* ..." but
I have to do that for each table seperate, or in a "grant all on table
a, b, ...".

I am not sure if there is an easier way, except perhaps through a select
from the pg_ catalog for this db and get the table names there ...

--
[ Clemens Schwaighofer                      -----=====:::::~ ]
[ IT Engineer/Web Producer/Planning/Manager                  ]
[ E-Graphics Communications SP Digital                       ]
[                6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ]
[ Tel: +81-(0)3-3545-7706            Fax: +81-(0)3-3545-7343 ]
[ http://www.e-gra.co.jp                                     ]

Clemens Schwaighofer wrote:
> The other problem is, that there is no "grant all on table db.* ..." but
> I have to do that for each table seperate, or in a "grant all on table
> a, b, ...".
>
> I am not sure if there is an easier way, except perhaps through a select
> from the pg_ catalog for this db and get the table names there ...
>

I believe there are some scripts that can help..

http://pgedit.com/public/sql/acl_admin/index.html
http://unf.be/postgresql/postgres_grant_all.perl
http://www.archonet.com/pgdocs/grant-all.html


one or more of those URLs may help.   FYI, I got these from the infobot
on the freenode #PostgreSQL IRC channel

On Wed, Feb 18, 2009 at 16:09, John R Pierce <pierce@hogranch.com> wrote:
> Clemens Schwaighofer wrote:
>>
>> The other problem is, that there is no "grant all on table db.* ..." but
>> I have to do that for each table seperate, or in a "grant all on table
>> a, b, ...".
>>
>> I am not sure if there is an easier way, except perhaps through a select
>> from the pg_ catalog for this db and get the table names there ...
>>
>
> I believe there are some scripts that can help..
>
> http://pgedit.com/public/sql/acl_admin/index.html
> http://unf.be/postgresql/postgres_grant_all.perl
> http://www.archonet.com/pgdocs/grant-all.html

Thanks a lot.

>
> one or more of those URLs may help.   FYI, I got these from the infobot on
> the freenode #PostgreSQL IRC channel

Time to start up another IRC connection.

--
[ Clemens Schwaighofer                      -----=====:::::~ ]
[ IT Engineer/Manager                                        ]
[ E-Graphics Communications, TEQUILA\ Japan IT Group         ]
[                6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ]
[ Tel: +81-(0)3-3545-7703            Fax: +81-(0)3-3545-7343 ]
[ http://www.tequila.jp                                      ]

Advertising Age Global Agency of the Year 2008
Adweek Global Agency of the Year 2008

This e-mail is intended only for the named person or entity to which
it is addressed and contains valuable business information that is
privileged, confidential and/or otherwise protected from disclosure.
Dissemination, distribution or copying of this e-mail or the
information herein by anyone other than the intended recipient, or
an employee or agent responsible for delivering the message to the
intended recipient, is strictly prohibited.  All contents are the
copyright property of TBWA Worldwide, its agencies or a client of
such agencies. If you are not the intended recipient, you are
nevertheless bound to respect the worldwide legal rights of TBWA
Worldwide, its agencies and its clients. We require that unintended
recipients delete the e-mail and destroy all electronic copies in
their system, retaining no copies in any media.If you have received
this e-mail in error, please immediately notify us via e-mail to
disclaimer@tbwaworld.com.  We appreciate your cooperation.

We make no warranties as to the accuracy or completeness of this
e-mail and accept no liability for its content or use.  Any opinions
expressed in this e-mail are those of the author and do not
necessarily reflect the opinions of TBWA Worldwide or any of its
agencies or affiliates.

John R Pierce wrote:
> that is correct.  DATABASE privileges relate to connecting to the 
> database, permissions to create objects and so forth.

To be precise, there are 3 privileges:
- create temporary tables
- connect to the database
- create schemata

To be allowed to create a table, you need the create privilege
on the containing schema.

To be allowed to select from a table, you also need the usage
privilege on the schema containing the object.

You have all privileges on an object you own.

I guess that should clarify the behaviour in the original question.

Yours,
Laurenz Albe

On Tue, Feb 17, 2009 at 9:20 PM, Clemens Schwaighofer
<clemens.schwaighofer@tequila.jp> wrote:

> But yesterday I run in some issues with table ownership and thought if I
> just give the user all rights for the DB, he should have all rights to
> the tables too.

Try granting select on a database and you will get this:

grant select on database smarlowe to stan;
ERROR:  invalid privilege type SELECT for database

OTOH,

 grant connect on database smarlowe to stan;
GRANT

But even easier is to use the db owner as the ROLE instead of as a
user, and just

alter group guywhoownsthedb add user guywhodoesnt;

On Wed, Feb 18, 2009 at 17:42, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Tue, Feb 17, 2009 at 9:20 PM, Clemens Schwaighofer
> <clemens.schwaighofer@tequila.jp> wrote:
>
>> But yesterday I run in some issues with table ownership and thought if I
>> just give the user all rights for the DB, he should have all rights to
>> the tables too.
>
> Try granting select on a database and you will get this:
>
> grant select on database smarlowe to stan;
> ERROR:  invalid privilege type SELECT for database
>
> OTOH,
>
>  grant connect on database smarlowe to stan;
> GRANT
>
> But even easier is to use the db owner as the ROLE instead of as a
> user, and just
>
> alter group guywhoownsthedb add user guywhodoesnt;

Thats a very good idea. Probably easier to handle, if you need to add
just another user or so.


--
[ Clemens Schwaighofer                      -----=====:::::~ ]
[ IT Engineer/Manager                                        ]
[ E-Graphics Communications, TEQUILA\ Japan IT Group         ]
[                6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ]
[ Tel: +81-(0)3-3545-7703            Fax: +81-(0)3-3545-7343 ]
[ http://www.tequila.jp                                      ]

Advertising Age Global Agency of the Year 2008
Adweek Global Agency of the Year 2008

This e-mail is intended only for the named person or entity to which
it is addressed and contains valuable business information that is
privileged, confidential and/or otherwise protected from disclosure.
Dissemination, distribution or copying of this e-mail or the
information herein by anyone other than the intended recipient, or
an employee or agent responsible for delivering the message to the
intended recipient, is strictly prohibited.  All contents are the
copyright property of TBWA Worldwide, its agencies or a client of
such agencies. If you are not the intended recipient, you are
nevertheless bound to respect the worldwide legal rights of TBWA
Worldwide, its agencies and its clients. We require that unintended
recipients delete the e-mail and destroy all electronic copies in
their system, retaining no copies in any media.If you have received
this e-mail in error, please immediately notify us via e-mail to
disclaimer@tbwaworld.com.  We appreciate your cooperation.

We make no warranties as to the accuracy or completeness of this
e-mail and accept no liability for its content or use.  Any opinions
expressed in this e-mail are those of the author and do not
necessarily reflect the opinions of TBWA Worldwide or any of its
agencies or affiliates.