我需要在用户和角色中记录所有更改（示例：将超级用户授予用户），日志添加新的用户/角色，日志删除用户/角色。这是为了监视我们的数据的安全性，并确保用户/角色更改登录到表甚至日志文件中。
我尝试通过在 pg_roles 和 pg_user 上创建触发器，
CREATE TRIGGER pg_roles_trigger BEFORE INSERT OR UPDATE OR DELETE ON pg_roles
FOR EACH ROW EXECUTE PROCEDURE user_access_log_trigger();
但我得到了错误错误：“ pg_roles”是一个视图。详细信息：视图在触发器之前或之后不能具有行级别，
因此尝试在基本表上创建触发器 pg_authid 。但这也给出了错误：
错误：拒绝权限：“ PG_AUTHID”是系统目录SQL状态：42501
PostgreSQL中是否有任何选项？
I need to log all changes(example: granting superuser to user) in user and role, log adding new user/roles, log dropping the user/role. This is regarding to monitor the security of our data and ensure user/roles changes are logged into table or even in log file.
I tried by creating a trigger on pg_roles and pg_user
CREATE TRIGGER pg_roles_trigger BEFORE INSERT OR UPDATE OR DELETE ON pg_roles
FOR EACH ROW EXECUTE PROCEDURE user_access_log_trigger();
But I got error Error:"pg_roles" is a view. DETAIL: Views cannot have row-level BEFORE or AFTER triggers
So tried to create a trigger on base table pg_authid. but it also gave error:
ERROR: permission denied: "pg_authid" is a system catalog SQL state: 42501
Is there any option in Postgresql ?
发布评论
评论（1）
Postgres官方文档在这里很有帮助。您可以通过将文件中的
log_statement键配置为ALL来配置postgresql.conf来记录所有查询和ddl。如果需要加载到表中并备份需要审核的日志。
The official postgres docs are helpful here. You can configure
postgresql.confto log all queries and ddl by configuringlog_statementkey in the file toall.https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT
If it needs to be loaded into a table you can parse the logs and write that information back into a table or just rotate and back up the logs for when they need to be audited.