// chapter_13.md

امنیت و User Management در PostgreSQL

امنیت دیتابیس از مهم‌ترین موضوعات DevOps است. در این فصل با Role‌ها، GRANT/REVOKE، Row Level Security (RLS)، رمزنگاری SSL، pgcrypto و audit با pgaudit آشنا می‌شویم.

Role‌ها در PostgreSQL

در PostgreSQL تفاوت بین “user” و “group” نیست – همه role هستند. role‌ای که می‌تواند login کند = user.

-- ساخت role با login (= user)
CREATE ROLE alice WITH LOGIN PASSWORD 'StrongPass!';
-- یا
CREATE USER alice WITH PASSWORD 'StrongPass!';

-- ساخت role بدون login (= group)
CREATE ROLE developers;
CREATE ROLE managers;

-- اضافه کردن user به group
GRANT developers TO alice;
GRANT managers TO bob;

-- یک role می‌تواند چندین role در دل خود داشته باشد
GRANT developers TO managers;  -- managers خودکار حقوق developers را دارد

گزینه‌های role

CREATE ROLE alice WITH 
    LOGIN
    PASSWORD 'pass'
    SUPERUSER             -- یا NOSUPERUSER (پیش‌فرض)
    CREATEDB              -- می‌تواند DB بسازد
    CREATEROLE            -- می‌تواند role بسازد
    REPLICATION           -- برای replication
    BYPASSRLS             -- bypass Row Level Security
    CONNECTION LIMIT 100  -- حداکثر اتصال همزمان
    VALID UNTIL '2026-12-31';   -- انقضای رمز

-- تغییر role
ALTER ROLE alice WITH PASSWORD 'NewPass!';
ALTER ROLE alice VALID UNTIL '2027-01-01';
ALTER ROLE alice CONNECTION LIMIT 50;
ALTER ROLE alice NOLOGIN;            -- غیرفعال

-- حذف
DROP ROLE alice;

لیست role‌ها

du                         -- دستور meta
du+                        -- با توضیحات

SELECT rolname, rolsuper, rolcanlogin, rolconnlimit, rolvaliduntil
FROM pg_roles;

Privileges – GRANT/REVOKE

دیتابیس

GRANT CONNECT ON DATABASE shopdb TO alice;
GRANT CREATE ON DATABASE shopdb TO developers;
GRANT TEMPORARY ON DATABASE shopdb TO alice;
GRANT ALL PRIVILEGES ON DATABASE shopdb TO admin;

REVOKE CONNECT ON DATABASE shopdb FROM PUBLIC;

Schema

GRANT USAGE ON SCHEMA public TO alice;       -- دیدن schema
GRANT CREATE ON SCHEMA public TO developers; -- ساخت object
GRANT ALL ON SCHEMA public TO admin;

Tables

-- یک جدول
GRANT SELECT, INSERT, UPDATE ON products TO alice;
GRANT DELETE ON products TO bob;

-- همه جدول‌های schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

-- privilege‌های ممکن:
-- SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, ALL

REVOKE INSERT ON products FROM alice;

Sequences

GRANT USAGE, SELECT ON SEQUENCE products_id_seq TO alice;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO developers;

Functions

GRANT EXECUTE ON FUNCTION calculate_total(INTEGER) TO alice;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO developers;

Default Privileges

اعمال privilege به جدول‌های آینده (که هنوز ساخته نشده‌اند):

-- هر جدول جدیدی که در schema public ساخته شد، alice می‌تواند SELECT کند
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT SELECT ON TABLES TO alice;

-- فقط برای جدول‌هایی که role developers می‌سازد
ALTER DEFAULT PRIVILEGES FOR ROLE developers IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE ON TABLES TO app_user;

User فقط-خواندنی

-- ساخت
CREATE USER reporting_user WITH PASSWORD 'ReadOnlyPass';

-- اتصال
GRANT CONNECT ON DATABASE shopdb TO reporting_user;

-- اتصال به DB
c shopdb

-- usage بر schema
GRANT USAGE ON SCHEMA public TO reporting_user;

-- SELECT روی همه جدول‌ها
GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporting_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO reporting_user;

-- برای جدول‌های آینده
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT SELECT ON TABLES TO reporting_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
    GRANT SELECT ON SEQUENCES TO reporting_user;

