Re: Grant Question

Travis Whitton <whitton(at)atlantic(dot)net> wrote in message news:<slrnbc045f(dot)42h(dot)whitton(at)grub(dot)ath(dot)cx>...
> Hi - I'm new to postgreSQL, and I'm having some issues with using GRANT.
> I'm looking for a way to grant privileges on all tables in a given database
> to a user. I can accomplish this using individual grant statements for each
> table; however, a global grant is not working for me. Here's an example.
>
> template1=# CREATE DATABASE dummy;
> CREATE DATABASE
>
> edit=# \c dummy;
> You are now connected to database dummy.
>
> dummy=# CREATE TABLE test (id int);
> CREATE TABLE
>
> dummy=# CREATE TABLE test2 (id int);
> CREATE TABLE
>
> dummy=# GRANT ALL ON DATABASE dummy TO devel;
> GRANT
>
> dummy=# \dp
> Access privileges for database "dummy"
> Schema | Table | Access privileges
> --------+-------+-------------------
> public | test |
> public | test2 |
> (2 rows)
>
> No privileges...
>
> dummy=# GRANT ALL ON test TO devel;
> GRANT
>
> dummy=# \dp
> Access privileges for database "dummy"
> Schema | Table | Access privileges
> --------+-------+------------------------------------
> public | test | {=,postgres=arwdRxt,devel=arwdRxt}
> public | test2 |
> (2 rows)
>
> This works but only by referencing a specific table. Any
> help would be appreciated.
>
> Thanks,
> Travis Whitton <whitton(at)atlantic(dot)net>

one solution is to create a user group with access privileges that you
desire to grant (say 'admin' is anything, 'users' can only read,
insert, update and delete).
You then grant privilege to that group only and maintain users in the
group.
I am not familiar with the schema that appears first(?) in 7.3, but
examine the admin help on http://www.postgresql.org/docs to see if
this offers a more direct approach.

clearly this (group) approach won't help you save time if the tables
already exist and you have to grant individually, but you can always
\dt into a file and edit it into sql to grant using search and
replace. This approach will save you having to do this again for
another user.

Good luck.

Kev.