現在位置: ホーム / よろずブログ / PostgreSQL 9.5 の行セキュリティポリシー

PostgreSQL 9.5 の行セキュリティポリシー

本記事では、この秋リリース予定の PostgreSQL9.5 の行セキュリティポリシーを紹介します。

こんにちは。

この秋リリース予定の PostgreSQL9.5 の行セキュリティポリシーを紹介します。これまではユーザーがアクセス可能な行を制限するにはビューを作成し、アクセスできるテーブルとビューを制限する方法が一般的でした。PostgreSQL 9.5 からは行セキュリティポリシーを利用してアクセス制御が可能になります。

■ ビューによる行アクセス制限の問題

ビューによって参照可能な行を制限できますが、大きな問題がありました。ビューの元となるテーブルのアクセス権限がないとビューにもアクセスできません。ビューでアクセス制限を行っても、元のテーブルにアクセスしてしまえばユーザーは全ての行にアクセスできました。トリガーやルールを駆使すれば可能でしたが行の挿入や更新、削除を簡単な方法で制限することもできませんでした。

行セキュリティポリシーによって PostgreSQL 9.5 からデータベースレベルで行の参照／挿入／更新／削除を簡単に制御できるようになります。

■ 利用する PostgreSQL の環境

この記事では Linux と PostgreSQL 9.5 Alpha2 のソースコードを利用しています。あまりないとは思いますが仕様変更の可能性もあるのでご注意ください。コマンドを実行するユーザーは root 以外の一般ユーザーを使います。ソースコードを展開後

./configure prefix=/usr/local/pgsql9.5 && make && make install

でコンパイルされ、/usr/local/pgsql9.5 ディレクトリにインストールされた PostgreSQL を利用するものとします。データベースは適当なディレクトリに作成してください。コンパイルには開発用ツールが必要です。詳しくは利用されている PostgreSQL のマニュアル／ディストリビューションのマニュアルを参照してください。

/usr/local/pgsql9.5/bin/initdb -E UTF-8 --locale=C -D pgdata9.5

などとします。現在のユーザー（UNIXユーザー名）を PostgreSQL の管理者ユーザーとしてデータベースが初期化されます。他の PostgreSQL サーバーとポートが競合しないよう、データベースディレクトリ内の postgresql.conf の port 設定を 5495 に変更します。

port = 5495

データベース作成後に表示される起動コマンドで PostgreSQL サーバーを起動します。

/usr/local/pgsql9.5/bin/pg_ctl start -l logfile -D pgdata9.5

テストに必要なデータベースを UNIX ドメインソケットと使って作成します。

/usr/local/pgsql9.5/bin/createdb -h /tmp -p 5495

データベース名を指定していないので現在のユーザー（UNIXユーザー名）のデータベースが作成されます。これでテストの準備ができました。

■ 行セキュリティポリシーの設定

行セキュリティポリシーの利用は以下の手順で行います。

1.テーブルを作成
2.セキュリティポリシーの作成
3.テーブルのセキュリティポリシーを有効化

2 と 3 の順序は逆でも構いません。設定を行うために psql を起動します。

/usr/local/pgsql9.5/bin/psql -h /tmp -p 5495

テーブルの作成

CREATE TABLE mytable (
  id BIGSERIAL PRIMARY KEY,
  username TEXT NOT NULL,
  userdata TEXT
  );

セキュリティポリシーの作成

CREATE POLICY mytable_policy ON mytable
  FOR ALL
  TO PUBLIC
  USING (username = current_user);

テーブルのセキュリティポリシーを有効化

ALTER TABLE mytable
  ENABLE ROW LEVEL SECURITY;

セキュリティポリシーを定義するだけではテーブルにセキュリティポリシーは適用されません。ALTER TABLE で明示的に有効化する必要があります。

この例では mytable にアクセス可能な行を現在のユーザー (username = current_user) のみに限定しています。この為、ログインしているユーザー名と一致する行のみが取得できるようになります。スーパーユーザー（デフォルトは initdb を行ったユーザー名）にはセキュリティポリシーは適用されず、全ての行が取得できます。

■ 行セキュリティポリシーの動作（参照）

二つ以上の PostgreSQL ユーザーが必要なのでテスト用ユーザーを作ります。

テストユーザー（testuser）の作成

/usr/local/pgsql9.5/bin/createuser -h /tmp -p 5495 testuser

テストデータの作成を管理者ユーザー（initdbを行ったユーザー）の psql コマンドラインで行います。

/usr/local/pgsql9.5/bin/psql -h /tmp -p 5495

データの挿入