Column-level Privileges

-- alice می‌تواند فقط name و price را ببیند، نه cost
GRANT SELECT (id, name, price) ON products TO alice;

-- alice می‌تواند email را UPDATE کند، اما نه password
GRANT UPDATE (email) ON users TO alice;

-- اگر alice سعی کند SELECT * بزند، فقط ستون‌های مجاز برمی‌گردند

Row Level Security (RLS)

RLS اجازه می‌دهد قانونی تعریف کنید که هر row فقط برای کاربر خاصی visible باشد – عالی برای multi-tenant:

-- جدول
CREATE TABLE messages (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    tenant_id INTEGER NOT NULL,
    content TEXT
);

-- فعال‌سازی RLS
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;

-- ساخت policy
CREATE POLICY user_isolation ON messages
    FOR ALL
    TO PUBLIC
    USING (user_id = current_setting('app.current_user_id')::int);

-- تنظیم متغیر در app
SET app.current_user_id = '42';

-- حالا alice فقط ردیف‌های خودش را می‌بیند
SELECT * FROM messages;       -- فقط user_id = 42

RLS برای multi-tenant

CREATE POLICY tenant_isolation ON messages
    USING (tenant_id = current_setting('app.tenant_id')::int);

-- در connection pool هر بار:
SET app.tenant_id = '5';

-- query مثل قبل، اما خودکار به tenant 5 محدود است
SELECT * FROM messages;

Policy‌های پیچیده‌تر

-- جداگانه برای SELECT و UPDATE
CREATE POLICY user_select ON messages 
    FOR SELECT 
    USING (user_id = current_user_id() OR is_admin());

CREATE POLICY user_update ON messages 
    FOR UPDATE 
    USING (user_id = current_user_id())
    WITH CHECK (user_id = current_user_id());    -- جلوگیری از تغییر user_id

-- bypass برای admin
CREATE POLICY admin_all ON messages 
    FOR ALL
    TO admin_role
    USING (true);

SSL/TLS

برای اتصال‌های شبکه، SSL ضروری است:

# تولید certificate (یا از CA)
openssl req -new -x509 -days 365 -nodes 
    -out server.crt -keyout server.key 
    -subj "/CN=postgres.example.com"

sudo cp server.crt server.key /var/lib/postgresql/16/main/
sudo chmod 600 /var/lib/postgresql/16/main/server.key
sudo chown postgres:postgres /var/lib/postgresql/16/main/server.*

# postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'              # اختیاری برای client cert
ssl_min_protocol_version = 'TLSv1.2'
ssl_ciphers = 'HIGH:!aNULL:!MD5'

# pg_hba.conf - فقط SSL
hostssl all all 0.0.0.0/0 scram-sha-256
hostnossl all all 0.0.0.0/0 reject

# client اتصال با SSL
psql "host=db.example.com dbname=shopdb sslmode=require"

# با verify
psql "host=db.example.com sslmode=verify-full sslrootcert=ca.crt"

Password Authentication

# postgresql.conf - استفاده از scram-sha-256 (نه md5 ضعیف‌تر)
password_encryption = scram-sha-256

# pg_hba.conf
host all all 0.0.0.0/0 scram-sha-256

-- اگر hash قدیمی md5 دارید، با تغییر رمز دوباره hash می‌شود
ALTER USER alice WITH PASSWORD 'NewStrongPass';

pgcrypto – رمزنگاری

CREATE EXTENSION pgcrypto;

-- hash رمز (یک‌طرفه)
SELECT crypt('password', gen_salt('bf'));    -- bcrypt
SELECT crypt('password', gen_salt('bf', 12)); -- 12 rounds

-- ذخیره
INSERT INTO users(email, password_hash) VALUES (
    'ali@example.com',
    crypt('user_password', gen_salt('bf'))
);

-- اعتبارسنجی
SELECT * FROM users 
WHERE email = 'ali@example.com'
  AND password_hash = crypt('user_password', password_hash);

-- رمزنگاری متقارن (دوطرفه)
INSERT INTO secret_data(content) VALUES (
    pgp_sym_encrypt('داده محرمانه', 'encryption_key')
);

SELECT pgp_sym_decrypt(content::bytea, 'encryption_key') FROM secret_data;

