role، GRANT و DEFAULT PRIVILEGES؛ اتصال امن با scram-sha-256 و SSL

کمترین دسترسی لازم، نه superuser برای همه

رایج‌ترین پیکربندی ناامن در پروژه‌های ایرانی این است که برنامه با کاربر postgres وصل می‌شود. یک SQL injection کافی است تا مهاجم با COPY ... TO PROGRAM روی خود سرور فرمان اجرا کند. الگوی درست سه لایه دارد: مالک (owner) که جدول‌ها را می‌سازد و فقط migration با آن اجرا می‌شود، نقش‌های گروهی بدون LOGIN که مجموعه‌ی دسترسی‌اند، و کاربرهای ورود که عضو گروه‌ها هستند.

CREATE ROLE carpet_owner NOLOGIN;
CREATE ROLE carpet_rw    NOLOGIN;
CREATE ROLE carpet_ro    NOLOGIN;

CREATE ROLE migrator   LOGIN PASSWORD 'رمز-قوی-۱' IN ROLE carpet_owner;
CREATE ROLE app_user   LOGIN PASSWORD 'رمز-قوی-۲' IN ROLE carpet_rw;
CREATE ROLE bi_reader  LOGIN PASSWORD 'رمز-قوی-۳' IN ROLE carpet_ro CONNECTION LIMIT 5;

REVOKE ALL ON DATABASE carpet FROM PUBLIC;
GRANT CONNECT ON DATABASE carpet TO carpet_rw, carpet_ro;
ALTER SCHEMA factory OWNER TO carpet_owner;

GRANT USAGE ON SCHEMA factory TO carpet_rw, carpet_ro;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA factory TO carpet_rw;
GRANT SELECT ON ALL TABLES IN SCHEMA factory TO carpet_ro;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA factory TO carpet_rw;

DEFAULT PRIVILEGES: برای جدول‌هایی که فردا ساخته می‌شوند

GRANT ... ON ALL TABLES فقط جدول‌های موجود را می‌گیرد. migration هفته‌ی بعد جدول تازه‌ای می‌سازد و برنامه با «permission denied» از کار می‌افتد. DEFAULT PRIVILEGES این را حل می‌کند، اما فقط برای اشیایی که همان role مشخص‌شده می‌سازد:

ALTER DEFAULT PRIVILEGES FOR ROLE carpet_owner IN SCHEMA factory
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO carpet_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE carpet_owner IN SCHEMA factory
  GRANT SELECT ON TABLES TO carpet_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE carpet_owner IN SCHEMA factory
  GRANT USAGE ON SEQUENCES TO carpet_rw;

-- migration باید با همین نقش جدول بسازد
SET ROLE carpet_owner;

با \ddp در psql پیش‌فرض‌ها را ببینید و با \dp factory.* دسترسی واقعی هر جدول را.

scram-sha-256 و SSL

روش احراز هویت در pg_hba را در فصل ۱ دیدیم. برای اتصال از شبکه دو چیز دیگر لازم است: رمز با هش scram (نه md5) و رمزنگاری کانال با SSL، به‌خصوص وقتی برنامه و دیتابیس در دو دیتاسنتر جدا هستند.

sudo -u postgres psql -c "SELECT rolname FROM pg_authid WHERE rolpassword LIKE 'md5%';"
# این roleها باید رمزشان دوباره تنظیم شود

# گواهی خودامضا برای شبکه‌ی داخلی (برای اینترنت از CA معتبر استفاده کنید)
cd /etc/postgresql/16/main
sudo openssl req -new -x509 -days 825 -nodes -subj "/CN=db.carpet.local" \
  -keyout server.key -out server.crt
sudo chown postgres:postgres server.key server.crt
sudo chmod 600 server.key
# سپس در postgresql.conf مسیر ssl_cert_file و ssl_key_file را به این دو فایل بدهید و reload کنید

SELECT pid, ssl, version, cipher FROM pg_stat_ssl JOIN pg_stat_activity USING (pid)
WHERE usename = 'app_user';

سمت کلاینت sslmode=verify-full هم کانال را رمز می‌کند و هم هویت سرور را بررسی می‌کند؛ require رمز می‌کند اما جلوی سرور جعلی را نمی‌گیرد.

نکته‌هایی که کمتر کسی می‌داند

• ستون‌های identity برای INSERT به مجوز USAGE روی sequence نیاز ندارند؛ اما serial قدیمی دارد. خطای «permission denied for sequence» تقریباً همیشه از جدول‌های serial است.
• نقش‌های آماده‌ی pg_read_all_data و pg_write_all_data (نسخه‌ی 14) و pg_monitor برای گزارش‌گیر و مانیتورینگ، نیاز به GRANT تک‌تک را از بین می‌برند. نسخه‌ی 17 مجوز MAINTAIN و نقش pg_maintain را اضافه کرد تا VACUUM و REINDEX بدون مالکیت ممکن شود.
• ALTER ROLE app_user SET search_path = factory باعث می‌شود برنامه بدون پیشوند schema کار کند، بی‌آن‌که search_path سراسری را عوض کنید.
• رمزی که با CREATE ROLE ... PASSWORD '...' می‌دهید ممکن است در لاگ سرور (اگر log_statement فعال باشد) و تاریخچه‌ی psql بماند؛ از \password app_user استفاده کنید که هش را در کلاینت می‌سازد.
• فایل server.key با مجوزی بازتر از 0600 باعث می‌شود سرور اصلاً بالا نیاید؛ پیام خطا در لاگ است، نه در خروجی systemctl.