Search Postgresql Archives

RE: Read only to schema

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux