Backup และ security

สองหน้าที่ปฏิบัติการสุดท้ายคือสิ่งที่หวังว่าจะไม่ต้องใช้และข้ามไม่ได้ Backup ให้คุณสร้างใหม่หลังดิสก์พัง deploy พลาด หรือ DELETE ที่ผิดพลาด Security ควบคุมว่าใครเชื่อมต่อได้ ใครเห็นอะไรได้ และข้อมูลเดินทางผ่านเครือข่ายอย่างไร ฐานข้อมูลที่เร็วและพร้อมใช้งานสูงยังเป็นภาระถ้ากู้คืนไม่ได้ หรือถ้าใครบนอินเทอร์เน็ตอ่านมันได้

บทเรียนนี้ครอบคลุมทั้งสองตระกูลของ backup ที่ PostgreSQL มี แล้วเดินลงตามชั้นของการควบคุมการเข้าถึงจากขอบเครือข่ายถึงแต่ละ row ทุกอย่างรันจริงบน PostgreSQL 18.6 (postgres:18.6, aarch64, Docker Desktop บน Mac, กันยายน 2026) ส่วน backup และ network security ใช้คอนเทนเนอร์เดียวที่เริ่มด้วย --network none (ไม่มีเครือข่ายเลย; การเชื่อมต่อผ่าน loopback ของมันเอง) และ volume ที่ตั้งชื่อ postgresql-b3-* ลบทั้งหมดเมื่อจบ ส่วน role และ row-level security เป็น fence sql ที่รันในฐานข้อมูลเปล่าหนึ่งตัว <your-password> แทนค่าใดก็ได้ที่คุณเลือก

Backup สองชนิด

backup ของ PostgreSQL มีสองแบบที่เหมาะกับความต้องการต่างกัน

ชนิด	เครื่องมือ	สิ่งที่มันจับ	เหมาะกับ
Logical	`pg_dump` / `pg_restore`	SQL หรือ archive ของฐานข้อมูลหนึ่งตัว	ฐานข้อมูลเดี่ยว การย้าย version
Physical	base backup + WAL archiving	ไฟล์ของทั้ง cluster แบบ byte ต่อ byte	cluster ใหญ่ point-in-time recovery

แล็บ: ฐานข้อมูลหนึ่งตัวที่เปิด archiving archive_command คัดลอก WAL segment ที่เสร็จแล้วแต่ละอันไปยัง volume ที่อยู่รอดกว่าเซิร์ฟเวอร์ summarize_wal (จำเป็นสำหรับ incremental backup) เปิดอยู่ด้วย

docker volume create postgresql-b3-pgdata
docker volume create postgresql-b3-archive
docker volume create postgresql-b3-backup
# the archive and backup volumes must be writable by the postgres user
docker run --rm --user root -v postgresql-b3-archive:/archive -v postgresql-b3-backup:/backup \
  postgres:18.6 chown postgres:postgres /archive /backup

docker run -d --name postgresql-b3-pg --network none --memory 768m --cpus 4 \
  -e POSTGRES_PASSWORD=<your-password> \
  -v postgresql-b3-pgdata:/var/lib/postgresql -v postgresql-b3-archive:/archive -v postgresql-b3-backup:/backup \
  postgres:18.6 \
  -c archive_mode=on -c 'archive_command=test ! -f /archive/%f && cp %p /archive/%f' -c summarize_wal=on

# 1. one database container with no network at all (--network none), WAL archiving on, and three volumes: data, archive, backups

$ docker run --rm --user root -v postgresql-b3-archive:/archive -v postgresql-b3-backup:/backup postgres:18.6 chown postgres:postgres /archive /backup

$ docker run -d --name postgresql-b3-pg --network none --memory 768m --cpus 4 -e POSTGRES_PASSWORD=<your-password> \
    -v postgresql-b3-pgdata:/var/lib/postgresql -v postgresql-b3-archive:/archive -v postgresql-b3-backup:/backup postgres:18.6 \
    -c archive_mode=on -c 'archive_command=test ! -f /archive/%f && cp %p /archive/%f' -c summarize_wal=on

-- database: script
SELECT name, setting FROM pg_settings WHERE name IN ('archive_mode', 'archive_command', 'summarize_wal', 'wal_level') ORDER BY name;
CREATE DATABASE shop;
-- database: psql output
      name       |                  setting
-----------------+--------------------------------------------
 archive_command | test ! -f /archive/%f && cp %p /archive/%f
 archive_mode    | on
 summarize_wal   | on
 wal_level       | replica
(4 rows)

CREATE DATABASE

-- database: script
CREATE TABLE orders (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id bigint NOT NULL,
  item      text NOT NULL,
  amount    numeric NOT NULL
);
INSERT INTO orders (tenant_id, item, amount)
SELECT g % 5 + 1, 'item-' || g, g % 100 FROM generate_series(1, 200000) g;
SELECT count(*) AS orders, sum(amount) AS total FROM orders;
-- database: psql output
CREATE TABLE
INSERT 0 200000
 orders |  total
