Thread: Grant Question

Grant Question

From
Travis Whitton
Date:
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@atlantic.net>

Re: Grant Question

From
Bruno Wolff III
Date:
On Mon, May 12, 2003 at 21:11:47 +0000,
  Travis Whitton <whitton@atlantic.net> wrote:
>
> dummy=# GRANT ALL ON DATABASE dummy TO devel;
> GRANT

Granting all on a database allows the grantee to create schemas and
temporary tables in that database. It doesn't grant access to any
tables in that database. To do that you want to write a script that
does a query to get a list of table names and then issue grant
statments for those tables.

Re: Grant Question

From
kevin@mtel.co.uk (kevin)
Date:
Travis Whitton <whitton@atlantic.net> wrote in message news:<slrnbc045f.42h.whitton@grub.ath.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@atlantic.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.