Cách thực hiện tính năng audit logging trong PostgreSQL?

Audit logging trong PostgreSQL là một tính năng quan trọng để theo dõi hoạt động của người dùng và các câu lệnh SQL đã thực hiện. Điều này hữu ích trong việc bảo mật và đảm bảo tuân thủ các quy định liên quan đến kiểm toán.

PostgreSQL không có tính năng audit logging tích hợp sẵn, nhưng có thể được thực hiện thông qua nhiều cách khác nhau, chẳng hạn như sử dụng pgAudit extension hoặc thiết lập logging với cấu hình phù hợp.

Dưới đây là hai phương pháp chính để thực hiện tính năng audit logging trong PostgreSQL:

1. Sử dụng Extension pgAudit

pgAudit (PostgreSQL Audit) là một extension mạnh mẽ cho phép ghi lại chi tiết các hoạt động trên cơ sở dữ liệu.

a. Cài đặt pgAudit

Trước tiên, bạn cần cài đặt extension pgAudit. Trên các hệ thống sử dụng Ubuntu hoặc Debian, bạn có thể cài đặt pgAudit như sau:

sudo apt-get install postgresql-contrib

Tiếp theo, bạn cần kích hoạt pgAudit trong tệp cấu hình postgresql.conf.

b. Cấu hình pgAudit

Mở tệp cấu hình PostgreSQL (postgresql.conf), thêm các dòng sau để bật audit logging cho các hoạt động bạn muốn theo dõi (ví dụ READ, WRITE, DDL, và ROLE):

shared_preload_libraries = 'pgaudit'
pgaudit.log = 'all'            # 'read', 'write', 'ddl', 'role', 'all'
pgaudit.log_catalog = on        # Theo dõi các thay đổi trong catalog

c. Tạo extension pgAudit trong cơ sở dữ liệu

Sau khi khởi động lại PostgreSQL, tạo extension pgAudit trong cơ sở dữ liệu mà bạn muốn theo dõi:

CREATE EXTENSION pgaudit;

d. Bật Audit Logging cho các hoạt động cụ thể

Bạn có thể bật audit logging cho các hoạt động cụ thể như sau:

pgaudit.log = 'read, write, ddl, role'

Trong đó:

• read: Ghi lại các hoạt động SELECT.
• write: Ghi lại các hoạt động INSERT, UPDATE, DELETE.
• ddl: Ghi lại các thay đổi trong cấu trúc cơ sở dữ liệu (Data Definition Language).
• role: Ghi lại các thay đổi liên quan đến quản lý người dùng và vai trò.

e. Kiểm tra Audit Log

Các hoạt động audit sẽ được ghi lại trong tệp log của PostgreSQL. Bạn có thể kiểm tra tệp log này ở thư mục log của PostgreSQL (thường ở /var/log/postgresql/ trên Linux hoặc thư mục cấu hình PostgreSQL).

Một ví dụ về log audit được ghi bởi pgAudit có thể như sau:

AUDIT: SESSION,10,1,READ,SELECT,,,SELECT * FROM users WHERE id = 1;

2. Sử dụng log_statement và log_duration trong PostgreSQL

Nếu bạn không muốn sử dụng extension, PostgreSQL có thể ghi lại các câu lệnh SQL thông qua cấu hình log mặc định.

a. Cấu hình log_statement

Bạn có thể cấu hình PostgreSQL để ghi lại các câu lệnh SQL với log_statement trong tệp postgresql.conf. Giá trị của log_statement có thể là:

• none: Không ghi log câu lệnh nào.
• ddl: Ghi lại các câu lệnh DDL như CREATE, ALTER, DROP.
• mod: Ghi lại các câu lệnh DDL và các câu lệnh thay đổi dữ liệu như INSERT, UPDATE, DELETE.
• all: Ghi lại tất cả các câu lệnh.

Thêm cấu hình sau vào tệp postgresql.conf:

log_statement = 'all'

b. Cấu hình log_duration

Cấu hình log_duration để ghi lại thời gian thực thi của các câu lệnh:

log_duration = on

c. Ghi lại truy cập theo thời gian thực

Ngoài việc ghi log các câu lệnh, bạn cũng có thể bật tính năng ghi log các truy vấn chậm (slow query log) bằng cách cấu hình log_min_duration_statement:

log_min_duration_statement = 1000   # Ghi log các câu lệnh mất hơn 1000ms để thực thi

d. Kiểm tra log

Tất cả các log này sẽ được ghi vào tệp log của PostgreSQL theo đường dẫn cấu hình log_directory và log_filename trong postgresql.conf. Ví dụ:

log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d.log'

Một ví dụ về câu lệnh được ghi log có thể trông như sau:

LOG:  statement: SELECT * FROM users WHERE id = 1;
LOG:  duration: 12.345 ms

3. So sánh pgAudit và Logging mặc định của PostgreSQL

Tùy thuộc vào nhu cầu của hệ thống, bạn có thể chọn phương pháp audit logging phù hợp. Nếu cần audit chi tiết và tuân thủ các tiêu chuẩn bảo mật cao, pgAudit là một lựa chọn tốt. Nếu chỉ cần ghi lại các câu lệnh SQL, cấu hình logging mặc định của PostgreSQL cũng đã đủ đáp ứng.