--------+---------
 200000 | 9900000
(1 row)

Logical backup

logical backup รัน pg_dump ซึ่งอ่านฐานข้อมูลและเขียนคำสั่งที่จำเป็นเพื่อสร้างมันใหม่ มันพกพาข้ามเครื่องและแม้แต่ข้าม major version จึงเหมาะกับการย้ายหรือ clone ฐานข้อมูลเดี่ยว (docs) มันจับฐานข้อมูลเดียวเท่านั้น; role และวัตถุระดับ cluster อื่นมาจาก pg_dumpall --globals-only รูปแบบ custom ถูกบีบอัดและให้ pg_restore เลือกส่วนและรันขนานได้; ฐานข้อมูลเดียวกันเป็น SQL ธรรมดาใหญ่กว่าสี่เท่าครึ่งที่นี่

$ docker exec --user postgres postgresql-b3-pg pg_dump --format=custom --file=/backup/shop.dump shop

$ docker exec --user postgres postgresql-b3-pg sh -c "pg_dump shop | wc -c; ls -l /backup/shop.dump | awk '{print $5, $9}'"    # plain SQL bytes, then the custom-format file
4559583
1006636 /backup/shop.dump

$ docker exec --user postgres postgresql-b3-pg pg_restore --list /backup/shop.dump | head -12
;
; Archive created at 2026-09-30 08:42:43 UTC
;     dbname: shop
;     TOC Entries: 9
;     Compression: gzip
;     Dump Version: 1.16-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 18.6 (Debian 18.6-1.pgdg13+2)
;     Dumped by pg_dump version: 18.6 (Debian 18.6-1.pgdg13+2)
;

# --create names the database to CONNECT to first; the database restored is the one in the dump

$ docker exec --user postgres postgresql-b3-pg pg_restore --dbname=shop_restored --create /backup/shop.dump    # shop_restored does not exist yet
pg_restore: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  database "shop_restored" does not exist

$ docker exec --user postgres postgresql-b3-pg createdb shop_restored

$ docker exec --user postgres postgresql-b3-pg pg_restore --dbname=shop_restored --jobs=2 /backup/shop.dump

$ docker exec --user postgres postgresql-b3-pg psql -d shop -Atc "SELECT count(*), sum(amount) FROM orders"
200000|9900000

$ docker exec --user postgres postgresql-b3-pg psql -d shop_restored -Atc "SELECT count(*), sum(amount) FROM orders"
200000|9900000

# restoring INTO the cluster under the dump's own name: drop the original, then --create with a maintenance database

$ docker exec --user postgres postgresql-b3-pg dropdb shop

$ docker exec --user postgres postgresql-b3-pg pg_restore --dbname=postgres --create /backup/shop.dump

$ docker exec --user postgres postgresql-b3-pg psql -d shop -Atc "SELECT count(*), sum(amount) FROM orders"
200000|9900000

สองบทเรียนใน transcript dump กับสำเนาที่กู้คืนตรงกันทั้ง count และ sum ซึ่งคือการตรวจที่การซ้อมกู้คืนควรทำ และ --create ไม่ได้แปลว่า “กู้คืนเข้าฐานข้อมูลใหม่ชื่อที่ฉันให้”: --dbname ระบุฐานข้อมูลที่ เชื่อมต่อก่อน (ต้องมีอยู่) และฐานข้อมูลที่ถูกสร้างคือชื่อที่อยู่ใน dump ความพยายามแรกล้มเพราะ shop_restored ไม่มีอยู่ รูปแบบที่ใช้ได้คือ createdb + กู้คืนโดยไม่มี --create หรือ --dbname=postgres --create หลัง drop ต้นฉบับ

Physical backup และ point-in-time recovery

สำหรับ cluster ใหญ่หรือยุ่ง การ dump แบบ logical ช้าเกินไป แทนที่จะทำเช่นนั้น คุณทำ base backup — สำเนาระดับ byte ของไฟล์ข้อมูล — และ archive write-ahead log ต่อเนื่อง การ replay WAL บน base backup ให้คุณกู้คืนไปยังช่วงเวลาใดก็ได้ เรียกว่า point-in-time recovery (PITR, docs)

flowchart LR
  PG[(Primary cluster)] -->|pg_basebackup| Base[Base backup]
  PG -->|archive_command| WAL[Archived WAL segments]
  Base --> Restore[Restore]
  WAL -->|replay to a chosen time| Restore
  Restore --> Recovered[(Recovered cluster)]

Base backup บวม WAL ที่ archive ไว้ทำให้ทำ point-in-time recovery ได้

