Re: Read only to schema

[Łukasz Jarych <jaryszek@xxxxxxxxx>]

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 

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