RE: Read only to schema

"Charles Clavadetscher" <clavadetscher@xxxxxxxxxxxx> · Sat, 14 Jul 2018 08:37:43 +0200

An addition to my previous post (marked as [addition]).

From: Charles Clavadetscher [mailto:clavadetscher@xxxxxxxxxxxx] 
Sent: Samstag, 14. Juli 2018 08:23
To: 'Łukasz Jarych' <jaryszek@xxxxxxxxx>; pgsql-general@xxxxxxxxxxxxxx
Subject: RE: Read only to schema

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;

[addition]
If you want that privileges are granted automatically then you can set up default privileges:

charles@db.localhost=# ALTER DEFAULT PRIVILEGES FOR ROLE jaryszek IN SCHEMA public GRANT SELECT ON TABLES TO public;
ALTER DEFAULT PRIVILEGES
charles@db.localhost=# \ddp
           Default access privileges
  Owner   | Schema | Type  | Access privileges
----------+--------+-------+-------------------
 jaryszek | public | table | =r/jaryszek
(1 row)

You are now connected to database "db" as user "jaryszek".
jaryszek@db.localhost=> CREATE TABLE public.test2 (a INTEGER);
CREATE TABLE
jaryszek@db.localhost=> INSERT INTO public.test2 VALUES (34);
INSERT 0 1

Through the default privilege setting everybody, including your readonly_user can read from the table.
If you prefer you may restrict the default setting only to your read user.

jaryszek@db.localhost=> SET ROLE readonly_user ;
SET
jaryszek@db.localhost=> SELECT * FROM public.test2;
 a
----
 34
(1 row)

Regards
Charles
[end of addition]

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