Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to GRANT SELECT on all tables?

How to GRANT SELECT on all tables?

From
Date:



Another noobish question.

I'm looking for the "moral equivalent" of the mythical

  GRANT SELECT ON TABLE * TO PUBLIC

I.e. I'm looking for a way to grant SELECT privileges to PUBLIC on all
the tables, without having to specify all the table names.

Naively, I tried the subquery approach

  GRANT SELECT
    ON TABLE (SELECT tablename FROM pg_tables WHERE schemaname='public')
    TO PUBLIC

but this failed too.

Thanks!

kj

Re: How to GRANT SELECT on all tables?

From
Tom Lane
Date:
<kynn@panix.com> writes:
> I.e. I'm looking for a way to grant SELECT privileges to PUBLIC on all
> the tables, without having to specify all the table names.

There isn't any direct way to do that, but various people have written
plpgsql functions that synthesize and execute all the required
individual GRANT commands.  Dig around in the PG list archives ...
http://archives.postgresql.org/

            regards, tom lane

Re: How to GRANT SELECT on all tables?

From
"Kevin Crenshaw"
Date:
One way to accomplish this is to create an SQL script to grant privileges to
all of your tables one at a time.  Then you would simple have to start up a
psql session and enter: \i sqlscriptname.sql;

HTH,

Kevin



-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of kynn@panix.com
Sent: Tuesday, January 31, 2006 4:11 PM
To: pgsql-novice@postgresql.org
Subject: [NOVICE] How to GRANT SELECT on all tables?





Another noobish question.

I'm looking for the "moral equivalent" of the mythical

  GRANT SELECT ON TABLE * TO PUBLIC

I.e. I'm looking for a way to grant SELECT privileges to PUBLIC on all
the tables, without having to specify all the table names.

Naively, I tried the subquery approach

  GRANT SELECT
    ON TABLE (SELECT tablename FROM pg_tables WHERE schemaname='public')
    TO PUBLIC

but this failed too.

Thanks!

kj


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

Re: How to GRANT SELECT on all tables?

From
Christoph Della Valle
Date:
another way is to use pgAdmin.

- right click on node 'Tables'
- choose grant wizard
the rest is self explaining ;-)

kynn@panix.com schrieb:
> Another noobish question.
>
> I'm looking for the "moral equivalent" of the mythical
>
>   GRANT SELECT ON TABLE * TO PUBLIC
>
> I.e. I'm looking for a way to grant SELECT privileges to PUBLIC on all
> the tables, without having to specify all the table names.
>
> Naively, I tried the subquery approach
>
>   GRANT SELECT
>     ON TABLE (SELECT tablename FROM pg_tables WHERE schemaname='public')
>     TO PUBLIC
>
> but this failed too.
>
> Thanks!
>
> kj
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>
>