はじめに

にゃーん
この記事は、PostgreSQL 10全部ぬこ Advent Calendar 2017 の7日目のエントリです。

ファイルの一覧表示関数

PostgreSQL 10からPostgreSQLの状態監視のために、以下の3つの関数が追加されました。

pg_ls_logdir()
pg_current_logfile()
pg_ls_waldir

今日はこの関数について、ちょいと説明したいと思いまする。

pg_ls_logdir()

この関数はPostgreSQLのサーバログの出力先にあるファイルの一覧を出力する関数です。
引数はなし。
たとえば、サーバログ出力先として、/tmp/log というディレクトリが設定されているとします。

[nuko@localhost ~]$ psql postgres -c "SHOW log_directory"
 log_directory 
---------------
 /tmp/log
(1 row)

このディレクトリをLinuxのls -lコマンドで参照すると

[nuko@localhost ~]$ ls -l /tmp/log
合計 8
-rw-rw-r--. 1 nuko nuko   0 11月 28 12:55 mysql.log
drwxrwxr-x. 2 nuko nuko   6 11月 28 12:58 oracle18-log
-rw-------. 1 nuko nuko 682 11月 28 12:53 postgresql-2017-11-28_125311.log
-rw-------. 1 nuko nuko 759 11月 28 15:32 postgresql-2017-11-28_125350.log

こうなっているとしましょう。mysql.logというファイルや、oracle18-logというディレクトリは自分で勝手に作ったもので、PostgreSQLのサーバログじゃないです。

この状態で、pg_ls_logdir() 関数を実行してみましょう。この関数はsetof record型を返すので、FROM句に書いてあげると見やすくなります。

[nuko@localhost ~]$ psql postgres -c "SELECT * FROM pg_ls_logdir()"
               name               | size |      modification      
----------------------------------+------+------------------------
 postgresql-2017-11-28_125311.log |  682 | 2017-11-28 12:53:50+09
 postgresql-2017-11-28_125350.log |  759 | 2017-11-28 15:32:03+09
 mysql.log                        |    0 | 2017-11-28 12:55:21+09
(3 rows)

さて、上記から分かることですが、pg_ls_dir()はこんな動作をするようです。

別にlog_line_prefixに一致しないファイルでも出力します。
ディレクトリは出力対象にはしません

pg_current_logfile()

この関数は名前のとおり、現在書き込みを行っているサーバログファイル名を返却する関数です。
引数はなし。
出力結果は、フルパスで表示されます。

[nuko@localhost ~]$ psql postgres -c "SELECT pg_current_logfile()"
            pg_current_logfile             
-------------------------------------------
 /tmp/log/postgresql-2017-11-28_125350.log
(1 row)

pg_ls_waldir()

この関数はPostgreSQLの更新ログ(wal)ディレクトリ内にあるファイルの一覧を出力する関数です。
引数はなし。

[nuko@localhost ~]$ psql postgres -c "SELECT * FROM pg_ls_waldir()"
           name           |   size   |      modification      
--------------------------+----------+------------------------
 00000001000000000000000D | 16777216 | 2017-11-28 15:32:55+09
 00000001000000000000000E | 16777216 | 2017-09-26 16:13:33+09
 00000001000000000000000F | 16777216 | 2017-09-26 16:34:05+09
 000000010000000000000010 | 16777216 | 2017-09-26 16:35:28+09
 000000010000000000000011 | 16777216 | 2017-09-26 16:36:27+09
 000000010000000000000012 | 16777216 | 2017-09-26 16:36:41+09
 000000010000000000000013 | 16777216 | 2017-09-26 16:38:40+09
 000000010000000000000014 | 16777216 | 2017-09-26 16:39:10+09
 000000010000000000000015 | 16777216 | 2017-09-26 16:39:27+09
 000000010000000000000016 | 16777216 | 2017-09-26 16:39:27+09
 walwal                   |        0 | 2017-11-28 17:48:51+09
(11 rows)

この関数も、特にWALセグメントファイルかどうかチェックしているわけではなく、単にpg_walディレクトリ上にある通常ファイルをリストしているだけですね。また、このディレクトリ上にはarchive_statusディレクトリが存在するはずですが、それは出力対象にはなりません。

pg_ls_dir()との違い

さて、PostgreSQLのかなり古いバージョン(8.2?)から、汎用ファイルアクセス関数 pg_ls_dir() というSQL関数がサポートされています。

pg_ls_dir(dirname text [, missing_ok boolean, include_dot_dirs boolean])

これは指定したディレクトリ内のファイル名(オプションによって隠しファイルも出力)してくれます。
たとえば、ls_pg_log_dir()の結果から、ファイル名だけを出力するような使い方と

[nuko@localhost ~]$ psql postgres -c "SELECT name FROM pg_ls_logdir()"               name               
----------------------------------
 postgresql-2017-11-28_125311.log
 postgresql-2017-11-28_125350.log
 mysql.log
(3 rows)

と同じような出力を、pg_ls_dir()を使って出力することができます。
(厳密には、サブディレクトリ名(oracle18-log)も出力するので、ちょっと挙動は違いますが)

[nuko@localhost ~]$ psql postgres -c \
> "SELECT * FROM pg_ls_dir((SELECT setting FROM pg_settings WHERE name = 'log_directory'), false, false)"
            pg_ls_dir             
----------------------------------
 postgresql-2017-11-28_125311.log
 postgresql-2017-11-28_125350.log
 mysql.log
 oracle18-log
(4 rows)

利用可能なロールが異なる

ここまで見ると、pg_ls_logdir()やpg_ls_waldir()はpg_ls_dir()のラッパ関数のようにも思えますが、実は本質的な違いはそこではなく、pg_ls_logdir()とpg_ls_waldirはがPostgreSQLのデフォルトロールであるpg_monitorロールで利用可能ということです。
これまで示した実行例は、全て特権ユーザ(postgres)で実行していました。
そう、pg_ls_dir()関数は、実は特権ロールを持つユーザでなくては実行できません。

これに対して、pg_ls_logdir()とpg_ls_waldir()は、PostgreSQL 10から導入されたpg_monitorロールを含むロールであれば実行可能です。これにより、特権ユーザではなく、「監視に特化した」ロールをもつ監視ユーザが、サーバログの一覧やWALセグメントファイルの一覧を取得することが可能になります。
pg_monitorロールについては、前日のAdvent Calenderでもちょっと書いているので、そっちも参照してもらえると嬉しいです。

たとえば、こんなロール/ユーザが設定されているとします。

[nuko@localhost ~]$ psql postgres -c "\du"                                         List of roles
    Role name    |                         Attributes                         |   Member of    
-----------------+------------------------------------------------------------+----------------
 general_user    |                                                            | {}
 monitor_user    |                                                            | {pg_monitor}
 postgres        | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

ロール名	superuser	member of	ロールの権限
general_user	No		一般国民。特殊なロール権限をもたないユーザ。
monitor_user	No	pg_monitor	特権ユーザしか参照できない一部のシステムカタログ情報の参照や、特権ユーザしか実行できない一部の監視用SQL関数を実行可能。
postgres	Yes		上級国民。特権ユーザ。PostgreSQL界では何でも操作できる。

それぞれのユーザが pg_ls_logdir(), pg_current_logfile(), pg_ls_waldir()を実行するとどうなるか、簡単にまとめるとこんな感じ。

関数名	postgres	monitor_user	general_user
pg_ls_dir()	実行可	実行不可	実行不可
pg_ls_logdir()	実行可	実行可	実行不可
pg_current_logfile()	実行可	実行不可	実行不可
pg_ls_waldir()	実行可	実行可	実行不可

postgresユーザは特権ユーザなので、関数の実行に制限はありません。
monitorユーザはpg_monitorロールの能力も持っているので、pg_ls_logdir()は実行できますが、pg_ls_dir()は権限エラーになります。なぜか、pg_current_logfile()も権限エラーになります。
- pg_ls_logdir()は実行できるのに、なんでpg_current_logfile()は権限エラーになるねん。特権ユーザじゃなければ汎用ファイルアクセス関数を使ったアクセスはできないんだから、pg_monitorロールがあれば実行できてもいいんじゃね？
- これってhackersに投げるべきなんだろうか。
general_userはpg_ls_dir(), pg_current_logfile(), pg_log_dir()ともに実行できません。

一般国民(general_user)の実行例を示します。

[nuko@localhost ~]$ psql postgres -U general_user -c "SELECT * FROM pg_ls_logdir()"
ERROR:  permission denied for function pg_ls_logdir
[nuko@localhost ~]$ psql postgres -U general_user -c "SELECT * FROM pg_ls_dir('/tmp/log')"
ERROR:  must be superuser to get directory listings

一般国民ごときはファイル名を知ることすら許されないのです。

pg_monitorロールを持つ中級国民(monitor_user)の実行例を示します。

[nuko@localhost ~]$ psql postgres -U monitor_user -c "SELECT * FROM pg_ls_logdir()"
               name               | size |      modification      
----------------------------------+------+------------------------
 postgresql-2017-11-28_125311.log |  682 | 2017-11-28 12:53:50+09
 postgresql-2017-11-28_125350.log | 1820 | 2017-11-28 18:19:54+09
 mysql.log                        |    0 | 2017-11-28 12:55:21+09
(3 rows)

[nuko@localhost ~]$ psql postgres -U monitor_user -c "SELECT * FROM pg_ls_dir('/tmp/log')"
ERROR:  must be superuser to get directory listings

中級国民は一部の開示された範囲であれば、データベースクラスタ内のファイル名を知ることはできるようです。もちろん無制限にファイル名を知ることはできない。

上級国民(postgres)は何でも実行できるので実行例は省略。
ロールと各関数の実行可否をまとめるとこんな感じ。

まとめ

ということで、今日はサーバログの一覧を表示したりWALセグメントファイルの一覧を表示するSQL関数について紹介しました。
pg_ls_logdir()はリモートサーバからサーバログの一覧を取得するのに役に立ちそうですし、pg_ls_waldir()はリモートサーバから、WALセグメントファイルの一覧取得に役に立ちそうです。
アーカイブログについては、archive_commandでコマンドを指定しちゃうから、こういう取得関数が使えないのが、ちょいと残念ではありますが。

参考：該当するリリースノート

本エントリに関連するPostgreSQL 10リリースノートの記載です。

E.2.3.1.6.1. Logging

Add functions to return the log and WAL directory contents (Dave Page)

Add function pg_current_logfile() to read logging collector's current stderr and csvlog output file names (Gilles Darold)

サーバログとWALファイルの一覧を表示する