-- رمزنگاری asymmetric با کلید عمومی
SELECT pgp_pub_encrypt('متن', dearmor(public_key));
SELECT pgp_pub_decrypt(encrypted::bytea, dearmor(private_key), 'passphrase');

Encryption at Rest

PostgreSQL خودش encryption-at-rest ندارد. راه‌ها:

Disk encryption: LUKS (Linux) یا BitLocker (Windows)
Filesystem: ZFS با encryption
pgcrypto: رمزنگاری انتخابی ستون‌های sensitive
Cloud: AWS RDS، GCP Cloud SQL خودشان رمزنگاری دارند

Audit با pgaudit

sudo apt install postgresql-16-pgaudit

# postgresql.conf
shared_preload_libraries = 'pgaudit'
pgaudit.log = 'write, ddl, role'
pgaudit.log_relation = on
pgaudit.log_parameter = on

CREATE EXTENSION pgaudit;

-- log برای یک role خاص
ALTER ROLE alice SET pgaudit.log TO 'all';

-- log فقط جدول‌های خاص
CREATE TABLE sensitive (...);
ALTER TABLE sensitive SET (pgaudit.log = 'all');

خروجی pgaudit

2026-04-30 10:15:23 LOG: AUDIT: SESSION,1,1,WRITE,UPDATE,TABLE,
public.products,UPDATE products SET price=...,<not logged>

جلوگیری از SQL Injection

# ❌ خطرناک - SQL injection
cursor.execute(f"SELECT * FROM users WHERE name = '{user_input}'")

# ✅ امن - parametrized
cursor.execute("SELECT * FROM users WHERE name = %s", (user_input,))

# ✅ در PL/pgSQL
EXECUTE 'SELECT * FROM users WHERE name = $1' USING user_input;

-- ✅ یا با format
EXECUTE format('SELECT * FROM %I WHERE id = $1', table_name) USING id;

محدودیت اتصال

-- حد در سطح user
ALTER USER alice CONNECTION LIMIT 10;

-- حد در سطح DB
ALTER DATABASE shopdb CONNECTION LIMIT 100;

-- در postgresql.conf
-- max_connections = 200

-- بستن اتصال‌های idle طولانی
ALTER USER alice SET idle_in_transaction_session_timeout = '5min';
ALTER USER alice SET statement_timeout = '30s';

بررسی pg_hba.conf

فایل pg_hba.conf اولین خط دفاع است. مرتب بررسی کنید:

# خوب - محدود
host    all     postgres    127.0.0.1/32       scram-sha-256
host    shopdb  shop_user   10.0.0.0/24        scram-sha-256
hostssl all     all         0.0.0.0/0          scram-sha-256

# بد - باز
host    all     all         0.0.0.0/0          trust       ❌
host    all     postgres    0.0.0.0/0          md5         ❌

چک‌لیست امنیتی

✅ scram-sha-256 به‌جای md5
✅ SSL برای اتصال‌های خارجی
✅ pg_hba.conf با IP محدود
✅ user جداگانه برای هر app
✅ Privilege‌های minimal
✅ password سخت با rotation دوره‌ای
✅ پسورد superuser فقط در emergency
✅ pgaudit برای جدول‌های sensitive
✅ rate limiting و connection limits
✅ بک‌آپ encrypted
✅ disk encryption (LUKS)
✅ patch کردن منظم PostgreSQL
✅ firewall – فقط پورت‌های لازم
✅ monitoring تلاش login ناموفق

بهترین شیوه‌ها

اصل least privilege: کمترین دسترسی لازم
user جداگانه برای هر app/سرویس
هرگز از postgres user در app
password rotation برای هرکسی که login می‌کند
SSL برای production
RLS برای multi-tenant
pgcrypto برای داده فوق‌سری
audit در جدول‌های حساس
review مرتب pg_hba.conf

جمع‌بندی

Role = user/group در PostgreSQL
GRANT/REVOKE برای کنترل دسترسی
Default Privileges برای جدول‌های آینده
Column و Row Level Security
SSL/TLS با ssl_cert_file
scram-sha-256 به‌جای md5
pgcrypto برای رمزنگاری
pgaudit برای audit logging
parametrized queries جلوی SQL injection