アクセス統計情報を使ってデータベースへのアクセスポリシーを自動生成する

アクセス統計情報を使ってアクセスポリシーを自動生成する PostgreSQLでは、テーブルへのアクセス権限をGRANT/REVOKE文を使って設定することが可能です。

この設定をきちんと行うことによってセキュリティのレベルを高めることができますが、粒度の細かな設定が必要となるため、手間がかかるのも現実です。

今回は、このテーブルへのアクセス権限の設定（アクセスポリシーの作成）を、アクセス統計情報を用いることで（半）自動化してみます。

■アクセス統計情報とは

PostgreSQLには、他のRDBMS製品と同様、アクセス統計情報を取得する仕組みがあります。

• 統計情報コレクタ
  http://www.postgresql.jp/document/9.2/html/monitoring-stats.html

アクセス統計情報とは、どのテーブルにシーケンシャルスキャンを何回実行したか、インデックススキャンを何回実行したか、あるいは何ブロックの読み取ったか、といった情報のことで、この情報を使うことによってデータベースにおけるアクセスの特徴やパターンを把握することができます。

もちろん、パフォーマンスチューニングの時には、この情報を活用することになります。

■アクセスポリシー（アクセス権限）とは

「アクセスポリシー」とは、データベースに接続してくるユーザに対して、どのテーブルやスキーマに、どのようなアクセス権限を提供するか、というルールのまとまりのことです。

PostgreSQLでは、他のRDBMS製品と同じようにGRANT/REVOKE文を使うことで、データベースやスキーマ、テーブルなどに対して、それぞれ参照、挿入、更新、削除などの権限をユーザごとに設定することができます。

• GRANT
  http://www.postgresql.jp/document/9.2/html/sql-grant.html

• REVOKE
  http://www.postgresql.jp/document/9.2/html/sql-revoke.html

この権限・ポリシーを適切に設定・管理することによって、ユーザに対して必要以上の権限を与えることなく運用することが可能になります。

■SQLインジェクション対策としてのアクセスポリシー管理

また、万一SQLインジェクションの攻撃にあった場合に、被害を低減する方法としても一定の効果が見込まれます。

• SQLインジェクション対策について - IPA 独立行政法人 情報処理推進機構
  http://www.ipa.go.jp/files/000024396.pdf

保険的対策
攻撃による影響を低減する「セーフティネット」
・データベースアカウントの権限見直し
　- 「権限全部入り」のアカウントは使わない
　　・権限を必要最小限にすれば、防げる攻撃もある

しかし一方で、対象となるデータベースオブジェクトの数が増えてくると、設定をするのが手間になり、結果として「権限全部入り」のアカウントで運用を開始せざるを得ないことも、またひとつの現実でしょう。

そこで今回は、PostgreSQLのアクセス統計情報を用いて、アクセスポリシーを自動生成する方法をご紹介します。

■アクセス統計情報から必要な権限情報を収集する

今回使うアクセス統計情報は pg_stat_user_tables システムビューで取得できる統計情報です。

以下は、PostgreSQL 9.2のpg_stat_user_tablesシステムビューですが、seq_scan, idx_tup_fetch, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd というカラムがあります。

pgbench=# \d pg_stat_user_tables
          View "pg_catalog.pg_stat_user_tables"
      Column       |           Type           | Modifiers
-------------------+--------------------------+-----------
 relid             | oid                      |
 schemaname        | name                     |
 relname           | name                     |
 seq_scan          | bigint                   |
 seq_tup_read      | bigint                   |
 idx_scan          | bigint                   |
 idx_tup_fetch     | bigint                   |
 n_tup_ins         | bigint                   |
 n_tup_upd         | bigint                   |
 n_tup_del         | bigint                   |
 n_tup_hot_upd     | bigint                   |
 n_live_tup        | bigint                   |
 n_dead_tup        | bigint                   |
 last_vacuum       | timestamp with time zone |
 last_autovacuum   | timestamp with time zone |
 last_analyze      | timestamp with time zone |
 last_autoanalyze  | timestamp with time zone |
 vacuum_count      | bigint                   |
 autovacuum_count  | bigint                   |
 analyze_count     | bigint                   |
 autoanalyze_count | bigint                   |

pgbench=# 

それぞれ

• schemaname, relnameはスキーマ名とテーブル名
• seq_scan, idx_tup_fetch は取得した行数
• n_tup_ins は挿入した行数
• n_tup_upd, n_tup_hot_upd は更新した行数
• n_tup_del は削除された行の行数

となっています。

つまり、アプリケーションを実行してみて、これらの数値がゼロ以上であれば該当するアクセス権限が必要で、数値がゼロであればアクセス権限が必要ない、ということになります。

そのため、これらの統計情報を用いることで、アクセスポリシー（権限、ルールの集合）を生成することができます。

■サンプルアプリ（pgbench）のアクセスポリシーを自動生成する

それでは、実際にアクセスポリシーを自動生成してみます。

サンプルアプリケーションとして、今回は pgbench を用います。また、snaga はスーパーユーザで権限を制限したいユーザは testuser であるものとします。

まず、権限を制御したいユーザに一旦全権限を付与し、pgbenchに必要なテーブルを作成します。また、統計情報をリセットして各テーブルの統計情報をゼロクリアします。

[snaga@devsv03 postgresql]$ pgbench -i pgbench -U snaga
NOTICE:  table "pgbench_branches" does not exist, skipping
(...snip...)
vacuum...done.
[snaga@devsv03 postgresql]$ psql -U snaga pgbench

pgbench=# GRANT ALL ON pgbench_accounts,pgbench_branches,pgbench_history,pgbench_tellers TO testuser;
GRANT
pgbench=# SELECT pg_stat_reset();
 pg_stat_reset
---------------

(1 row)

pgbench=# SELECT relname,seq_tup_read,idx_tup_fetch,n_tup_ins,n_tup_upd,n_tup_del,n_tup_hot_upd from pg_stat_user_tables;
     relname      | seq_tup_read | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd
------------------+--------------+---------------+-----------+-----------+-----------+---------------
 pgbench_accounts |            0 |             0 |         0 |         0 |         0 |             0
 pgbench_branches |            0 |             0 |         0 |         0 |         0 |             0
 pgbench_tellers  |            0 |             0 |         0 |         0 |         0 |             0
 pgbench_history  |            0 |               |         0 |         0 |         0 |             0
(4 rows)

pgbench=# 

次に権限を制限したいユーザ（testuser）でpgbenchを実行してワークロードを発生させます。

[snaga@devsv03 postgresql]$ pgbench -n -t 100 -c 8 -U testuser pgbench
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 1
number of transactions per client: 100
number of transactions actually processed: 800/800
tps = 92.705298 (including connections establishing)
tps = 93.013078 (excluding connections establishing)
[snaga@devsv03 postgresql]$

ワークロードが終了したら、以下のSQLを使ってGRANT/REVOKE文を自動生成します。

• pgsql-utils/sql/accesspolicy_autogen.sql at master · snaga/pgsql-utils
  https://github.com/snaga/pgsql-utils/blob/master/sql/accesspolicy_autogen.sql

[snaga@devsv03 postgresql]$ psql -A -t -f accesspolicy_autogen.sql -U testuser pgbench > mypolicy.sql
[snaga@devsv03 postgresql]$ cat mypolicy.sql
REVOKE ALL ON DATABASE pgbench FROM testuser;
REVOKE ALL ON public.pgbench_branches FROM testuser;
REVOKE ALL ON public.pgbench_accounts FROM testuser;
REVOKE ALL ON public.pgbench_tellers FROM testuser;
REVOKE ALL ON public.pgbench_history FROM testuser;
GRANT SELECT,UPDATE ON public.pgbench_branches TO testuser;
GRANT SELECT,UPDATE ON public.pgbench_accounts TO testuser;
GRANT SELECT,UPDATE ON public.pgbench_tellers TO testuser;
GRANT INSERT ON public.pgbench_history TO testuser;
[snaga@devsv03 postgresql]$

■アクセスポリシーを適用して動作確認する

最後に、作成したアクセスポリシーを実際に適用して動作確認をしてみます。

pgbench=# \i mypolicy.sql
REVOKE
REVOKE
REVOKE
REVOKE
REVOKE
GRANT
GRANT
GRANT
GRANT
pgbench=# \dp
                                 Access privileges
 Schema |       Name       | Type  |  Access privileges  | Column access privileges
--------+------------------+-------+---------------------+--------------------------
 public | pgbench_accounts | table | snaga=arwdDxt/snaga+|
        |                  |       | testuser=rw/snaga   |
 public | pgbench_branches | table | snaga=arwdDxt/snaga+|
        |                  |       | testuser=rw/snaga   |
 public | pgbench_history  | table | snaga=arwdDxt/snaga+|
        |                  |       | testuser=a/snaga    |
 public | pgbench_tellers  | table | snaga=arwdDxt/snaga+|
        |                  |       | testuser=rw/snaga   |
(4 rows)

pgbench=# \q

テーブルのアクセス権限を見ると、各テーブルにアクセス権限が設定されていることが分かります。（権限の表記の詳細については、マニュアルのGRANTコマンドの説明を参照してください）

この状態で、再度pgbenchを実行してみます。

[snaga@devsv03 postgresql]$ pgbench -n -t 100 -c 8 -U testuser pgbench
transaction type: TPC-B (sort of)
scaling factor: 1
query mode: simple
number of clients: 8
number of threads: 1
number of transactions per client: 100
number of transactions actually processed: 800/800
tps = 91.389175 (including connections establishing)
tps = 91.682842 (excluding connections establishing)
[snaga@devsv03 postgresql]$

上記の通り、pgbenchの実行には何も支障がないことが分かります。

一方で、pgbench_historyテーブルをSELECTしようとしたり、pgbench_accountsテーブルからDELETEしようとしても、権限エラーとなって実行できません。

[snaga@devsv03 postgresql]$ psql -U testuser pgbench

pgbench=> SELECT * FROM pgbench_history ;
ERROR:  permission denied for relation pgbench_history
pgbench=> DELETE FROM pgbench_accounts ;
ERROR:  permission denied for relation pgbench_accounts
pgbench=>

これは、先ほど作成したアクセスポリシーによって、不必要な権限がユーザから剥奪されたためです。

このように適切なアクセス権限を設定することで、ユーザによる予期せぬ動作を一部制約することができるようになります。

■まとめ

以上、簡単ではありましたが、PostgreSQLのアクセス統計情報を使ってポリシーを自動生成する方法をご紹介しました。

もちろん、実際にアクセス権限を設定する際には他にも考慮するべき点はありますが、テーブルのアクセス統計情報をポリシー作成のインプットとすることができる、ということを知っていただければと思います。

データベースやWebシステムが普及した今、SQLインジェクションによる被害の報告が途切れることがないのが現実です。ぜひ、セキュリティにも配慮しつつ、より手間の少ない方法で効果的な環境設定をしていただければと思います。

では、また。