GRANT CREATE ON SCHEMA PUBLIC TO jaryszek;
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;
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
