RE: Read only to schema

"Charles Clavadetscher" <clavadetscher@xxxxxxxxxxxx> · Sat, 14 Jul 2018 12:31:36 +0200

Hello Jacek

From: Łukasz Jarych [mailto:jaryszek@xxxxxxxxx] 
Sent: Samstag, 14. Juli 2018 11:55
To: clavadetscher@xxxxxxxxxxxx; pgsql-general@xxxxxxxxxxxxxx >> PG-General Mailing List <pgsql-general@xxxxxxxxxxxxxx>
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 <clavadetscher@xxxxxxxxxxxx> napisał(a):
Hello

From: Łukasz Jarych [mailto:jaryszek@xxxxxxxxx] 
Sent: Freitag, 13. Juli 2018 16:39
To: pgsql-general@xxxxxxxxxxxxxx >> PG-General Mailing List <pgsql-general@xxxxxxxxxxxxxx>
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 <jaryszek@xxxxxxxxx> napisał(a):
Maybe read-only view?

Best,
Jacek 

pt., 13 lip 2018 o 07:00 Łukasz Jarych <jaryszek@xxxxxxxxx> 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