pg_basebackup ทำ base backup และ pg_verifybackup ตรวจมันกับ manifest ที่มันเขียน PostgreSQL 17 เพิ่ม backup แบบ incremental: หลัง full backup อันที่สองคัดลอกเฉพาะ block ที่เปลี่ยน (สิ่งที่ summarize_wal บันทึก) และ pg_combinebackup รวมสายกลับเป็น full backup (release notes, docs)

$ docker exec --user postgres postgresql-b3-pg pg_basebackup --pgdata=/backup/full --checkpoint=fast --verbose
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/7000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_227"
pg_basebackup: write-ahead log end point: 0/7000120
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

$ docker exec --user postgres postgresql-b3-pg pg_verifybackup /backup/full
backup successfully verified

$ docker exec --user postgres postgresql-b3-pg du -sh /backup/full
88M  /backup/full

-- database: script
INSERT INTO orders (tenant_id, item, amount) VALUES (1, 'after-backup-1', 10), (2, 'after-backup-2', 20), (3, 'after-backup-3', 30);
INSERT INTO orders (tenant_id, item, amount) SELECT g % 5 + 1, 'bulk-' || g, g % 100 FROM generate_series(1, 50000) g;
SELECT count(*) AS orders FROM orders;
-- database: psql output
INSERT 0 3
INSERT 0 50000
 orders
--------
 250003
(1 row)

# an incremental backup: only the blocks changed since the full one (needs summarize_wal = on)

$ docker exec --user postgres postgresql-b3-pg pg_basebackup --pgdata=/backup/incr1 --incremental=/backup/full/backup_manifest --checkpoint=fast

$ docker exec --user postgres postgresql-b3-pg du -sh /backup/full /backup/incr1
88M  /backup/full
30M  /backup/incr1

$ docker exec --user postgres postgresql-b3-pg pg_combinebackup /backup/full /backup/incr1 --output=/backup/combined

$ docker exec --user postgres postgresql-b3-pg pg_verifybackup /backup/combined
backup successfully verified

incremental backup ขนาด 30 MB เทียบกับ 88 MB ของ full หลังเพิ่ม 50 003 row ใน table 200 000 row ผลรวมที่ได้ verify ได้เหมือน base backup ใด ๆ

ทีนี้ภัยพิบัติ มี base backup อยู่ เพิ่มสาม row หลังมัน และมีคนรัน DELETE FROM orders โดยไม่มี WHERE สองวินาทีก่อนความผิดพลาดเราจดเวลาไว้ pg_switch_wal() ปิด WAL segment ปัจจุบันเพื่อให้ถูก archive และ pg_stat_archiver กับรายการ archive แสดงว่า archive ได้รับ segment (archive แล้ว 13 ล้ม 0):

-- database: script
SELECT pg_sleep(2);
SELECT to_char(clock_timestamp(), 'YYYY-MM-DD HH24:MI:SS.US') || '+00' AS t1 \gset
SELECT :'t1' AS restore_target_time;
SELECT pg_sleep(2);
DELETE FROM orders;   -- the mistake
SELECT pg_switch_wal() IS NOT NULL AS wal_switched;
SELECT count(*) AS orders_now FROM orders;
-- database: psql output
 pg_sleep
----------

(1 row)

      restore_target_time
-------------------------------
 2026-09-30 08:42:49.102022+00
(1 row)

 pg_sleep
----------

(1 row)

DELETE 250003
 wal_switched
--------------
 t
(1 row)

 orders_now
------------
          0
(1 row)

-- database: script
SELECT archived_count, failed_count, last_archived_wal FROM pg_stat_archiver;
-- database: psql output
 archived_count | failed_count |    last_archived_wal
----------------+--------------+--------------------------
             13 |            0 | 00000001000000000000000B
(1 row)

$ docker exec --user postgres postgresql-b3-pg ls /archive | tail -4
000000010000000000000009
000000010000000000000009.00000028.backup
00000001000000000000000A
00000001000000000000000B

การกู้คืนคือ data directory ใหม่ จึงเกิดห่างจากเซิร์ฟเวอร์ที่เสียหาย คัดลอก base backup เข้า directory ว่าง สร้างไฟล์ recovery.signal (ที่บอกเซิร์ฟเวอร์ให้เริ่มใน archive recovery) และให้การตั้งค่าสามอย่าง: restore_command (วิธีดึง segment ที่ archive ไว้; %f คือชื่อไฟล์ และ %p คือ path ที่จะคัดลอกไป) recovery_target_time และ recovery_target_action = 'promote' (เปิดให้เขียนเมื่อถึงเป้าหมาย) log แสดงสิ่งที่ PostgreSQL ทำ:

# restore: copy the base backup into an empty data directory, ask for recovery, and say where to fetch WAL and when to stop

