Összehasonlítás

A kiválasztott és az jelenlegi változat között az alábbi különbségek fordulnak elő:

--- postgresql:szerepkorok [2026/04/20 23:53] – riba.zoltan
+++ postgresql:szerepkorok [2026/04/21 01:02] (aktuális) – riba.zoltan
@@ Sor 131: / Sor 131: @@
 [postgres@gepnev data]$ psql -c 'DROP ROLE test'
 DROP ROLE
+[postgres@gepnev data]$ psql -c '\du'
+                             List of roles
+ Role name |                         Attributes
+-----------+------------------------------------------------------------
+ postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 </code>
+===== Szerepkör hierarchia =====
+==== Szerepkörök létrehozása ====
+<code>
+[postgres@gepnev data]$ psql -c '\du'
+                             List of roles
+ Role name |                         Attributes
+-----------+------------------------------------------------------------
+ postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
+[postgres@gepnev data]$ psql -c 'CREATE ROLE db_read'
+CREATE ROLE
+[postgres@gepnev data]$ psql -c 'CREATE ROLE db_write'
+CREATE ROLE
+[postgres@gepnev data]$ psql -c 'CREATE ROLE db_admin'
+CREATE ROLE
+[postgres@gepnev data]$ psql -c '\du'
+                             List of roles
+ Role name |                         Attributes
+-----------+------------------------------------------------------------
+ db_admin  | Cannot login
+ db_read   | Cannot login
+ db_write  | Cannot login
+ postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
+</code>
+==== Jogosultságok hozzáadása ====
+<code>
+[postgres@gepnev data]$ psql -c "GRANT SELECT ON ALL TABLES IN SCHEMA public TO db_read"
+GRANT
+[postgres@gepnev data]$ psql -c "GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO db_write"
+GRANT
+[postgres@gepnev data]$ psql -c "GRANT USAGE, CREATE ON SCHEMA public TO db_admin"
+GRANT
+[postgres@gepnev data]$ psql -c "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO db_admin"
+GRANT
+[postgres@gepnev data]$ psql -c "GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO db_read"
+GRANT
+[postgres@gepnev data]$ psql -c "GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO db_write"
+GRANT
+[postgres@gepnev data]$ psql -c "GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO db_admin"
+GRANT
+[postgres@gepnev data]$ psql -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO db_read"
+ALTER DEFAULT PRIVILEGES
+[postgres@gepnev data]$ psql -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT, UPDATE, DELETE ON TABLES TO db_write"
+ALTER DEFAULT PRIVILEGES
+[postgres@gepnev data]$ psql -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO db_admin"
+ALTER DEFAULT PRIVILEGES
+[postgres@gepnev data]$ psql -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO db_read"
+ALTER DEFAULT PRIVILEGES
+[postgres@gepnev data]$ psql -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO db_write"
+ALTER DEFAULT PRIVILEGES
+[postgres@gepnev data]$ psql -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO db_admin"
+ALTER DEFAULT PRIVILEGES
+</code>
+==== Szerepkörök egymásba ágyazása ====
+<code>
+[postgres@gepnev data]$ psql -c "GRANT db_read TO db_write"
+GRANT ROLE
+[postgres@gepnev data]$ psql -c "GRANT db_write TO db_admin"
+GRANT ROLE
+</code>
+==== Felhasználók szerepkörei ====
+<code>
+[postgres@gepnev data]$ psql -c "GRANT db_read TO db_write"
+GRANT ROLE
+[postgres@gepnev data]$ psql -c "GRANT db_write TO db_admin"
+GRANT ROLE
+[postgres@gepnev data]$ psql -c "CREATE ROLE admin LOGIN"
+CREATE ROLE
+[postgres@gepnev data]$ psql -c "GRANT db_admin TO admin"
+GRANT ROLE
+[postgres@gepnev data]$ psql -c "CREATE ROLE developer LOGIN"
+CREATE ROLE
+[postgres@gepnev data]$ psql -c "GRANT db_write TO developer"
+GRANT ROLE
+[postgres@gepnev data]$ psql -c "CREATE ROLE monitor LOGIN"
+CREATE ROLE
+[postgres@gepnev data]$ psql -c "GRANT db_read TO monitor"
+GRANT ROLE
+[postgres@note02 data]$ psql -c "\du"
+                             List of roles
+ Role name |                         Attributes
+-----------+------------------------------------------------------------
+ admin     |
+ db_admin  | Cannot login
+ db_read   | Cannot login
+ db_write  | Cannot login
+ developer |
+ monitor   |
+ postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
+</code>
+==== Teszteles ====
+<code>
+[postgres@gepnev data]$ psql -U admin -d postgres -c "\dt"
+Did not find any tables.
+[postgres@gepnev data]$ psql -U admin -d postgres -c "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT)"
+CREATE TABLE
+[postgres@gepnev data]$ psql -U admin -d postgres -c "\dt"
+         List of tables
+ Schema | Name  | Type  | Owner
+--------+-------+-------+-------
+ public | users | table | admin
+(1 row)
+[postgres@gepnev data]$ psql -U admin -d postgres -c "DROP TABLE users"
+DROP TABLE
+[postgres@gepnev data]$ psql -U admin -d postgres -c "\dt"
+Did not find any tables.
+[postgres@gepnev data]$ psql -U developer -d postgres -c "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT)"
+ERROR:  permission denied for schema public
+LINE 1: CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT)
+                     ^
+[postgres@gepnev data]$ psql -U monitor -d postgres -c "CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT)"
+ERROR:  permission denied for schema public
+LINE 1: CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT)
+                     ^
+</code>