Reverse-Logic wiki

Felhasználói eszközök

Eszközök a webhelyen

Nyomvonal:
postgresql:szerepkorok

Összehasonlítás

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

Összehasonlító nézet hivatkozása

Következő változat		Előző változat
postgresql:szerepkorok [2026/04/20 23:11] – létrehozva riba.zoltanpostgresql:szerepkorok [2026/04/21 01:02] (aktuális) – riba.zoltan
Sor 10: Sor 10:
 ===== Gyakorlati példák ===== ===== Gyakorlati példák =====
  
 +Egyszerű „felhasználó” szerepkör létrehozása. Felhasználónak az a szerepkör tekinthető, amelynek van ''LOGIN'' tulajdonsága. A szerepkör létrehozása többféle módszerrel történhet.
 +
 +<code>
 +[root@gepnev ~]# su - postgres
 +
 +[postgres@gepnev ~]$ psql -c '\du'
 +                             List of roles
 + Role name |                         Attributes                         
 +-----------+------------------------------------------------------------
 + postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 +
 +
 +[postgres@gepnev ~]$ createuser test
 +
 +[postgres@gepnev ~]$ psql -c '\du'
 +                             List of roles
 + Role name |                         Attributes                         
 +-----------+------------------------------------------------------------
 + postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 + test      | 
 +
 +[postgres@gepnev ~]$ dropuser test
 +
 +[postgres@gepnev ~]$ psql -c '\du'
 +                             List of roles
 + Role name |                         Attributes                         
 +-----------+------------------------------------------------------------
 + postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 +</code>
 +
 +Másik módszerrel ugyanez
 +
 +<code>
 +[root@gepnev ~]# su - postgres
 +
 +[postgres@gepnev ~]$ psql -c '\du'
 +                             List of roles
 + Role name |                         Attributes                         
 +-----------+------------------------------------------------------------
 + postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 +
 +[postgres@gepnev ~]$ psql -c 'CREATE ROLE test LOGIN'
 +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 'DROP ROLE test'
 +DROP ROLE
 +[postgres@gepnev ~]$ psql -c '\du'
 +                             List of roles
 + Role name |                         Attributes                         
 +-----------+------------------------------------------------------------
 + postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
 +</code>
 +
 +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>
  
postgresql/szerepkorok.1776726674.txt.gz · legutóbbi módosítás: – Szerkesztő: riba.zoltan