PostgreSQL 用户胡作非为只能受着 --- 警告他

文摘 2024-12-06 06:00 天津

开头还是介绍一下群，如果感兴趣PolarDB ,MongoDB ,MySQL ,PostgreSQL ,Redis, OceanBase, Sql Server等有问题，有需求都可以加群群内有各大数据库行业大咖，可以解决你的问题。加群请联系 liuaustin3 ，（共2610人左右 1 + 2 + 3 + 4 +5 + 6 + 7 + 8 ）(1 2 3 4 5 6群均已爆满，新人进7群，8群，准备9群)

全世界都在“搞” PostgreSQL ，从Oracle 得到一个“馊主意”开始

PostgreSQL 加索引系统OOM 怨我了--- 不怨你怨谁

PostgreSQL “我怎么就连个数据库都不会建？” --- 你还真不会！

接着上期，没礼貌的那个人说的问题写完文章后，培训中心的同学留给我一段话：

“老师好：这有一个新问题，关于谁修改了work_mem, maintenance_work_mem，那些用户在自己的权限下，可以直接修改本地session的参数，这也可以导致PostgreSQL崩溃，那这个问题我们怎么发现和排查，比如一个用户将maintenance_work_mem 改成6G或更大，我们怎么发现这些用户修改过这些参数呢，因为SESSION 失效后参数设置也失效了，我们的有办法，后续发现，好进行排查时作为一个依据，感谢老师，这是我读完那篇文章后的担心。”

人和人的确是不一样的，有些人能举一反三，有些人只能填鸭式的教育，人生就是一盒杂拌糖，这样的问题必须马上回答。

注：maintenance_work_mem 是需要超级用户的权限才能在系统中变更的，但你见过把管理员账号权限给应用程序使用的吗？多预防此类情况吧！

下面开启这段对话：

我：咱们是有简单的方案来处理的，比如还记得课上讲的关于日志的参数，log_statement = all 这个参数，这个参数是可以对所有的操作语句进行一个记录的。

同学：老师，我有一个疑问，如果我们使用了这个参数后，我们的数据库的日志部分，是不是会很大，后面还涉及到这些数据的分析问题，您说这应该怎么做才好。

我: 来我们先做一个事例，来验证一下这个问题，然后我们在说后面的事情。

#log_parameter_max_length_on_error = 0  # when logging an error, limit logged
                                        # bind-parameter values to N bytes;
                                        # -1 means print in full, 0 disables
log_statement = 'all'                   # none, ddl, mod, all
#log_replication_commands = off
#log_temp_files = -1                    # log temporary files equal or larger
                                        # than the specified size in kilobytes;

参数修改后，我们重启数据库,打开日志，我们对其进行监控，在另一个界面里面，我们对当前的session进行参数修改。

dvdrental=# 
dvdrental=# ALTER SYSTEM SET work_mem = '100MB';
ALTER SYSTEM
dvdrental=#

024-11-28 21:14:19.923 EST [1941] LOG:  PID 1702 in cancel request did not match any process
2024-11-28 21:14:54.792 EST [1944] LOG:  statement: ALTER SYSTEM SET work_mem = '100MB';
2024-11-28 21:19:08.218 EST [1931] LOG:  checkpoint starting: time
2024-11-28 21:19:08.222 EST [1931] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.002 s, total=0.005 s; sync files=2, longest=0.001 s, average=0.001 s; distance=0 kB, estimate=0 kB; lsn=0/4F879F8, redo lsn=0/4F879C0

看我们已经可以发现数据库中执行了相关的语句了。

同学：谢谢老师，这里我想顺着这个问题在问，如果我的老板问我，到底谁哪个人，在什么时间修改的这个参数，产生什么影响，我应该怎么办？

我：同学你看，这里说明一个问题，咱们的日志记录的信息不全，需要去调整日志的一些参数，把必要的一些记录的参数添加进来，解决信息不全的问题。解决这个问题，我们可以将下面的一些参数打开，这里着重log_hostname 和 log_line_prefix的部分要进行修改记录需要的信息，在修改后，我们在重启一下服务器。

log_connections = on
log_disconnections = on
log_duration = on
log_error_verbosity = default           # terse, default, or verbose messages
log_hostname = on
log_line_prefix = '%m [%p] [%u] %h %r %a  '             # special values:
                                        #   %a = application name
                                        #   %u = user name
                                        #   %d = database name
                                        #   %r = remote host and port
                                        #   %h = remote host
                                        #   %b = backend type
                                        #   %p = process ID
                                        #   %P = process ID of parallel group leader
                                        #   %t = timestamp without milliseconds
                                        #   %m = timestamp with milliseconds
                                        #   %n = timestamp with milliseconds (as a Unix epoch)
                                        #   %Q = query ID (0 if none or not computed)
                                        #   %i = command tag
                                        #   %e = SQL state
                                        #   %c = session ID
                                        #   %l = session line number
                                        #   %s = session start timestamp

2024-11-28 21:34:16.906 EST [2139] [[unknown]] localhost localhost(51550) [unknown]  LOG:  connection received: host=localhost port=51550
2024-11-28 21:34:16.907 EST [2139] [postgres] localhost localhost(51550) [unknown]  LOG:  connection authorized: user=postgres database=dvdrental application_name=psql
2024-11-28 21:34:18.215 EST [2139] [postgres] localhost localhost(51550) psql  LOG:  statement: ALTER SYSTEM SET work_mem = '10MB';
2024-11-28 21:34:18.216 EST [2139] [postgres] localhost localhost(51550) psql  LOG:  duration: 1.034 ms

在我们修改了参数后，可以捕捉到谁在什么时间做了什么事情。

同学：好的老师，这个我明白了，那么一般我们怎么发现这个问题呢？或者说我们是被动的去发现问题对吗？

我：对，这个事情咱们是被动的，一般通过日志来过滤已经发现的问题，当然发现的时候，基本上都是人家做完这件事后，影响已经产生了，此时我们能做的也仅仅是亡羊补牢了。

同学：老师，这里我还有一个小问题，拿到日志后，分析的途径很多，比如之前您提到的开源日志分析的工具，同时还有sed awk，但这些工具搜索起来比较麻烦，而且这些信息定位也不容易，有没有其他的方案或者增强型的方案。

我：有的，你还记得我们提过的一个工具pgaudit，可以用这个来进行 audit 信息的标注且，可以更方便的找到需要进行鉴别的信息。我们可以通过网站来下载pgaudit的源代码，然后进行编译，这里就省去编译的环节了，咱们上课都讲过什么变量环境下去编译哈。

https://github.com/pgaudit/pgaudit

同学：我知道的，需要再contrib 目录里面，否则报错。

我：OK，那我们现在来说说pgaudit的一些基础知识。

首先要明确，基本语句日志记录可以通过标准日志记录工具提供，设置log_statement = all，这对监控和其他用途是可以接受的，但不能提供审计定位信息通常所需的详细度。

仅拥有对数据库执行的所有操作的信息是不够的，还必须能够找到日志数据的特点，标记，出问题的特定文字。标准日志记录工具记录的是用户请求的内容，而 pgAudit 专注于满足特定情况时发生问题后的详细情况描述。

在我们编译后，我们还有一些设置需要进行


shared_preload_libraries = 'pgaudit'    # (change requires restart)
#jit_provider = 'llvmjit'               # JIT library to use
pgaudit.log = 'ddl, role'

可用的审计类别：

ddl: 审计所有数据定义语言 (DDL) 语句，例如 CREATE, DROP, ALTER。

role: 审计角色和权限的变化。

read: 审计 SELECT 查询。

write: 审计数据修改操作，例如 INSERT, UPDATE, DELETE。

function: 审计函数和存储过程的调用。

在我们配置后，需要对数据库服务器重新启动。

然后我们模拟在session级别的操作，我们就可以在审计日志中看到对应的记录了。

postgres=# \c dvdrental
You are now connected to database "dvdrental" as user "test".
dvdrental=# create extension pgaudit;
CREATE EXTENSION
dvdrental=# alter system set work_mem = '100MB';
ALTER SYSTEM
dvdrental=#

2024-11-29 00:34:08.116 EST [2992] [test] PGCE PGCE(50444) psql  STATEMENT:  alter system set work_mem = 100m;
2024-11-29 00:34:17.711 EST [2992] [test] PGCE PGCE(50444) psql  LOG:  statement: alter system set work_mem = '100MB';
2024-11-29 00:34:17.713 EST [2992] [test] PGCE PGCE(50444) psql  LOG:  AUDIT: SESSION,2,1,DDL,ALTER SYSTEM,,,alter system set work_mem = '100MB',<not logged>
2024-11-29 00:34:17.713 EST [2992] [test] PGCE PGCE(50444) psql  LOG:  duration: 2.450 ms

同学：老师我这边看到日志上有 audit的日志了，但这个和我们平时日志有什么不同，或者他的意义在哪里呢？谢谢

我：同学咱们试一下，将当前log_statement里面的配置改成none，也就是我们的日志不在进行任何的操作的记录了，这样是不是省下很大的日志空间，通过pgaudit的插件，对我们关心的操作进记录。

同学：发现了，以Audit作为标记，标记了什么样的操作，什么级别，这里提示是session级别的，还提示是DDL的操作，和操作的语句是什么，还有操作的人的账号。

我：对这样我们在进行日志分析的时候，是不是更简单了。

同学：对，老师咱们有没有对这个日志进行分析的技巧呢？

我：有，这里我们可以写一个脚本，来对日志audit的信息进行分析。

const fs = require('fs');
const path = require('path');


const logDir = './logs'; // 这里换成你的PG日志的目录
const alterSystemFile = './alter_system.log';  这里是单独将对系统SESSION 修改alter_system的语句单独挑出来


fs.readdir(logDir, (err, files) => {
    if (err) {
        console.error('无法读取目录:', err);
        return;
    }

   //这里对所有的postgresql 开头的文件都进行分析一个不落
    const logFiles = files.filter(file => file.startsWith('postgresql'));

    logFiles.forEach(logFile => {
        const logFilePath = path.join(logDir, logFile);

     
        fs.readFile(logFilePath, 'utf8', (err, data) => {
            if (err) {
                console.error('无法读取文件:', err);
                return;
            }

            // 我们对文件中的包含audit的语句进行正则处理
            const auditLogs = data.match(/AUDIT:[^\n]+/g);

            if (auditLogs) {
                auditLogs.forEach(log => {
                    // 特别注意session 发起的变更
                    if (log.includes('SESSION')) {
                        const timestampMatch = log.match(/\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}/);
                        const levelMatch = log.match(/SESSION,\d+/);
                        const userMatch = log.match(/identity="([^"]+)"/);
                        const queryMatch = log.match(/CREATE|ALTER.*SYSTEM.*?;/);

                        if (timestampMatch && levelMatch && userMatch && queryMatch) {
                            const timestamp = timestampMatch[0];
                            const level = levelMatch[0];
                            const user = userMatch[1];
                            const query = queryMatch[0];

                            // 将信息打印到文件
                            console.log(`${timestamp} - ${level} - User: ${user} executed: ${query}`);

                            // 如果是 ALTER SYSTEM 语句，单独写入我们特定的文件
                            if (query.includes('ALTER SYSTEM')) {
                                fs.appendFile(alterSystemFile, `${timestamp} - User: ${user} executed: ${query}\n`, (err) => {
                                    if (err) {
                                        console.error('写入 ALTER SYSTEM 文件失败:', err);
                                    }
                                });
                            }
                        }
                    }
                });
            }
        });
    });
});