Hi Guys,
Yesterday i tried all day to figure out system to read only schemas.
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;
\password jaryszek
2. Create role who can read only data (only watching tables) (role: readonly)
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) (role: readall)
CREATE ROLE candoall LOGIN;
\password candoall
GRANT USAGE, CREATE ON SCHEMA xyz;
GRANT SELECT, INSERT, UPDATE DELETE ON ALL TABLES IN SCHEMA xyz TO candoall;
For schema:
USAGE: allows users to see or modify contents of tables in the schema
CREATE: allows users to create new objects in the schema
For tables:
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 privileges.
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).
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 them:
https://www.postgresql.org/docs/current/static/sql-alterdefaultprivileges.html
Regards
Charles
I want to review tables as views only,
After work i want to grant myself role readall to schema public.