PG数据库审计

原创 已于 2025-03-27 17:41:16 修改 · 633 阅读 · 8 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#数据库 #oracle

于 2023-12-03 21:40:58 首次发布

目录标题

默认审计

pgsql开启数据库审计

相关参数
在这里插入图片描述

???
在这里插入图片描述
none，表示不记录。
ddl (记录create,drop和alter) 记录所有数据定义命令，比如CREATE,ALTER,和DROP语句。（生产环境）
mod (记录ddl+insert,delete,update和truncate) 记录所有ddl语句,加上数据修改语句INSERT,UPDATE等。
all (mod+select) 记录所有执行的语句，将此配置设置为all可跟踪整个数据库执行的SQL语句，但会对数据库性能产生较大影响，生产环境不建议配置此值。

插件

验证

DDL

在这里插入图片描述

WRITE

在这里插入图片描述

ROLE

在这里插入图片描述

READ

在这里插入图片描述

pgbench -i -s 10 bpx
pgbench -r -j2 -c2  -T2 bpx
pgbench -i -s 10 bpx

在这里插入图片描述

pgaudit.log

psql
show pgaudit.log;
alter system set pgaudit.log = 'write,ddl,role';
select pg_reload_conf();
show pgaudit.log;
\q

在这里插入图片描述

关闭审计

[postgres@postgres-476a875c-0-0 /]$ psql
psql (15.7)
Type "help" for help.

postgres=# alter system set pgaudit.log = 'none';
ALTER SYSTEM
postgres=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

postgres=# show  pgaudit.log;
 pgaudit.log 
-------------
 none
(1 row)

在这里插入图片描述
在这里插入图片描述

openGauss学习笔记-117 openGauss 数据库管理-设置数据库审计-查看审计结果
超哥的博客
11-07 618
- 审计功能总开关已开启。 - 需要审计的审计项开关已开启。 - 数据库正常运行，并且对数据库执行了一系列增、删、改、查操作，保证在查询时段内有审计结果产生。 - 数据库各个节点审计日志单独记录。
PG数据库之触发器详解
一个老Java开发的自白
10-30 2613
PostgreSQL中的触发器是一种在特定数据库事件（如插入、更新、删除）发生时自动执行的函数。触发器可用于强制业务规则、记录审计日志、同步数据等。PostgreSQL支持多种触发器类型，包括行级、语句级和INSTEAD OF触发器，以及处理DDL事件的事件触发器。创建触发器需先定义触发器函数，该函数在触发器被触发时执行，并返回trigger类型的值。
mysql安全-数据库审计（audit）
热门推荐
jesseyoung
11-14 1万+
1 简介     数据库审计（简称DBAudit）能够实时记录网络上的数据库活动，对数据库操作进行细粒度审计的合规性管理，对数据库遭受到的风险行为进行告警，对攻击行为进行阻断。它通过对用户访问数据库行为的记录、分析和汇报，用来帮助用户事后生成合规报告、事故追根溯源，同时加强内外部数据库网络行为记录，提高数据资产安全。     在MySQL数据库中（5.5版本），增加了一个新的插件：Audit
pg-数据库审计
weixin_33958366的博客
09-05 483
2019独角兽企业重金招聘Python工程师标准>>> ...
深入了解并实现cpp-pgAudit的PostgreSQL审计功能
weixin_35899324的博客
05-19 809
简介：本文详细介绍了cpp-pgAudit，一个增强PostgreSQL数据库审计功能的开源扩展。文章涵盖pgAudit的功能、安装、配置以及如何在C/C++环境中应用。还讨论了pgAudit的日志记录细节，性能优化策略，并强调了其在保证数据安全和法规遵从性方面的重要性。
数据库审计
Poli Liu's Oracle World
10-07 1079
背景： 数据库审计，顾名思义，就是捕捉和存储发生在数据库内部的事件信息。本文档重点关注， 1：标准的数据库审计（Standard database auditing） 通过初始化参数AUDIT_TRAIL控制实例级别的审计。 2：基于值的审计 (Value-based auditing) 它拓展了标准数据库审计，不仅捕捉审计事件，还捕捉那些被insert,update和delete的值。
如何在PostgreSQL中进行数据库审计，记录用户对数据库的操作行为？
发现生活，分享智慧，一起成长！
04-24 1487
通过使用pgAudit扩展或PostgreSQL的内置日志记录功能，您可以有效地在PostgreSQL中进行数据库审计，并记录用户对数据库的操作行为。根据您的具体需求和安全策略，选择适合的解决方案并进行配置。这些解决方案将帮助您更好地理解和监控数据库的使用情况，确保数据的安全性和完整性。相关阅读推荐PostgreSQL入门到精通.PDF 领取Postgres专栏推荐如何在PostgreSQL中备份和恢复整个数据库，包括相关的用户和权限设置。
flink CDC监控PG数据库的demo案例
04-19
标题 "flink CDC监控PG数据库的demo案例" 涉及到的是使用Apache Flink的Change Data Capture (CDC)功能来实时监控PostgreSQL (PG)数据库的变化。Flink CDC允许我们近乎实时地捕获数据库中的插入、更新和删除操作，...
PG数据库推进医疗向量搜索优化路径研究
AllenLV的博客
12-03 801
例如，在医疗影像数据库中，可以将医学影像的特征向量存储在 数据库 数据库中，通过 PGVector 和 LangChain 的组合，实现快速的相似影像检索，为医生的诊断提供参考。例如，在查询患者数据时，可以使用 WHERE 子句根据患者的年龄、性别、疾病类型等条件筛选出符合要求的向量数据，然后通过 ORDER BY 按照相似度或其他相关指标对结果进行排序，最后通过 JOIN 操作将向量数据与其他相关的结构化数据（如患者的基本信息表）进行关联，获取更全面的患者信息。在医疗领域，开源社区的力量尤为重要。
数据库之审计
qq_45169180的博客
03-24 820
数据库之审计 因为任何系统的安全保护措施都不是完美无缺的，蓄意盗窃，破坏数据的人总是想方设法打破控制，审计功能把用户对数据库的所有操作自动记录下来放入审计日记(audit log)中，审计员可以利用审计日志监控数据库的各种行为，重现导致数据库现状的一系列事件，找出非法存取数据的人，时间和内容等。 1.审计事件 服务器事件:审计数据库服务器发生的事件，包含数据库服务器的启动，停在，数据库服务器配置文件的重新加载。 系统权限:对系统用有的结构或模式对象进行操作的审计，要求该操作的权限是通过系统权限获得的。 语句
数据库的审计
weixin_43965595的博客
01-25 1126
1.查看审计功能是否开启：show parameter audit; 2.开启审计功能： SQL> alter system set audit_sys_operations=TRUE scope=spfile;–审计管理用户(以sysdba/sysoper角色登陆) SQL> alter system set audit_trail=db,extended scope=spfile; 3.关闭审计功能： SQL> conn /as sysdba SQL> show paramete
数据库审计（全）
一个只求实际的程序猿
07-05 3840
数据库审计（全）
数据库审计介绍
mzchenghaonan的博客
02-03 2767
Agent部署方式一般是因为：当Web应用和数据库在同一台物理服务器上的话，那么Web应用访问数据库的流量都是在本地产生的，没法通过交换机来镜像到数据库审计，此时需要在这种主机上安装agent代理，主动监听Web应用访问数据库的流量，从而主动推送给数据库审计系统。不需要云环境底层支持流量镜像，只需要安装Agent即可完成云环境数据库的安全审计，支持主流的云环境中的主流的linux和windows等虚拟主机 ，单台审计设备可以同时支持多个数据库的审计。
用的是pg数据库
最新发布
07-19
<think>我们正在处理一个PostgreSQL存储过程执行效率问题，具体现象是：在数据库内部调用存储过程耗时显著增加（与之前相比或与外部调用相比）。根据用户提供的信息和引用，我们可以从多个角度进行排查。 关键引用回顾： [^1]: 优化PostgreSQL存储过程需要综合考虑查询优化、存储过程结构、服务器配置和定期维护。 [^2]: 合理配置日志有助于故障排除和性能分析，特别是审计日志和安全监控。 [^3]: 使用pg_stat_statements扩展可以统计历史查询的执行次数、总耗时、平均耗时，识别高频慢查询。 [^4]: 提到了压测和参数调优，以及如何分析性能瓶颈。 根据这些引用，我们可以制定排查步骤： 1. **分析存储过程内部查询**：使用EXPLAIN ANALYZE分析存储过程中的每个查询语句，查看执行计划是否低效。 2. **使用pg_stat_statements**：识别存储过程中哪些查询最耗时。 3. **检查参数嗅探（Parameter Sniffing）**：PostgreSQL中也可能存在类似问题，即由于参数值不同导致执行计划不佳。 4. **检查锁等待**：存储过程执行期间是否被其他事务阻塞。 5. **检查日志**：特别是审计日志和错误日志，看是否有异常或警告。 6. **资源使用情况**：检查CPU、内存、I/O在存储过程执行期间的使用情况。 7. **事务和并发设置**：检查存储过程是否在事务中运行，以及事务隔离级别等。 下面我们详细展开： ### 一、执行计划分析 使用`EXPLAIN ANALYZE`执行存储过程中的关键查询，或者将整个存储过程包装在一个事务中执行并分析。 例如，对于存储过程中的一个查询： ```sql EXPLAIN ANALYZE SELECT ... -- 存储过程中的查询语句 ``` 如果存储过程包含多个语句，可以逐个分析。关注是否有全表扫描、嵌套循环连接、不合适的索引等。 ### 二、使用pg_stat_statements 安装并启用pg_stat_statements扩展（如果尚未启用）： ```sql CREATE EXTENSION IF NOT EXISTS pg_stat_statements; ``` 然后查询最耗时的SQL语句： ```sql SELECT query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; ``` 注意：可能需要重置pg_stat_statements（使用`SELECT pg_stat_statements_reset()`）来获取最新的数据。 ### 三、参数嗅探问题 在PostgreSQL中，执行计划会缓存，但有时会因为参数值的变化导致计划不佳。我们可以通过以下方式解决： 1. 使用`PLAN`选项强制重新规划： ```sql EXECUTE your_procedure(param1, param2) WITH (PLAN); ``` 2. 在存储过程内部使用动态SQL，并添加`EXECUTE ... USING`，这样可以为每次执行生成新的计划（但需谨慎，可能增加规划开销）。 3. 使用`CREATE STATISTICS`创建扩展统计信息，帮助优化器做出更好的选择。 ### 四、锁等待 在存储过程执行期间，检查是否有锁等待： ```sql SELECT * FROM pg_locks WHERE NOT granted; ``` 或者使用`pg_blocking_pids`函数查找阻塞当前会话的进程： ```sql SELECT pg_blocking_pids(pid) FROM pg_stat_activity WHERE pid = <your_pid>; ``` ### 五、日志分析 配置PostgreSQL日志，记录慢查询（例如超过1秒的查询）： 在postgresql.conf中设置： ``` log_min_duration_statement = 1000 # 单位毫秒 log_statement = 'all' # 可选，记录所有语句，但生产环境慎用 ``` 然后重启或重新加载配置，查看日志中记录的慢查询。 ### 六、资源监控 使用系统工具（如top, iotop, iostat, vmstat）监控数据库服务器资源使用情况。同时，PostgreSQL自带的`pg_stat_activity`视图可以查看当前活动会话： ```sql SELECT * FROM pg_stat_activity WHERE state = 'active'; ``` 关注状态为“active”的会话，特别是执行时间长的。 ### 七、事务设置 检查存储过程是否在事务中运行，以及事务的隔离级别。长时间的事务可能导致锁持有时间过长，或者由于快照过旧导致性能下降（在REPEATABLE READ或SERIALIZABLE隔离级别下）。 另外，检查存储过程中是否有大量的中间提交（COMMIT），这可能导致额外的开销。 ### 优化建议 1. **索引优化**：根据执行计划添加缺失的索引。 2. **重写存储过程**：避免复杂的逻辑，将一些操作拆分成多个存储过程，或者将部分逻辑移到应用层。 3. **调整配置参数**：如`work_mem`、`shared_buffers`等，根据[^4]提到的压测方法进行调优。 4. **定期维护**：执行`VACUUM ANALYZE`更新统计信息，重建索引等。 如果问题仍然存在，考虑使用性能分析工具（如pg_profile、pgBadger）对数据库进行更深入的分析。 §§ 相关问题 §§ 1. 如何解释PostgreSQL的执行计划？ 2. pg_stat_statements扩展的详细使用方法是什么？ 3. 如何配置PostgreSQL的慢查询日志？ 4. PostgreSQL中有哪些常见的锁类型？如何解决锁等待问题？ 5. 如何调整PostgreSQL的内存参数以优化性能？[^4]</think>### PostgreSQL 存储过程执行效率排查指南 当存储过程在数据库内部调用耗时显著增加时，需从执行计划、资源争用和参数优化三个维度排查。以下是系统化的诊断流程： --- #### 一、核心原因分析 1. **执行计划劣化（参数嗅探问题）** PostgreSQL 会根据首次调用参数生成执行计划缓存，当后续参数分布差异大时（如`WHERE status=@status`中`@status`从高频值变为低频值），会导致全表扫描等低效操作[^1][^3]。 **验证方法**： ```sql EXPLAIN (ANALYZE, BUFFERS) CALL your_procedure(param1, param2); -- 获取实际执行计划 ``` 2. **资源争用与锁阻塞** - **锁等待**：存储过程涉及`SELECT ... FOR UPDATE`或数据修改时，可能被其他事务阻塞 - **内存不足**：`work_mem`设置过小导致磁盘临时文件写入（检查`EXPLAIN`中的`Disk Usage`） **诊断工具**： ```sql -- 查看锁等待 SELECT * FROM pg_locks WHERE NOT granted; -- 检查临时文件写入 SELECT datname, temp_files FROM pg_stat_database; ``` 3. **统计信息过时** 数据分布变化后未及时更新统计信息，导致优化器误判： ```sql SELECT last_analyzed FROM pg_stat_all_tables WHERE relname = 'your_table'; -- 检查统计信息更新时间 ``` --- #### 二、优化解决方案 1. **执行计划优化** - **禁用参数嗅探**（PostgreSQL 12+）： ```sql CREATE OR REPLACE PROCEDURE your_proc(param INT) AS $$ DECLARE local_param INT = param; -- 屏蔽参数嗅探 BEGIN SELECT ... WHERE col = local_param; END; $$ LANGUAGE plpgsql; ``` - **强制重新编译**： ```sql ALTER PROCEDURE your_proc SET plan_cache_mode = force_custom_plan; ``` 2. **资源争用处理** - **提升`work_mem`**（针对排序/哈希操作）： ```sql SET LOCAL work_mem = '64MB'; -- 在存储过程内动态设置 ``` - **缩短锁持有时间**： ```sql BEGIN; CALL your_proc(...); COMMIT; -- 显式控制事务范围 ``` 3. **统计信息维护** ```sql ANALYZE your_table; -- 手动更新统计信息 VACUUM ANALYZE your_table; -- 清理死元组并更新统计信息 ``` --- #### 三、深度诊断工具 | 工具 | 命令/方法 | 关键指标 | |---------------------|--------------------------------------------------------------------------|----------------------------| | `pg_stat_statements`| `SELECT * FROM pg_stat_statements WHERE query ~ 'your_proc'` | `mean_time`, `rows` [^3] | | 执行计划分析 | `EXPLAIN (ANALYZE, BUFFERS) CALL your_proc()` | `Planning Time`, `Execution Time` | | 日志分析 | 设置`log_min_duration_statement = 1000` 记录慢查询 | 定位具体慢速语句 [^2] | | 系统视图 | `SELECT * FROM pg_stat_activity WHERE query ~ 'your_proc'` | `wait_event_type`（锁等待） | > **关键建议**：优先检查`EXPLAIN ANALYZE`输出中的`Planning Time`。若计划时间占比高（如>30%），说明存在参数嗅探或统计信息问题；若执行时间高，需优化查询或调整资源参数[^1][^4]。 --- #### 四、高级调优策略 1. **批量处理优化** 避免逐行操作，使用`RETURN QUERY`或`FORALL`： ```sql CREATE PROCEDURE batch_update() AS $$ BEGIN UPDATE table SET col = new_val WHERE condition; END; $$ LANGUAGE plpgsql; ``` 2. **连接池控制** 使用`pgbouncer`限制并发连接，避免连接风暴导致资源争用。 3. **参数化压测** 参考[^4]方法调整压测参数： ```bash pgbench -c 20 -j 4 -T 60 -f proc_test.sql ```
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值