INSERT INTO mytable (username, userdata) VALUES ('someuser', 'dummy1');
INSERT INTO mytable (username, userdata) VALUES ('otheruser', 'dummy2');
INSERT INTO mytable (username, userdata) VALUES ('testuser', 'dummy3');

データベースを作成したユーザーのみにアクセス権が与えられているので、testuser に mytable のアクセス権を全て与えます。データ挿入にはシークエンスの権限も必要なのでここで権限を与えておきます。

testuser へのアクセス権限設定

GRANT ALL ON mytable TO testuser;
GRANT ALL ON mytable_id_seq TO testuser;

管理者ユーザーからの SELECT

# SELECT * FROM mytable;
┌────┬───────────┬──────────┐
│ id │ username  │ userdata │
├────┼───────────┼──────────┤
│  1 │ someuser  │ dummy1   │
│  2 │ otheruser │ dummy2   │
│  3 │ testuser  │ dummy3   │
└────┴───────────┴──────────┘
(3 行)

管理者ユーザーには行セキュリティポリシーは適用されないので全ての行が表示されます。

他のターミナルから testuser で psql を起動します。

/usr/local/pgsql9.5/bin/psql -h /tmp -p 5495 -U testuser

※には現在のユーザー名（PostgreSQL のスーパーユーザー名）を記載してください

testuser からの SELECT

> SELECT * FROM mytable;
┌────┬──────────┬──────────┐
│ id │ username │ userdata │
├────┼──────────┼──────────┤
│  3 │ testuser │ dummy3   │
└────┴──────────┴──────────┘
(1 rows)

mytable には 3 行のレコードがありますが、行セキュリティポリシーで設定した条件「username = current_user」に一致する行のみ返します。

■ 挿入／更新／削除の動作

参照と同様に、挿入／更新／削除にも行セキュリティポリシーが適用され制限されます。

testuser からの INSERT

> INSERT INTO mytable (username, userdata) VALUES ('someuser', 'dummy1');
ERROR:  42501: new row violates row level security policy for "mytable"
LOCATION:  ExecWithCheckOptions, execMain.c:1821

挿入はセキュリティポリシーエラーが発生します。

testuser からの UPDATE

> UPDATE mytable SET userdata = 'updated' WHERE username = 'someuser';
UPDATE 0

testuser からの DELETE

> DELETE FROM mytable WHERE username = 'someuser';
DELETE 0

UPDATE / DELETE はセキュリティポリシー「username = current_user」に一致する行のみが対象になります。

このように挿入／更新／削除にも行セキュリティポリシーが適用されます。

username = 'testuser' となる行は操作できるので以下の挿入／削除は実行できます。

> INSERT INTO mytable (username, userdata) VALUES ('testuser', 'dummy1');
INSERT 0 1
> SELECT * FROM mytable;
┌────┬──────────┬──────────┐
│ id │ username │ userdata │
├────┼──────────┼──────────┤
│  3 │ testuser │ dummy3   │
│  5 │ testuser │ dummy1   │
└────┴──────────┴──────────┘
(2 rows)

> DELETE FROM mytable;
DELETE 2

> SELECT * FROM mytable;
┌────┬──────────┬──────────┐
│ id │ username │ userdata │
├────┼──────────┼──────────┤
└────┴──────────┴──────────┘
(0 rows)

testuser は自分の行を全て削除しましたが、他の行には影響を与えません。

管理者ユーザーからの SELECT

# SELECT * FROM mytable;
┌────┬───────────┬──────────┐
│ id │ username  │ userdata │
├────┼───────────┼──────────┤
│  1 │ someuser  │ dummy1   │
│  2 │ otheruser │ dummy2   │
└────┴───────────┴──────────┘
(2 rows)

■ 行セキュリティポリシーとインデックス

行セキュリティポリシーはクエリ実行時の検索条件になります。インデックスは自動作成されません。多くの行がある場合はインデックスを利用しないと多数のテーブルのフルスキャンが発生しパフォーマンスに影響します。

■ まとめ

不特定多数のユーザーから大量にアクセスされる Web アプリケーションには多数のデータベース接続が必要です。利用する接続にはアプリケーションが利用する全てのテーブルの権限を持つデータベースユーザーを利用することがほとんどです。これは接続が多すぎるとスループットが低下する為です。

しかし、理想的にはセキュリティ対策の「最小権限の原則」を適用し必要最小限のテーブルやデータベースオブジェクトへのアクセスを許可すべきです。大量のデータベース接続が必要な Web アプリケーションへの利用は難しい場合も多いですが、アプリケーションによってはセキュリティを向上させるためにとても有用です。つまり、PostgreSQL 9.5 を使えばより安全なデータベース管理を実現できます。