Hello
From: Łukasz Jarych [mailto:jarys...@gmail.com] Sent: Freitag, 13. Juli 2018 16:39 To: pgsql-gene...@postgresql.org >> PG-General Mailing List <pgsql-gene...@postgresql.org> Subject: Re: Read only to schema I found something like this: CREATE ROLE readonly_user WITH LOGIN ENCRYPTED PASSWORD '1234' ALTER ROLE readonly_user SET search_path to public GRANT CONNECT ON DATABASE "TestDb" TO readonly_user; GRANT USAGE ON SCHEMA public TO readonly_user; GRANT USAGE ON ALL SEQUENCES -- Alternatively: ON SEQUENCE seq1, seq2, seq3 ... IN SCHEMA public TO readonly_user; GRANT SELECT ON ALL TABLES -- Alternatively: ON TABLE table1, view1, table2 ... IN SCHEMA public TO readonly_user; Question is how to give this user opposite access? I mean give him access to all functionalities like inserting, deleting, creating tables and staff like this. I mean i want to assign user "jaryszek" to this read_only role and after changing schema i want to give user "jaryszek" all credentials. Best, Jacek You can change your readonly_user to NOINHERIT and GRANT the role to jaryszek. When you then want to act as readonly_user you set the role explicitly. Here basically: Revoke create from public, so that only granted users will be able to create or drop objects. REVOKE CREATE ON SCHEMA PUBLIC FROM public; Create the role as group (nologin) and without implicit inheritance of privileges. CREATE ROLE readonly_user NOINHERIT NOLOGIN; Your normal user should be able to create tables. GRANT CREATE ON SCHEMA PUBLIC TO jaryszek; Add your user to the readonly_user group. GRANT readonly_user TO jaryszek; Now when you log in as jaryszek you can create table add data, etc. jaryszek@db.localhost=> SELECT SESSION_USER, CURRENT_USER; session_user | current_user --------------+-------------- jaryszek | jaryszek jaryszek@db.localhost=> CREATE TABLE public.test (a INTEGER); CREATE TABLE jaryszek@db.localhost=> INSERT INTO public.test VALUES (1); INSERT 0 1 jaryszek@db.localhost=> SELECT * FROM public.test; a --- 1 (1 row) Now let’s set up the permissions of readonly_user. GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO readonly_user; When you want to act as readonly_user you set explicitly that role. jaryszek@db.localhost=> SET ROLE readonly_user ; SET jaryszek@db.localhost=> SELECT SESSION_USER, CURRENT_USER; session_user | current_user --------------+--------------- jaryszek | readonly_user (1 row) After this all privileges will be checked against readonly_user. That means: You can read from tables, but you cannot modify data or change/create tables. jaryszek@db.localhost=> SELECT * FROM public.test; a --- 1 (1 row) jaryszek@db.localhost=> INSERT INTO public.test VALUES (2); ERROR: permission denied for relation test jaryszek@db.localhost=> CREATE TABLE public.test2 (a INTEGER); ERROR: permission denied for schema public LINE 1: CREATE TABLE public.test2 (a INTEGER); When you want to get back to your normal role then use jaryszek@db.localhost=> RESET ROLE; RESET jaryszek@db.localhost=> INSERT INTO public.test VALUES (2); INSERT 0 1 The idea is to put all permissions in (group) roles and then impersonate the role that you need setting it explicitly. I hope this helps. Bye Charles pt., 13 lip 2018 o 12:58 Łukasz Jarych <jarys...@gmail.com <mailto:jarys...@gmail.com> > napisał(a): Maybe read-only view? Best, Jacek pt., 13 lip 2018 o 07:00 Łukasz Jarych <jarys...@gmail.com <mailto:jarys...@gmail.com> > napisał(a): Hi Guys, Yesterday i tried all day to figure out system to read only schemas. I want to : 1. Create user who can login (user: jaryszek) 2. Create role who can read only data (only watching tables) (role: readonly) 3, Create role who can read all data (inserting, deleting, altering, dropping) (role: readall) What sqls should i use for this? What grants should i add? And now i am logged as jaryszek I want to grant myself role read only to schema public (when owner is postgres). I want to review tables as views only, After work i want to grant myself role readall to schema public. It is possible? Or possible workaround ? Best, Jacek
