PostgRESTに向けたPostgreSQLでの制約と権限の実現例

今日のテーマ

最近、PostgRESTというもので遊んでいます。
公式によると以下のように説明されてます。

PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API. The structural constraints and permissions in the database determine the API endpoints and operations.

PostgREST は、PostgreSQL データベースを直接 RESTful API に変えるスタンドアロン Web サーバーです。データベースの構造上の制約と権限によって、API エンドポイントと操作が決まります。
by Google翻訳

これまでの私の経験上、複数の表に対する「射影、選択、結合、集約」の集合体の関係代数の世界で、業務ロジックをSQL(データ操作言語)で実装したことがあります。

しかし、「データベースの構造上の制約と権限」はRDB上でGRANT/REVOKEなどDCL(データ制御言語)と分類される権限の概念があることは知っているものの取り扱い経験がないので、やりたかったことをどう実現したかということを書きます。

Todoアプリのための権限設計

要件

よくあるテーマですが、要件を明らかにします。

• 複数の利用者のデータを表で管理
• 利用者は他の利用者のデータの利用不可
• 利用者は自身のデータを参照、追加、削除、更新可能

簡単ですね。

PostgRESTを意識した特有なこと

アプリケーションサーバ等からSQLを発行するならクエリに制限をかけることで要件を満たせます。
このような形になるでしょう。
SELECT * FROM todos WHERE user = %ログインユーザ%
INSERT todos(user,task) VALUES(%ログインユーザ%, %タスクの内容%)

しかし、PostgRESTでは外部からRESTAPIの形態で利用されるため、クエリに制限ができません。
そこで「データベースの構造上の制約と権限」が求められます。

制約と制限

要件のために以下のアイデアを導入します。

• ビューを経由して実表のデータを参照、追加、削除、更新する
• ビューの定義は１つ（利用者ごとではない）
• 利用者自身のデータを参照、追加、削除、更新可能なビューを作る
• 実表は利用者から利用不可

こんなイメージ図です。

検証

今はいい時代なので、WebブラウザでもPostgreSQLが動くのでそこで試してみます。

このあと出てくるSQLはコメント文を除いて実行すると動作するものを書いています。

利用者の認証はPostgRESTの仕組みによるとJWTを利用し、検証結果に基づいてPostgreSQL上でSET ROLE文が実行されるようです。

本記事ではPostgreSQLだけで検証を行うので SET ROLEで試します

実験準備

先ほどの絵の状態を作成します。

-- [準備]３つのアクターをつくる
CREATE ROLE user1;
CREATE ROLE user2;
CREATE ROLE admin;
-- [準備]user1,user2にusersのロールを付与
CREATE ROLE users;
GRANT users to user1;
GRANT users to user2;
-- [準備]adminにsuperusers(postgres)のロールを付与
GRANT postgres to admin;

-- adminで作業
SET ROLE admin;

-- [準備] 実表を作る
SET ROLE admin;
CREATE TABLE todos (
    id serial PRIMARY KEY,
    user_name text NOT NULL,
    content text NOT NULL
);
-- 自動採番にも権限が必要なのでusersに付与
GRANT USAGE ON SEQUENCE todos_id_seq TO users;

ビューの作成では、重要なことが２点あります。

• ビュー経由のデータ挿入時にビュー範囲内であることをチェック
  • （WITH LOCAL CHECK OPTION）
• usersが参照、追加、削除、更新可能に設定
  • GRANT

これらを意識して以下のようにビューを作成します。

-- [準備] ビューを作る
CREATE VIEW my_todos AS
SELECT
    id,
    user_name,
    content
FROM
    todos
WHERE
    user_name = CURRENT_USER
WITH LOCAL CHECK OPTION;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_todos TO users;

検証

作ったuser1で、参照、追加、削除、更新可能であるか確認します。

-- user1で作業
SET ROLE user1;

-- データ投入(成功)
INSERT INTO my_todos(user_name, content) VALUES('user1', 'wake up'); 

-- １件のデータを確認
SELECT * FROM my_todos;

-- １件のデータを更新
UPDATE my_todos SET content='long sleep';

-- １件のデータを確認（更新済）
SELECT * FROM my_todos;

別のユーザuser2からuser1のデータにちょっかいをかけてみます。
要件に上げたように人のデータには干渉できてはいけない。

-- user2で作業
SET ROLE user2;

-- 0件が確認(user1のデータはみえない)
SELECT * FROM my_todos;

-- 0件の消去(user1のデータは消せない)
DELETE FROM my_todos WHERE id = 1;

-- 0件の追加(user1のデータを作れない)
INSERT INTO my_todos(user_name, content) VALUES('user1', 'heavy work'); 

-- 実表の参照（できない）
SELECT * FROM todos;

-- 1件の追加(user2のデータは作れる)
INSERT INTO my_todos(user_name, content) VALUES('user2', 'shopping'); 

最後にadminで、実表にデータが有ることを確認

-- user2で作業
SET ROLE admin;

-- 実表を確認(2件のデータを確認)
SELECT * FROM USER;

要件にある、できること、できないこと、が概ね確認できました。

まとめ

PostgreSQLの構造上の制約と権限を設計することで、一つのテーブルに複数人のデータを格納したテーブルのRESTAPIを安全に公開可能にしました。

データベース上の設計で宣言的に実現できるので、SQLのクエリで手続き的に実現するよりも機密性の担保に大きな効果があると考えられます。

また、別の実現手段としてRLS(行レベルセキュリティ)があるので、使い分け等検討したい。