Posted inDatabase

Làm chủ pgAudit trong PostgreSQL: Hướng dẫn Audit Logging và Giám sát Bảo mật Production

Database tutorial - IT technology blog
Database tutorial - IT technology blog

Khi Log mặc định bất lực: Cái giá tiềm ẩn của các truy vấn không được theo dõi

Một truy vấn UPDATE bất thường xảy ra trên cluster production của chúng tôi vào lúc 2:15 sáng, làm thay đổi trạng thái tài khoản của hơn 14.000 khách hàng đang hoạt động. Khi đội ngũ bảo mật yêu cầu cung cấp IP nguồn, database user và các tham số được truyền vào (bound parameters), các file log chuẩn của PostgreSQL hoàn toàn không có thông tin gì.

Hệ thống của chúng tôi chỉ ghi nhận deadlock, lỗi và các truy vấn chạy quá 250ms. Nhưng các thao tác sửa đổi dữ liệu thông thường và các truy vấn đọc đã lọt qua mà không để lại dấu vết. Đối với các đội ngũ phải tuân thủ các tiêu chuẩn như SOC 2 Type II, HIPAA hay PCI-DSS v4.0, việc thiếu một audit trail bất biến (immutable) cho các truy cập dữ liệu nhạy cảm có thể khiến đợt đánh giá tuân thủ thất bại ngay lập tức.

Tại sao cơ chế Log mặc định của PostgreSQL lại chưa đủ

PostgreSQL tích hợp sẵn các cờ ghi log như log_statement = 'all' và log_min_duration_statement. Dù rất dễ bật tắt, các thiết lập này lại gây ra nhiều vấn đề vận hành nghiêm trọng trong môi trường production có tải cao:

  • Không nhắm mục tiêu ở cấp object: Thiết lập log_statement = 'all' sẽ ghi lại mọi truy vấn trên toàn bộ cluster. Trên một hệ thống xử lý 15.000 QPS, điều này tạo ra hơn 60 GB log thô mỗi giờ, chôn vùi các sự kiện bảo mật quan trọng trong hàng tá log nhiễu thông thường.
  • Điểm mù đối với các thao tác đọc: Các thiết lập mặc định không thể theo dõi có chọn lọc các truy vấn SELECT trên từng bảng cụ thể. Bạn buộc phải ghi log mọi thao tác đọc trên toàn database hoặc không ghi gì cả.
  • Thiếu phân loại sự kiện có cấu trúc: Log mặc định không phân loại thao tác theo mức độ rủi ro (chẳng hạn như thay đổi schema, sửa đổi role hay thao tác ghi). Điều này khiến việc phân tích tự động (parsing) và gửi cảnh báo đến hệ thống SIEM trở nên khó khăn không cần thiết.

So sánh các chiến lược Audit trong PostgreSQL

Các kỹ sư database thường cân nhắc ba giải pháp khi thiết kế kiến trúc audit:

1. Native Statement Logging (log_statement)

Cấu hình log_statement = 'ddl' hoặc 'mod' trong postgresql.conf cung cấp khả năng hiển thị cơ bản đối với các thay đổi cấu trúc. Dù vậy, nó không thể nhắm mục tiêu vào từng bảng riêng lẻ, thường bỏ sót các giá trị tham số hóa và gây áp lực I/O đĩa lớn khi có tải ghi liên tục.

2. Custom Table Triggers

Viết các trigger AFTER INSERT OR UPDATE OR DELETE sẽ đẩy các thay đổi dữ liệu theo từng dòng trực tiếp vào bảng audit nội bộ. Trigger hoạt động tốt để theo dõi lịch sử chỉnh sửa. Tuy nhiên, chúng không thể audit các truy vấn SELECT, làm tăng độ trễ ghi từ 10% đến 25%, gây phình to database (bloat), và superuser có thể vô hiệu hóa chúng bất kỳ lúc nào.

3. Extension pgAudit

Extension pgAudit can thiệp trực tiếp vào bộ thực thi truy vấn (query executor) và bộ phân tích cú pháp (parser) của PostgreSQL. Nó ghi log các sự kiện dựa trên các danh mục có cấu trúc (DDL, Role, Read, Write) hoặc theo quyền trên từng bảng cụ thể. Trong các bài benchmark trên production, pgAudit thường chỉ chiếm thêm dưới 2% đến 3% CPU overhead trong khi vẫn giữ cho log kiểm toán bảo mật sạch sẽ và tách biệt.

Hướng dẫn từng bước thiết lập pgAudit trên Production

Bước 1: Cài đặt Package pgAudit

Trên Debian hoặc Ubuntu chạy PostgreSQL 16, cài đặt package tương ứng với phiên bản database engine của bạn:

sudo apt-get update
sudo apt-get install -y postgresql-16-pgaudit

Trên môi trường RHEL 9 hoặc Rocky Linux:

sudo dnf install -y pgaudit16_16

Bước 2: Cập nhật cấu hình Server và Preload thư viện

Vì pgAudit sử dụng các hook trong bộ nhớ chia sẻ (shared memory), bạn phải khai báo extension trong postgresql.conf và khởi động lại PostgreSQL:

