Re: GRANT on all tables?

From: Michael Brusser <michael(at)synchronicity(dot)com>
To: dudsen <dudsen(at)koen(dot)dk>, pgsql-admin(at)postgresql(dot)org
Subject: Re: GRANT on all tables?
Date: 2003-04-24 15:43:26
Message-ID: DEEIJKLFNJGBEMBLBAHCOEHDDBAA.michael@synchronicity.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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(at)postgresql(dot)org
> [mailto:pgsql-admin-owner(at)postgresql(dot)org]On Behalf Of dudsen
> Sent: Wednesday, April 16, 2003 9:17 AM
> To: pgsql-admin(at)postgresql(dot)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
>

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Victor Yegorov 2003-04-24 15:46:04 Re: Creating schema for multiple tables/fields/indices.
Previous Message Tom Lane 2003-04-24 15:41:15 Re: Rename constraint?