PostgreSQL的扩展 insert_username

PostgreSQL的扩展 insert_username

insert_username 是 PostgreSQL 的一个实用扩展，用于自动记录数据行的创建者和最后修改者信息。这个扩展特别适合需要审计跟踪的应用场景。

一 扩展安装与启用

1.1 安装扩展

-- 使用超级用户安装
CREATE EXTENSION insert_username;

1.2 验证安装

-- 查看已安装扩展
SELECT * FROM pg_available_extensions WHERE name = 'insert_username';-- 查看扩展提供的函数
\df+ insert_username.*

输出示例：

white=# CREATE EXTENSION insert_username;
CREATE EXTENSION
white=# SELECT * FROM pg_available_extensions WHERE name = 'insert_username';name       | default_version | installed_version |                  comment                   
-----------------+-----------------+-------------------+--------------------------------------------insert_username | 1.0             | 1.0               | functions for tracking who changed a table
(1 row)white=# \df+ insert_username.*List of functionsSchema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Internal name | D
escription 
--------+------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+---------------+--
-----------
(0 rows)white=# 

二 基本功能与使用

2.1 核心功能

该扩展提供两个主要功能：

• 自动插入当前用户名到指定列（记录创建者）
• 自动更新当前用户名到指定列（记录最后修改者）

2.2 基本用法示例

-- 创建测试表
CREATE TABLE documents (id serial PRIMARY KEY,content text,created_by text,  -- 记录创建者modified_by text  -- 记录最后修改者
);-- 为表添加触发器
CREATE TRIGGER track_document_users
BEFORE INSERT OR UPDATE ON documents
FOR EACH ROW EXECUTE FUNCTION insert_username();

输出示例：

white=# CREATE TABLE yewu1.documents (
white(#     id int,
white(#     name varchar(20),
white(#     created_by text,   
white(#     modified_by text   
white(# );
CREATE TABLE
white=# CREATE TRIGGER track_document_users
white-# BEFORE INSERT OR UPDATE ON yewu1.documents
white-# FOR EACH ROW EXECUTE FUNCTION insert_username();
CREATE TRIGGER
white=# 
white=# \d yewu1.documentsTable "yewu1.documents"Column    |         Type          | Collation | Nullable | Default 
-------------+-----------------------+-----------+----------+---------id          | integer               |           |          | name        | character varying(20) |           |          | created_by  | text                  |           |          | modified_by | text                  |           |          | 
Triggers:track_document_users BEFORE INSERT OR UPDATE ON yewu1.documents FOR EACH ROW EXECUTE FUNCTION insert_username()

三 高级配置选项

3.1 自定义列名

-- 创建使用不同列名的表
CREATE TABLE projects (id serial PRIMARY KEY,name text,creator text,      -- 替代 created_bylast_editor text   -- 替代 modified_by
);-- 使用参数指定列名
CREATE TRIGGER track_project_users
BEFORE INSERT OR UPDATE ON projects
FOR EACH ROW EXECUTE FUNCTION insert_username('creator', 'last_editor');

3.2 选择性应用

-- 只跟踪创建者
CREATE TRIGGER track_creator_only
BEFORE INSERT ON projects
FOR EACH ROW EXECUTE FUNCTION insert_username('creator', NULL);-- 只跟踪修改者
CREATE TRIGGER track_editor_only
BEFORE UPDATE ON projects
FOR EACH ROW EXECUTE FUNCTION insert_username(NULL, 'last_editor');

四 扩展内部原理

4.1 实现机制

insert_username 扩展实际上创建了一个触发器函数，该函数：

1. 在 INSERT 操作时设置 created_by 列为当前用户名
2. 在 UPDATE 操作时设置 modified_by 列为当前用户名

4.2 使用的PostgreSQL特性

• 触发器函数
• current_user 系统函数
• 可变参数处理

五 性能考虑

5.1 性能影响

• 触发器增加的开销通常很小
• 每行操作增加少量CPU时间
• 不影响查询性能，只影响DML操作

5.2 优化建议

-- 对大表考虑禁用触发器进行批量操作
ALTER TABLE large_table DISABLE TRIGGER track_large_table_users;-- 批量更新操作
UPDATE large_table SET ...;-- 重新启用触发器
ALTER TABLE large_table ENABLE TRIGGER track_large_table_users;

六 与其他扩展的对比

特性	insert_username	temporal_tables	pg_audit
用户跟踪	✓	✗	✓
时间跟踪	✗	✓	✓
完整审计	✗	✗	✓
使用复杂度	简单	中等	复杂
性能影响	低	中	高

七 最佳实践

1. 命名一致性：在所有表中使用相同的列名（如created_by/modified_by）
2. 组合使用：与时间戳触发器结合使用
3. 权限控制：

   -- 防止手动修改跟踪列
   REVOKE UPDATE ON created_by FROM public;
   

4. 文档记录：在数据库文档中记录审计策略
5. 异常处理：考虑添加错误处理逻辑

八 限制与注意事项

1. 不记录超级用户的更改（如postgres用户）
2. 需要触发器权限才能创建相关触发器
3. 不会跟踪DELETE操作
4. 在多语句事务中，用户名是事务开始时的用户

insert_username扩展提供了轻量级、简单易用的用户变更跟踪功能，非常适合基本的审计需求，但对于复杂的审计场景可能需要更全面的解决方案。