PostgreSQL للفرق: تثبيت آمن وصلاحيات واضحة

أغلب فرق التطوير تبدأ PostgreSQL بنفس الطريقة: سطر docker run -e POSTGRES_PASSWORD=postgres -p 5432:5432 postgres من صفحة Docker Hub، ثم يتصل التطبيق بالمستخدم postgres نفسه، وتمر الأشهر وهذا الإعداد يعمل في الإنتاج دون أن يراجعه أحد. والمشكلة أن هذا الإعداد يعني أن المنفذ مفتوح لكل الإنترنت، وأن التطبيق يملك صلاحيات المستخدم الخارق Superuser، فأي ثغرة حقن SQL Injection أو خطأ في كود الترحيل Migration يستطيع أن يحذف الجداول كلها، وأن النسخ الاحتياطي غالباً غير موجود أو لم تجرب استعادته أبداً. وفي مقال أنواع قواعد البيانات شرحنا متى تختار قاعدة علائقية Relational مثل PostgreSQL، وفي هذا المقال سوف نقوم بتجهيزها لفريق حقيقي: تثبيت بإصدار مثبت، وأدوار Roles واضحة، وتطبيق لا يستطيع حذف الجداول، واتصال مشفر، ونسخة احتياطية اختبرنا استعادتها فعلاً.

وسوف نناقش في هذا المقال ما يلي:

• التثبيت مع Docker Compose بإصدار مثبت، وكلمات مرور من ملفات Secrets، ومنفذ لا يفتح إلا على السيرفر نفسه.
• التثبيت على Ubuntu من مستودع PGDG الرسمي، ولماذا نفضله على حزمة التوزيعة.
• الأدوار Roles: من يملك الجداول، ومن يغيرها، ومن يستخدمها فقط.
• المخطط Schema والصلاحيات الافتراضية Default Privileges، والخطأين اللذين وقعنا فيهما أثناء المختبر.
• مختبر حقيقي: مستخدم التطبيق يحاول حذف جدول فيرفض PostgreSQL.
• من يدخل ومن أين: الملف pg_hba.conf والتشفير TLS.
• النسخ الاحتياطي المنطقي بالأداة pg_dump، واختبار الاستعادة كل ليلة، والاستعادة على سيرفر جديد.
• أساسيات المراقبة Monitoring، والأخطاء الشائعة، وقائمة تحقق.

وهذا المقال يركز على التشغيل اليومي لقاعدة واحدة، أما الاستعادة إلى لحظة محددة قبل خطأ بشري فلها مقال pgBackRest والاستعادة إلى نقطة زمنية، وضبط الذاكرة والأداء له مقال ضبط أداء PostgreSQL.

التثبيت مع Docker Compose

إذا كانت خدماتك تعمل على Docker كما شرحنا في دليل تثبيت Docker على Ubuntu، فالأسهل أن تعمل قاعدة البيانات بجانبها في ملف Compose، ولكن بأربعة فروق عن السطر المنسوخ من الإنترنت: إصدار مثبت بالرقم الكامل، وكلمة المرور من ملف Secret وليس من متغير مكتوب في الملف، والمنفذ على 127.0.0.1 فقط، وملفات الإعداد من المجلد حتى تراجعها في Git. والملف الكامل كما يلي:

# PostgreSQL 18 for a small team: pinned image, password from a secret file, local port only, TLS and pg_hba from files.
name: lab-pgsetup
services:
  db:
    image: postgres:18.6
    container_name: lab-pgsetup-db
    restart: unless-stopped
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD_FILE: /run/secrets/pg_superuser
      POSTGRES_DB: postgres
      POSTGRES_INITDB_ARGS: "--data-checksums --auth-host=scram-sha-256 --auth-local=peer"
    secrets: [pg_superuser, pg_app_migrator, pg_app_user, pg_app_readonly]
    # the key must belong to the postgres user with mode 0600, which a bind mount from Windows or macOS cannot give,
    # so it is copied inside the container before the normal entrypoint starts
    entrypoint: ["bash", "-c", "install -d -o postgres -m 700 /run/pgcerts && install -o postgres -m 600 /certs-src/server.key /certs-src/server.crt /run/pgcerts/ && exec docker-entrypoint.sh postgres -c config_file=/etc/postgresql/postgresql.conf"]
    volumes:
      - lab-pgsetup-data:/var/lib/postgresql
      - ./conf/postgresql.conf:/etc/postgresql/postgresql.conf:ro
      - ./conf/pg_hba.conf:/etc/postgresql/pg_hba.conf:ro
      - ./certs:/certs-src:ro
      - ./initdb:/docker-entrypoint-initdb.d:ro
      - ./backup.sh:/scripts/backup.sh:ro
      - ./restore-test.sh:/scripts/restore-test.sh:ro
      - lab-pgsetup-backups:/backup
    ports:
      - "127.0.0.1:55432:5432"
    shm_size: 256mb
    healthcheck:
      test: ["CMD", "runuser", "-u", "postgres", "--", "pg_isready", "-d", "postgres"]
      interval: 5s
      timeout: 3s
      retries: 10
secrets:
  pg_superuser:
    file: ./secrets/pg_superuser.txt
  pg_app_migrator:
    file: ./secrets/pg_app_migrator.txt
  pg_app_user:
    file: ./secrets/pg_app_user.txt
  pg_app_readonly:
    file: ./secrets/pg_app_readonly.txt
volumes:
  lab-pgsetup-data:
    name: lab-pgsetup-data
  lab-pgsetup-backups:
    name: lab-pgsetup-backups

في الإعداد أعلاه لاحظ التالي:

• الصورة postgres:18.6 وليس postgres:latest أو postgres:18، والسبب أن الوسم المتحرك قد يأتي بإصدار جديد عند أي docker compose pull دون أن تقرر ذلك، والترقية إلى إصدار رئيسي Major Version جديد تحتاج نقل البيانات ولا تتم بتغيير الصورة فقط.
• تغير مكان البيانات في صورة PostgreSQL 18 وما بعده، فكما تذكر صفحة الصورة الرسمية أصبح PGDATA هو /var/lib/postgresql/18/docker، وأصبح ال Volume يربط على /var/lib/postgresql وليس على /var/lib/postgresql/data كما في الأدلة القديمة، فإذا نسخت سطر Volume قديماً فسوف تكتب البيانات داخل ال Container وتضيع عند حذفه.
• المتغير POSTGRES_PASSWORD_FILE يقرأ كلمة المرور من ملف، والملفات في المجلد secrets لا تدخل Git أبداً، وأنشأناها بسكربت صغير يولد كلمات عشوائية وشهادة TLS للمختبر.
• تفعيل فحص سلامة الصفحات Data Checksums أصبح الافتراضي في initdb منذ PostgreSQL 18، وكتبناه صراحة في POSTGRES_INITDB_ARGS حتى لا يعتمد الإعداد على إصدار معين، وهذا الفحص يكشف الصفحة التالفة على القرص بدل أن تقرأها القاعدة كأنها سليمة.
• المنفذ 127.0.0.1:55432 لا يقبل اتصالاً من خارج السيرفر، والتطبيقات الأخرى في Compose تصل إلى القاعدة باسم الخدمة db داخل الشبكة كما شرحنا في مقال شبكات Docker، وتذكر أن Docker يتجاوز قواعد UFW عند نشر المنافذ، لذلك لا يحميك جدار الحماية إذا كتبت 5432:5432.
• فحص الصحة Healthcheck يعمل باسم المستخدم postgres عبر runuser، وفي أول محاولة تركناه يعمل باسم root فامتلأ السجل كل خمس ثوان بالرسالة Peer authentication failed for user "postgres" لأن قاعدة peer تطابق اسم مستخدم النظام مع اسم مستخدم القاعدة.

وقد يتساءل البعض: لماذا هذا السطر الطويل في entrypoint؟ والإجابة أن PostgreSQL يرفض أن يبدأ إذا كان مفتاح TLS الخاص Private Key قابلاً للقراءة من مستخدمين آخرين، ولا يمكن ضبط المالك والصلاحيات على ملف مربوط من Windows أو macOS، لذلك ننسخ الشهادة داخل ال Container بالمالك postgres والصلاحية 600 قبل أن يبدأ السكربت الأصلي للصورة. وعلى سيرفر Linux تستطيع ضبط المالك على الملف نفسه وحذف هذا السطر.

أما ملف الإعدادات فهو قصير، وكل سطر فيه له سبب:

listen_addresses = '*'
port = 5432
max_connections = 100
shared_buffers = 256MB
password_encryption = scram-sha-256
hba_file = '/etc/postgresql/pg_hba.conf'

ssl = on
ssl_cert_file = '/run/pgcerts/server.crt'
ssl_key_file  = '/run/pgcerts/server.key'
ssl_min_protocol_version = 'TLSv1.2'

log_connections = 'authentication'
log_disconnections = on
log_line_prefix = '%m [%p] %q%u@%d from %h '
log_min_duration_statement = 500ms
log_lock_waits = on
log_temp_files = 0
shared_preload_libraries = 'pg_stat_statements'

لاحظ أن log_connections في PostgreSQL 18 لم يعد قيمة on أو off فقط، وإنما يقبل مراحل مثل authentication لتسجيل من دخل وبأي طريقة، وأن log_min_duration_statement يسجل كل استعلام أبطأ من نصف ثانية، وسوف نحتاجه في المراقبة. وقيم الذاكرة هنا قيم بداية فقط، وطريقة حسابها بالقياس شرحناها في مقال ضبط الأداء.

التثبيت على Ubuntu من مستودع PGDG

إذا كانت القاعدة على سيرفر مخصص دون Docker، فالطريقة البسيطة هي apt install postgresql من مستودع التوزيعة، وهي تعمل، ولكنها تربطك بالإصدار الرئيسي الذي اختارته التوزيعة طوال عمرها، فإذا احتجت إصداراً أحدث أو أقدم لتطابق سيرفراً آخر فلن تجده. والحل الأنسب هو مستودع PGDG الذي يديره مجتمع PostgreSQL نفسه، والذي توصي به صفحة التنزيل الرسمية لـ Ubuntu، وفيه كل الإصدارات المدعومة وتحديثاتها الصغرى Minor Releases في يوم صدورها، وكل إصدار رئيسي مدعوم خمس سنوات كما في سياسة الإصدارات. والأوامر على Ubuntu 26.04 كما يلي:

sudo apt install -y postgresql-common ca-certificates
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh -y
sudo apt install -y postgresql-18
pg_lsclusters

والمخرج سوف يكون كما يلي (من Container بنظام Ubuntu 26.04.1، وعلى سيرفر حقيقي يبدأ systemd ال Cluster تلقائياً):

--- distro package:
postgresql:
  Installed: (none)
  Candidate: 18+290ubuntu1
You can now start installing packages from apt.postgresql.org.
Types: deb
URIs: https://apt.postgresql.org/pub/repos/apt
Suites: resolute-pgdg
Components: main
Architectures: amd64
Signed-By: /usr/share/postgresql-common/pgdg/apt.postgresql.org.gpg
--- installed:
ii  postgresql-18  18.6-1.pgdg26.04+2 amd64        The World's Most Advanced Open Source Relational Database
Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log
PostgreSQL 18.6 (Ubuntu 18.6-1.pgdg26.04+2) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 15.2.0-16ubuntu1) 15.2.0, 64-bit
/var/lib/postgresql/18/main
/etc/postgresql/18/main/pg_hba.conf
on

في المخرج أعلاه لاحظ التالي:

• السكربت يضيف ملف المستودع مع مفتاح التوقيع Signed-By، فلا تحتاج إلى نسخ أوامر apt-key القديمة من المدونات.
• الحزمة جاءت بالإصدار 18.6 نفسه الذي في صورة Docker، والسطر الأخير on هو data_checksums، أي أن الإعداد الافتراضي في 18 يفعله.
• على Ubuntu ملفات الإعداد في /etc/postgresql/18/main والبيانات في /var/lib/postgresql/18/main، والأمر pg_lsclusters يعرض كل ال Clusters على السيرفر، وهو مفيد عند الترقية حيث يعمل القديم والجديد معاً بالأداة pg_upgradecluster.

وبعد التثبيت ينطبق كل ما يلي في هذا المقال كما هو، فقط ضع الملفات في /etc/postgresql/18/main ونفذ سكربت الأدوار بالأمر sudo -u postgres psql. والترخيص في الحالتين هو PostgreSQL License وهو ترخيص متساهل Permissive يشبه MIT وBSD.

الأدوار Roles: من يملك الجداول ومن يستخدمها

في PostgreSQL لا يوجد فرق بين المستخدم User والمجموعة Group، فكلاهما دور Role، والدور الذي يملك صلاحية LOGIN نسميه مستخدماً، والدور الذي يمنح لأدوار أخرى نسميه مجموعة. والقاعدة الأهم هنا هي أقل صلاحية Least Privilege: كل جهة تتصل بالقاعدة تأخذ ما تحتاجه فقط، والسبب أن الخطأ أو الاختراق في أي جهة يتوقف عند حدود صلاحياتها. ولذلك نستخدم أربعة أدوار لكل تطبيق:

الصورة التالية تبين العلاقة بين الأدوار الأربعة والمخطط:

وقد يتساءل البعض: لماذا لا يكون app_migrator نفسه هو المالك؟ والإجابة أن فصل المالك عن المستخدم الذي يسجل الدخول يجعلك تغير كلمة مرور خط النشر أو تضيف مستخدماً ثانياً للترحيل، أو تسحب الصلاحية من شخص غادر الفريق، دون أن تنقل ملكية أي جدول. وسكربت الإنشاء الكامل يعمل مرة واحدة عند أول تشغيل من المجلد docker-entrypoint-initdb.d:

#!/bin/bash
# Runs once, on an empty data directory. Passwords come from secret files, never from the compose file.
set -euo pipefail
psql -v ON_ERROR_STOP=1 -U postgres -d postgres \
  -v migrator_pw="$(cat /run/secrets/pg_app_migrator)" \
  -v user_pw="$(cat /run/secrets/pg_app_user)" \
  -v ro_pw="$(cat /run/secrets/pg_app_readonly)" <<'SQL'
-- the owner of the schema and tables: nobody logs in as it
CREATE ROLE app_owner NOLOGIN;
-- migrations (CI or the deploy job) log in as app_migrator and act as app_owner
CREATE ROLE app_migrator LOGIN PASSWORD :'migrator_pw';
GRANT app_owner TO app_migrator;
-- the running application: data only, no DDL
CREATE ROLE app_user LOGIN PASSWORD :'user_pw' CONNECTION LIMIT 40;
-- reports and support staff
CREATE ROLE app_readonly LOGIN PASSWORD :'ro_pw' CONNECTION LIMIT 5;

CREATE DATABASE app OWNER app_owner;
REVOKE ALL ON DATABASE app FROM PUBLIC;
GRANT CONNECT ON DATABASE app TO app_migrator, app_user, app_readonly;

-- migrations always run as app_owner and create objects in shop, so default privileges apply
ALTER ROLE app_migrator IN DATABASE app SET role = 'app_owner';
ALTER ROLE app_migrator IN DATABASE app SET search_path = shop;
ALTER ROLE app_user     IN DATABASE app SET search_path = shop;
ALTER ROLE app_user     IN DATABASE app SET statement_timeout = '15s';
ALTER ROLE app_user     IN DATABASE app SET idle_in_transaction_session_timeout = '60s';
ALTER ROLE app_readonly IN DATABASE app SET search_path = shop;
ALTER ROLE app_readonly IN DATABASE app SET default_transaction_read_only = on;
SQL

psql -v ON_ERROR_STOP=1 -U postgres -d app <<'SQL'
REVOKE ALL ON SCHEMA public FROM PUBLIC;
CREATE SCHEMA shop AUTHORIZATION app_owner;
GRANT USAGE ON SCHEMA shop TO app_user, app_readonly;
-- tables and sequences that app_owner creates later get these grants automatically
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA shop
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA shop
  GRANT USAGE, SELECT ON SEQUENCES TO app_user;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA shop
  GRANT SELECT ON TABLES TO app_readonly;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SQL

في الإعداد أعلاه لاحظ التالي:

• كلمات المرور تمرر إلى psql كمتغيرات :'user_pw' من ملفات ال Secrets، فلا تظهر في الملف ولا في سجل الأوامر، وهي تحفظ كهاش scram-sha-256، وتذكر أن PostgreSQL 18 أعلن إلغاء كلمات مرور MD5 في إصدار قادم وأصبح يحذرك عند استخدامها.
• CONNECTION LIMIT لكل دور يمنع تطبيقاً فيه تسرب اتصالات من أخذ كل اتصالات السيرفر، وحساب هذا الرقم شرحناه في مقال تجميع الاتصالات Connection Pooling.
• الإعدادات المرتبطة بالدور داخل القاعدة ALTER ROLE … IN DATABASE app SET تجعل لكل اتصال من التطبيق مهلة استعلام statement_timeout ومهلة للمعاملة المعلقة idle_in_transaction_session_timeout دون تعديل الكود.
• REVOKE ALL ON DATABASE app FROM PUBLIC يمنع أي دور آخر على السيرفر من مجرد الاتصال بهذه القاعدة، فإذا استضفت عدة تطبيقات على نفس السيرفر فلا يرى أحدها قاعدة الآخر.

المخطط Schema والصلاحيات الافتراضية Default Privileges

المخطط Schema مجلد داخل قاعدة البيانات يجمع الجداول، وكل قاعدة جديدة فيها المخطط public. وقبل PostgreSQL 15 كان أي مستخدم يستطيع إنشاء جداول فيه، ثم تغير ذلك فأصبح مالك public هو الدور pg_database_owner أي مالك القاعدة، وسحبت صلاحية الإنشاء من الجميع، كما تشرح وثائق المخططات. ومع ذلك نضع جداول التطبيق في مخطط خاص shop يملكه app_owner، والسبب أن الصلاحيات عليه تصبح صريحة وواضحة عند المراجعة.

والمشكلة التي يقع فيها أغلب الفرق هي هذه: الأمر GRANT SELECT ON ALL TABLES IN SCHEMA shop TO app_user يعطي الصلاحية على الجداول الموجودة الآن فقط، فإذا أضاف الترحيل جدولاً جديداً غداً فالتطبيق لا يستطيع قراءته، ويظهر الخطأ في الإنتاج بعد النشر. والحل هو ALTER DEFAULT PRIVILEGES الذي يقول: كل جدول ينشئه الدور app_owner في هذا المخطط مستقبلاً يأخذ هذه الصلاحيات تلقائياً. ولاحظ كلمة «ينشئه الدور app_owner»، فهي سبب الخطأ الأول الذي وقعنا فيه.

الخطأ الأول: الترحيل أنشأ الجدول باسمه وليس باسم المالك

في النسخة الأولى من السكربت لم يكن فيه السطر SET role = 'app_owner'، فاتصل app_migrator وأنشأ جدولاً، وبما أنه عضو في app_owner فقد ورث صلاحية الإنشاء في المخطط، ولكن الجدول أصبح ملكه هو:

CREATE TABLE shop.customers (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL);
CREATE TABLE
SELECT tableowner FROM pg_tables WHERE tablename='customers';
  tableowner
--------------
 app_migrator
(1 row)

=== app_user
SELECT count(*) FROM customers;
ERROR:  permission denied for table customers

والسبب أن الصلاحيات الافتراضية مسجلة باسم app_owner، والجدول أنشأه app_migrator، فلم تطبق عليه. والحل إما أن يبدأ كل ملف ترحيل بالأمر SET ROLE app_owner، وهذا يعتمد على ذاكرة كل مطور، أو أن تجعله إعداداً للدور نفسه كما في السكربت النهائي: ALTER ROLE app_migrator IN DATABASE app SET role = 'app_owner'، فيبدأ كل اتصال من خط النشر وهو يعمل باسم المالك، كما تشرح وثائق عضوية الأدوار.

الخطأ الثاني: الجداول ذهبت إلى public

بعد إصلاح الخطأ الأول أنشأنا الجداول باسمها فقط CREATE TABLE customers دون اسم المخطط، فلم تذهب إلى shop، وإنما إلى public:

 schemaname | tablename | tableowner
------------+-----------+------------
 public     | customers | app_owner
 public     | orders    | app_owner

وهذا غريب في البداية لأننا سحبنا كل الصلاحيات على public، ولكن app_owner هو مالك القاعدة، ومالك القاعدة هو pg_database_owner الذي يملك public، فيستطيع الإنشاء فيه دون أي صلاحية إضافية، وsearch_path الافتراضي هو "$user", public. والحل هو السطر ALTER ROLE app_migrator IN DATABASE app SET search_path = shop، فتذهب الجداول التي لا يذكر مخططها إلى shop.

المختبر: تطبيق لا يستطيع حذف الجداول

بعد السكربت النهائي شغلنا القاعدة من الصفر، واتصلنا من Container آخر في نفس شبكة Compose عبر TLS، أولاً باسم app_migrator لإنشاء الجداول:

SELECT current_user, session_user, current_schema;
CREATE TABLE customers (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name text NOT NULL);
CREATE TABLE orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id),
  total numeric(12,2) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
SELECT schemaname, tablename, tableowner FROM pg_tables WHERE schemaname = 'shop';

والمخرج سوف يكون كما يلي:

 current_user | session_user | current_schema
--------------+--------------+----------------
 app_owner    | app_migrator | shop
(1 row)

CREATE TABLE
CREATE TABLE
 schemaname | tablename | tableowner
------------+-----------+------------
 shop       | customers | app_owner
 shop       | orders    | app_owner
(2 rows)

لاحظ أن session_user هو من سجل الدخول، وcurrent_user هو الدور الذي يعمل باسمه الآن، والجداول في shop ويملكها app_owner. الآن نتصل باسم app_user كما يفعل التطبيق، فنضيف بيانات ثم نحاول ما لا يجب أن ينجح:

INSERT INTO customers (name) SELECT 'customer ' || g FROM generate_series(1, 1000) g;
INSERT INTO orders (customer_id, total, created_at)
  SELECT 1 + (g % 1000), round((random() * 500)::numeric, 2), now() - g * interval '1 minute'
  FROM generate_series(1, 50000) g;
SELECT count(*) FROM orders;
DROP TABLE orders;
TRUNCATE orders;
ALTER TABLE orders ADD COLUMN note text;
CREATE TABLE shop.tmp (x int);
CREATE TABLE public.tmp (x int);

والمخرج سوف يكون كما يلي:

INSERT 0 1000
INSERT 0 50000
 count
-------
 50000
(1 row)

ERROR:  must be owner of table orders
ERROR:  permission denied for table orders
ERROR:  must be owner of table orders
ERROR:  permission denied for schema shop
LINE 1: CREATE TABLE shop.tmp (x int);
                     ^
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE public.tmp (x int);
                     ^

وهذا ما نريده بالضبط: التطبيق أضاف ألف عميل وخمسين ألف طلب، لأن الصلاحيات الافتراضية أعطته INSERT على الجداول الجديدة دون أي GRANT يدوي، ولكنه لا يستطيع حذف الجدول ولا تفريغه بالأمر TRUNCATE ولا تغيير بنيته ولا إنشاء جداول، فإذا وجد مخترق ثغرة حقن SQL في التطبيق فأسوأ ما يستطيعه هو ما يستطيعه التطبيق نفسه. ولاحظ أن DELETE ما زال مسموحاً لأن التطبيق يحتاجه، وحماية البيانات من حذف خاطئ بالأمر DELETE تكون بالنسخ الاحتياطي والاستعادة إلى نقطة زمنية كما سيأتي.

أما دور القراءة فقد ضبطنا له default_transaction_read_only = on، ولكن هذا الإعداد وحده ليس حماية، والسبب أن المستخدم يستطيع تغييره في جلسته:

DELETE FROM customers WHERE id = 1;
ERROR:  cannot execute DELETE in a read-only transaction
SET default_transaction_read_only = off;
SET
DELETE FROM customers WHERE id = 1;
ERROR:  permission denied for table customers

فالرفض الأول جاء من الإعداد، وبعد أن أطفأه المستخدم جاء الرفض الثاني من الصلاحيات، وهي الحماية الحقيقية، أما الإعداد فهو حزام أمان إضافي يمنع الخطأ غير المقصود. والقائمة النهائية للأدوار من الأمر \du:

                               List of roles
  Role name   |                         Attributes
--------------+------------------------------------------------------------
 app_migrator |
 app_owner    | Cannot login
 app_readonly | 5 connections
 app_user     | 40 connections
 postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS

من يدخل ومن أين؟ pg_hba.conf وTLS

الصلاحيات تحدد ماذا يفعل الدور بعد أن يدخل، أما الملف pg_hba.conf فيحدد من يستطيع الدخول أصلاً: أي نوع اتصال، ولأي قاعدة، وبأي دور، ومن أي عنوان، وبأي طريقة مصادقة Authentication. وPostgreSQL يقرأ الملف من الأعلى إلى الأسفل ويأخذ أول سطر يطابق الاتصال، ثم يتوقف، فإذا فشلت المصادقة في ذلك السطر فلا ينتقل إلى السطر التالي. والملف الذي استخدمناه:

# TYPE   DATABASE  USER          ADDRESS          METHOD
local    all       postgres                       peer
local    all       all                            scram-sha-256
# inside the container (healthcheck, backups run with docker exec)
host     all       all           127.0.0.1/32     scram-sha-256
# everything from the network must use TLS, and only these roles to this database
hostssl  app       app_user      0.0.0.0/0        scram-sha-256
hostssl  app       app_migrator  0.0.0.0/0        scram-sha-256
hostssl  app       app_readonly  0.0.0.0/0        scram-sha-256
# anything else is refused, with or without TLS
host     all       all           0.0.0.0/0        reject

الصورة التالية تبين ما حدث لثلاثة اتصالات في المختبر:

والمخرج الحقيقي للاتصالات الثلاثة كما يلي:

=== no tls
psql: error: connection to server at "db" (192.168.48.2), port 5432 failed: FATAL:  pg_hba.conf rejects connection for host "192.168.48.3", user "app_user", database "app", no encryption
=== superuser over network
psql: error: connection to server at "db" (192.168.48.2), port 5432 failed: FATAL:  pg_hba.conf rejects connection for host "192.168.48.3", user "postgres", database "app", SSL encryption
=== tls info
 ssl | version |         cipher
-----+---------+------------------------
 t   | TLSv1.3 | TLS_AES_256_GCM_SHA384
(1 row)

في المخرج أعلاه لاحظ التالي:

• الرسالة تذكر العنوان والدور والقاعدة وحالة التشفير، وهي نفسها تظهر في سجل السيرفر، فإذا اشتكى مطور من أنه لا يستطيع الاتصال فابحث عنها في السجل أولاً.
• المستخدم الخارق postgres لا يدخل من الشبكة أبداً حتى مع TLS وكلمة مرور صحيحة، فالإدارة تكون من داخل السيرفر بالأمر docker exec -u postgres أو عبر SSH، وهذا يغلق أخطر باب لتخمين كلمة المرور.
• الجدول pg_stat_ssl يبين لكل اتصال هل هو مشفر وبأي بروتوكول، وسوف نستخدمه في المراقبة للتأكد من أنه لا يوجد اتصال دون تشفير.

ولكن التشفير وحده لا يكفي لمنع من يقف في منتصف الطريق Man in the Middle، والسبب أن الوضع الافتراضي للكلاينت في libpq هو sslmode=prefer ثم require يشفر ولكنه لا يتحقق من هوية السيرفر، فيقبل أي شهادة. والوضع الصحيح للإنتاج هو verify-full مع شهادة الجهة المصدرة sslrootcert، وعندها يتحقق الكلاينت من أن اسم السيرفر في الشهادة هو الاسم الذي يتصل به:

$ psql "host=db dbname=app user=app_user sslmode=verify-full sslrootcert=/ca.crt"
psql: error: connection to server at "db" (192.168.48.2), port 5432 failed: server certificate for "db.lab.internal" (and 2 other names) does not match host name "db"

$ psql "host=db.lab.internal dbname=app user=app_user sslmode=verify-full sslrootcert=/ca.crt" -c "select current_user"
 current_user
--------------
 app_user
(1 row)

فالاتصال الأول رفض لأن الاسم db غير موجود في الشهادة، والثاني نجح لأن الاسم يطابق. وفي المختبر استخدمنا شهادة موقعة ذاتياً Self-Signed، وفي الإنتاج استخدم جهة إصدار داخلية أو شهادة لنطاق حقيقي، وقد شرحنا الشهادات وصيغها في مقال الشهادات الرقمية، وإعدادات السيرفر الكاملة في وثائق TLS في PostgreSQL.

النسخ الاحتياطي المنطقي مع pg_dump

النسخ الاحتياطي في PostgreSQL نوعان: النسخ المنطقي Logical Backup بالأداة pg_dump الذي يكتب الجداول والبيانات كأوامر يعاد تنفيذها، والنسخ الفيزيائي Physical Backup الذي ينسخ ملفات القاعدة نفسها مع سجل WAL. والمنطقي هو البداية الصحيحة لقاعدة صغيرة أو متوسطة، والسبب أنه بسيط، ويعمل بين الإصدارات المختلفة، وتستطيع استعادة جدول واحد منه. ولكنه يمثل لحظة واحدة فقط، فإذا نسخت في الثانية فجراً وحدث الخطأ في المساء فقد خسرت بيانات اليوم كله، ولهذا تحتاج القواعد المهمة النسخ الفيزيائي مع الاستعادة إلى نقطة زمنية، وهو موضوع مقال pgBackRest. والقواعد العامة للنسخ مثل قاعدة 3-2-1 وال RPO وال RTO شرحناها في مقال النسخ الاحتياطي بقاعدة 3-2-1، فلن نكررها.

الصورة التالية تبين الخطة كاملة:

والسكربت الذي يعمل كل ليلة داخل ال Container:

#!/bin/sh
# Nightly logical backup: one custom-format dump per database plus the roles, kept for 14 days.
# Run on the host: docker exec -u postgres lab-pgsetup-db sh /scripts/backup.sh
set -eu
DIR=/backup/$(date -u +%Y-%m-%dT%H%MZ)
mkdir -p "$DIR"
pg_dumpall --globals-only --no-role-passwords > "$DIR/globals.sql"
pg_dump --format=custom --compress=zstd:3 --file="$DIR/app.dump" app
pg_restore --list "$DIR/app.dump" > /dev/null          # the file is readable, not a proof it restores
find /backup -mindepth 1 -maxdepth 1 -type d -mtime +14 -exec rm -rf {} +
ls -l "$DIR"

في الإعداد أعلاه لاحظ التالي:

• الصيغة --format=custom مضغوطة، وتسمح للأداة pg_restore باستعادة جدول واحد، وباستعادة متوازية بالخيار --jobs، وهي أفضل من ملف SQL نصي لكل شيء تقريباً، والضغط zstd متاح منذ PostgreSQL 16.
• pg_dump ينسخ قاعدة واحدة فقط ولا ينسخ الأدوار، لأن الأدوار على مستوى ال Cluster كله، لذلك نحفظها بالأمر pg_dumpall --globals-only، والخيار --no-role-passwords يجعل الملف خالياً من هاش كلمات المرور، فتعيد ضبطها من مكان حفظ الأسرار عند الاستعادة.
• pg_restore --list يتأكد فقط من أن الملف مقروء، وليس دليلاً على أن الاستعادة تنجح.
• وتذكر أن ال Volume /backup على نفس السيرفر، فهو نسخة واحدة فقط من ثلاث، وانقل المجلد بعدها إلى مكان آخر بأداة مثل restic كما في مقارنة أدوات النسخ الاحتياطي.

والمخرج سوف يكون كما يلي:

total 512
-rw-r--r-- 1 postgres postgres 516657 Oct 10 05:40 app.dump
-rw-r--r-- 1 postgres postgres   1179 Oct 10 05:40 globals.sql

اختبار الاستعادة كل ليلة

النسخة التي لم تجرب استعادتها ليست نسخة، والطريقة الأسهل لتجربتها هي استعادتها في قاعدة مؤقتة على نفس السيرفر ومقارنة عدد الصفوف، ثم حذفها:

#!/bin/sh
# Restores the latest dump into a scratch database and compares row counts with production.
set -eu
LATEST=$(ls -d /backup/*/ | sort | tail -1)
dropdb --if-exists app_restore_test
createdb --owner=app_owner app_restore_test
START=$(date +%s)
pg_restore --dbname=app_restore_test --jobs=2 --exit-on-error "$LATEST/app.dump"
echo "restore took $(( $(date +%s) - START ))s from $LATEST"
for t in shop.customers shop.orders; do
  a=$(psql -XAtc "select count(*) from $t" app)
  b=$(psql -XAtc "select count(*) from $t" app_restore_test)
  [ "$a" = "$b" ] && echo "OK   $t $a rows" || { echo "FAIL $t prod=$a restored=$b"; exit 1; }
done
dropdb app_restore_test

وعند تشغيله أول مرة لم ينجح، وكانت أخطاؤنا مفيدة، ففي النسخة الأولى استخدمنا --no-owner --role=app_owner مع قاعدة يملكها postgres، فظهر الخطأ:

pg_restore: error: could not execute query: ERROR:  permission denied for database app_restore_test
Command was: CREATE SCHEMA shop;

فجعلنا app_owner مالك القاعدة المؤقتة، فظهر الخطأ التالي:

pg_restore: error: could not execute query: ERROR:  permission denied to create extension "pg_stat_statements"
HINT:  Must be superuser to create this extension.
Command was: CREATE EXTENSION IF NOT EXISTS pg_stat_statements WITH SCHEMA public;

والسبب أن هذه الإضافة Extension لا ينشئها إلا المستخدم الخارق، فالحل الأبسط هو أن تعمل الاستعادة باسم postgres دون --no-owner، فتبقى ملكية كل شيء كما كانت في الإنتاج، وهذا ما في السكربت النهائي. والمخرج بعد الإصلاح:

restore took 1s from /backup/2026-10-10T0540Z/
OK   shop.customers 1000 rows
OK   shop.orders 50000 rows

وهنا الفائدة الحقيقية من الاختبار: هذه الأخطاء ظهرت في مختبر هادئ، ولم تظهر في ليلة تعطل فيها السيرفر والإدارة تسأل متى يعود النظام. واجعل السكربت يرسل تنبيهاً عند الفشل، وسجل الوقت الذي أخذته الاستعادة، فهو تقريباً وقت توقفك عند الكارثة.

الاستعادة على سيرفر جديد

الآن نفترض أن السيرفر ضاع، ولدينا الملفان فقط، فنشغل Container جديداً من نفس الصورة ونستعيد ملف البيانات مباشرة:

$ createdb app && pg_restore -d app --exit-on-error /tmp/app.dump
pg_restore: error: could not execute query: ERROR:  role "app_owner" does not exist
Command was: ALTER SCHEMA shop OWNER TO app_owner;

وهذا هو الخطأ الذي يقع فيه من يحفظ ملف pg_dump فقط، فالأدوار ليست فيه. والترتيب الصحيح أن تستعيد الأدوار أولاً، ثم القاعدة بالخيار --create الذي ينشئها بنفس الاسم مع صلاحياتها وإعدادات الأدوار داخلها:

psql -X -q -f /tmp/globals.sql            # "role postgres already exists" is expected
pg_restore --create --dbname=postgres --exit-on-error /tmp/app.dump
psql -XAtc "select count(*) from shop.orders" app
psql -XAt -c "select r.rolname, s.setconfig from pg_db_role_setting s join pg_roles r on r.oid = s.setrole"

والمخرج سوف يكون كما يلي:

restored
50000
app_migrator|{role=app_owner,search_path=shop}
app_user|{search_path=shop,statement_timeout=15s,idle_in_transaction_session_timeout=60s}
app_readonly|{search_path=shop,default_transaction_read_only=on}

لاحظ السطور الثلاثة الأخيرة، فإعدادات الأدوار داخل القاعدة مثل search_path وstatement_timeout لا توجد في globals.sql، وإنما تأتي مع القاعدة عند استخدام --create فقط، فإذا استعدت دون هذا الخيار في قاعدة أنشأتها بنفسك فسوف يعمل التطبيق دون مهلة الاستعلام، وتعود الجداول الجديدة إلى public، ولن تلاحظ ذلك إلا بعد أسابيع. وبقيت خطوة أخيرة: الأدوار استعيدت دون كلمات مرور، فاضبطها بالأمر ALTER ROLE app_user PASSWORD '…' من مكان حفظ الأسرار قبل تشغيل التطبيق.

المراقبة: ماذا تتابع كل يوم؟

لا تحتاج في البداية إلى نظام مراقبة كبير، فPostgreSQL يجمع إحصائياته في جداول النظام كما في وثائق الإحصائيات، وهذه أربعة استعلامات تجيب عن أهم الأسئلة:

-- size, transactions, deadlocks, temp files and cache hits per database
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size,
       numbackends, xact_commit, xact_rollback, deadlocks, temp_files,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 1) AS cache_hit_pct
FROM pg_stat_database WHERE datname IN ('app', 'postgres');
-- who is connected and what are they doing
SELECT usename, state, count(*) FROM pg_stat_activity WHERE backend_type = 'client backend' GROUP BY 1, 2;
-- how close are we to the limit
SELECT (SELECT count(*) FROM pg_stat_activity) AS connections, current_setting('max_connections') AS max;
-- transactions open for more than five minutes
SELECT pid, usename, now() - xact_start AS xact_age, state, left(query, 40) AS query
FROM pg_stat_activity WHERE state <> 'idle' AND xact_start < now() - interval '5 minutes';

والمخرج من المختبر:

 datname  |  size   | numbackends | xact_commit | xact_rollback | deadlocks | temp_files | cache_hit_pct
----------+---------+-------------+-------------+---------------+-----------+------------+---------------
 postgres | 7678 kB |           0 |         162 |             0 |         0 |          0 |          95.1
 app      | 12 MB   |           1 |          70 |             8 |         0 |          0 |          99.7
(2 rows)

 usename  | state  | count
----------+--------+-------
 postgres | active |     1
(1 row)

 connections | max
-------------+-----
           9 | 100
(1 row)

 pid | usename | xact_age | state | query
-----+---------+----------+-------+-------
(0 rows)

وما الذي تبحث عنه في هذه الأرقام؟

• عدد الاتصالات Connections: إذا اقترب من max_connections فالمشكلة غالباً في مجمع الاتصالات وليس في الحد نفسه.
• المعاملات الطويلة Long Transactions: المعاملة المفتوحة دقائق تمسك أقفالاً وتمنع ال VACUUM من تنظيف الصفوف القديمة، وأسبابها وطريقة قراءة الأقفال في مقال الأقفال والجمود Deadlock.
• الجمود Deadlocks والملفات المؤقتة Temp Files: العداد الذي يزيد كل يوم يعني مشكلة في ترتيب الأقفال أو استعلامات ترتب بيانات أكبر من ذاكرتها، وطريقة إصلاح الثانية في مقال ضبط الأداء.
• حجم القاعدة ومساحة القرص: القرص الممتلئ يوقف PostgreSQL عن الكتابة تماماً، فراقب المساحة الحرة بنسبة وليس بالحجم فقط.
• السجل Log: الإعدادات في postgresql.conf أعلاه تسجل الاتصالات المرفوضة، والاستعلامات الأبطأ من نصف ثانية، وانتظار الأقفال، وكل ملف مؤقت، فاقرأه أو أرسله إلى نظام السجلات كما شرحنا في مقال السجلات Logging.

وعندما تحتاج لوحات ورسوماً وتنبيهات، فالطريقة المعتادة هي postgres_exporter مع Prometheus وGrafana، وهو يقرأ نفس هذه الجداول، وأعطه دوراً خاصاً عضواً في pg_monitor وليس المستخدم الخارق. أما أبطأ الاستعلامات بالإضافة pg_stat_statements التي فعلناها في الإعداد، فقد شرحناها في مقال PostgreSQL للجداول الضخمة.

أخطاء شائعة

• التطبيق يتصل بالمستخدم postgres: أي ثغرة في التطبيق تصبح تحكماً كاملاً في القاعدة، بل وتنفيذ أوامر على السيرفر عبر COPY … PROGRAM المسموح للمستخدم الخارق.
• التطبيق يملك الجداول: حتى دون صلاحية المستخدم الخارق، المالك يستطيع DROP وTRUNCATE، فافصل المالك عن الدور الذي يستخدمه التطبيق.
• GRANT … ON ALL TABLES دون صلاحيات افتراضية: يعمل اليوم ويتعطل مع أول جدول جديد.
• المنفذ 5432 مفتوح للإنترنت: بسبب -p 5432:5432 في Docker الذي يتجاوز جدار الحماية، وPostgreSQL المفتوح من أكثر الخدمات التي تفحصها البرامج الآلية بحثاً عن كلمات مرور ضعيفة.
• sslmode=require وتظن أنك محمي: الاتصال مشفر ولكن الكلاينت لا يعرف مع من يتكلم، والحماية الكاملة هي verify-full.
• نسخ pg_dump فقط: دون الأدوار، ودون اختبار استعادة، وعلى نفس القرص.
• الوسم latest في الإنتاج: ترقية رئيسية غير مقصودة تمنع القاعدة من البدء لأن ملفات البيانات من إصدار آخر.

قائمة تحقق

1. هل الإصدار مثبت برقمه الكامل، والتحديثات الصغرى تطبق كل فترة؟
2. هل المنفذ مغلق من الخارج، والإدارة من داخل السيرفر فقط؟
3. هل يوجد دور مالك لا يسجل الدخول، ودور للترحيل، ودور للتطبيق، ودور للقراءة؟
4. هل الصلاحيات الافتراضية مضبوطة للمالك، وهل جربت أن جدولاً جديداً يقرأه التطبيق؟
5. هل جربت أن دور التطبيق لا يستطيع DROP وTRUNCATE وALTER؟
6. هل ينتهي pg_hba.conf بسطر reject، ولا يوجد فيه trust ولا md5؟
7. هل الكلاينت يتصل بالوضع verify-full؟
8. هل النسخة اليومية تحفظ القاعدة والأدوار، وتنقل خارج السيرفر، وتجرب استعادتها آلياً؟
9. هل جربت الاستعادة على سيرفر جديد مرة واحدة على الأقل، وكتبت خطواتها ووقتها؟
10. هل يوجد تنبيه على الاتصالات والمعاملات الطويلة ومساحة القرص؟

الخلاصة

وصلنا لنهاية الموضوع، وأهم ما فيه:

• ثبت PostgreSQL بإصدار مثبت، مع Docker Compose بكلمات مرور من ملفات Secrets ومنفذ على 127.0.0.1، أو على Ubuntu من مستودع PGDG الرسمي، وانتبه لمكان البيانات الجديد في صورة 18.
• أربعة أدوار لكل تطبيق: مالك لا يسجل الدخول، ودور ترحيل يعمل باسم المالك، ودور تطبيق للبيانات فقط، ودور قراءة، وفي المختبر رفض PostgreSQL أوامر DROP وTRUNCATE وALTER من التطبيق.
• الصلاحيات الافتراضية تطبق على ما ينشئه المالك فقط، فاجعل دور الترحيل يعمل باسم المالك وحدد له search_path، وإلا ذهبت الجداول إلى المالك الخطأ أو المخطط الخطأ.
• pg_hba.conf يقرأ من الأعلى ويأخذ أول سطر يطابق، فاجعله ينتهي بالرفض، واسمح بالشبكة مع TLS فقط، وتحقق من هوية السيرفر بالوضع verify-full.
• النسخة المنطقية ملفان: القاعدة بالأداة pg_dump والأدوار بالأداة pg_dumpall --globals-only، والاستعادة على سيرفر جديد تكون بالأدوار أولاً ثم pg_restore --create، وجرب الاستعادة آلياً كل ليلة.
• للاستعادة إلى لحظة قبل خطأ بشري تحتاج النسخ الفيزيائي مع WAL، وهو موضوع المقال التالي.

دليلاستعادة PostgreSQL إلى أي لحظة مع pgBackRestكيف تأخذ نسخاً احتياطية من PostgreSQL مع أرشيف WAL، وتستعيد القاعدة إلى الثانية التي سبقت حذفاً خاطئاً.دليلضبط أداء PostgreSQL بالقياس وليس بالتخمينكيف تضبط ذاكرة PostgreSQL ونقاط التفتيش وال autovacuum، وتقيس أثر كل إعداد على نفس الحمل قبل أن تعتمده.دليلConnection Pooling: لماذا الاتصال بقاعدة البيانات مكلفلماذا تظهر رسالة too many clients عندما تزيد نسخ تطبيقك، وكيف يحل مجمع الاتصالات وPgBouncer هذه المشكلة.دليلالنسخ الاحتياطي بقاعدة 3-2-1 واختبار الاستعادةكيف تحدد كم بيانات تقبل أن تخسر، وتبني نسخاً لا يستطيع المخترق حذفها، وتتأكد أنك تستطيع استعادتها فعلاً.

سجل التحديثات

• أكتوبر 2026: كتابة المقال واختباره على PostgreSQL 18.6 (الصورة postgres:18.6) وعلى Ubuntu 26.04.1 مع الحزمة postgresql-18 من مستودع PGDG، وDocker Engine 29.8.0.