More than 1 year has passed since last update.

PostgreSQL log_statementパラメータによる監査ログの取得

Last updated at 2022-11-09Posted at 2022-11-09

こんにちは！

今回は、log_statement パラメータによる監査ログについてまとめていきます。

log_statement ってなに

log_statement は、どのSQL分をログに記録するかを制御します。
このパラメータは、スーパーユーザーのみ変更でき、設定値は以下の4つがあります。

none
出力をoffにする。デフォルトの状態。
ddl
CREATE、ALTER、およびDROP文といったデータ定義分を全てログに記録します。
mod
ddl文に加えて、INSERT、UPDATE、DELETE、TRUNCATE、およびCOPY FROMといった、データ変更文をログに記録します。
PREPAREとEXPLAIN ANALYZEコマンドも、そこに含まれるコマンドが適切な種類であればログが録られます。
all
全てを記録する。
単純な構文エラーは記録されないため、記録したい場合はlog_min_error_statement を eror に設定することで記録される。

情報ソースはこちら

log_statement='none'

1.設定変更

postgres=# alter system set log_statement='none';
ALTER SYSTEM

2.サーバー再起動

[postgres@postgresql ~]$ pg_ctl stop
サーバーは停止しました
[postgres@postgresql ~]$ pg_ctl start
サーバー起動完了

3.テストデータ作成

postgres=# create table ta_1(id integer,name varchar(10));
CREATE TABLE
postgres=# insert into ta_1  values(1,'tanaka');
INSERT 0 1
postgres=# select * from ta_1;
 id |  name
----+--------
  1 | tanaka
(1 行)

4.監査ログ確認
none の設定通り、監査ログが出力していません。

[postgres@postgresql log]$ ll
合計 0

5分ほど時間を空けると以下のログが出力されていました。
チェックポイントの実行履歴ですね。

[postgres@postgresql log]$ ll
合計 4
-rw-------. 1 postgres postgres 703 11月  9 00:37 postgresql-Wed.log
[postgres@postgresql log]$ tail -f postgresql-Wed.log
-rw-------. 1 postgres postgres 322 11月  9 00:02 postgresql-Wed.log
2022-11-09 00:02:41.346 EST [5213] LOG:  checkpoint starting: time
2022-11-09 00:02:43.520 EST [5213] LOG:  checkpoint complete: wrote 24 buffers (0.1%); 0 WAL file(s) added, 0 removed, 0 recycled; write=2.114 s, sync=0.057 s, total=2.174 s; sync files=18, longest=0.056 s, average=0.004 s; distance=99 kB, estimate=99 kB

チェックポイント
チェックポイントとは、溜まっているトランザクションログを削除する機能のことです。
これがあることでデータの信頼性を確保しています。
checkpoint_timeout で制御され、デフォルトの実行間隔は5分です。
また、もう一つの制御方法として、checkpoint_segments パラメータによる最大ログファイル数があります。
こちらのデフォルトは3セグメントです。

postgres=# select name, setting, unit from pg_settings where name='checkpoint_timeout';
        name        | setting | unit
--------------------+---------+------
 checkpoint_timeout | 300     | s
(1 行)

log_statement='ddl'

1.設定変更

postgres=# alter system set log_statement='ddl';
ALTER SYSTEM

2.サーバー再起動

[postgres@postgresql ~]$ pg_ctl stop
サーバーは停止しました
[postgres@postgresql ~]$ pg_ctl start
サーバー起動完了

3.テストデータ作成

postgres=# create table ta_1(id integer,name varchar(10));
CREATE TABLE
postgres=# insert into ta_1  values(1,'tanaka');
INSERT 0 1
postgres=# select * from ta_1;
 id |  name
----+--------
  1 | tanaka
(1 行)

4.監査ログ確認
今回は PostgreSQL 再起動から記録されていますね。
また、ddlのcreate文は記録されていますが、insert文、select文は記録されていません。

[postgres@postgresql log]$ ll
合計 4
-rw-------. 1 postgres postgres 703 11月  9 00:21 postgresql-Wed.log
[postgres@postgresql log]$ tail -f postgresql-Wed.log
2022-11-09 00:21:31.286 EST [6261] LOG:  starting PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit
2022-11-09 00:21:31.287 EST [6261] LOG:  listening on IPv6 address "::1", port 5432
2022-11-09 00:21:31.287 EST [6261] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2022-11-09 00:21:31.289 EST [6261] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-11-09 00:21:31.296 EST [6261] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-11-09 00:21:31.302 EST [6265] LOG:  database system was shut down at 2022-11-09 00:21:29 EST
2022-11-09 00:21:31.307 EST [6261] LOG:  database system is ready to accept connections
2022-11-09 00:23:28.152 EST [6270] LOG:  statement: create table ta_1(id integer,name varchar(10));

log_statement='mod'

1.設定変更

postgres=# alter system set log_statement='mod';
ALTER SYSTEM

2.サーバー再起動

[postgres@postgresql ~]$ pg_ctl stop
サーバーは停止しました
[postgres@postgresql ~]$ pg_ctl start
サーバー起動完了

3.テストデータ作成

postgres=# create table ta_1(id integer,name varchar(10));
CREATE TABLE
postgres=# insert into ta_1  values(1,'tanaka');
INSERT 0 1
postgres=# select * from ta_1;
 id |  name
----+--------
  1 | tanaka
(1 行)

4.監査ログ確認
今回は insert文も記録されていますが、select文は記録されていません。

[postgres@postgresql log]$ ll
合計 4
-rw-------. 1 postgres postgres 703 11月  9 00:30 postgresql-Wed.log
[postgres@postgresql log]$ tail -f postgresql-Wed.log
2022-11-09 00:30:30.407 EST [6643] LOG:  starting PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit
2022-11-09 00:30:30.407 EST [6643] LOG:  listening on IPv6 address "::1", port 5432
2022-11-09 00:30:30.407 EST [6643] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2022-11-09 00:30:30.409 EST [6643] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-11-09 00:30:30.415 EST [6643] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-11-09 00:30:30.457 EST [6647] LOG:  database system was shut down at 2022-11-09 00:30:28 EST
2022-11-09 00:30:30.462 EST [6643] LOG:  database system is ready to accept connections
2022-11-09 00:30:59.664 EST [6660] LOG:  statement: create table ta_1(id integer,name varchar(10));
2022-11-09 00:31:04.788 EST [6660] LOG:  statement: insert into ta_1  values(1,'tanaka');

log_statement='all'

1.設定変更

postgres=# alter system set log_statement='all';
ALTER SYSTEM

2.サーバー再起動

[postgres@postgresql ~]$ pg_ctl stop
サーバーは停止しました
[postgres@postgresql ~]$ pg_ctl start
サーバー起動完了

3.テストデータ作成

postgres=# create table ta_1(id integer,name varchar(10));
CREATE TABLE
postgres=# insert into ta_1  values(1,'tanaka');
INSERT 0 1
postgres=# select * from ta_1;
 id |  name
----+--------
  1 | tanaka
(1 行)

4.監査ログ確認
all で設定したので、実行したコマンドがすべて記録されていますね。

[postgres@postgresql log]$ ll
合計 4
-rw-------. 1 postgres postgres 703 11月  9 00:37 postgresql-Wed.log
[postgres@postgresql log]$ tail -f postgresql-Wed.log
2022-11-09 00:37:54.859 EST [6934] LOG:  starting PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit
2022-11-09 00:37:54.860 EST [6934] LOG:  listening on IPv6 address "::1", port 5432
2022-11-09 00:37:54.860 EST [6934] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2022-11-09 00:37:54.861 EST [6934] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-11-09 00:37:54.867 EST [6934] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-11-09 00:37:54.909 EST [6938] LOG:  database system was shut down at 2022-11-09 00:37:45 EST
2022-11-09 00:37:54.914 EST [6934] LOG:  database system is ready to accept connections
2022-11-09 00:38:50.259 EST [6945] LOG:  statement: create table ta_1(id integer,name varchar(10));
2022-11-09 00:38:53.761 EST [6945] LOG:  statement: insert into ta_1  values(1,'tanaka');
2022-11-09 00:38:57.202 EST [6945] LOG:  statement: select * from ta_1;

これで4つの設定項目はすべて確認できました。
以上です！

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up