From: | Vijaykumar Jain <vijaykumarjain(dot)github(at)gmail(dot)com> |
---|---|
To: | depesz(at)depesz(dot)com |
Cc: | celati Laurent <laurent(dot)celati(at)gmail(dot)com>, pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Grant select for all tables of the 12 schemas of my one db ? |
Date: | 2021-10-13 10:03:20 |
Message-ID: | CAM+6J96q2orrAu-U8BmtSwyoSaCXW+U8v--vKi3k14a210D=vw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)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(at)depesz(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | hubert depesz lubaczewski | 2021-10-13 11:00:16 | Re: Grant select for all tables of the 12 schemas of my one db ? |
Previous Message | hubert depesz lubaczewski | 2021-10-13 09:52:15 | Re: Grant select for all tables of the 12 schemas of my one db ? |