RE: Read only to schema

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