PostgreSQL 的审计日志

开启审计日志功能
要开启审计日志功能，可以在 postgresql.conf 文件中添加或修改以下配置：

logging_collector = on

这将启用日志收集器，使 PostgreSQL 开始记录审计日志。同时，您也需要指定日志文件的路径：

log_directory = 'pg_log'

这将指定日志文件存放在 pg_log 目录下。您还可以指定日志文件的名称：

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

这将将日志文件命名为 postgresql-年-月-日.log，例如 postgresql-2023-04-13.log。
配置日志格式
在 PostgreSQL 中，有多种日志格式可供选择。以下是 csvlog 格式的示例：

log_destination = 'csvlog'

log_line_prefix = '%t,%u,%d,%p,'

这将使用 CSV 格式记录审计日志，并在每行日志信息前添加时间戳、用户 ID、数据库名称和进程 ID。例如，以下是一个示例日志行：
2023-04-13 10:42:34.821172,postgres,demo,5768,localhost,4562,1,LOG,00000,“statement: SELECT * FROM users;”
其中，逗号分隔的字段依次为时间戳、用户名、数据库名、进程 ID、主机名、会话 ID、命令标识符、日志级别和日志消息。
配置日志级别
在 PostgreSQL 中，有多个日志级别可供选择。以下是一个示例：

log_min_messages = 'INFO'

这将记录所有 INFO 级别及以上的信息。如果您希望记录所有信息，可以将日志级别设置为 DEBUG5：

log_min_messages = 'DEBUG5'

这将记录所有调试信息。
DEBUG5：最详细的日志级别，记录所有调试信息。
DEBUG4：记录详细的调试信息。
DEBUG3：记录更加详细的调试信息。
DEBUG2：记录非常详细的调试信息。
DEBUG1：记录较为详细的调试信息。
INFO：记录普通信息。
NOTICE：记录警告信息。
WARNING：记录警告信息。
ERROR：记录错误信息。
LOG：记录所有日志信息。
配置审计日志选项
在 PostgreSQL 中，还有许多其他的选项可供配置，以记录更详细的审计日志信息。以下是一些示例：

log_connections = on

这将记录每个连接到数据库的客户端的信息，例如：
2023-04-13 10:42:34.821172,postgres,demo,5768,localhost,4562,1,LOG,00000,“connection received: host=localhost port=5432”

log_statement = 'all'

这将记录每个执行的 SQL 语句，例如：
2023-04-13 10:42:34.821172,postgres,demo,5768,localhost,4562,1,LOG,00000,“statement: SELECT * FROM users;”

log_duration = on

这将记录每个 SQL 语句的执行时间，例如：
2023-04-13 10:42:34.821172,postgres,demo,5768,localhost,4562,1,LOG,00000,"duration: 0.005 ms statement: SELECT * FROM

其他配置:

log_connections：记录每个连接到数据库的客户端信息。
log_disconnections：记录每个断开与数据库的客户端的信息。 log_duration：记录每个 SQL 语句的执行时间。
log_error_verbosity：设置错误信息的详细程度。 log_hostname：记录每个连接到数据库的客户端的主机名。
log_line_prefix：设置每行日志信息的前缀，可以包括时间戳、用户名、数据库名等信息。
log_lock_waits：记录等待锁的信息。 log_statement：记录每个执行的 SQL 语句，包括
SELECT、INSERT、UPDATE 和 DELETE 等操作。 log_temp_files：记录使用临时文件的信息。
log_timezone：设置日志记录时

创建日志表

CREATE TABLE pg_audit_log (
    event_time TIMESTAMP WITH TIME ZONE,
    user_name TEXT,
    database_name TEXT,
    process_id INTEGER,
    remote_host TEXT,
    remote_port TEXT,
    session_id INTEGER,
    command_tag TEXT,
    session_start_time TIMESTAMP WITH TIME ZONE,
    virtual_transaction_id TEXT,
    transaction_id BIGINT,
    error_severity TEXT,
    sql_state_code TEXT,
    message TEXT,
    detail TEXT,
    hint TEXT,
    internal_query TEXT,
    internal_query_pos INTEGER,
    context TEXT,
    query TEXT,
    query_pos INTEGER,
    location TEXT,
    application_name TEXT,
    client_hostname TEXT,
    client_port_num INTEGER,
    client_username TEXT
);

COPY pg_audit_log FROM '/var/lib/postgresql/data/postgresql14.1-2023-04-27.csv' DELIMITER ',' CSV HEADER;

log_line_prefix 可以用来配置所有的日志信息，你可以根据需要在 log_line_prefix 中包含所有你需要的列和标记。

以下是一个示例 log_line_prefix 配置，它包含了 PostgreSQL 日志中的所有列和标记：

log_line_prefix = '%m [%p] %c %l %x %a %u %d %s:%i %t %r %e %q '

解释一下这个配置：

%m: 时间戳（格式为YYYY-MM-DD HH24:MI:SS）
[%p]: 进程 ID
%c: 数据库名称
%l: 日志级别（DEBUG1, DEBUG2, DEBUG3, LOG, INFO, NOTICE, WARNING, ERROR, FATAL, or PANIC）
%x: 事务 ID（如果日志消息是事务的一部分）
%a: 客户端 IP 地址
%u: 用户名
%d: 连接的数据库名称
%s:%i: 客户端的套接字地址和端口号
%t: 日志消息的文本
%r: 关联的相关信息（例如，错误信息、SQL 语句等）
%e: SQLSTATE 错误码
%q: 查询开始时的毫秒数