PostgreSQL
PostgreSQL10

はじめに

にゃーん
この記事は、PostgreSQL 10全部ぬこ Advent Calendar 2017 の6日目のエントリです。
今日はPGCONF.Asia 2017のDay-2。その後のパーティーにも出席する予定。

ロールとは

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

デフォルトロール

PostgreSQL 9.6からはPostgreSQLがデフォルトで利用可能なロールが追加されました。
そして、PostgreSQL 10ではデフォルトロールが3つ追加されました。
デフォルトロールについては昨日のPGCONF.Asiaのセッションでもちょっと話がありましたね。

ロール名 サポートバージョン 役割
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=# \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

良くわからなかったので、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 2017DAY-2 オフィシャルパーティーライトニングトークで発表予定です。
諸事情により今日のLTのテーマは変更しますが、お暇なら聞きに来てくださいね!にゃーん!

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

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

E.2.3.1.6. Monitoring

  • Add default monitoring roles (Dave Page)