Hi ! I have user :
[image: image.png] and this user can not login to Database AccessLog. I tried to use: GRANT CONNECT ON DATABASE " AccessLog" TO "Luke"; GRANT CREATE ON SCHEMA PUBLIC TO "Luke"; GRANT USAGE ON SCHEMA public TO "Luke" So expecting result : can modyfy DDL and DML in whole database but user it is not SUPERUSER. i tried to use: GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "Luke"; I have error: [image: image.png] Please help, How to create USER with all privilages (but no superuser) at once? And how to delete User - drop role "Luke" is saying that there are dependiences for this user... Please help, Jacek sob., 14 lip 2018 o 12:31 Charles Clavadetscher <clavadetsc...@swisspug.org> napisał(a): > Hello Jacek > > > > *From:* Łukasz Jarych [mailto:jarys...@gmail.com] > *Sent:* Samstag, 14. Juli 2018 11:55 > *To:* clavadetsc...@swisspug.org; pgsql-gene...@postgresql.org >> > PG-General Mailing List <pgsql-gene...@postgresql.org> > *Subject:* Re: Read only to schema > > > > 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 ? > > > > No. There are 2 privileges that you can grant on a schema: > > USAGE: Allows to use objects in that schema. > > CREATE: Allows to create and destroy objects in that schema. > > > > None of those says anything about which privileges users have within the > schema. You need to define additionally privileges (e.g. select, insert, > update, delete) on the objects within the schema. > > > > So in order to get access to a table public.test a user must have: > > > > USAGE on schema public AND SELECT (or whatever) on the table itself. If > any of those is missing the user will not be able to access the table. > > > > GRANT ALL is generally a bad idea, althought on schemas there is not much > you can do wrong. > > It doesn’t hurt if you add USAGE (which would be included in ALL along > CREATE) for user jaryszek, but it is not necessary, because public (= any > user) was only revoked CREATE (s. example in last mail). That means public > still has USAGE on schema public and obviously you are on one of “any user”. > > > > I hope I could explain that somehow. > > If you still have questions just get back on the list. > > > > Bye > > Charles > > > > 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 > >