User, Role, GRANT, REVOKE — huquqlarni boshqarish

~20 daq · PostgreSQL · Xavfsizlik va Boshqaruv

Loyihada bir nechta xizmat bor: API server, hisobot generatori, backup skript. Hammasi postgres superuser bilan ulanmoqda. API server buzilsa — butun database xavf ostida.

Principle of Least Privilege: har xizmat faqat kerakli huquqga ega bo'lishi kerak.

Role va User

PostgreSQL da user va role — bir narsa. CREATE USER = CREATE ROLE ... LOGIN.

-- Foydalanuvchi yaratish (login huquqi bilan)
CREATE USER api_server WITH PASSWORD 'kuchli_parol_123';

-- Role yaratish (login huquqisiz — guruh uchun)
CREATE ROLE o_quvchilar;
CREATE ROLE yozuvchilar;

GRANT — huquq berish

-- Jadvalga SELECT huquqi
GRANT SELECT ON foydalanuvchilar TO api_server;

-- Bir nechta amal
GRANT SELECT, INSERT, UPDATE ON buyurtmalar TO api_server;

-- Barcha jadvallar
GRANT SELECT ON ALL TABLES IN SCHEMA public TO hisobot_xizmati;

-- Schema ga
GRANT USAGE ON SCHEMA public TO api_server;

REVOKE — huquq olish

REVOKE DELETE ON buyurtmalar FROM api_server;
REVOKE ALL ON foydalanuvchilar FROM api_server;

Role orqali guruh huquqlari

-- Role yaratish
CREATE ROLE o_quvchilar;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO o_quvchilar;

-- Userlarga role berish
GRANT o_quvchilar TO hisobot_xizmati;
GRANT o_quvchilar TO analitika_xizmati;

Endi o_quvchilar rolida o'zgarish qilinsa — barcha a'zolarga tegadi.

Amaliy namuna — xizmatlar uchun minimal huquqlar

-- API server — faqat CRUD
CREATE USER api_user WITH PASSWORD 'parol1';
GRANT CONNECT ON DATABASE myapp TO api_user;
GRANT USAGE ON SCHEMA public TO api_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO api_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO api_user;

-- Hisobot xizmati — faqat o'qish
CREATE USER report_user WITH PASSWORD 'parol2';
GRANT CONNECT ON DATABASE myapp TO report_user;
GRANT USAGE ON SCHEMA public TO report_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO report_user;

-- Backup — faqat o'qish
CREATE USER backup_user WITH PASSWORD 'parol3';
GRANT pg_read_all_data TO backup_user; -- built-in role

Mavjud huquqlarni ko'rish

-- Jadval huquqlari
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'foydalanuvchilar';

-- Foydalanuvchi rollari
SELECT rolname FROM pg_roles
WHERE pg_has_role('api_user', oid, 'member');

Tez-tez uchraydigan xatolar

postgres superuser bilan production ulanishi:

❌ DATABASE_URL=postgresql://postgres:parol@localhost/myapp
✅ DATABASE_URL=postgresql://api_user:parol@localhost/myapp

Yangi jadvallar uchun huquq unutilishi:

-- Mavjud jadvallar uchun GRANT berildi
GRANT SELECT ON ALL TABLES IN SCHEMA public TO report_user;

-- Yangi jadval yaratildi — report_user ko'ra olmaydi!
CREATE TABLE yangi_jadval (...);

-- Yechim — DEFAULT PRIVILEGES
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO report_user;

Xulosa

CREATE USER ism WITH PASSWORD 'parol';   -- login huquqi bilan
CREATE ROLE ism;                          -- guruh uchun

GRANT SELECT ON jadval TO ism;
GRANT SELECT, INSERT ON jadval TO ism;
GRANT ALL ON jadval TO ism;
REVOKE DELETE ON jadval FROM ism;

-- Yangi jadvallar uchun avtomatik
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO report_user;

Keyingi dars

53-dars: Row Level Security — RLS, policy yaratish