Thread: GRANT SELECT

Is there a simple way to grant SELECT privileges to a user for all the
tables in a database, without having to do it for each table?

Thanks,
Steve

--
Stephen Crawford
Center for Environmental Informatics
The Pennsylvania State University

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [, ...] | ALL [ PRIVILEGES ] } ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] } TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] See more on PostgreSQL Documentation

---

View this message in context: Re: GRANT SELECT
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

I see that works for 9, but I'm still in 8.4.  But another person just sent me a bit of code to do it:

do $$
declare tn text;
begin
for tn in select c.oid::regclass::text            from pg_class c join pg_namespace n on n.oid = c.relnamespace            where n.nspname not in ('pg_catalog',
'information_schema') and n.nspname !~ '^pg_toast' and c.relkind in
('r','')
loop execute format('grant select on %s to your_user', tn);
end loop;
end;
$$ ;

Thanks,
Steve

On 8/27/2012 1:20 PM, ElEucas wrote:

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER } [, ...] | ALL [ PRIVILEGES ] } ON { [ TABLE ] table_name [, ...] | ALL TABLES IN SCHEMA schema_name [, ...] } TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] See more on PostgreSQL Documentation

--
ElEucas
México

---

View this message in context: Re: GRANT SELECT
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Stephen Crawford
Center for Environmental Informatics
The Pennsylvania State University

2012/8/27 Stephen Crawford <src176@psu.edu>:
> I see that works for 9, but I'm still in 8.4.  But another person just sent
> me a bit of code to do it:
>

just don't use "format" function

execure 'grant select on ' || tn || 'to ....';

>
> do $$
> declare tn text;
> begin
> for tn in select c.oid::regclass::text
>              from pg_class c join pg_namespace n on n.oid = c.relnamespace
>              where n.nspname not in ('pg_catalog',
> 'information_schema') and n.nspname !~ '^pg_toast' and c.relkind in
> ('r','')
> loop
>   execute format('grant select on %s to your_user', tn);
> end loop;
> end;
> $$ ;
>
> Thanks,
> Steve
>
>
> On 8/27/2012 1:20 PM, ElEucas wrote:
>
> GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES |
> TRIGGER } [, ...] | ALL [ PRIVILEGES ] } ON { [ TABLE ] table_name [, ...] |
> ALL TABLES IN SCHEMA schema_name [, ...] } TO { [ GROUP ] role_name | PUBLIC
> } [, ...] [ WITH GRANT OPTION ] See more on PostgreSQL Documentation
> --
> ElEucas
> México
>
> ________________________________
> View this message in context: Re: GRANT SELECT
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Stephen Crawford
> Center for Environmental Informatics
> The Pennsylvania State University

On 2012-08-27, Stephen Crawford <src176@psu.edu> wrote:
> Is there a simple way to grant SELECT privileges to a user for all the
> tables in a database, without having to do it for each table?

grant the role (membership of) a role that already has the priveleges.


--
⚂⚃ 100% natural