Thread: Grant select for all tables of the 12 schemas of my one db ?
Good morning,
I work on Postgresql 13 (windows) and Postgis.
For some "basic USERS", i have to grant select/read for all tables of the 12 schemas of my db ?
With Postgresql 13, i am obliged to write :
GRANT SELECT ON ALL TABLES IN SCHEMA TO username ?
Or a easiest way is possible?
With Postgresql 14, it seems to be easiest :
https://www.postgresql.org/docs/14/predefined-roles.html
I work on Postgresql 13 (windows) and Postgis.
For some "basic USERS", i have to grant select/read for all tables of the 12 schemas of my db ?
With Postgresql 13, i am obliged to write :
GRANT SELECT ON ALL TABLES IN SCHEMA TO username ?
Or a easiest way is possible?
With Postgresql 14, it seems to be easiest :
https://www.postgresql.org/docs/14/predefined-roles.html
In advance, thanks a lot for your help.
Re: Grant select for all tables of the 12 schemas of my one db ?
From
hubert depesz lubaczewski
Date:
On Wed, Oct 13, 2021 at 11:04:42AM +0200, celati Laurent wrote: > Good morning, > > I work on Postgresql 13 (windows) and Postgis. > For some "basic USERS", i have to grant select/read for all tables of the > 12 schemas of my db ? > > With Postgresql 13, i am obliged to write : > *GRANT SELECT ON ALL TABLES IN SCHEMA TO username ?* Yes. For each schema. You could write a DO query, or even get psql to run it automaticaly-ish for every schema, but it will still be separate query for each schema. depesz
something like this ?
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 readonlyuser_role';
raise notice '%', stmt;
execute stmt;
stmt = 'GRANT SELECT ON ALL TABLES IN SCHEMA ' || sch || ' TO readonlyuser_role';
raise notice '%', stmt;
execute stmt;
end loop;
end; $$;
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.
postgres@db1:~$ psql -E
psql (12.8 (Ubuntu 12.8-1.pgdg18.04+1))
Type "help" for help.
postgres=# \dn *.*
********* QUERY **********
SELECT n.nspname AS "Name",
pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
ORDER BY 1;
**************************
List of schemas
Name | Owner
--------------------+----------
information_schema | postgres
pg_catalog | postgres
pg_temp_1 | postgres
pg_toast | postgres
pg_toast_temp_1 | postgres
public | postgres
(6 rows)
On Wed, 13 Oct 2021 at 15:22, hubert depesz lubaczewski <depesz@depesz.com> wrote:
On Wed, Oct 13, 2021 at 11:04:42AM +0200, celati Laurent wrote:
> Good morning,
>
> I work on Postgresql 13 (windows) and Postgis.
> For some "basic USERS", i have to grant select/read for all tables of the
> 12 schemas of my db ?
>
> With Postgresql 13, i am obliged to write :
> *GRANT SELECT ON ALL TABLES IN SCHEMA TO username ?*
Yes. For each schema.
You could write a DO query, or even get psql to run it automaticaly-ish
for every schema, but it will still be separate query for each schema.
depesz
Thanks,
Vijay
Mumbai, India
Re: Grant select for all tables of the 12 schemas of my one db ?
From
hubert depesz lubaczewski
Date:
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
On Wed, 13 Oct 2021 at 16:30, hubert depesz lubaczewski <depesz@depesz.com> 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