$ docker run --rm --user postgres -v postgresql-b3-backup:/backup:ro -v postgresql-b3-restore-data:/var/lib/postgresql postgres:18.6 sh -c \
    'mkdir -p /var/lib/postgresql/18/docker && cp -a /backup/full/. /var/lib/postgresql/18/docker/ && chmod 700 /var/lib/postgresql/18/docker && touch /var/lib/postgresql/18/docker/recovery.signal'

# appended to postgresql.auto.conf in the restored data directory:
restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-09-30 08:42:49.102022+00'
recovery_target_action = 'promote'

$ docker run -d --name postgresql-b3-restore --network none --memory 768m -v postgresql-b3-restore-data:/var/lib/postgresql -v postgresql-b3-archive:/archive:ro postgres:18.6

$ docker logs postgresql-b3-restore 2>&1 | grep -E "recovery|restored log file|redo|promot|consistent"   (timestamps and process ids removed)
LOG:  starting backup recovery with redo LSN 0/7000028, checkpoint LSN 0/7000080, on timeline ID 1
LOG:  restored log file "000000010000000000000007" from archive
LOG:  starting point-in-time recovery to 2026-09-30 08:42:49.102022+00
LOG:  redo starts at 0/7000028
LOG:  restored log file "000000010000000000000008" from archive
LOG:  completed backup recovery with redo LSN 0/7000028 and end LSN 0/7000120
LOG:  consistent recovery state reached at 0/7000120
LOG:  restored log file "000000010000000000000009" from archive
LOG:  restored log file "00000001000000000000000A" from archive
LOG:  restored log file "00000001000000000000000B" from archive
LOG:  recovery stopping before commit of transaction 773, time 2026-09-30 08:42:51.197887+00
LOG:  redo done at 0/BC8E170 system usage: CPU: user: 0.09 s, system: 0.01 s, elapsed: 0.17 s
LOG:  restored log file "00000001000000000000000B" from archive
LOG:  archive recovery complete

-- restored server: script
SELECT pg_is_in_recovery() AS still_recovering;
\c shop
SELECT count(*) AS orders, count(*) FILTER (WHERE item LIKE 'after-backup%') AS after_backup_rows FROM orders;
-- restored server: psql output
 still_recovering
------------------
 f
(1 row)

You are now connected to database "shop" as user "postgres".
 orders | after_backup_rows
--------+-------------------
 250003 |                 3
(1 row)

มันดึง segment 7 ถึง B จาก archive replay และหยุด ก่อน commit ของ transaction 773 คือ DELETE ที่เวลา commit อยู่หลังเป้าหมายของเรา เซิร์ฟเวอร์ที่กู้คืนไม่อยู่ใน recovery แล้ว และ orders มี 250 003 row: 200 000 จาก backup บวม 3 กับ 50 000 ที่เขียนทีหลัง มีแต่ความผิดพลาดที่หายไป นั่นคือประเด็นของ PITR: backup อย่างเดียวจะได้ 200 000 row และเสียงานที่ทำทีหลัง

สามข้อควรระวัง archive_command ต้องรายงานความล้มเหลวตรงไปตรงมา (test ! -f ... && cp ... ปฏิเสธการเขียนทับไฟล์ที่มีอยู่); ถ้ามันล้มต่อเนื่อง WAL กองบน primary จนดิสก์เต็ม backup และ WAL ของมันต้องเก็บที่ที่ความล้มเหลวของ primary เอาไปด้วยไม่ได้ และทั้งหมดนี้ไม่ใช่ backup จนกว่าจะกู้คืนจากมันสำเร็จ ซึ่งคือสิ่งที่ transcript นี้ทำ

ใน pgAdmin: เมนู Tools มี dialog Backup และ Restore ที่รัน pg_dump และ pg_restore ให้ ส่วน physical backup และ PITR ข้างบนทำจาก command line

Security เป็นชั้น

การควบคุมการเข้าถึงใน PostgreSQL มองเป็นชุดประตูที่ request ผ่านได้ดีที่สุด: เครือข่ายก่อน แล้ว authentication แล้ว privilege ของ role และสุดท้ายกฎระดับ row แต่ละชั้นหยุด request ที่ชั้นก่อนหน้าปล่อยผ่านมาได้

flowchart LR
  Client[Client] -->|encrypted with TLS| HBA["pg_hba.conf - who may connect"]
  HBA --> Auth[Authenticate the role]
  Auth --> Priv["Role privileges - GRANT and REVOKE"]
  Priv --> RLS[Row-level security policy]
  RLS --> Data[(Table rows)]

connection ผ่านประตูเครือข่าย, auth, privilege และ row

ประตูเครือข่าย: pg_hba.conf และ TLS

ประตูแรกคือ pg_hba.conf (host-based authentication) แต่ละบรรทัดบอกว่า role ไหนเชื่อมต่อฐานข้อมูลไหนจากที่อยู่ไหนได้ และต้องพิสูจน์ตัวตนอย่างไร กฎอ่านจากบนลงล่างและ อันแรกที่ตรงชนะ (docs) รหัสผ่านถูกเก็บเป็น SCRAM verifier โดย default (password_encryption = scram-sha-256); ค่าที่เก็บขึ้นต้นด้วย SCRAM-SHA-256$:

-- database: script
SHOW password_encryption;
CREATE ROLE app LOGIN PASSWORD '<your-password>';
SELECT rolname, left(rolpassword, 14) AS stored_as FROM pg_authid WHERE rolname = 'app';
-- database: psql output
 password_encryption
---------------------
 scram-sha-256
(1 row)

CREATE ROLE
 rolname |   stored_as
---------+----------------
 app     | SCRAM-SHA-256$
(1 row)

# 1. first match wins: the reject line for app comes before the general rule

# pg_hba.conf (reject before allow):
local   all   all                    trust
host    all   app   127.0.0.1/32      reject
host    all   all   127.0.0.1/32      scram-sha-256

$ docker exec --user postgres postgresql-b3-pg psql "host=127.0.0.1 user=app dbname=shop" -Atc "SELECT current_user AS connected_as"
psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  pg_hba.conf rejects connection for host "127.0.0.1", user "app", database "shop", no encryption

# pg_hba.conf (allow app first):
local   all   all                    trust
host    all   app   127.0.0.1/32      scram-sha-256
host    all   all   127.0.0.1/32      reject

$ docker exec --user postgres postgresql-b3-pg psql "host=127.0.0.1 user=app dbname=shop" -Atc "SELECT current_user AS connected_as"
app

$ docker exec --user postgres postgresql-b3-pg psql "host=127.0.0.1 user=postgres dbname=shop" -Atc "SELECT 1"
psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  pg_hba.conf rejects connection for host "127.0.0.1", user "postgres", database "shop", no encryption

เมื่อบรรทัด reject มาก่อน app ถูกปฏิเสธแม้กฎข้างล่างจะอนุญาต; เมื่อบรรทัด allow มาก่อน app เข้าได้ และตอนนี้ คนอื่นทุกคน รวม postgres ชนบรรทัด reject เพราะมันตรงด้วย ข้อความ error ระบุกฎที่ทำงาน: pg_hba.conf rejects connection ผิดลำดับในไฟล์นี้เป็นวิธีที่พบบ่อยที่ล็อกตัวเองออก จึงควรเก็บบรรทัด local socket ไว้สำหรับผู้ดูแล

คีย์เวิร์ด hostssl บังคับให้การเชื่อมต่อเข้ารหัสด้วย TLS ซึ่งคุณเปิดโดยชี้ PostgreSQL ไปที่ certificate และ key แล็บสร้าง certificate แบบ self-signed ที่มีอายุหนึ่งวัน เซิร์ฟเวอร์จริงใช้อันจาก certificate authority ของคุณ เมื่อมีเฉพาะบรรทัด hostssl สำหรับ app ความพยายามที่ไม่เข้ารหัสตกไปที่บรรทัด reject และแบบเข้ารหัสถูกรับ:

# 2. TLS: a self-signed certificate made for this lab (never use one like it in production)

$ docker exec --user postgres postgresql-b3-pg sh -c "cd /var/lib/postgresql/18/docker && openssl req -new -x509 -nodes -days 1 -subj '/CN=localhost' -keyout server.key -out server.crt && chmod 600 server.key"

-- database: script
ALTER SYSTEM SET ssl = on;
SELECT pg_reload_conf();
-- database: psql output
ALTER SYSTEM
 pg_reload_conf
----------------
 t
(1 row)

# pg_hba.conf (hostssl only for app):
local   all   all                    trust
hostssl all   app   127.0.0.1/32      scram-sha-256
host    all   all   127.0.0.1/32      reject

$ docker exec --user postgres postgresql-b3-pg psql "host=127.0.0.1 user=app dbname=shop sslmode=disable" -Atc "SELECT current_user AS connected_as"
psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  pg_hba.conf rejects connection for host "127.0.0.1", user "app", database "shop", no encryption

$ docker exec --user postgres postgresql-b3-pg psql "host=127.0.0.1 user=app dbname=shop sslmode=require" -Atc "SELECT ssl, version AS tls_version FROM pg_stat_ssl WHERE pid = pg_backend_pid()"
t|TLSv1.3

pg_stat_ssl ยืนยันว่า session เข้ารหัสจริง TLS 1.3 ที่นี่ hash รหัสผ่าน MD5 แบบเก่ายังใช้ได้แต่ deprecated: การสร้างวันนี้พิมพ์คำเตือนว่า MD5 password support “will be removed in a future release” จึงอย่าสร้าง role ใหม่บนมัน:

-- sql/md5-warning.sql
SHOW password_encryption;
SET password_encryption = 'md5';
CREATE ROLE shop_legacy LOGIN PASSWORD '<your-password>';
RESET password_encryption;
DROP ROLE shop_legacy;

 password_encryption
---------------------
 scram-sha-256
(1 row)

SET
WARNING:  setting an MD5-encrypted password
DETAIL:  MD5 password support is deprecated and will be removed in a future release of PostgreSQL.
HINT:  Refer to the PostgreSQL documentation for details about migrating to another password type.
CREATE ROLE
RESET
DROP ROLE

Role และ privilege: GRANT และ REVOKE

เมื่อเชื่อมต่อแล้ว สิ่งที่ role ทำได้ถูกควบคุมด้วย privilege คุณสร้าง role แล้ว GRANT สิทธิ์เฉพาะและ REVOKE หลักการนำทางคือ least privilege: ให้แต่ละ role แค่สิ่งที่ต้องการ fence ด้านล่างใช้สาม role ที่ไม่มี login (จึง switch เข้าได้ด้วย SET ROLE): เจ้าของ table, role รายงาน และแอป

-- sql/setup.sql
CREATE ROLE shop_owner NOLOGIN;      -- owns the tables
CREATE ROLE shop_reporting NOLOGIN;  -- read-only reports
CREATE ROLE shop_app NOLOGIN;        -- the application
GRANT CREATE ON SCHEMA public TO shop_owner;

SET ROLE shop_owner;
CREATE TABLE orders (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id bigint NOT NULL,
  item      text NOT NULL,
  amount    numeric NOT NULL
);
INSERT INTO orders (tenant_id, item, amount) VALUES (1, 'lamp', 30), (1, 'desk', 200), (2, 'chair', 80);
RESET ROLE;

ตั้งแต่ PostgreSQL 15 role ธรรมดาสร้างวัตถุใน schema public โดย default ไม่ได้อีก role ของแอปสร้าง table ไม่ได้แม้แต่ตารางเดียวจนกว่าจะมีคนให้สิทธิ์:

-- @expect-error 42501
SET ROLE shop_app;
CREATE TABLE sneaky (x int);
RESET ROLE;

SET
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE sneaky (x int);
                     ^
RESET

ทีนี้ role รายงานแบบอ่านอย่างเดียวและ role ของแอปได้สิ่งที่ต้องการพอดี และ role รายงานเปลี่ยนอะไรไม่ได้:

-- sql/grants.sql
GRANT USAGE ON SCHEMA public TO shop_reporting, shop_app;
GRANT SELECT ON orders TO shop_reporting;
GRANT SELECT, INSERT, UPDATE ON orders TO shop_app;

SET ROLE shop_reporting;
SELECT count(*) AS reporting_sees FROM orders;
RESET ROLE;

GRANT
GRANT
GRANT
SET
 reporting_sees
----------------
              3
(1 row)

RESET

-- @expect-error 42501
SET ROLE shop_reporting;
DELETE FROM orders WHERE id = 1;
RESET ROLE;

SET
ERROR:  permission denied for table orders
RESET

หนึ่งกับดัก: GRANT SELECT ON ALL TABLES ครอบคลุม table ที่มีอยู่ตอนนี้ ไม่ใช่ที่สร้างทีหลัง table ใหม่อ่านไม่ได้จนกว่าจะ grant; ALTER DEFAULT PRIVILEGES แก้อนาคต:

-- sql/refunds.sql
SET ROLE shop_owner;
CREATE TABLE refunds (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, order_id bigint NOT NULL);
RESET ROLE;

-- @expect-error 42501
SET ROLE shop_reporting;
SELECT count(*) FROM refunds;
RESET ROLE;

SET
ERROR:  permission denied for table refunds
RESET

-- sql/default-privileges.sql
ALTER DEFAULT PRIVILEGES FOR ROLE shop_owner IN SCHEMA public GRANT SELECT ON TABLES TO shop_reporting;

SET ROLE shop_owner;
CREATE TABLE payouts (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, amount numeric NOT NULL);
RESET ROLE;

SET ROLE shop_reporting;
SELECT count(*) AS payouts_readable FROM payouts;
RESET ROLE;

ALTER DEFAULT PRIVILEGES
SET
CREATE TABLE
RESET
SET
 payouts_readable
------------------
                0
(1 row)

RESET

Row-level security

privilege ควบคุมทั้ง table Row-level security (RLS) ละเอียดกว่า ให้ policy ตัดสินว่า role เห็นหรือเปลี่ยน row ไหนได้ — รากฐานของระบบ multi-tenant ที่แต่ละ tenant ต้องเห็นเฉพาะข้อมูลของตัวเอง (docs) policy ด้านล่างเทียบ tenant_id ของแต่ละ row กับค่าที่แอปเลือกสำหรับ session ของมัน

-- sql/rls.sql
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;   -- run as the owner in real life; the superuser session here is equivalent
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::bigint);

เมื่อไม่มีค่าตั้ง query แรกไม่ได้คืนว่างเงียบ ๆ แต่ล้ม:

-- @expect-error 42704
SET ROLE shop_app;
SELECT count(*) FROM orders;
RESET ROLE;

SET
ERROR:  unrecognized configuration parameter "app.tenant_id"
RESET

เมื่อ app.tenant_id = 1 role ของแอปเห็นเฉพาะ row ของ tenant 1 และ insert row ของ tenant อื่นไม่ได้ (เงื่อนไขของ policy ใช้กับ row ใหม่ด้วย):

-- sql/rls-tenant.sql
SET app.tenant_id = 1;
SET ROLE shop_app;
SELECT id, item FROM orders ORDER BY id;
RESET ROLE;

SET
SET
 id | item
----+------
  1 | lamp
  2 | desk
(2 rows)

RESET

-- @expect-error 42501
SET app.tenant_id = 1;
SET ROLE shop_app;
INSERT INTO orders (tenant_id, item, amount) VALUES (2, 'sneaky', 1);
RESET ROLE;

SET
SET
ERROR:  new row violates row-level security policy for table "orders"
RESET

Reading AI-generated code

ผู้ช่วย AI ถูกขอให้แยก tenant และเขียน policy ที่ถูกต้องพร้อมคอมเมนต์ที่มั่นใจ:

🐛 หา bug ในโค้ดนี้

-- AI comment: "tenant isolation is enforced by RLS, so even a buggy query cannot leak"
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
USING (tenant_id = current_setting('app.tenant_id')::bigint);
-- (the application connects as shop_owner, the role that created the table)

เฉลย

policy ถูก และมันไม่ปกป้องใครที่เชื่อมต่อในฐานะเจ้าของ table: เจ้าของ table ข้าม row-level security เว้นแต่บังคับ (superuser และ role ที่มี BYPASSRLS ข้ามเสมอ) เมื่อ app.tenant_id = 1 policy อนุญาตสอง row และเจ้าของเห็นทั้งสาม:

-- sql/owner-bypass.sql
SET app.tenant_id = 1;
SET ROLE shop_owner;
SELECT count(*) AS owner_sees FROM orders;   -- the policy says tenant 1 only; the owner ignores it
RESET ROLE;

SET
SET
 owner_sees
------------
          3
(1 row)

RESET

วิธีแก้คือเชื่อมต่อแอปเป็น role อื่นที่ไม่มีสิทธิ์พิเศษ (ตัวเลือกที่ดีที่สุด) หรือทำให้ policy ใช้กับเจ้าของด้วย FORCE ROW LEVEL SECURITY:

-- sql/force-rls.sql
ALTER TABLE orders FORCE ROW LEVEL SECURITY;
SET app.tenant_id = 1;
SET ROLE shop_owner;
SELECT count(*) AS owner_sees_now FROM orders;
RESET ROLE;

ALTER TABLE
SET
SET
 owner_sees_now
----------------
              2
(1 row)

RESET

อีกสองรายละเอียดของ policy ที่ล้มอย่างปลอดภัย หลังจากค่าตั้งแบบ custom ถูกใช้ใน session RESET ทิ้งมันเป็น string ว่าง และ ''::bigint เป็น error ไม่ใช่ “ไม่มี tenant”:

-- @expect-error 22P02
SET app.tenant_id = 1;
RESET app.tenant_id;   -- the setting now exists, with an empty string as its value
SET ROLE shop_app;
SELECT count(*) FROM orders;
RESET ROLE;

SET
RESET
SET
ERROR:  invalid input syntax for type bigint: ""
RESET

ดังนั้นเขียน policy ให้ fail closed: current_setting(name, true) คืน NULL เมื่อไม่มีค่าตั้ง nullif(..., '') เปลี่ยน string ว่างเป็น NULL และการเทียบกับ NULL ไม่ตรงกับ row ใดเลย role ที่สร้างด้วย BYPASSRLS เช่น auditor ยังเห็นทุกอย่าง แม้ภายใต้ FORCE:

-- sql/fail-closed.sql
DROP POLICY tenant_isolation ON orders;
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = nullif(current_setting('app.tenant_id', true), '')::bigint);

SET ROLE shop_app;
SELECT count(*) AS setting_missing FROM orders;
SET app.tenant_id = 2;
SELECT count(*) AS tenant_two FROM orders;
RESET ROLE;

DROP POLICY
CREATE POLICY
SET
 setting_missing
-----------------
               0
(1 row)

SET
 tenant_two
------------
          1
(1 row)

RESET

-- sql/bypassrls.sql
CREATE ROLE shop_auditor NOLOGIN BYPASSRLS;
GRANT SELECT ON orders TO shop_auditor;
SET ROLE shop_auditor;
SELECT count(*) AS auditor_sees FROM orders;
RESET ROLE;

CREATE ROLE
GRANT
SET
 auditor_sees
--------------
            3
(1 row)

RESET

เมื่อเสร็จกับแล็บ ลบมัน:

docker rm -f postgresql-b3-pg postgresql-b3-restore
docker volume rm postgresql-b3-pgdata postgresql-b3-archive postgresql-b3-backup postgresql-b3-restore-data

Mental Model

backup คือไทม์แมชชีน และ WAL archive คือฟิล์มที่ฉายระหว่าง snapshot dump คือภาพถ่ายของห้องหนึ่ง base backup บวม WAL คือการบันทึกทั้งอาคารที่คุณย้อนไปวินาทีใดก็ได้ security คือชุดประตู แต่ละบานแคบกว่าบานก่อน: เส้นเครือข่ายตัดสินว่าใครเคาะได้ role ตัดสินว่าเข้าห้องไหนได้ และ row-level security ตัดสินว่ามองชั้นวางไหนได้ ประตูถูกตรวจตามลำดับ และกฎแรกที่ตรงตัดสิน

เคล็ดลับและข้อควรระวัง

backup จะเป็นจริงก็ต่อเมื่อคุณกู้คืนสำเร็จแล้ว ตั้งเวลาซ้อมกู้คืนเข้าฐานข้อมูลทิ้งขว้างเป็นประจำและเทียบ count เหมือนที่แล็บทำ
logical dump พกพาและเหมาะกับฐานข้อมูลเดี่ยว; physical base backup บวม WAL archiving คือสิ่งที่ต้องใช้กับ cluster ใหญ่และ point-in-time recovery pg_dump ไม่รวม role; pg_dumpall --globals-only รวม
จดเวลาเป้าหมายกู้คืนที่แน่นอนก่อนการทำงานเสี่ยง หรือสร้าง restore point ที่มีชื่อด้วย pg_create_restore_point()
ใช้ least privilege ทุกที่ role ของแอปไม่ควรเป็นเจ้าของ schema หรือ drop table ได้ และรายงานควรอ่านอย่างเดียว
อย่าใช้ trust authentication บนที่อยู่ใดที่เข้าถึงได้จากนอกโฮสต์ — มันให้ใครก็ได้เชื่อมต่อเป็น role ใดก็ได้โดยไม่ต้องใช้รหัสผ่าน
บังคับ TLS สำหรับการเชื่อมต่อที่ข้ามเครือข่ายด้วย hostssl และจำว่ากฎ pg_hba.conf จับคู่จากบนลงล่าง อันแรกที่ตรงชนะ
เชื่อมต่อแอปด้วย role ที่ไม่เป็นเจ้าของ table และไม่มี BYPASSRLS ไม่งั้น RLS จะไม่ใช้กับมัน

ข้อแลกเปลี่ยน

ตัวเลือก	Benefit	Cost
Logical backup (`pg_dump`)	พกพา เลือกได้ archive 1 MB สำหรับ table 200 000 row	ทีละฐานข้อมูล กู้คืนด้วยการ replay SQL ไม่มี point-in-time
Physical backup + WAL archive	ช่วงเวลาใดก็ได้ (การกู้คืนหยุดก่อน `DELETE`) ทั้ง cluster	major version เดียวกัน ชิ้นส่วนเคลื่อนไหวมากขึ้น archive ห้ามล้ม
Incremental backup	30 MB แทน 88 MB	ต้องมี `summarize_wal` และสายที่ต้องรวมก่อนกู้คืน
RLS กับ policy เข้มงวด	การแยกที่ฐานข้อมูลบังคับ	เจ้าของและ role `BYPASSRLS` ข้ามมัน; ค่าตั้งที่หายทำให้ error เว้นแต่เขียน policy ให้ fail closed
พึ่ง replication อย่างเดียวเพื่อความคงทน	failover เร็ว high availability	replica ไม่ใช่ backup — มันคัดลอกความผิดพลาดและการลบด้วย

Common Misconceptions

“มี backup แล้ว ฉันปลอดภัย” มีแต่การกู้คืนที่พิสูจน์ แล็บรันหนึ่งครั้งและเทียบ count
“pg_restore —create กู้คืนเข้าฐานข้อมูลที่ฉันระบุด้วย —dbname” มันเชื่อมต่อที่นั่นและสร้างฐานข้อมูลชื่อที่อยู่ใน dump
“Replication คือ backup” DELETE จะไปถึงทุก replica ภายในมิลลิวินาที
“RLS ปกป้องทุก connection” ไม่ใช่เจ้าของ ไม่ใช่ superuser ไม่ใช่ role BYPASSRLS; เจ้าของเห็น 3 row เทียบ 2 ของ policy
“GRANT SELECT ON ALL TABLES ครอบคลุม table อนาคต” table refunds ใหม่อ่านไม่ได้จน ALTER DEFAULT PRIVILEGES