はじめに
にゃーん
この記事は、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.
長時間、テーブルに対して共有ロックをとる可能性のある監視機能を実行する。という文章に読めるのだが・・・
良くわからなかったので、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のDAY-2 オフィシャルパーティーのライトニングトークで発表予定です。
諸事情により今日のLTのテーマは変更しますが、お暇なら聞きに来てくださいね！にゃーん！
参考：該当するリリースノート
本エントリに関連するPostgreSQL 10リリースノートの記載です。
E.2.3.1.6. Monitoring
- Add default monitoring roles (Dave Page)