Ö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:38] – riba.zoltan
+++ postgresql:szerepkorok [2026/04/21 01:02] (aktuális) – riba.zoltan
@@ Sor 70: / Sor 70: @@
 A ''CREATE USER name'' megegyezik a ''CREATE ROLE name LOGIN'' paranccsal, azzal a különbséggel, hogy automatikusan tartalmazza a LOGIN attribútumot.
+<code>
+[postgres@gepnev ~]$ psql -c "CREATE ROLE test LOGIN PASSWORD 'test'"
+CREATE ROLE
+[postgres@gepnev ~]$ psql -c '\du'
+                             List of roles
+ Role name |                         Attributes
+-----------+------------------------------------------------------------
+ postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
+ test      |
+[postgres@gepnev ~]$ psql -c "SELECT * FROM pg_roles WHERE rolname = 'test'"
+ rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig |  oid
+---------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
+ test    | f        | t          | f             | f           | t           | f              |           -1 | ********    |               | f            |           | 16390
+(1 row)
+[postgres@gepnev data]$ cat ${PGDATA}/pg_hba.conf
+# TYPE DATABASE USER ADDRESS METHOD
+local all all trust
+host all all 127.0.0.1/32 trust
+host all all ::1/128 trust
+local replication all peer
+host replication all 127.0.0.1/32 ident
+host replication all ::1/128 ident
+[postgres@gepnev ~]$ psql -U test -d postgres -c "SELECT 'z' < 'á'"
+ ?column?
+----------
+ f
+(1 row)
+[postgres@gepnev data]$ cat ${PGDATA}/pg_hba.conf
+# TYPE DATABASE USER ADDRESS METHOD
+local all test md5
+local all all trust
+host all all 127.0.0.1/32 trust
+host all all ::1/128 trust
+local replication all peer
+host replication all 127.0.0.1/32 ident
+host replication all ::1/128 ident
+[postgres@gepnev data]$ pg_ctl reload
+server signaled
+[postgres@gepnev data]$ psql -U test -d postgres -c "SELECT 'z' < 'á'"
+Password for user test: xxx
+psql: error: connection to server on socket "/run/postgresql/.s.PGSQL.5432" failed: FATAL:  password authentication failed for user "test"
+[postgres@gepnev data]$ psql -U test -d postgres -c "SELECT 'z' < 'á'"
+Password for user test:
+ ?column?
+----------
+ f
+(1 row)
+[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>