Thread: grant select on all tables of a schema

grant select on all tables of a schema

From
Nabil Sayegh
Date:
Hi,

is it possible to 'GRANT <WHATEVER> ON ALL TABLES' in 1 command ?

I'm using 7.3.2

TIA
--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de


Re: grant select on all tables of a schema

From
Bruno Wolff III
Date:
On Wed, Jun 18, 2003 at 01:39:43 +0200,
  Nabil Sayegh <postgresql@e-trolley.de> wrote:
> Hi,
>
> is it possible to 'GRANT <WHATEVER> ON ALL TABLES' in 1 command ?
>
> I'm using 7.3.2

Not unless you write your own function to do it. The GRANT command
only works on one object at a time.

Re: grant select on all tables of a schema

From
Dani Oderbolz
Date:
Nabil Sayegh wrote:

>Hi,
>
>is it possible to 'GRANT <WHATEVER> ON ALL TABLES' in 1 command ?
>
>I'm using 7.3.2
>
>TIA
>
>
For this case of problem, I find it great to generate scripts out of the
catalog(here for the current schema):

SELECT 'GRANT <WHATEVER> ON '||tablename||' TO <WHOEVER>;'
FROM pg_tables
WHERE
schemaname = current_schema();

This way, you can speed up such Problems a lot, and you are still flexible.
Sure, you can put this into a function as well, and run the SQL dynamically.
(But I rather have allok at the generated statements before I run them.

Cheers,
Dani