From: kevin@mtel.co.uk
To: pgsql-admin@postgresql.org
Subject: Re: Grant Question
Date: 20 May 2003 13:57:58 -0700
Message-ID: <6675f17.0305201257.28d6daba@posting.google.com> (raw)
References: <slrnbc045f.42h.whitton@grub.ath.cx>
Travis Whitton <whitton@atlantic.net> wrote in message news:<grub.ath.cx/">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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: kevin@mtel.co.uk
Subject: Re: Grant Question
In-Reply-To: <6675f17.0305201257.28d6daba@posting.google.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox