メンテナンス専用スーパーユーザ

はじめに

みなさん、こんにちは。ぬこ@横浜です。
この記事はPostgreSQL Advent Calendar 2015 - Qiitaの5日目です。
4日目は sayamada さんに書いていただきました。

さて・・・深刻なネタ不足です。困りました。

困ったので、先日のPostgreSQLカンファレンス2015のライトニングトークで喋ったネタ(フックの鬼)の続編を書いてみようと思います。

まず、本編に入る前に、ライトニングトークで喋った、誰得拡張の pg_sulog について簡単に説明します。

pg_sulog

このPostgreSQL拡張モジュールはHOOK拡張機能を使ったもので、非常に簡単に言えば

  • スーパーユーザ権限をもつユーザが実行する全てのSQLコマンドを強制ロギングする
  • オプションでスーパーユーザ権限をもつユーザが実行する全てのSQLコマンドを実行不可能にする

という、何の役に立つのかわからなさそうな拡張モジュールです。

例えば、以下のようにスーパーユーザ(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 コマンドの実行のみ許容し、その他のコマンドはブロックします。操作のロギングもsます。

ということで、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などの適用を考えたほうがいいとは思いますがw

おわりに

ということで、今日のエントリは誰得な自作モジュールで、メンテナンス専用ユーザを疑似ってみるという誰得な話でした。

明日の PostgreSQL Advent Calenderの担当はsurumegohanさんです。よろしくお願いします。