Thread: Grant on several tables at once

Hello All,

I have a database that contain 72 tables. How can I grant "SELECT" on
all those 72 tables to a user, at once?

I use to do:

# grant select on table1 to marcelo;
# grant select on table2 to marcelo;
# ...
# grant select on table72 to marcelo;

but it is a lot of tables... I would like to do this at once!

Thanks in advance and
Best Regards,

Marcelo

if its possible to generate the list of tables
from a sql query then do something like


\o out.sql
select ' grant select on table1 to ' || tablename || ' ;' ;
\i out.sql


regds
mallah.

MaRcElO PeReIrA wrote:

> Hello All,
>
> I have a database that contain 72 tables. How can I grant "SELECT" on
> all those 72 tables to a user, at once?
>
> I use to do:
>
> # grant select on table1 to marcelo;
> # grant select on table2 to marcelo;
> # ...
> # grant select on table72 to marcelo;
>
> but it is a lot of tables... I would like to do this at once!
>
> Thanks in advance and
> Best Regards,
>
> Marcelo
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

MaRcElO PeReIrA wrote:

> Hello All,
>
> I have a database that contain 72 tables. How can I grant "SELECT" on
> all those 72 tables to a user, at once?
>
> I use to do:
>
> # grant select on table1 to marcelo;
> # grant select on table2 to marcelo;
> # ...
> # grant select on table72 to marcelo;
>
> but it is a lot of tables... I would like to do this at once!
>
> Thanks in advance and
> Best Regards,
>
> Marcelo


Hi Marcelo,
you will need a tool for that. pgAdmin2 (win32 only) can grant on
multiple objects at once, pgAdmin3 lacks this feature at the moment.

Regards,
Andreas

Marcelo,

Something like this should work to grant rights on all non system tables

psql -t -A -U $DBOWNER -c "select tablename from pg_tables where tablename not
like 'pg_%'" $DB_NAME | xargs -i ./psql -t -A -U $DBOWNER -c "grant ALL on {} to
$USER" $DB_NAME



Quoting MaRcElO PeReIrA <gandalf_mp@yahoo.com.br>:

> Hello All,
>
> I have a database that contain 72 tables. How can I grant "SELECT" on
> all those 72 tables to a user, at once?
>
> I use to do:
>
> # grant select on table1 to marcelo;
> # grant select on table2 to marcelo;
> # ...
> # grant select on table72 to marcelo;
>
> but it is a lot of tables... I would like to do this at once!
>
> Thanks in advance and
> Best Regards,
>
> Marcelo
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

There was a review of a DB book on Slashdot some time ago, and parts of
the book said that SELECTs are always safe since it never modifies
anything in the db :)

This proves it wrong:


Try this (7.4Devel but should work on 7.3): Granting to several users.

-- pg_grant(priviledge, table, user)
create or replace function
   pg_grant(text, text, text) returns boolean language plpgsql as
'begin
execute ''grant '' || $1 || '' on '' || $2 || '' to '' || $3;
return true;
end;';

create or replace function
   pg_revoke(text, text, text) returns boolean language plpgsql as
'begin
execute ''revoke '' || $1 || '' on '' || $2 || '' from '' || $3;
return true;
end;';

-- Check if these are the tables you want:
select schemaname || '.' || tablename from pg_tables where
   schemaname = 'public';

-- Here we go:
select count(*) from pg_tables where
   schemaname = 'public' and
   pg_grant('select', schemaname || '.' || tablename, 'marcelo');

-- "Undo"
select count(*) from pg_tables where
   schemaname = 'public' and
   pg_revoke('select', schemaname || '.' || tablename, 'marcelo');

--
Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386
GNU/Linux
  11:00am  up 222 days,  2:05,  5 users,  load average: 5.03, 5.08, 5.08