Sxemalar, rollar va RLS

Bitta bazada yuzlab jadval bo'lishi mumkin. Ularni tartibga solish uchun sxema, kim nimani ko'rishini boshqarish uchun esa rol va huquq bor.

DROP ROLE IF EXISTS oquvchi_rol;
DROP ROLE IF EXISTS yozuvchi_rol;
DROP ROLE IF EXISTS malika_rol;

CREATE ROLE oquvchi_rol;
CREATE ROLE yozuvchi_rol;
CREATE ROLE malika_rol LOGIN;

CREATE SCHEMA savdo;
CREATE SCHEMA hisobot;

CREATE TABLE savdo.buyurtma (
    id       int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sotuvchi text NOT NULL,
    summa    numeric(10,2) NOT NULL
);

INSERT INTO savdo.buyurtma (sotuvchi, summa) VALUES
    ('malika_rol',  250000.00),
    ('malika_rol',  180000.00),
    ('nodira_rol',  320000.00),
    ('aziza_rol',   145000.00);

Sxema nima #

Sxema - bu jadvallar uchun papka. Har bir jadvalning to'liq nomi uchta qismdan iborat:

baza  .  sxema  .  jadval
------   -------   -------
dokon   .  savdo  .  buyurtma

Bazadagi sxemalarni ko'rish:

SELECT schema_name
FROM information_schema.schemata
WHERE schema_name NOT LIKE 'pg\_%'
ORDER BY schema_name;

    schema_name
--------------------
 hisobot
 information_schema
 public
 savdo
(4 rows)

search_path #

Har safar savdo.buyurtma deb yozish noqulay. search_path sxemalarni qaysi tartibda qidirishni belgilaydi:

SHOW search_path;

SET search_path TO savdo, public;

SELECT sotuvchi, summa FROM buyurtma ORDER BY id LIMIT 2;

   search_path
-----------------
 "$user", public
(1 row)

SET
  sotuvchi  |   summa
------------+-----------
 malika_rol | 250000.00
 malika_rol | 180000.00
(2 rows)

SET search_path dan keyin buyurtma deb yozish yetarli bo'ldi.

Rollar #

PostgreSQL da "foydalanuvchi" va "guruh" alohida tushuncha emas - ikkalasi ham rol. Farq bitta xususiyatda:

SELECT rolname, rolcanlogin AS kira_oladi, rolsuper AS super
FROM pg_roles
WHERE rolname LIKE '%\_rol'
ORDER BY rolname;

   rolname    | kira_oladi | super
--------------+------------+-------
 malika_rol   | t          | f
 oquvchi_rol  | f          | f
 yozuvchi_rol | f          | f
(3 rows)

GRANT va REVOKE #

Huquq berish va olib tashlash:

GRANT USAGE ON SCHEMA savdo TO oquvchi_rol;
GRANT SELECT ON savdo.buyurtma TO oquvchi_rol;

GRANT oquvchi_rol TO yozuvchi_rol;
GRANT INSERT, UPDATE ON savdo.buyurtma TO yozuvchi_rol;

SELECT grantee, privilege_type
FROM information_schema.table_privileges
WHERE table_name = 'buyurtma' AND grantee LIKE '%\_rol'
ORDER BY grantee, privilege_type;

GRANT
GRANT
GRANT ROLE
GRANT
   grantee    | privilege_type
--------------+----------------
 oquvchi_rol  | SELECT
 yozuvchi_rol | INSERT
 yozuvchi_rol | UPDATE
(3 rows)

GRANT oquvchi_rol TO yozuvchi_rol qatori muhim: u yozuvchi_rol ga oquvchi_rol ning hamma huquqini meros qilib beradi.

malika_rol  ->  yozuvchi_rol  ->  oquvchi_rol
(odam)          (guruh)           (guruh)

Huquqni olib tashlash:

GRANT SELECT, INSERT ON savdo.buyurtma TO malika_rol;

REVOKE INSERT ON savdo.buyurtma FROM malika_rol;

SELECT privilege_type
FROM information_schema.table_privileges
WHERE table_name = 'buyurtma' AND grantee = 'malika_rol'
ORDER BY privilege_type;

GRANT
REVOKE
 privilege_type
----------------
 SELECT
(1 row)

Kelajakdagi jadvallar #

