Thread: GRANT on all tables?

How do I GRANT an user privileges for all tables in an database with one
command, as it is now i need to specify the name of each table.
I want to write something like
GRANT SELECT,INSERT ON mydatabase TO user; this doesn't work but are there a
way of getting someting similar to work?

--
Daniel Udsen

GRANT ALL TO PUBLIC;


"dudsen" <dudsen@koen.dk> wrote in message
news:1050499026.680723@kalvebod.groenjord.dk...
> How do I GRANT an user privileges for all tables in an database with one
> command, as it is now i need to specify the name of each table.
> I want to write something like
> GRANT SELECT,INSERT ON mydatabase TO user; this doesn't work but are there
a
> way of getting someting similar to work?
>
> --
> Daniel Udsen

codeWarrior wrote:
> GRANT ALL TO PUBLIC;

Uh, that doesn't work, and actually we don't have a global 'give all
permissions on all tables' capability.


>
>
> "dudsen" <dudsen@koen.dk> wrote in message
> news:1050499026.680723@kalvebod.groenjord.dk...
> > How do I GRANT an user privileges for all tables in an database with one
> > command, as it is now i need to specify the name of each table.
> > I want to write something like
> > GRANT SELECT,INSERT ON mydatabase TO user; this doesn't work but are there
> a
> > way of getting someting similar to work?
> >
> > --
> > Daniel Udsen
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Can't remember where I found this but its worked in the past for
granting privileges to all tables in a DB to a user.

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


--------------------------------------------------------------------------
Simon Fortelny
Research Technician
Fred Hutchinson Cancer Research Center
1100 Fairview Ave. N. D4-100
Seattle, WA 98109-1024

tel. 206.667.5618
email. sforteln@fred.fhcrc.org

On Wed, 16 Apr 2003, dudsen wrote:

> How do I GRANT an user privileges for all tables in an database with one
> command, as it is now i need to specify the name of each table.
> I want to write something like
> GRANT SELECT,INSERT ON mydatabase TO user; this doesn't work but are there a
> way of getting someting similar to work?
>
> --
> Daniel Udsen
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>

You can try something like this, just modify it for the language you use:

set permission "SELECT" ;# update, all...
lappend sql "SELECT 'grant $permission on ' || relname || ' to \"$username\"
;' from pg_class "
lappend sql "WHERE relname not like 'pg_%' and relkind in ('r','v');"

Now you have sql which, when executed, dynamically generates sql
for granting desired permission for all tables and views.
You may add 's' to the last clause to include sequences, if needed.

You can run something like that in a single shot;
for example you can execute sql with output redirected to a temp file,
then include this file, or maybe come up with something more fancy to
avoid using files at all.

Hope it helps.
Mike.


> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org
> [mailto:pgsql-admin-owner@postgresql.org]On Behalf Of dudsen
> Sent: Wednesday, April 16, 2003 9:17 AM
> To: pgsql-admin@postgresql.org
> Subject: [ADMIN] GRANT on all tables?
>
>
> How do I GRANT an user privileges for all tables in an database with one
> command, as it is now i need to specify the name of each table.
> I want to write something like
> GRANT SELECT,INSERT ON mydatabase TO user; this doesn't work but
> are there a
> way of getting someting similar to work?
>
> --
> Daniel Udsen
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>