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 ?

Best,
Jacek

sob., 14 lip 2018 o 08:23 Charles Clavadetscher <clavadetscher(at)swisspug(dot)org>
napisał(a):

> Hello
>
>
>
> *From:* Łukasz Jarych [mailto:jaryszek(at)gmail(dot)com]
> *Sent:* Freitag, 13. Juli 2018 16:39
> *To:* pgsql-general(at)postgresql(dot)org >> PG-General Mailing List <
> pgsql-general(at)postgresql(dot)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(at)db(dot)localhost=> SELECT SESSION_USER, CURRENT_USER;
>
> session_user | current_user
>
> --------------+--------------
>
> jaryszek | jaryszek
>
>
>
> jaryszek(at)db(dot)localhost=> CREATE TABLE public.test (a INTEGER);
>
> CREATE TABLE
>
> jaryszek(at)db(dot)localhost=> INSERT INTO public.test VALUES (1);
>
> INSERT 0 1
>
> jaryszek(at)db(dot)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(at)db(dot)localhost=> SET ROLE readonly_user ;
>
> SET
>
> jaryszek(at)db(dot)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(at)db(dot)localhost=> SELECT * FROM public.test;
>
> a
>
> ---
>
> 1
>
> (1 row)
>
>
>
> jaryszek(at)db(dot)localhost=> INSERT INTO public.test VALUES (2);
>
> ERROR: permission denied for relation test
>
>
>
> jaryszek(at)db(dot)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(at)db(dot)localhost=> RESET ROLE;
>
> RESET
>
> jaryszek(at)db(dot)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 <jaryszek(at)gmail(dot)com> napisał(a):
>
> Maybe read-only view?
>
>
>
> Best,
>
> Jacek
>
>
>
> pt., 13 lip 2018 o 07:00 Łukasz Jarych <jaryszek(at)gmail(dot)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
>
>