Klassik muammo: bugun GRANT qildingiz, ertaga yangi jadval yaratildi - va u yangi jadvalda huquq yo'q.

Yechim - ALTER DEFAULT PRIVILEGES:

ALTER DEFAULT PRIVILEGES IN SCHEMA savdo
    GRANT SELECT ON TABLES TO oquvchi_rol;

CREATE TABLE savdo.tolov (
    id    int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    summa numeric(10,2) NOT NULL
);

SELECT grantee, privilege_type
FROM information_schema.table_privileges
WHERE table_name = 'tolov' AND grantee LIKE '%\_rol'
ORDER BY grantee;

ALTER DEFAULT PRIVILEGES
CREATE TABLE
   grantee   | privilege_type
-------------+----------------
 oquvchi_rol | SELECT
(1 row)

Jadval GRANT dan keyin yaratilgan bo'lsa ham, oquvchi_rol unga SELECT huquqiga ega.

RLS - qator darajasidagi xavfsizlik #

GRANT butun jadvalga ruxsat beradi. Ba'zan esa foydalanuvchi faqat o'z qatorlarini ko'rishi kerak.

Bunday holatda RLS (Row Level Security) ishlatiladi:

GRANT USAGE ON SCHEMA savdo TO malika_rol;
GRANT SELECT ON savdo.buyurtma TO malika_rol;

ALTER TABLE savdo.buyurtma ENABLE ROW LEVEL SECURITY;

CREATE POLICY oz_buyurtmasi ON savdo.buyurtma
    FOR SELECT
    USING (sotuvchi = current_user);

SET ROLE malika_rol;

SELECT sotuvchi, summa FROM savdo.buyurtma ORDER BY id;

RESET ROLE;

GRANT
GRANT
ALTER TABLE
CREATE POLICY
SET
  sotuvchi  |   summa
------------+-----------
 malika_rol | 250000.00
 malika_rol | 180000.00
(2 rows)

RESET

Jadvalda to'rtta qator bor edi, lekin malika_rol faqat ikkitasini ko'rdi - qolganlari boshqa sotuvchilarniki.

USING shartini baza har bir SELECT ga avtomatik qo'shadi. Foydalanuvchi buni chetlab o'tolmaydi - WHERE yozmasa ham, shart baribir qo'llanadi.

Egasi esa hammasini ko'radi:

ALTER TABLE savdo.buyurtma ENABLE ROW LEVEL SECURITY;

CREATE POLICY oz_buyurtmasi ON savdo.buyurtma
    FOR SELECT USING (sotuvchi = current_user);

SELECT count(*) AS egasi_koradigan FROM savdo.buyurtma;

ALTER TABLE
CREATE POLICY
 egasi_koradigan
-----------------
               4
(1 row)

ALTER TABLE savdo.buyurtma FORCE ROW LEVEL SECURITY;

Siyosat turlari #

WITH CHECK bo'lmasa, foydalanuvchi o'z qatorini boshqa odam nomiga o'tkazib yuborishi mumkin - shuning uchun UPDATE siyosatida ikkalasi ham kerak.

Eng kam huquq qoidasi #

Dastur superuser bilan ulansa, dasturdagi bitta SQL in'ektsiya butun klasterni qo'lga olish imkonini beradi. Oddiy rol bilan esa zarar faqat shu rolning huquqi bilan chegaralanadi.

Xulosa #

• Sxema - jadvallar uchun papka; to'liq nom sxema.jadval ko'rinishida.
• Ikki sxemada bir xil nomli jadval bo'lishi mumkin.
• search_path qidiruv tartibini belgilaydi - qo'lda ishlashda qulay.
• Migratsiya va funksiyalarda har doim to'liq nom yozing.
• PostgreSQL da foydalanuvchi va guruh - ikkalasi ham rol; farq LOGIN da.
• Huquqni odamga emas, guruh rolga bering.
• GRANT guruh TO odam huquqlarni meros qilib beradi.
• ALTER DEFAULT PRIVILEGES kelajakda yaratiladigan jadvallarni qamrab oladi.
• RLS qator darajasida filtr qo'yadi va uni chetlab o'tib bo'lmaydi.
• Lekin jadval egasi va ayniqsa superuser RLS dan ozod - dastur superuser bilan ulanmasin.

Keyingi bo'limda PL/pgSQL funksiyalari va triggerlarni ko'ramiz.