PostgreSQL 10がやってくる（その3） pg_hba_file_rulesを使ってみる

# はじめに
なんかPostgreSQL 10から、ホストベース認証ファイル(pg_hba.conf)をビューとして参照できる機能が入っているので見てみることにした。

# pg_hba.conf
PostgreSQLの認証を管理する設定ファイル。詳細は[PostgreSQL文書 pg_hba.confファイル] (http://www.postgresql.jp/document/9.6/html/auth-pg-hba-conf.html)を参照。

まあ、こんなファイルですね。

```
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     nuko                                trust
#host    replication     nuko        127.0.0.1/32            trust
#host    replication     nuko        ::1/128                 trust
```

# pg_hba_file_rules

これはPostgreSQL 10に入る(かもしれない)システムビュー。

こんな感じのビューを提供する。

```
postgres=# TABLE pg_hba_file_rules ;
 line_number | type  | database | user_name |  address  |                 netmask                 | auth_method | options | error 
-------------+-------+----------+-----------+-----------+-----------------------------------------+-------------+---------+-------
          84 | local | {all}    | {all}     |           |                                         | trust       |         | 
          86 | host  | {all}    | {all}     | 127.0.0.1 | 255.255.255.255                         | trust       |         | 
          88 | host  | {all}    | {all}     | ::1       | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust       |         | 
(3 rows)
```

ビューなので、普通にWHERE句やORDER BYも使えるし、他のテーブルとも結合できる。

```
postgres=# SELECT type, database, user_name FROM pg_hba_file_rules WHERE type = 'host';
 type | database | user_name 
------+----------+-----------
 host | {all}    | {all}
 host | {all}    | {all}
(2 rows)
```
このビューの面白いところは、line_numberなんて列があることだ。
そう、このビューはpg_hba.confファイルを読み取って、ホストベース認証として有効な行も報告するのだ。
たとえば、

```
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     nuko                                trust
#host    replication     nuko        127.0.0.1/32            trust
#host    replication     nuko        ::1/128                 trust
```

このファイルの内容だと、

```
postgres=# SELECT line_number, type, database, user_name FROM pg_hba_file_rules;
 line_number | type  | database | user_name 
-------------+-------+----------+-----------
           4 | local | {all}    | {all}
           6 | host  | {all}    | {all}
           8 | host  | {all}    | {all}
(3 rows)
```

こう、報告されるわけだが、さっきのファイルの先頭2行(コメント行と空白行)を削除すると

```
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     nuko                                trust
#host    replication     nuko        127.0.0.1/32            trust
#host    replication     nuko        ::1/128                 trust
```

こう編集したあと、PostgreSQLサーバをリロードするとpg_hba_file_rulesの結果も変更される。

```
postgres=# SELECT line_number, type, database, user_name FROM pg_hba_file_rules;
 line_number | type  | database | user_name 
-------------+-------+----------+-----------
           2 | local | {all}    | {all}
           4 | host  | {all}    | {all}
           6 | host  | {all}    | {all}
(3 rows)
```

ファイル内の行番号という物理的な情報も表示するから、このビューはpg_hba_rulesじゃなくて、pg_hba_file_rulesなんだなーと思った。

## 各エントリの内容
まあ、これも[PostgreSQL 10devel Documentation](https://www.postgresql.org/docs/devel/static/index.html)の[pg_hba_file_rules](https://www.postgresql.org/docs/devel/static/view-pg-hba-file-rules.html) を見てもらえばいいかと。

line_number列とerror列が、pg_hba.confには存在しない情報になる。
error列は、リロードしたときにエラーとなったときは無視されるのだけど、そのときのエラー原因が表示されるので、「この認証設定が何か挙動おかしいんだけど？」みたいなときの解析情報に使えそうだ。

たとえば、以下のようなpg_hba.confファイルを

```
# "local" is for Unix domain socket connections only
local   postgres                                                trust
local   all             all                                     trust
# IPv4 local connections:
host    foo_db          foo             127.0.0.1/32            md4
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/256                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     nuko                                trust
nost     replication     nuko        127.0.0.1/32            trust
#host    replication     nuko        ::1/128                 trust
```

```pg_ctl reload```でリロードして、pg_hba_file_rulesを参照すると、error列にエラー内容がレポートされる。

```
postgres=# SELECT line_number, error FROM pg_hba_file_rules ;
 line_number |                  error                   
-------------+------------------------------------------
           2 | end-of-line before authentication method
           3 | 
           5 | invalid authentication method "md4"
           6 | 
           8 | invalid CIDR mask in address "::1/256"
          12 | invalid connection type "nost"
```

- 2行目：認証方式の前に行終端になった。(DB名の指定が抜けていた)
- 5行目：不正な認証方式md4を指定。(正しくはmd5)
- 8行目：不正なマスクアドレスの指定(正しくは/256)
- 12行目：不正なtypeの指定(正しくはhost)

これらのエラー情報はreload時にサーバログにも出力されるけど、後から確認できるのはいい感じ。

## pg_hba_file_rulesの定義

psqlのメタコマンド ```\d+``` でpg_hba_file_rulesの定義を確認してみる。

```
postgres=# \d+ pg_hba_file_rules 
                       View "pg_catalog.pg_hba_file_rules"
   Column    |  Type   | Collation | Nullable | Default | Storage  | Description 
-------------+---------+-----------+----------+---------+----------+-------------
 line_number | integer |           |          |         | plain    | 
 type        | text    |           |          |         | extended | 
 database    | text[]  |           |          |         | extended | 
 user_name   | text[]  |           |          |         | extended | 
 address     | text    |           |          |         | extended | 
 netmask     | text    |           |          |         | extended | 
 auth_method | text    |           |          |         | extended | 
 options     | text[]  |           |          |         | extended | 
 error       | text    |           |          |         | extended | 
View definition:
 SELECT a.line_number,
    a.type,
    a.database,
    a.user_name,
    a.address,
    a.netmask,
    a.auth_method,
    a.options,
    a.error
   FROM pg_hba_file_rules() a(line_number, type, database, user_name, address, netmask, auth_method, options, error);
```

このビューのソースはpg_hba_file_rules()というSQL関数のようだ。なので、このビューに対しては当然ながらDMLによる更新はかけられない。

## pg_hba_file_rulesの参照権限

pg_hba_file_rulesはスーパーユーザ権限を持つロールしか参照できない。
例えばスーパーユーザ権限をもたないfooというユーザがpg_hba_file_rulesを参照しようとすると権限がないと怒られてしまう。

```
$ psql -U foo postgres -c "TABLE pg_hba_file_rules"
ERROR:  permission denied for relation pg_hba_file_rules
```

一般ユーザが他のユーザも含む認証情報を参照できちゃいけない気がするので、そのへんはしっかりガードしてあるようだ。

# 大道芸 - SQLを使ってpg_hba.confを生成する

さて、pg_hba_file_rulesでホストベース認証情報が参照できるようになったので、SQLでpg_hba.confファイルを生成できるんじゃないか？
ということでやってみた。

## 新規エントリの挿入

こんなSQLファイルを用意する。

```
$ cat /tmp/insert_hba.sql 
COPY (
SELECT
  type,
  array_to_string(database, ','),
  array_to_string(user_name, ','),
  address,
  netmask,
  auth_method,
  array_to_string(options)
  FROM (
    SELECT line_number, type, database, user_name, address, netmask, auth_method, options FROM pg_hba_file_rules
    UNION
    SELECT 1 as line_number, 'host' as host, '{foo_db}' as database, '{foo}' as user_name, 'localhost' as address, null as netmask, 'md5' as auth_method, null as options
  ) as t ORDER BY line_number
) TO '/tmp/pgdata/pg_hba.conf.new' (NULL '');
```

最初のSELECTは現在のpg_hba_file_rulesの値を取るためのもの。
そして、UNION後のSELECTに追加したいエントリを加える。
行番号を使って、どの位置に配置するかも指定できるようにした。
で、この結果(行番号は除去する)をCOPY TOに渡してファイルを作成するというもの。

このSQLを動かしてみた。

```
$ psql -U nuko postgres -f /tmp/insert_hba.sql 
COPY 4
```

なんか4行の結果をファイルに出力してますね。
で、COPY先のファイルを参照する。

```
host    foo_db  foo localhost       md5
local   all all         trust
host    all all 127.0.0.1   255.255.255.255 trust
host    all all ::1 ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff trust
```

はい、追加できましたね。コメントは全部消えちゃうけどｗ

## 既存エントリの削除

これはSELECTにWHERE句を設定した検索結果をCOPY TOでファイル出力すればOK。

## 既存エントリの更新

これは・・・一撃では難しいかな。削除＆挿入の組み合わせが必要そう。

## やってはみたものの

まあ、フツーにエディタでpg_hba.confを設定したほうが良いような気がしてきた。

# 使いみち
他の使いみちをちょっと考えてみた。

- リモートから認証設定を確認できる、というのが一番かな。
- 試験環境の認証設定とか取るのには便利？
- あとは、pg_hba.confには書いたけど、存在しないデータベース名やロール名のチェックのために、pg_database, pg_rolesと結合させてみるとか。

かなあ。