Re: Strange Grant behavior in postgres 8.3

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 ]