Teledbx的慢查询pg_stat_log插件
1.概要
pg_stat_log旧版本只能基于超级用户去查看普通用户记录的慢查询语句，不能用超级用户产生慢查询语句给超级用户查看。
功能需求：
-
需求1：超级用户的慢语句需要被记录再pg_stat_log（）函数视图中。
-
需求2：管控的“teledbx"超级用户的慢查询语句不需要被记录在pg_stat_log（）的视图中。
-
需求3：超级用户可以看到所有用户的慢查询记录语句。
下述测试主要涉及3类用户：
--管控超级用户: teledbx
--非管控的超级用户: tangyujie
--普通用户: tt
postgres=# \du
List of roles
Role name | Attributes | Member of
-------------+------------------------------------------------------------+-----------
audit_admin | No inheritance | {}
mls_admin | No inheritance, Create role | {}
tangyujie | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
teledbx | Superuser | {}
tt | | {}
2.功能与用户测试
2.1 功能测试
配置
shared_preload_libraries='pg_stat_statements,pg_stat_log'
log_min_duration_statement=1000
使用插件
create user tyj with password '******';
create extension pg_stat_statements;
create extension pg_stat_log;
--切换到普通用户
\c - tyj
--制造慢查询语句
select pg_sleep(2);
--切换到超级用户tangyujie
\c - tangyujie
postgres=# select pg_stat_log();--或者执行select * from pg_stat_log();输出字段和pg_stat_statements插件记录内容类似
pg_stat_log
--------------------------------------------------------------------------------------------------------------
----------------------------------------------------------
(16385,13436,105080,0,22621640,[local],"select pg_sleep(2)","select pg_sleep($1)",1,"2023-11-01 15:49:45","20
23-11-01 15:49:47",2003.870625,1,0,0,0,0,0,0,0,0,0,0,0,0)
(1 row)
--根据捞出的userid去查询用户名即可
SELECT rolname FROM pg_catalog.pg_authid WHERE oid = '$userid';
2.2 用户测试
-
非管控超级用户：tangyujie
--1.超级用户tangyujie ^
postgres=# select current_user;
current_user
--------------
tangyujie
(1 row)
postgres=# create extension pg_stat_log ;
CREATE EXTENSION
--当前无数据
postgres=# select * from pg_stat_log();
userid | dbid | procid | txid | queryid | host | query | norm_query | calls | begin_time | end_time | total_time | rows | sh
ared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | local_blks_
dirtied | local_blks_written | temp_blks_read | temp_blks_written | blk_read_time | blk_write_time
--------+------+--------+------+---------+------+-------+------------+-------+------------+----------+------------+------+---
--------------+------------------+---------------------+---------------------+----------------+-----------------+------------
--------+--------------------+----------------+-------------------+---------------+----------------
(0 rows)
--产生慢查询语句
postgres=# select pg_sleep(2);
pg_sleep
----------
(1 row)
--有一条记录，有权限看到具体的慢语句是啥
postgres=# select * from pg_stat_log();
userid | dbid | procid | txid | queryid | host | query | norm_query | calls | begin_time
| end_time | total_time | rows | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written
| local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_written | temp_blks_read | temp_blks_written | blk_read_
time | blk_write_time
--------+-------+--------+------+----------+---------+--------------------+---------------------+-------+--------------------
-+---------------------+-------------+------+-----------------+------------------+---------------------+---------------------
+----------------+-----------------+--------------------+--------------------+----------------+-------------------+----------
-----+----------------
10 | 13436 | 86259 | 0 | 22621640 | [local] | select pg_sleep(2) | select pg_sleep($1) | 1 | 2023-11-07 10:09:56
| 2023-11-07 10:09:58 | 2001.991669 | 1 | 0 | 0 | 0 | 0
| 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0
(1 row)
-
管控的超级用户：teledbx
--2.切换到teledbx超级用户
postgres=# \du
List of roles
Role name | Attributes | Member of
-------------+------------------------------------------------------------+-----------
audit_admin | No inheritance | {}
mls_admin | No inheritance, Create role | {}
tangyujie | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
teledbx | Superuser | {}
tt | | {}
postgres=# \c - teledbx
Password for user teledbx:
You are now connected to database "postgres" as user "teledbx".
--查看当前用户
postgres=# select current_user;
current_user
--------------
teledbx
(1 row)
--产生慢查询语句
postgres=# select pg_sleep(3);
pg_sleep
----------
(1 row)
--还是之前的那条记录，并没有记录select pg_sleep(3);这条慢查询语句
postgres=# select * from pg_stat_log();
userid | dbid | procid | txid | queryid | host | query | norm_query | calls | begin_time
| end_time | total_time | rows | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_writte
n | local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_written | temp_blks_read | temp_blks_written | blk_rea
d_time | blk_write_time
--------+-------+--------+------+----------+-----------+--------------------+---------------------+-------+------------------
---+---------------------+-------------+------+-----------------+------------------+---------------------+-------------------
--+----------------+-----------------+--------------------+--------------------+----------------+-------------------+--------
-------+----------------
10 | 13436 | 192684 | 0 | 22621640 | 127.0.0.1 | select pg_sleep(2) | select pg_sleep($1) | 1 | 2023-11-06 11:21:
38 | 2023-11-06 11:21:40 | 2002.179105 | 1 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0
(1 row)
-
普通用户tt
--3.切换到普通用户tt
postgres=# \c - tt
Password for user tt:
You are now connected to database "postgres" as user "tt".
postgres=> select current_user;
current_user
--------------
tt
(1 row)
--普通用户没有权限查询慢语句的权限。
postgres=> select * from pg_stat_log();
ERROR: permission denied for function pg_stat_log
postgres=> select pg_sleep(10);
pg_sleep
----------
(1 row)
--切换到tangyujie的超级用户有权限看到普通用户tt产生的慢语句
postgres=> \c - tangyujie
Password for user tangyujie:
You are now connected to database "postgres" as user "tangyujie".
postgres=# select * from pg_stat_log();
userid | dbid | procid | txid | queryid | host | query | norm_query | calls | be
gin_time | end_time | total_time | rows | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_
blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_written | temp_blks_read | temp_blks_writte
n | blk_read_time | blk_write_time
--------+-------+--------+------+----------+-----------+--------------------------+--------------------------+-------+-------
--------------+---------------------+-------------+------+-----------------+------------------+---------------------+--------
-------------+----------------+-----------------+--------------------+--------------------+----------------+-----------------
--+---------------+----------------
16405 | 13436 | 1319 | 0 | 22621640 | 127.0.0.1 | select pg_sleep(10) | select pg_sleep($1) | 1 | 2023-1
1-06 11:25:46 | 2023-11-06 11:25:56 | 10009.70209 | 1 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0
10 | 13436 | 192684 | 0 | 22621640 | 127.0.0.1 | select pg_sleep(2) | select pg_sleep($1)| 1 | 2023-1
1-06 11:21:38 | 2023-11-06 11:21:40 | 2002.179105 | 1 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0
(2 rows)
--切换导teledbx的超级用户可以看到所有用户的慢查询语句。
postgres=# \c - teledbx
Password for user teledbx:
You are now connected to database "postgres" as user "teledbx".
postgres=# select * from pg_stat_log();
userid | dbid | procid | txid | queryid | host | query | norm_query | calls | begin_time
| end_time | total_time | rows | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_writt
en | local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_written | temp_blks_read | temp_blks_written | blk_re
ad_time | blk_write_time
--------+-------+--------+------+----------+-----------+---------------------+---------------------+-------+-----------------
----+---------------------+-------------+------+-----------------+------------------+---------------------+------------------
---+----------------+-----------------+--------------------+--------------------+----------------+-------------------+-------
--------+----------------
16405 | 13436 | 1319 | 0 | 22621640 | 127.0.0.1 | select pg_sleep(10) | select pg_sleep($1) | 1 | 2023-11-06 11:25
:46 | 2023-11-06 11:25:56 | 10009.70209 | 1 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0
10 | 13436 | 192684 | 0 | 22621640 | 127.0.0.1 | select pg_sleep(2) | select pg_sleep($1) | 1 | 2023-11-06 11:21
:38 | 2023-11-06 11:21:40 | 2002.179105 | 1 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0
(2 rows)
