Знання

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

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

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

Розбіжності

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

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

Порівняння попередніх версій Попередня ревізія
Наступна ревізія 		Попередня ревізія
programming:databases:postgresql:roles_example [2020/07/29 15:26]
charon 		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 =====
Рядок 28: Рядок 29:
 GRANT CONNECT ON DATABASE postgres TO experiment_admin, experiment_read_write, experiment_read_only; GRANT CONNECT ON DATABASE postgres TO experiment_admin, experiment_read_write, experiment_read_only;
 </code>  </code> 
 +
 +----
  
 ===== Create new scheme 'myschema' and grant permissions: REPEAT FOR EVERY SCHEMA ===== ===== Create new scheme 'myschema' and grant permissions: REPEAT FOR EVERY SCHEMA =====
Рядок 36: Рядок 39:
 </code> </code>
  
-Grant access to the schema+==== Grant access to the schema ====
 <code postgresql> <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;
Рядок 42: Рядок 45:
 </code> </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> <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;
Рядок 51: Рядок 63:
 </code> </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 ====
 <code postgresql> <code postgresql>
-ALTER DEFAULT PRIVILEGES FOR ROLE 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_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 SELECT ON TABLES TO experiment_read_only; +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 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 ALL PRIVILEGES ON SEQUENCES TO experiment_read_write, experiment_admin; 
-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 USAGE, SELECT ON SEQUENCES TO experiment_read_only; 
-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 ALL PRIVILEGES ON ROUTINES TO experiment_read_write, experiment_admin;
 </code> </code>
  
-How to create new users+<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>
  
-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.+
  
 +===== How to create new users =====
  
-Basic setup+==== 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 ===
 <code postgresql> <code postgresql>
 CREATE ROLE reader1 WITH LOGIN PASSWORD 'mypass1'; CREATE ROLE reader1 WITH LOGIN PASSWORD 'mypass1';
Рядок 73: Рядок 90:
 </code> </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> <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;
Рядок 82: Рядок 99:
 </code> </code>
  
-Cleanup+=== Cleanup ===
 <code postgresql> <code postgresql>
 REVOKE reader1 FROM CURRENT_USER; REVOKE reader1 FROM CURRENT_USER;
 </code> </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> <code postgresql>
 CREATE ROLE writer1 WITH LOGIN PASSWORD 'mypass2'; CREATE ROLE writer1 WITH LOGIN PASSWORD 'mypass2';
Рядок 99: Рядок 119:
 </code> </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> <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;
Рядок 108: Рядок 128:
 </code> </code>
  
-Cleanup+=== Cleanup ===
 <code postgresql> <code postgresql>
 REVOKE writer1 FROM CURRENT_USER; REVOKE writer1 FROM CURRENT_USER;
 </code> </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> <code postgresql>
 CREATE ROLE admin1 WITH LOGIN PASSWORD 'mypass3'; CREATE ROLE admin1 WITH LOGIN PASSWORD 'mypass3';
Рядок 126: Рядок 145:
 </code> </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> <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;
Рядок 135: Рядок 154:
 </code> </code>
  
-Cleanup+=== Cleanup ===
 <code postgresql> <code postgresql>
 REVOKE admin1 FROM CURRENT_USER; REVOKE admin1 FROM CURRENT_USER;
 </code> </code>
programming/databases/postgresql/roles_example.1596025608.txt.gz · Востаннє змінено: 2020/07/29 15:26 повз charon

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

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