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

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".

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