RE: Read only to schema

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

From: Charles Clavadetscher [mailto:clavadetscher(at)swisspug(dot)org]
Sent: Samstag, 14. Juli 2018 08:23
To: 'Łukasz Jarych' <jaryszek(at)gmail(dot)com>; pgsql-general(at)postgresql(dot)org
Subject: RE: Read only to schema

Hello

From: Łukasz Jarych [mailto:jaryszek(at)gmail(dot)com]
Sent: Freitag, 13. Juli 2018 16:39
To: pgsql-general(at)postgresql(dot)org <mailto:pgsql-general(at)postgresql(dot)org> >> PG-General Mailing List <pgsql-general(at)postgresql(dot)org <mailto: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 <mailto:jaryszek(at)db(dot)localhost> => SELECT SESSION_USER, CURRENT_USER;

session_user | current_user

--------------+--------------

jaryszek | jaryszek

jaryszek(at)db(dot)localhost <mailto:jaryszek(at)db(dot)localhost> => CREATE TABLE public.test (a INTEGER);

CREATE TABLE

jaryszek(at)db(dot)localhost <mailto:jaryszek(at)db(dot)localhost> => INSERT INTO public.test VALUES (1);

INSERT 0 1

jaryszek(at)db(dot)localhost <mailto: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;

[addition]

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

charles(at)db(dot)localhost=# ALTER DEFAULT PRIVILEGES FOR ROLE jaryszek IN SCHEMA public GRANT SELECT ON TABLES TO public;

ALTER DEFAULT PRIVILEGES

charles(at)db(dot)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(at)db(dot)localhost=> CREATE TABLE public.test2 (a INTEGER);

CREATE TABLE

jaryszek(at)db(dot)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(at)db(dot)localhost=> SET ROLE readonly_user ;

SET

jaryszek(at)db(dot)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(at)db(dot)localhost <mailto:jaryszek(at)db(dot)localhost> => SET ROLE readonly_user ;

SET

jaryszek(at)db(dot)localhost <mailto: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 <mailto:jaryszek(at)db(dot)localhost> => SELECT * FROM public.test;

a

---

1

(1 row)

jaryszek(at)db(dot)localhost <mailto:jaryszek(at)db(dot)localhost> => INSERT INTO public.test VALUES (2);

ERROR: permission denied for relation test

jaryszek(at)db(dot)localhost <mailto: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 <mailto:jaryszek(at)db(dot)localhost> => RESET ROLE;

RESET

jaryszek(at)db(dot)localhost <mailto: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 <mailto: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 <mailto: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