Thank you very much Charles! Awesome knowledge, thank you!
I will test it and let you know if it is working like a charm (i am on vacation now and without access to postgresql). I am wondering with one thing: GRANT CREATE ON SCHEMA PUBLIC TO jaryszek; This will allow to create, drop, isnert and delete? All ddl and dml commands? Or should i use GRANT ALL ON SCHEMA PUBLIC TO jaryszek ? Best, Jacek sob., 14 lip 2018 o 08:23 Charles Clavadetscher <clavadetsc...@swisspug.org> napisał(a): > 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> napisał(a): > > Maybe read-only view? > > > > Best, > > Jacek > > > > pt., 13 lip 2018 o 07:00 Łukasz Jarych <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 > >
