Go to Qiita Advent Calendar 2024 Top
LoginSignup
4
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@nuko_yokohama(nuko yokohama)

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

Last updated at Posted at 2017-02-14

はじめに

なんか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のpg_hba_file_rules を見てもらえばいいかと。

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と結合させてみるとか。

かなあ。

4
3
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?