Розбіжності

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

--- programming:databases:postgresql:roles_example [2020/07/29 15:31]
charon [New read-only user]
+++ 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>
+----
 ===== Create basic roles: DO IT ONCE =====
@@ Рядок 28: / Рядок 29: @@
 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 =====
@@ Рядок 40: / Рядок 43: @@
 GRANT USAGE ON SCHEMA myschema TO experiment_read_write, experiment_read_only;
 GRANT ALL PRIVILEGES ON SCHEMA myschema TO experiment_admin;
+</code>
+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>
@@ Рядок 53: / Рядок 65: @@
 ==== Grant usage for future tables, sequences, routines in schema for basic roles ====
 <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>
+<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 =====

Знання

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

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

Розбіжності

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