# Chỉnh sửa file postgresql.conf
shared_preload_libraries = 'pgaudit'

# Các tham số cấu hình audit logging
pgaudit.log = 'role, ddl, write'
pgaudit.log_catalog = off
pgaudit.log_parameter = on
pgaudit.log_relation = on
pgaudit.log_statement_once = off

Khởi động lại service systemd để load shared library:

sudo systemctl restart postgresql

Đăng nhập vào database cluster bằng psql và kích hoạt extension:

CREATE EXTENSION pgaudit;

Bước 3: Thiết lập Auditing ở cấp Object

Audit mọi truy vấn trên toàn bộ database sẽ gây lãng phí dung lượng đĩa và I/O. Phương pháp tối ưu nhất là object-level auditing, giúp thu hẹp phạm vi giám sát vào các schema kinh doanh nhạy cảm và các bảng quan trọng đối với yêu cầu tuân thủ.

Đầu tiên, tạo một audit role không có đặc quyền và không có quyền đăng nhập:

-- Tạo role chuyên dụng cho auditor
CREATE ROLE auditor NOLOGIN;

-- Chỉ định pgAudit theo dõi các quyền gắn với role này
ALTER SYSTEM SET pgaudit.role = 'auditor';
SELECT pg_reload_conf();

Tiếp theo, cấp các quyền cụ thể cho audit role này chỉ trên những bảng cần theo dõi tuân thủ:

-- Theo dõi toàn bộ hoạt động DML và truy vấn đọc trên các bảng quan trọng
GRANT SELECT, INSERT, UPDATE, DELETE ON customer_payments TO auditor;
GRANT SELECT ON user_credentials TO auditor;

Giờ đây, bất kỳ câu lệnh nào đọc từ hoặc ghi vào customer_payments đều sẽ tạo ra một bản ghi audit chi tiết. Các thao tác thông thường trên hàng đợi job chạy nền và các bảng cache sẽ không bị ghi log.

Khi tạo dữ liệu mẫu (seed) cho môi trường staging, chúng tôi cần các bản ghi thanh toán giả lập từ các bảng tính thô. Để tạo nhanh test fixture, tôi sử dụng toolcraft.app/vi/tools/data/csv-to-json vì công cụ này xử lý file cục bộ ngay trên trình duyệt. Điều này giúp giữ dữ liệu fixture nhạy cảm an toàn trên máy trạm của bạn mà không cần gửi qua bất kỳ API web bên ngoài nào.

Bước 4: Phân tích và kiểm tra định dạng Output của pgAudit

Sau khi được kích hoạt, pgAudit sẽ ghi các dòng có cấu trúc vào luồng log chuẩn của PostgreSQL hoặc syslog:

LOG:  AUDIT: OBJECT,1,1,READ,SELECT,TABLE,public.customer_payments,SELECT * FROM customer_payments WHERE id = $1;,<42>

Các trường phân tách bằng dấu phẩy này cung cấp metadata rõ ràng, dễ dự đoán cho các log shipper:

  • AUDIT TYPE: SESSION (cho các quy tắc áp dụng toàn session) hoặc OBJECT (kích hoạt bởi quyền của role).
  • STATEMENT ID / SUBSTATEMENT ID: Theo dõi các câu lệnh gồm nhiều bước bên trong một transaction lớn.
  • CLASS: Phân loại truy vấn (READ, WRITE, DDL, ROLE, hoặc MISC).
  • COMMAND: Lệnh SQL đã thực thi (ví dụ: SELECT, UPDATE, ALTER TABLE).
  • OBJECT TYPE & NAME: Định danh đối tượng mục tiêu (ví dụ: TABLE, public.customer_payments).
  • STATEMENT: Chuỗi truy vấn SQL thô được gửi từ client.
  • PARAMETERS: Các đối số được truyền vào khi pgaudit.log_parameter = on được bật.

Bước 5: Best Practice cho Production và Gom Log (Log Aggregation)

Đảm bảo pipeline audit hoạt động ổn định dưới tải nặng với các kinh nghiệm thực tế sau:

  • Chuyển log định dạng có cấu trúc: Cấu hình log_destination = 'csvlog,jsonlog' trên PostgreSQL 15+. Các log agent như Vector, Fluent Bit hoặc Promtail có thể forward các sự kiện đã parse tới OpenSearch, Datadog hoặc Grafana Loki mà không cần viết các quy tắc regex phức tạp.
  • Tắt catalog logging: Giữ pgaudit.log_catalog = off để bỏ qua các truy vấn nội bộ thông thường do ORM chạy vào pg_catalog và information_schema.
  • Thiết lập chính sách xoay vòng log (log rotation) chặt chẽ: Tránh tràn ổ đĩa bằng cách đặt log_rotation_age = 1d và log_rotation_size = 1GB trên máy chủ PostgreSQL.

Kết hợp pgAudit với các object role mục tiêu giúp hệ thống của bạn sẵn sàng đáp ứng các tiêu chuẩn tuân thủ nghiêm ngặt mà không làm quá tải dung lượng lưu trữ log.

Share:
Tags: