在PostgreSQL中获取前端用户机器名、IP等唯一标识用于审计

1. 创建审计表table_audit #

首先创建存储审计日志的核心表，包含需求中的关键字段：

CREATE TABLE table_audit (
    audit_id SERIAL PRIMARY KEY,
    operation_type VARCHAR(10) NOT NULL, -- 记录操作类型：INSERT/UPDATE/DELETE
    operation_timestamp TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    client_hostname VARCHAR(100), -- 客户端主机名（如JerryLap、TomPC）
    client_ip INET NOT NULL, -- 客户端IP地址
    database_user VARCHAR(100) NOT NULL DEFAULT CURRENT_USER,
    affected_row_data JSONB -- 可选：存储操作前后的行数据，按需启用
);

2. 编写触发器函数 #

创建触发器函数，用于捕获table_A的DML操作并提取客户端标识信息：

CREATE OR REPLACE FUNCTION audit_table_a()
RETURNS TRIGGER AS $$
BEGIN
    -- 根据操作类型插入审计记录
    IF TG_OP = 'INSERT' THEN
        INSERT INTO table_audit (operation_type, client_hostname, client_ip, affected_row_data)
        VALUES ('INSERT', current_setting('client_hostname', true), current_setting('client_addr')::INET, to_jsonb(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO table_audit (operation_type, client_hostname, client_ip, affected_row_data)
        VALUES ('UPDATE', current_setting('client_hostname', true), current_setting('client_addr')::INET, to_jsonb(OLD) || jsonb_build_object('new_data', to_jsonb(NEW)));
        RETURN NEW;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO table_audit (operation_type, client_hostname, client_ip, affected_row_data)
        VALUES ('DELETE', current_setting('client_hostname', true), current_setting('client_addr')::INET, to_jsonb(OLD));
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

• current_setting('client_addr')：PostgreSQL内置变量，直接获取客户端IP，无需额外配置
• current_setting('client_hostname', true)：获取客户端主机名，第二个参数true表示变量不存在时返回NULL而非报错

3. 配置PostgreSQL以启用主机名解析 #

默认情况下PostgreSQL不会主动解析客户端主机名，需修改postgresql.conf：

log_hostname = on

修改后重启PostgreSQL服务，服务器会尝试通过反向DNS解析客户端IP对应的主机名，或直接获取客户端主动提供的主机名。

4. 给table_A绑定审计触发器 #

为table_A的所有DML操作绑定触发器：

CREATE TRIGGER trigger_audit_table_a_insert
AFTER INSERT ON table_A
FOR EACH ROW EXECUTE FUNCTION audit_table_a();

CREATE TRIGGER trigger_audit_table_a_update
AFTER UPDATE ON table_A
FOR EACH ROW EXECUTE FUNCTION audit_table_a();

CREATE TRIGGER trigger_audit_table_a_delete
AFTER DELETE ON table_A
FOR EACH ROW EXECUTE FUNCTION audit_table_a();

5. 验证效果 #

用不同客户端（如JerryLap、TomPC）连接数据库，对table_A执行增删改操作，然后查询审计表：

SELECT operation_type, operation_timestamp, client_hostname, client_ip FROM table_audit;

即可看到对应客户端的主机名与IP被完整记录。

补充说明 #

• 若客户端主机名返回NULL，可能是反向DNS解析失败，可要求客户端在连接时指定application_name为主机名，然后用current_setting('application_name')替代client_hostname
• SECURITY DEFINER确保触发器函数以创建者权限执行，避免普通用户因权限不足无法写入审计日志
• 不需要存储行数据时，可直接删除affected_row_data字段以节省存储空间

内容的提问来源于stack exchange，提问作者SQLLER