From: Łukasz Jarych [mailto:jarys...@gmail.com]
Sent: Freitag, 13. Juli 2018 07:00
To: pgsql-gene...@postgresql.org >> PG-General Mailing List
Subject: Read only to schema
Yesterday i tried all day to figure out system to read only schemas.
I want to :
Here is a try. I did not test it all, but it should go in this direction.
1. Create user who can login (user: jaryszek)
CREATE ROLE jaryszek LOGIN;
2. Create role who can read only data (only watching tables) (role:
Suppose you have a schema xyz
GRANT USAGE ON SCHEMA xyz;
GRANT SELECT ON ALL TABLES IN SCHEMA xyz TO jaryzsek;
3, Create role who can read all data (inserting, deleting, altering, dropping)
CREATE ROLE candoall LOGIN;
GRANT USAGE, CREATE ON SCHEMA xyz;
GRANT SELECT, INSERT, UPDATE DELETE ON ALL TABLES IN SCHEMA xyz TO candoall;
USAGE: allows users to see or modify contents of tables in the schema
CREATE: allows users to create new objects in the schema
SELECT without other privileges: user can only read from tables
INSERT, UPDATE, DELETE: Well, that shoud be obvious
If you have other objects (sequences, functions, etc) you may need to add other
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
Schema public has a default privilege setting that grants basically everything
to everybody (public). So you must first revoke all those privileges from publc.
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO jaryszek;
You may want to change the default privilege settings for schema public. It
would help you to read about which default privileges are set and how to change
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 ?