Search Postgresql Archives

Re: Grant select for all tables of the 12 schemas of my one db ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Wed, 13 Oct 2021 at 16:30, hubert depesz lubaczewski <depesz@xxxxxxxxxx> wrote:
On Wed, Oct 13, 2021 at 03:33:20PM +0530, Vijaykumar Jain wrote:
> something like this ?

Like, but not exactly.

Consider what will happen if you have schema named "whatever something
else" - with spaces in it. Or "badlyNamedSchema".


Yeah, my bad. I ran that casually, which was wrong. Thanks for correcting it.


postgres=# \dn
   List of schemas
   Name    |  Owner
-----------+----------
 my Schema | postgres
 public    | postgres
(2 rows)

-- the problem with my original dynamic sql
postgres=# do $$
declare sch text; stmt text;
begin
for sch in select nspname from pg_namespace where nspname not like 'pg\_%' and nspname not like 'information%' loop -- use what you want, filter out rest
stmt = 'GRANT USAGE ON SCHEMA ' || sch || ' TO postgres';
raise notice '%', stmt;
execute stmt;
end loop;
end; $$;
NOTICE:  GRANT USAGE ON SCHEMA public TO postgres
NOTICE:  GRANT USAGE ON SCHEMA my Schema TO postgres
ERROR:  syntax error at or near "Schema"
LINE 1: GRANT USAGE ON SCHEMA my Schema TO postgres
                                 ^
QUERY:  GRANT USAGE ON SCHEMA my Schema TO postgres
CONTEXT:  PL/pgSQL function inline_code_block line 7 at EXECUTE

-- the solution
postgres=# do $$
declare sch text; stmt text;
begin
for sch in select nspname from pg_namespace where nspname not like 'pg\_%' and nspname not like 'information%' loop -- use what you want, filter out rest
stmt = 'GRANT USAGE ON SCHEMA ' || quote_ident(sch) || ' TO postgres';
raise notice '%', stmt;
execute stmt;
end loop;
end; $$;
NOTICE:  GRANT USAGE ON SCHEMA public TO postgres
NOTICE:  GRANT USAGE ON SCHEMA "my Schema" TO postgres
DO


/*
-- OR using format
postgres=# do $$
declare sch text; stmt text;
begin
for sch in select nspname from pg_namespace where nspname not like 'pg\_%' and nspname not like 'information%' loop -- use what you want, filter out rest
stmt = format('GRANT USAGE ON SCHEMA %I TO postgres', sch);
raise notice '%', stmt;
execute stmt;
end loop;
end; $$;
NOTICE:  GRANT USAGE ON SCHEMA public TO postgres
NOTICE:  GRANT USAGE ON SCHEMA "my Schema" TO postgres
DO
*/





 
Generally you'd want to use:

execute format('GRANT USAGE ON SCHEMA %I TO readonlyuser_role', sch);

and it will take care of it.

> also,
> in case you like, I have kind of liked this
> you can try running meta commands using psql -E to get the query that you
> would like to run for DO block.

while in psql, you can simply:
select format() ... from ...;
make sure that it returns list of correct sql queries, with no mistakes,
and with ; at the end of each command, and then rerun it like:
select format() ... from ... \gexec

depesz



--
Thanks,
Vijay
Mumbai, India

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux