Hello From: Łukasz Jarych [mailto:jaryszek@xxxxxxxxx] Hi Guys, 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; \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. It is possible? Or possible workaround ? Best, Jacek |
