監視用デフォルトロール

# はじめに
にゃーん
この記事は、[PostgreSQL 10全部ぬこ Advent Calendar 2017](https://qiita.com/advent-calendar/2017/pg10-nuko-only) の6日目のエントリです。
今日は[PGCONF.Asia 2017のDay-2](http://www.pgconf.asia/JA/2017/day-2/)。その後のパーティーにも出席する予定。


# ロールとは
PostgreSQLではロールという概念で、ユーザの権限を設定することができます。といってもPostgreSQLの場合は、ログイン権限のないユーザ、とも言えますが。ロールは他の複数のロールを含むことが可能ですし、ネストも可能です。
（そのために、このロールはどんなロールの権限ももっているのか分かりにくくなることも。そういうときには、[指定したロールに属するロールを再帰クエリで検索する](https://qiita.com/nuko_yokohama/items/ac09ef3dbae9e4990a96)で紹介したようなクエリでロールの関係を調べてみるのもいいかと）。

# デフォルトロール
PostgreSQL 9.6からはPostgreSQLがデフォルトで利用可能なロールが追加されました。
そして、PostgreSQL 10ではデフォルトロールが3つ追加されました。
デフォルトロールについては[昨日のPGCONF.Asiaのセッション](http://www.pgconf.asia/JA/2017/day-1/#A1)でもちょっと話がありましたね。

|ロール名|サポートバージョン|役割|
|:--|--:|:--|
|pg_signal_backend|9.6|他のバックエンドにシグナルを送信する|
|pg_read_all_settings|10|PostgreSQLパラメータのうち特権ユーザでないと参照できないパラメータも参照可能にする。|
|pg_read_all_stats|10|pg_stat_* ビューについて、特権ユーザでないと参照できない情報を参照可能にする。|
|pg_stat_scan_tables|10|統計情報に関するSQL関数のうち、特権ユーザでないと実行できない関数を実行可能にする。|
|pg_monitor|10|上記のpg_read_all_settings, pg_read_all_stats, pg_stat_scan_tablesをメンバとして持つロール。監視に関しては権限を強化したロール。監視のみを行うユーザなんかに付与すると良さげ。|

要するにPostgreSQL 10で追加されたロールは、特権ユーザでないと参照できない情報を一般ユーザでも可能にするためのものです。

## 誤解
このロールが追加されたときに、「もしかして、一般ユーザではpg_stat_* ビューや、pg_* システムカタログが参照できなくなった？」と思ったのですが、実はそーでもなかった。

# デフォルトロールを付与することによる効果
PostgreSQL 10で追加されたロールが、どういった効果をもたらすのか、実際に動作させて確認してみる。
この検証のために、以下の3つのロール(ユーザ)を設定しておく。

```postgres-console
postgres=# \du
                                             List of roles
    Role name     |                         Attributes                         |       Member of        
------------------+------------------------------------------------------------+------------------------
 general_user     |                                                            | {}
 monitor_user     |                                                            | {pg_monitor}
 postgres         | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 scan_tables_user |                                                            | {pg_stat_scan_tables}
 settings_user    |                                                            | {pg_read_all_settings}
 stats_user       |                                                            | {pg_read_all_stats}
```

## pg_read_all_settingsロール
pg_read_all_settingsロールは、特権ロールしか参照できないパラメータを、一般ユーザにも与えることができるロールである。

### 特権ロールしか参照できないパラメータ

みんなお馴染みのSHOW ALLコマンドをpostgresユーザとgeneral_userで実行してみる。

まず、general_userで実行。

```
[nuko@localhost ~]$ psql -U general_user postgres -c "SHOW ALL"
                name                 |      setting       |                                               
           description                                                          
-------------------------------------+--------------------+-------------------------------------------------------------------------------------------------------------------------------
 allow_system_table_mods             | off                | Allows modifications of the structure of system tables.
 application_name                    | psql               | Sets the application name to be reported in statistics and logs.

（中略）

 xmloption                           | content            | Sets whether XML data in implicit parsing and serialization operations is to be considered as documents or content fragments.
 zero_damaged_pages                  | off                | Continues processing past damaged page headers.
(253 rows)
```

次に、postgresユーザで実行。

```
[nuko@localhost ~]$ psql postgres -U postgres  -c "SHOW ALL"
                name                 |               setting                |                                                          description                                                          
-------------------------------------+--------------------------------------+-------------------------------------------------------------------------------------------------------------------------------
 allow_system_table_mods             | off                | Allows modifications of the structure of system tables.
 application_name                    | psql               | Sets the application name to be reported in statistics and logs.

（中略）

 xmloption                           | content                              | Sets whether XML data in implicit parsing and serialization operations is to be considered as documents or content fragments.
 zero_damaged_pages                  | off                                  | Continues processing past damaged page headers.
(269 rows)
```

おわかりいただけただろうか...

general_userで実行したときに見えなかったのに、postgresユーザで実行するとパラメータが見えちゃった...。
いや、当たり前だけどね。

では、一体どのPostgreSQLパラメータが、特権ロールでないと見えないのか。
自分はてっきり、contextがsuperuserのもの、だと思いこんでいたが、実際にはそう単純な話ではない。
PostgreSQL 10.1環境で、一般ロールと特権ロールを用いて、pg_settingsを検索して、その結果の差分をとると、これらのパラメータが特権ロールでないとパラメータとして表示すらされないことが分かった。

| name | context|
|:--|:--|
|  config_file                         | postmaster |
|  data_directory                      | postmaster |
|  external_pid_file                   | postmaster |
|  hba_file                            | postmaster |
|  ident_file                          | postmaster |
|  shared_preload_libraries            | postmaster |
|  unix_socket_directories             | postmaster |
|  krb_server_keyfile                  | sighup |
|  log_directory                       | sighup |
|  log_filename                        | sighup |
|  ssl_ciphers                         | sighup |
|  ssl_dh_params_file                  | sighup |
|  ssl_ecdh_curve                      | sighup |
|  stats_temp_directory                | sighup |
|  dynamic_library_path                | superuser |
|  session_preload_libraries           | superuser |

へぇー。これは知らんかった。やっぱり調べてみるもんだな。

### 本題
前置きが長くなってしまった。このように、一般ユーザでは参照できない、PostgreSQLパラメータは存在するが、pg_read_all_settingsロールは、特権ロールでなくても、これらのパラメータを参照可能にするロールになる。
以下、ただのユーザ(general_user)、特権ユーザ(postgres)、pg_read_all_settingsロール(settings_user)で、PostgreSQLパラメータ hba_file を参照する例を示す。

```
[nuko@localhost ~]$ psql postgres -U general_user -c "SHOW hba_file"
ERROR:  must be superuser or a member of pg_read_all_settings to examine "hba_file"
[nuko@localhost ~]$ psql postgres -U postgres -c "SHOW hba_file"
             hba_file             
----------------------------------
 /home/nuko/pgdata/10/pg_hba.conf
(1 row)

[nuko@localhost ~]$ psql postgres -U settings_user -c "SHOW hba_file"
             hba_file             
----------------------------------
 /home/nuko/pgdata/10/pg_hba.conf
(1 row)
```

## pg_read_all_statsロール
pg_read_all_statsロールは、特権ユーザでないと参照できない情報を、一般ユーザでも参照可能にするロールである。

例えば、セッションの監視でお馴染み、pg_stat_acitivityの、一部の列が特権ロールまたは、pg_read_all_statsロールを持つユーザでないと参照することはできない（権限エラーではなく、~~モザイク~~空白や、特殊な文字列で表示される)

まず、特権ロール(postgres)ユーザで検索してみる。

```
[nuko@localhost ~]$ psql postgres -U postgres -c "SELECT pid, usename, state, query,backend_type FROM pg_stat_activity"
  pid  |   usename    |        state        |                                query                           
      |    backend_type     
-------+--------------+---------------------+----------------------------------------------------------------------+---------------------
 39444 |              |                     |                                                                      | autovacuum launcher
 39446 | postgres     |                     |                                                                      | background worker
 48462 | user_a       | idle                |                                                                      | client backend
 48723 | user_b       | idle in transaction | SELECT 'b';                                                          | client backend
 48975 | general_user | idle                | SELECT 'general';                                                    | client backend
 48984 | postgres     | active              | SELECT pid, usename, state, query,backend_type FROM pg_stat_activity | client backend
 39442 |              |                     |                                                                      | background writer
 39441 |              |                     |                                                                      | checkpointer
 39443 |              |                     |                                                                      | walwriter
(9 rows)
```

次に、一般ユーザ(general_user)ユーザで検索してみる。
そうすると、行数は同じなのだが、以下のように一部の情報が見えなくなっている。

* 自分と同じユーザ以外のstate, backend_typeが空白になる。
* 自分と同じユーザ以外のqueryは```<insufficient privilege>```と表示される。
   * 特殊な文字列を出すのはqueryの値域が確定していないからなんだろうな。

```
[nuko@localhost ~]$ psql postgres -U general_user -c "SELECT pid, usename, state, query,backend_type FROM pg_stat_activity"
  pid  |   usename    | state  |                                query                                 |  backend_type  
-------+--------------+--------+----------------------------------------------------------------------+------
----------
 39444 |              |        | <insufficient privilege>                                             | 
 39446 | postgres     |        | <insufficient privilege>                                             | 
 48462 | user_a       |        | <insufficient privilege>                                             | 
 48723 | user_b       |        | <insufficient privilege>                                             | 
 48975 | general_user | idle   | SELECT 'general';                                                    | client backend
 49087 | general_user | active | SELECT pid, usename, state, query,backend_type FROM pg_stat_activity | client backend
 39442 |              |        | <insufficient privilege>                                             | 
 39441 |              |        | <insufficient privilege>                                             | 
 39443 |              |        | <insufficient privilege>                                             | 
(9 rows)
```

最後に、pg_read_all_statsロールをもつユーザ(stats_user)ユーザで検索してみる。
これは特権ユーザによる検索と同じ結果となる。

```
[nuko@localhost ~]$ psql postgres -U stats_user -c "SELECT pid, usename, state, query,backend_type FROM pg_stat_activity"
  pid  |   usename    |        state        |                                query                           
      |    backend_type     
-------+--------------+---------------------+----------------------------------------------------------------------+---------------------
 39444 |              |                     |                                                                      | autovacuum launcher
 39446 | postgres     |                     |                                                                      | background worker
 48462 | user_a       | idle                |                                                                      | client backend
 48723 | user_b       | idle in transaction | SELECT 'b';                                                          | client backend
 48975 | general_user | idle                | SELECT 'general';                                                    | client backend
 49192 | stats_user   | active              | SELECT pid, usename, state, query,backend_type FROM pg_stat_activity | client backend
 39442 |              |                     |                                                                      | background writer
 39441 |              |                     |                                                                      | checkpointer
 39443 |              |                     |                                                                      | walwriter
(9 rows)

[nuko@localhost ~]$ 
```

正直、どの稼働統計情報ビューのどの列が、特権ユーザのみ表示対象となるのか、全部は確認しきれていないが、いずれにせよ稼働統計情報ビューを参照するユーザにはpg_read_all_statsをつけておくべき、ということだろう。

## pg_stat_scan_tablesロール
pg_stat_scan_tablesロール・・・これは最初文書を読んだときに、何を言っているのかさっぱりわからなかった。
文書にはこう書かれている。

> Execute monitoring functions that may take ACCESS SHARE locks on tables, potentially for a long time.

長時間、テーブルに対して共有ロックをとる可能性のある監視機能を実行する。という文章に読めるのだが・・・

![おまえは何を言ってるんだ.png](https://qiita-image-store.s3.amazonaws.com/0/32626/9e2128e5-6a89-0961-cc86-7a9b45e13ba5.png)

良くわからなかったので、PostgreSQL 10.1のソースアカイブから、このロールを使っている箇所が力技(findとgrep)で探してみる。と・・・見つかった。
どうやら、現状は、contrib/pgstattuple モジュールの関数で使われているようだ。たしかに、contrib/pgstattupleを巨大なテーブルに対して実行すると、長時間かかるし、それにロックも取るよなあ。
ということで実際に動かしてみた。

まず、特権ロール(postgres)ユーザで検索してみる。

```
[nuko@localhost ~]$ psql bench -U postgres -c "SELECT * FROM pgstattuple('pgbench_accounts');"
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
  13434880 |      100000 |  12100000 |         90.06 |                0 |              0 |                  0 |     188960 |         1.41
(1 row)
```

次に一般ユーザ(general_user)で実行。これは権限エラーになる。

```
[nuko@localhost ~]$ psql bench -U general_user -c "SELECT * FROM pgstattuple('pgbench_accounts');"
ERROR:  permission denied for function pgstattuple
```

最後に、pg_stat_scan_tablesロールをもつユーザ(scan_table_user)で実行。これは実行可能。

```
[nuko@localhost ~]$ psql bench -U scan_tables_user -c "SELECT * FROM pgstattuple('pgbench_accounts');"
 table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent 
-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
  13434880 |      100000 |  12100000 |         90.06 |                0 |              0 |                 0 |     188960 |         1.41
(1 row)
```

他に該当する関数があるかどうかは調べていない。悪しからず・・・。

## pg_monitorロール
pg_monitorロールは、ここまで説明したpg_read_all_settings, pg_read_all_stats, pg_stat_scan_tablesのロールを全て包含するロールになる。
なので、監視の役割を詳細に分離するのでなければ、pg_monitorを設定しておけばOK!

# おわりに
今日はPostgreSQL 10に追加された、デフォルトロールについて書いてみました。今後、これらのロールが更に充実していくと、postgresユーザのような特権ロールをもつユーザをなるべく使わなくする運用が可能になりそうです。
まあ、この辺はまだまだOracleには大きく及ばないところですが・・・

# おわりに
このエントリ、予想以上に書くのに時間かかった・・・。
~~なお、PostgreSQL 10のデフォルトロールに少し関連したネタを、[PGCONF.Asia 2017](http://www.pgconf.asia/JA/2017/)の[DAY-2 オフィシャルパーティー](http://www.pgconf.asia/JA/2017/day-2-official-party/)の[ライトニングトーク](http://www.pgconf.asia/JA/2017/lightning-talk/)で発表予定です。~~
諸事情により今日のLTのテーマは変更しますが、お暇なら聞きに来てくださいね！にゃーん！

# 参考：該当するリリースノート
本エントリに関連するPostgreSQL 10リリースノートの記載です。

E.2.3.1.6. Monitoring

> * Add default monitoring roles (Dave Page)