Search Postgresql Archives

Re: Strange Grant behavior in postgres 8.3

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxx> 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@xxxxxxxxxxxxxx  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. 


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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux