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

Поиск
Список
Период
Сортировка
От Vijaykumar Jain
Тема Re: Grant select for all tables of the 12 schemas of my one db ?
Дата
Msg-id CAM+6J96q2orrAu-U8BmtSwyoSaCXW+U8v--vKi3k14a210D=vw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Grant select for all tables of the 12 schemas of my one db ?  (hubert depesz lubaczewski <depesz@depesz.com>)
Ответы Re: Grant select for all tables of the 12 schemas of my one db ?
Список pgsql-general
Дерево обсуждения
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

В списке pgsql-general по дате отправления:

Предыдущее
От: hubert depesz lubaczewski
Дата:
Сообщение: Re: Grant select for all tables of the 12 schemas of my one db ?
Следующее
От: hubert depesz lubaczewski
Дата:
Сообщение: Re: Grant select for all tables of the 12 schemas of my one db ?