Розбіжності

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

--- programming:databases:postgresql:roles_example [2020/07/29 15:21]
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>
+----
 ===== Create basic roles: DO IT ONCE =====
-  CREATE ROLE experiment_read_write NOLOGIN;
+<code postgresql>
-  CREATE ROLE experiment_read_only NOLOGIN;
+CREATE ROLE experiment_read_write NOLOGIN;
-  CREATE ROLE experiment_admin NOLOGIN;
+CREATE ROLE experiment_read_only NOLOGIN;
+CREATE ROLE experiment_admin NOLOGIN;
+</code>
 ===== 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;
+</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 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 SEQUENCES IN SCHEMA myschema TO experiment_admin, experiment_read_write;
@@ Рядок 42: / Рядок 61: @@
 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;
+</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';
 GRANT experiment_read_only TO reader1;
 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 SELECT ON TABLES TO experiment_read_only;
@@ Рядок 69: / Рядок 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 ALL PRIVILEGES ON ROUTINES TO experiment_read_write, experiment_admin;
+</code>
-Cleanup
+=== Cleanup ===
+<code postgresql>
 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).
-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';
 GRANT experiment_read_write TO writer1;
 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 SELECT ON TABLES TO experiment_read_only;
@@ Рядок 92: / Рядок 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 ALL PRIVILEGES ON ROUTINES TO experiment_read_write, experiment_admin;
+</code>
-Cleanup
+=== Cleanup ===
+<code postgresql>
 REVOKE writer1 FROM CURRENT_USER;
+</code>
-New admin user
+==== New admin user ====
 This user can create new objects in schema
-Basic setup
+=== Basic setup ===
+<code postgresql>
 CREATE ROLE admin1 WITH LOGIN PASSWORD 'mypass3';
 GRANT experiment_admin TO admin1;
 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 SELECT ON TABLES TO experiment_read_only;
@@ Рядок 113: / Рядок 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 ALL PRIVILEGES ON ROUTINES TO experiment_read_write, experiment_admin;
+</code>
-Cleanup
+=== Cleanup ===
+<code postgresql>
 REVOKE admin1 FROM CURRENT_USER;
+</code>

Знання

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

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

Розбіжності

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