Kiểm tra truy cập và ghi log hành động người dùng

Hãy tưởng tượng database của bạn giống như một câu lạc bộ với luật lệ thành viên cực kỳ nghiêm ngặt. Chỉ có người quen mới được vào, nhưng tụi mình muốn biết ai đến, lúc nào, ở lại bao lâu và họ làm gì trong đó. Đó chính là lý do cần audit trong PostgreSQL. Nhờ nó, bạn có thể:

1. Theo dõi hành động của người dùng. Ví dụ, ai và lúc nào đã thực hiện một truy vấn quan trọng.

2. Phát hiện hoạt động đáng ngờ. Có thể ai đó đang cố đọc dữ liệu mà họ không nên truy cập.

3. Tuân thủ yêu cầu pháp luật và tiêu chuẩn. Nhiều ngành (như tài chính hoặc y tế) bắt buộc phải audit chi tiết hành động người dùng.

4. Hiểu hệ thống từ bên trong. Ghi log giúp bạn biết truy vấn nào được chạy nhiều nhất, chỗ nào nghẽn cổ chai và cách tối ưu hiệu suất.

Cấu hình các tham số ghi log

PostgreSQL cho phép cấu hình audit hành động thông qua các tham số như log_statement và log_connections. Cùng tìm hiểu kỹ hơn nhé.

1. log_statement: ghi lại cái gì?

Tham số log_statement xác định truy vấn SQL nào sẽ được ghi vào log. Cực kỳ hữu ích để hiểu chuyện gì đang diễn ra trong hệ thống.

Các giá trị có thể của log_statement:

• none — không ghi gì cả (dành cho ai thích sống nguy hiểm).
• ddl — chỉ ghi các lệnh thay đổi cấu trúc database (ví dụ, CREATE TABLE).
• mod — ghi các lệnh thay đổi dữ liệu (ví dụ, INSERT, UPDATE, DELETE).
• all — ghi lại tất tần tật.

Ví dụ cấu hình: để thay đổi tham số này, bạn cần sửa file postgresql.conf:

# Cấu hình ghi log tất cả truy vấn SQL
log_statement = 'all'

Sau đó lưu lại và khởi động lại server PostgreSQL:

pg_ctl reload

Bây giờ câu lạc bộ của bạn sẽ ghi lại mọi hành động, từ gọi nước đến nhảy nhót luôn nhé.

2. log_connections: ai vào club vậy?

Tham số log_connections dùng để ghi log mỗi lần có kết nối mới vào database. Ngoài ra còn có log_disconnections để ghi lại lúc đóng kết nối.

Ví dụ cấu hình: Lại sửa file postgresql.conf nhé:

# Ghi log kết nối
log_connections = on

# Ghi log ngắt kết nối
log_disconnections = on

Vậy thì được gì? Ví dụ, bạn sẽ thấy trong log là ông manager của bạn đã cố gắng kết nối vào database suốt hai tiếng với mật khẩu sai. Đúng 3 giờ sáng luôn.

Phân tích log: có thể tìm thấy gì?

Sau khi cấu hình log_statement và log_connections, PostgreSQL sẽ bắt đầu ghi log. Đây là ví dụ log file khi bật log_statement = 'mod':

2023-11-01 12:45:01 UTC [12345] LOG:  connection authorized: user=admin database=university
2023-11-01 12:46:15 UTC [12345] STATEMENT:  INSERT INTO students (name, age) VALUES ('Alice', 22);
2023-11-01 12:47:30 UTC [12345] STATEMENT:  UPDATE students SET age = 23 WHERE name = 'Alice';
2023-11-01 12:48:45 UTC [12345] LOG:  disconnection: session time: 2:45 connection: 1/5

Những điểm thú vị:

1. Ai đã kết nối: user=admin database=university — sếp của tụi mình đó.
2. Làm gì nè: chèn dòng có tên Alice và cập nhật tuổi của cô ấy.
3. Khi nào rời đi: 12:48:45, sau hai phút rưỡi hoạt động.

Ví dụ thực tế: dùng audit như thế nào?

Cùng xem vài kịch bản mà audit và ghi log sẽ cực kỳ hữu ích nhé.

Kịch bản 1: theo dõi thay đổi dữ liệu

Bạn nghi ngờ ai đó đang sửa dữ liệu trong bảng students. Đây là cách audit:

1. Đặt log_statement = 'mod' để ghi lại mọi truy vấn thay đổi dữ liệu.
2. Phân tích log:

cat /var/log/postgresql/postgresql.log | grep "UPDATE students"

Bây giờ bạn sẽ biết ai, khi nào và đã thay đổi gì.

Kịch bản 2: phát hiện kết nối đáng ngờ

Nếu log xuất hiện nhiều kết nối từ các IP khác nhau, có thể có gì đó không ổn. Để phân tích, dùng:

1. Log kết nối (log_connections).
2. Lọc theo địa chỉ IP:

cat /var/log/postgresql/postgresql.log | grep "connection authorized"

Kịch bản 3: phân tích hiệu suất truy vấn

Muốn biết tại sao server chậm? Cách đơn giản là bật log_statement = 'all' trong thời gian ngắn (ví dụ 1 tiếng), thu thập log rồi xem server tốn thời gian ở đâu nhiều nhất.

Best practice

Đừng ghi log mọi thứ. Hãy cấu hình log_statement chỉ ghi lại hành động quan trọng — DDL và thay đổi dữ liệu thôi.

Tự động hóa phân tích log. Dùng script hoặc tool để đọc và phân tích log thường xuyên, như grep, awk hoặc cả ELK Stack (Elasticsearch, Logstash, Kibana).

Quản lý dung lượng log. Cấu hình xoay vòng log bằng tham số PostgreSQL hoặc tool hệ điều hành (ví dụ logrotate trên Linux).

Hy vọng giờ bạn cảm thấy mình như sheriff thực thụ của database rồi nhé. Ghi log và audit không chỉ để bảo vệ mà còn giúp hiểu rõ hơn về hệ thống của bạn. Sẽ còn thấy mấy kiến thức này áp dụng trong dự án thực tế luôn!