Cách thực hiện audit để theo dõi truy cập trong PostgreSQL?

Question

Hiếu Nguyễn · Accepted Answer

Để thực hiện audit (theo dõi truy cập) trong PostgreSQL, có một số cách phổ biến mà bạn có thể sử dụng, tùy thuộc vào nhu cầu cụ thể của bạn. Sau đây là một số phương pháp bạn có thể tham khảo:
1. Sử dụng Extension pgAudit
pgAudit là một extension phổ biến cho phép ghi lại các hoạt động của người dùng trong PostgreSQL. Nó có thể theo dõi và ghi lại các lệnh SQL mà người dùng thực hiện, đặc biệt là các lệnh liên quan đến quyền truy cập dữ liệu.
Cài đặt pgAudit
Cài đặt extension:
Trên hệ điều hành Ubuntu, bạn có thể cài đặt thông qua apt:
sudo apt-get install postgresql-contrib
Thêm extension vào cơ sở dữ liệu:
Kết nối vào PostgreSQL và thêm extension vào cơ sở dữ liệu cần theo dõi:CREATE EXTENSION pgaudit;
CREATE EXTENSION pgaudit;
Cấu hình pgaudit trong file postgresql.conf: Bạn cần chỉnh sửa file cấu hình postgresql.conf để kích hoạt audit:
pgaudit.log = 'all'
pgaudit.log_level = 'log'
pgaudit.log_catalog = 'on'
Khởi động lại PostgreSQL để áp dụng thay đổi:
sudo systemctl restart postgresql
Kiểm tra logs:
Sau khi cấu hình, các lệnh như SELECT, INSERT, UPDATE, DELETE, và các thay đổi khác sẽ được ghi lại trong log file của PostgreSQL.
2. Sử dụng Triggers (Cò truy cập)
Bạn có thể tạo các trigger để ghi lại thông tin truy cập khi có sự thay đổi dữ liệu, đặc biệt là với các lệnh như INSERT, UPDATE, DELETE.
Ví dụ trigger audit:
CREATE TABLE audit_log (
    id serial PRIMARY KEY,
    operation text,
    table_name text,
    old_data jsonb,
    new_data jsonb,
    changed_at timestamp DEFAULT current_timestamp
);
CREATE OR REPLACE FUNCTION audit_function() RETURNS trigger AS $
BEGIN
    IF (TG_OP = 'INSERT') THEN
        INSERT INTO audit_log (operation, table_name, new_data)
        VALUES ('INSERT', TG_TABLE_NAME, row_to_json(NEW));
        RETURN NEW;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO audit_log (operation, table_name, old_data, new_data)
        VALUES ('UPDATE', TG_TABLE_NAME, row_to_json(OLD), row_to_json(NEW));
        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        INSERT INTO audit_log (operation, table_name, old_data)
        VALUES ('DELETE', TG_TABLE_NAME, row_to_json(OLD));
        RETURN OLD;
    END IF;
END;
$ LANGUAGE plpgsql;
-- Áp dụng trigger vào một bảng cụ thể
CREATE TRIGGER my_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON my_table
FOR EACH ROW EXECUTE FUNCTION audit_function();
3. Sử dụng Logging (Ghi lại log)
PostgreSQL có khả năng ghi lại các truy cập thông qua tính năng log_statement trong file cấu hình postgresql.conf. Bạn có thể cấu hình để ghi lại các loại lệnh SQL mà bạn muốn audit.
Cấu hình postgresql.conf:
log_statement = 'all'  # Ghi lại tất cả các lệnh SQL
log_duration = on      # Ghi lại thời gian thực hiện câu lệnh
log_connections = on   # Ghi lại khi có kết nối
log_disconnections = on # Ghi lại khi kết nối bị ngắt
Sau khi thay đổi, bạn cần khởi động lại PostgreSQL.
4. Sử dụng Event Trigger
PostgreSQL hỗ trợ event trigger để theo dõi các sự kiện nhất định như tạo, thay đổi hoặc xóa các đối tượng trong cơ sở dữ liệu.
Ví dụ tạo event trigger:
CREATE EVENT TRIGGER ddl_audit
    ON ddl_command_end
    EXECUTE PROCEDURE log_ddl_commands();
CREATE OR REPLACE FUNCTION log_ddl_commands() RETURNS event_trigger AS $
BEGIN
    INSERT INTO audit_log (operation, table_name, changed_at)
    VALUES (TG_TAG, NULL, current_timestamp);
END;
$ LANGUAGE plpgsql;
5. Sử dụng công cụ theo dõi bên ngoài
Ngoài các phương pháp trên, bạn có thể sử dụng các công cụ giám sát cơ sở dữ liệu chuyên biệt, chẳng hạn như pgAdmin hoặc Percona Monitoring and Management (PMM) để theo dõi hoạt động truy cập và audit.
Tổng kết
pgAudit phù hợp nếu bạn cần theo dõi tất cả các lệnh SQL.
Triggers giúp theo dõi cụ thể các thay đổi dữ liệu trong bảng.
Logging đơn giản và dễ triển khai nhưng có thể tạo ra rất nhiều log.
Event triggers hữu ích cho các sự kiện DDL.
Bạn nên cân nhắc kỹ nhu cầu của mình để chọn phương pháp phù hợp nhất.