Знання

Користувальницькькі налаштування

Налаштування сайту

Ви тут: start » programming » databases » postgresql » roles_example
Відвідано: • roles_example
programming:databases:postgresql:roles_example

Розбіжності

Тут показані розбіжності між вибраною ревізією та поточною версією сторінки.

Посилання на цей список змін

Порівняння попередніх версій Попередня ревізія
Наступна ревізія 		Попередня ревізія
programming:databases:postgresql:roles_example [2020/07/29 15:22]
charon [Create basic roles: DO IT ONCE] 		programming:databases:postgresql:roles_example [2020/07/29 15:45] (поточний)
charon [Grant usage for future tables, sequences, routines in schema for basic roles]
Рядок 16: Рядок 16:
 </WRAP> </WRAP>
  
 +----
  
 ===== Create basic roles: DO IT ONCE ===== ===== Create basic roles: DO IT ONCE =====
-<code sql>+<code postgresql>
 CREATE ROLE experiment_read_write NOLOGIN; CREATE ROLE experiment_read_write NOLOGIN;
 CREATE ROLE experiment_read_only NOLOGIN; CREATE ROLE experiment_read_only NOLOGIN;
 CREATE ROLE experiment_admin NOLOGIN; CREATE ROLE experiment_admin NOLOGIN;
 </code> </code>
 +
 ===== Grant access to database 'postgres': REPEAT FOR EVERY DATABASE ===== ===== Grant access to database 'postgres': REPEAT FOR EVERY DATABASE =====
-  GRANT CONNECT ON DATABASE postgres TO experiment_admin, experiment_read_write, experiment_read_only; +<code postgresql> 
- +GRANT CONNECT ON DATABASE postgres TO experiment_admin, experiment_read_write, experiment_read_only; 
 +</code> 
  
 +----
  
-Create new scheme 'myschema' and grant permissions: REPEAT FOR EVERY SCHEMA+===== Create new scheme 'myschema' and grant permissions: REPEAT FOR EVERY SCHEMA =====
  
-Create new schema+==== Create new schema ==== 
 +<code postgresql>
 CREATE SCHEMA myschema; CREATE SCHEMA myschema;
 +</code>
  
-Grant access to the schema+==== Grant access to the schema ==== 
 +<code postgresql>
 GRANT USAGE ON SCHEMA myschema TO experiment_read_write, experiment_read_only; GRANT USAGE ON SCHEMA myschema TO experiment_read_write, experiment_read_only;
 GRANT ALL PRIVILEGES ON SCHEMA myschema TO experiment_admin; GRANT ALL PRIVILEGES ON SCHEMA myschema TO experiment_admin;
 +</code>
  
-Grant usage of existing tables, sequences, routines in schema for basic roles+All next commands in this section require that current user can administer roles //experiment_admin//, //experiment_read_write//, //experiment_read_only//. To achieve it, use commands: 
 +<code postgresql> 
 +GRANT experiment_admin, experiment_read_write, experiment_read_only TO CURRENT_USER WITH ADMIN OPTION; 
 +</code> 
 +and 
 +<code postgresql> 
 +REVOKE experiment_admin, experiment_read_write, experiment_read_only FROM CURRENT_USER; 
 +</code> 
 + 
 +==== Grant usage of existing tables, sequences, routines in schema for basic roles ==== 
 +<code postgresql>
 GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA myschema TO experiment_admin, experiment_read_write; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA myschema TO experiment_admin, experiment_read_write;
 GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA myschema TO experiment_admin, experiment_read_write; GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA myschema TO experiment_admin, experiment_read_write;
Рядок 43: Рядок 61:
 GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO experiment_read_only; GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO experiment_read_only;
 GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA myschema TO experiment_read_only; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA myschema TO experiment_read_only;
 +</code>
  
-Grant usage for future tables, sequences, routines in schema for basic roles +==== Grant usage for future tables, sequences, routines in schema for basic roles ==== 
-ALTER DEFAULT PRIVILEGES FOR ROLE experiment_read_write, experiment_admin IN SCHEMA myschema GRANT ALL PRIVILEGES ON TABLES TO experiment_read_write, experiment_admin; +<code postgresql> 
-ALTER DEFAULT PRIVILEGES FOR ROLE experiment_read_write, experiment_admin IN SCHEMA myschema GRANT SELECT ON TABLES TO experiment_read_only; +ALTER DEFAULT PRIVILEGES FOR ROLE experiment_read_only, experiment_read_write, experiment_admin IN SCHEMA myschema GRANT ALL PRIVILEGES ON TABLES TO experiment_read_write, experiment_admin; 
-ALTER DEFAULT PRIVILEGES FOR ROLE experiment_read_write, experiment_admin IN SCHEMA myschema GRANT ALL PRIVILEGES ON SEQUENCES TO experiment_read_write, experiment_admin; +ALTER DEFAULT PRIVILEGES FOR ROLE experiment_read_only, experiment_read_write, experiment_admin IN SCHEMA myschema GRANT SELECT ON TABLES TO experiment_read_only; 
-ALTER DEFAULT PRIVILEGES FOR ROLE experiment_read_write, experiment_admin IN SCHEMA myschema GRANT USAGE, SELECT ON SEQUENCES TO experiment_read_only; +ALTER DEFAULT PRIVILEGES FOR ROLE experiment_read_only, experiment_read_write, experiment_admin IN SCHEMA myschema GRANT ALL PRIVILEGES ON SEQUENCES TO experiment_read_write, experiment_admin; 
-ALTER DEFAULT PRIVILEGES FOR ROLE experiment_read_write, experiment_admin IN SCHEMA myschema GRANT ALL PRIVILEGES ON ROUTINES TO experiment_read_write, experiment_admin; +ALTER DEFAULT PRIVILEGES FOR ROLE experiment_read_only, experiment_read_write, experiment_admin IN SCHEMA myschema GRANT USAGE, SELECT ON SEQUENCES TO experiment_read_only; 
- +ALTER DEFAULT PRIVILEGES FOR ROLE experiment_read_only, experiment_read_write, experiment_admin IN SCHEMA myschema GRANT ALL PRIVILEGES ON ROUTINES TO experiment_read_write, experiment_admin; 
 +</code>
  
 +<WRAP center round info 60%>
 +These rules above will work only if user switches role (//SET ROLE experiment_admin//), otherwise DEFAULT PRIVILEGES for **user** will be used. DEFAULT PRIVILEGES are not inherited, unlike other permissions.
 +</WRAP>
  
-How to create new users+----
  
-New read-only user +===== How to create new users =====
-This user can only read existing objects. But in schema public it can also create and manage his own objects, like tables.+
  
 +==== New read-only user ====
 +This user can only read existing objects. But in schema public it can also create and manage his own objects, like tables.
  
-Basic setup+=== Basic setup === 
 +<code postgresql>
 CREATE ROLE reader1 WITH LOGIN PASSWORD 'mypass1'; CREATE ROLE reader1 WITH LOGIN PASSWORD 'mypass1';
 GRANT experiment_read_only TO reader1; GRANT experiment_read_only TO reader1;
 GRANT reader1 TO CURRENT_USER WITH ADMIN OPTION; GRANT reader1 TO CURRENT_USER WITH ADMIN OPTION;
 +</code>
  
-Grant usage for future tables, sequences, routines in schema: REPEAT FOR EVERY SCHEMA +=== Grant usage for future tables, sequences, routines in schema: REPEAT FOR EVERY SCHEMA === 
 +<code postgresql>
 ALTER DEFAULT PRIVILEGES FOR ROLE reader1 IN SCHEMA myschema GRANT ALL PRIVILEGES ON TABLES TO experiment_read_write, experiment_admin; ALTER DEFAULT PRIVILEGES FOR ROLE reader1 IN SCHEMA myschema GRANT ALL PRIVILEGES ON TABLES TO experiment_read_write, experiment_admin;
 ALTER DEFAULT PRIVILEGES FOR ROLE reader1 IN SCHEMA myschema GRANT SELECT ON TABLES TO experiment_read_only; ALTER DEFAULT PRIVILEGES FOR ROLE reader1 IN SCHEMA myschema GRANT SELECT ON TABLES TO experiment_read_only;
Рядок 70: Рядок 97:
 ALTER DEFAULT PRIVILEGES FOR ROLE reader1 IN SCHEMA myschema GRANT USAGE, SELECT ON SEQUENCES TO experiment_read_only; ALTER DEFAULT PRIVILEGES FOR ROLE reader1 IN SCHEMA myschema GRANT USAGE, SELECT ON SEQUENCES TO experiment_read_only;
 ALTER DEFAULT PRIVILEGES FOR ROLE reader1 IN SCHEMA myschema GRANT ALL PRIVILEGES ON ROUTINES TO experiment_read_write, experiment_admin; ALTER DEFAULT PRIVILEGES FOR ROLE reader1 IN SCHEMA myschema GRANT ALL PRIVILEGES ON ROUTINES TO experiment_read_write, experiment_admin;
 +</code>
  
-Cleanup+=== Cleanup === 
 +<code postgresql>
 REVOKE reader1 FROM CURRENT_USER; REVOKE reader1 FROM CURRENT_USER;
 +</code>
    
- +==== New read-write user ====
- +
-New read-write user+
 This user cannot create new objects in schema, like tables. It can read, write, delete and update existing objects (for example, modify rows in tables). This user cannot create new objects in schema, like tables. It can read, write, delete and update existing objects (for example, modify rows in tables).
  
-Exception: in schema public it can also create and manage his own objects, like tables.+<WRAP center round important 60%> 
 +Exception: in schema //public// it can also create and manage his own objects, like tables. 
 +</WRAP>
  
  
-Basic setup+=== Basic setup === 
 +<code postgresql>
 CREATE ROLE writer1 WITH LOGIN PASSWORD 'mypass2'; CREATE ROLE writer1 WITH LOGIN PASSWORD 'mypass2';
 GRANT experiment_read_write TO writer1; GRANT experiment_read_write TO writer1;
 GRANT writer1 TO CURRENT_USER WITH ADMIN OPTION; GRANT writer1 TO CURRENT_USER WITH ADMIN OPTION;
 +</code>
  
-Grant usage for future tables, sequences, routines in schema: REPEAT FOR EVERY SCHEMA +=== Grant usage for future tables, sequences, routines in schema: REPEAT FOR EVERY SCHEMA === 
 +<code postgresql>
 ALTER DEFAULT PRIVILEGES FOR ROLE writer1 IN SCHEMA myschema GRANT ALL PRIVILEGES ON TABLES TO experiment_read_write, experiment_admin; ALTER DEFAULT PRIVILEGES FOR ROLE writer1 IN SCHEMA myschema GRANT ALL PRIVILEGES ON TABLES TO experiment_read_write, experiment_admin;
 ALTER DEFAULT PRIVILEGES FOR ROLE writer1 IN SCHEMA myschema GRANT SELECT ON TABLES TO experiment_read_only; ALTER DEFAULT PRIVILEGES FOR ROLE writer1 IN SCHEMA myschema GRANT SELECT ON TABLES TO experiment_read_only;
Рядок 93: Рядок 126:
 ALTER DEFAULT PRIVILEGES FOR ROLE writer1 IN SCHEMA myschema GRANT USAGE, SELECT ON SEQUENCES TO experiment_read_only; ALTER DEFAULT PRIVILEGES FOR ROLE writer1 IN SCHEMA myschema GRANT USAGE, SELECT ON SEQUENCES TO experiment_read_only;
 ALTER DEFAULT PRIVILEGES FOR ROLE writer1 IN SCHEMA myschema GRANT ALL PRIVILEGES ON ROUTINES TO experiment_read_write, experiment_admin; ALTER DEFAULT PRIVILEGES FOR ROLE writer1 IN SCHEMA myschema GRANT ALL PRIVILEGES ON ROUTINES TO experiment_read_write, experiment_admin;
 +</code>
  
-Cleanup+=== Cleanup === 
 +<code postgresql>
 REVOKE writer1 FROM CURRENT_USER; REVOKE writer1 FROM CURRENT_USER;
- +</code>
  
  
-New admin user+==== New admin user ====
 This user can create new objects in schema This user can create new objects in schema
  
  
-Basic setup+=== Basic setup === 
 +<code postgresql>
 CREATE ROLE admin1 WITH LOGIN PASSWORD 'mypass3'; CREATE ROLE admin1 WITH LOGIN PASSWORD 'mypass3';
 GRANT experiment_admin TO admin1; GRANT experiment_admin TO admin1;
 GRANT admin1 TO CURRENT_USER WITH ADMIN OPTION; GRANT admin1 TO CURRENT_USER WITH ADMIN OPTION;
 +</code>
  
-Grant usage for future tables, sequences, routines in schema: REPEAT FOR EVERY SCHEMA+=== Grant usage for future tables, sequences, routines in schema: REPEAT FOR EVERY SCHEMA === 
 +<code postgresql>
 ALTER DEFAULT PRIVILEGES FOR ROLE admin1 IN SCHEMA myschema GRANT ALL PRIVILEGES ON TABLES TO experiment_read_write, experiment_admin; ALTER DEFAULT PRIVILEGES FOR ROLE admin1 IN SCHEMA myschema GRANT ALL PRIVILEGES ON TABLES TO experiment_read_write, experiment_admin;
 ALTER DEFAULT PRIVILEGES FOR ROLE admin1 IN SCHEMA myschema GRANT SELECT ON TABLES TO experiment_read_only; ALTER DEFAULT PRIVILEGES FOR ROLE admin1 IN SCHEMA myschema GRANT SELECT ON TABLES TO experiment_read_only;
Рядок 114: Рядок 152:
 ALTER DEFAULT PRIVILEGES FOR ROLE admin1 IN SCHEMA myschema GRANT USAGE, SELECT ON SEQUENCES TO experiment_read_only; ALTER DEFAULT PRIVILEGES FOR ROLE admin1 IN SCHEMA myschema GRANT USAGE, SELECT ON SEQUENCES TO experiment_read_only;
 ALTER DEFAULT PRIVILEGES FOR ROLE admin1 IN SCHEMA myschema GRANT ALL PRIVILEGES ON ROUTINES TO experiment_read_write, experiment_admin; ALTER DEFAULT PRIVILEGES FOR ROLE admin1 IN SCHEMA myschema GRANT ALL PRIVILEGES ON ROUTINES TO experiment_read_write, experiment_admin;
 +</code>
  
-Cleanup+=== Cleanup === 
 +<code postgresql>
 REVOKE admin1 FROM CURRENT_USER; REVOKE admin1 FROM CURRENT_USER;
 +</code>
programming/databases/postgresql/roles_example.1596025369.txt.gz · Востаннє змінено: 2020/07/29 15:22 повз charon

Налаштування сторінки

Якщо не вказано інше, вміст цієї Вікі підпадає під дію такої ліцензії: GNU Free Documentation License 1.3
GNU Free Documentation License 1.3 Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki