メンテナンス専用スーパーユーザ
はじめに
みなさん、こんにちは。ぬこ＠横浜です。
この記事はPostgreSQL Advent Calendar 2015 - Qiitaの5日目です。
4日目は sayamada さんに書いていただきました。
さて・・・深刻なネタ不足です。困りました。
困ったので、先日のPostgreSQLカンファレンス2015のライトニングトークで喋ったネタ(フックの鬼)の続編を書いてみようと思います。
まず、本編に入る前に、ライトニングトークで喋った、誰得拡張の pg_sulog について簡単に説明します。
pg_sulog
このPostgreSQL拡張モジュールはHOOK拡張機能を使ったもので、非常に簡単に言えば
という、何の役に立つのかわからなさそうな拡張モジュールです。
例えば、以下のようにスーパーユーザ(admin, postgres)と一般ユーザ(nuko)が存在する環境があるとします。
$ psql sampledb -U nuko -c "\du"
ロール一覧
ロール名 | 属性 | メンバー
----------+----------------------------------------------------------------------------------+----------
admin | スーパーユーザ, ロールを作成できる, DBを作成できる
| {}
nuko | | {}
postgres | スーパーユーザ, ロールを作成できる, DBを作成できる, レプリケーション, Bypass RLS | {}
$pg_sulogを組み込むと、log_statementの設定に関わらず、スーパーユーザ権限を持つユーザの操作のみをサーバログに出力します。
- クライアント側ログ
$ psql sampledb -U admin -c "SELECT 2"
WARNING: pg_sulog: 2015-12-05 10:20:03 JST [logging] user=admin SELECT 2
?column?
----------
2
(1 行)
- サーバログ
WARNING: pg_sulog: 2015-12-05 10:20:03 JST [logging] user=admin SELECT 2
また、オプションで、スーパーユーザ権限を持つユーザの操作を全てブロックするという誰得な機能があります。
- クライアント側ログ
$ psql sampledb -U admin -c "SELECT 2" WARNING: pg_sulog: 2015-12-05 10:22:17 JST [blocked] user=admin SELECT 2 SELECT 0 $ psql sampledb -U admin -c "CREATE TABLE foo (id int)" WARNING: pg_sulog: 2015-12-05 10:22:37 JST [blocked] user=admin CREATE TABLE foo (id int) CREATE TABLE $ psql sampledb -U admin -c "CREATE TABLE foo (id int)" WARNING: pg_sulog: 2015-12-05 10:22:38 JST [blocked] user=admin CREATE TABLE foo (id int) CREATE TABLE
- サーバログ
WARNING: pg_sulog: 2015-12-05 10:22:17 JST [blocked] user=admin SELECT 2 WARNING: pg_sulog: 2015-12-05 10:22:37 JST [blocked] user=admin CREATE TABLE foo (id int) WARNING: pg_sulog: 2015-12-05 10:22:38 JST [blocked] user=admin CREATE TABLE foo (id int)
- クライアント側を見ると分かるように
- SELECTの結果が返却されない。
- DDLもブロックします。"CREATE TABLE"とメッセージは出るけど、実は実行されていない。
という、スーパーユーザへのいやがらせとして、作ってみた誰得機能です。
スーパーユーザなんでもできちゃう問題
で、話は変わりますが、昔からPostgreSQLのスーパーユーザは全ての権限を無視するので、なんでもできちゃうという問題があります。
データベースのメンテナンスのためにスーパーユーザ権限で動作はしたいけど、スーパーユーザでログインしちゃうと
- 任意のユーザデータの不正な参照
- 任意のユーザデータの不正な更新(改竄)
- ユーザのスキーマ情報の不正な参照や更新
- PostgreSQLの設定自体の更新
と、やりたい放題です。
ということで、先ほど紹介した誰得モジュールをちょっとだけ改造して、スーパーユーザ権限だと、特定のSQLコマンドしか発行できなくするという機能を追加しました。
pg_sulog・改
pg_sulogはGithub上に公開してあります。
https://github.com/nuko-yokohama/pg_sulog
こいつをビルド＆インストールします(今回はPostgreSQL 9.5 beta2で試してます)。
[nuko@localhost pg_sulog]$ ls LICENSE README.md pg_sulog--1.0.sql pg_sulog.conf sql Makefile expected pg_sulog.c pg_sulog.control [nuko@localhost pg_sulog]$ make USE_PGXS=1 install （略）
pg_sulog.conf に設定のサンプルが書いてあります。postgresql.confの末尾にcatすると楽かと。
[nuko@localhost pg_sulog]$ cat pg_sulog.conf # pg_sulog configuration shared_preload_libraries = pg_sulog #pg_sulog.mode = 'LOGGING' # LOGGING or MAINTENANCE or BLOCK
オプションは pg_sulog.mode だけです。
- LOGGING: スーパユーザ操作のロギングのみ行います。
- BLOCK:スーパーユーザ操作の操作を全てブロックし、その操作をロギングします。
- MAINTENANCE: VACCUM, ANALYZE, REINDEX, CLUSTER コマンドの実行のみ許容し、その他のコマンドはブロックします。操作のロギングもｓます。
ということで、pg_sulog.mode = 'MAINTENANCE' に設定して、PostgreSQLを再起動します。
起動すると、pg_sulogをどのモードで組み込んだのかを表示します。
LOG: pg_sulog: initialized. mode=MAINTENANCE LOG: database system was shut down at 2015-12-05 10:42:44 JST LOG: MultiXact member wraparound protections are now enabled LOG: database system is ready to accept connections LOG: autovacuum launcher started
さて、この環境にはユーザ nuko が作成した sampledb があり、その中に test というテーブルが存在します。
$ psql sampledb -U nuko -c "\d foo"
テーブル "public.foo"
列 | 型 | 修飾語
------+---------+--------
id | integer |
data | text |
インデックス:
"foo_pkey" UNIQUE, btree (id)
$ psql sampledb -U nuko -c "TABLE foo LIMIT 3"
id | data
----+----------------------------------
1 | db8bc177002555502079ded2a4aec157
2 | a3cab330c03ea5dc60747f155c822786
3 | c69cf1b05ac98d974a2a94c236560b7d
(3 行)adminやpostgresqlといったユーザは、このテーブルに対してVACUUMやANALYZE、REINDEXなどのメンテンナンスコマンドは実行できます。
- VACUUMの例
$ psql sampledb -U postgres -c "VACUUM VERBOSE foo" WARNING: pg_sulog: 2015-12-05 10:49:32 JST [logging] user=postgres VACUUM VERBOSE foo INFO: vacuuming "public.foo" INFO: index "foo_pkey" now contains 10000 row versions in 30 pages （略） CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM $
- REINDEXの例
$ psql sampledb -U postgres -c "REINDEX (VERBOSE) TABLE foo" WARNING: pg_sulog: 2015-12-05 10:52:50 JST [logging] user=postgres REINDEX (VERBOSE) TABLE foo INFO: index "foo_pkey" was reindexed DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: index "pg_toast_16470_index" was reindexed DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. REINDEX $
しかし、postgresユーザでは、fooテーブルの内容を見ることはできない。
もちろん、レコードを削除したり、テーブルをTRUNCTAE/DROPもできない。
$ psql sampledb -U postgres -c "TABLE foo LIMIT 3" WARNING: pg_sulog: 2015-12-05 10:54:33 JST [blocked] user=postgres TABLE foo LIMIT 3 SELECT 0 $ psql sampledb -U postgres -c "DELETE FROM foo" WARNING: pg_sulog: 2015-12-05 10:54:53 JST [blocked] user=postgres DELETE FROM foo DELETE 0 $ psql sampledb -U postgres -c "TRUNCATE foo" WARNING: pg_sulog: 2015-12-05 10:55:03 JST [blocked] user=postgres TRUNCATE foo TRUNCATE TABLE $ psql sampledb -U postgres -c "DROP TABLE foo" WARNING: pg_sulog: 2015-12-05 10:55:10 JST [blocked] user=postgres DROP TABLE foo DROP TABLE $ psql sampledb -U nuko -c "TABLE foo LIMIT 3" id | data ----+---------------------------------- 1 | db8bc177002555502079ded2a4aec157 2 | a3cab330c03ea5dc60747f155c822786 3 | c69cf1b05ac98d974a2a94c236560b7d (3 行)
こうやって、無理やり postgres などのスーパーユーザによる操作を制限することで、誤ってデータベースオブジェクトを破壊しちゃうような事故とか、不正な参照等を防止できたりしないかなーと思っています。
（このモジュール自体はLT発表のためにやっつけで作ったものだけど）
もっとも、こういう機能を真面目に使いたい場合には、永安さんの作ったsql_firewall とか 海外さんが作ったcontribモジュールのsepgsqlなどの適用を考えたほうがいいとは思いますがｗ
おわりに
ということで、今日のエントリは誰得な自作モジュールで、メンテナンス専用ユーザを疑似ってみるという誰得な話でした。
明日の PostgreSQL Advent Calenderの担当はsurumegohanさんです。よろしくお願いします。
