Zenn
🔐

PostgreSQLのRLS (Row Level Security) キャッチアップ

2024/11/30に公開
Docker
PostgreSQL
Security
RLS
tech

概要

今回は RLS (Row Level Security) に関してサポートしているPostgreSQLを使って
色々動かしながら試していきたいと思います。

RLS (Row Level Security)

https://www.postgresql.org/docs/current/ddl-rowsecurity.html

Row Security Policies では、通常のクエリで返される行や、データ変更コマンドで挿入、更新、削除できる行をユーザーごとに制限します。この機能は Row-Level Security とも呼ばれます。

特徴

  • デフォルトではテーブルには何のポリシーもない
  • 特定のコマンド、特定のロール、あるいはその両方に対して定義できる
  • ユーザの問い合わせにあるどの条件や関数よりも前に適用される
  • スーパーユーザ、およびBYPASSRLS属性のあるロールは例外
  • テーブルの所有者も通常は行セキュリティを無視するが適用する事もできる
  • 1つのテーブルに複数のポリシーを定義できる
  • 1つのテーブルの各ポリシーは異なる名前でないといけない
  • 複数のポリシーが適用される場合
    • デフォルトの許容(permissive)ポリシーについては OR
    • 制限(restrictive)ポリシーについては AND

検証用環境の作成

検証で使用する環境を作成する為に、以下内容の compose.yml で検証してきます。

volumes:
  db_volume:

services:
  postgres:
    image: postgres:16.3
    container_name: postgres_inspection
    working_dir: /usr/src/app
    environment:
      POSTGRES_PASSWORD: 'postgres'
      POSTGRES_USER: 'postgres'
    ports:
      - '5432:5432'
    volumes:
      - .:/usr/src/app
      - db_volume:/var/lib/postgresql/data

current_user を利用したポリシーの作成

PostgreSQLには current_user という組み込み関数があり、現在実行しているユーザー(又はRole) を返してくれます。

ユーザーとロールの違い

いまいちピンときてなかったので、ちゃんと調べてみると👇の違いがあるそうです。

  • ユーザは作成時デフォルトでログイン権限がある
  • ロールは作成時デフォルトでログイン権限がない

https://qiita.com/dai_chi/items/6da92ab9a691b70c8772

現在のロール一覧を表示

\du またはシステムカタログの pg_roles から表示させる事ができます。

$ psql -U postgres
postgres=# \du
                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

postgres=# SELECT CURRENT_USER;
 current_user 
--------------
 postgres
(1 row)

ここで managers という Role を作成してみます。

postgres=# CREATE ROLE managers;
CREATE ROLE
postgres=# SET ROLE managers;
SET
postgres=> SELECT CURRENT_USER;
 current_user 
--------------
 managers
(1 row)

current_user は managers になっています。ここでPostgreSQLのセッションに接続したオリジナルのユーザーを表示させてみます。

postgres=> SELECT SESSION_USER;
 session_user 
--------------
 postgres
(1 row)

current_user とは異なるpostgres ユーザーになっている事が分かります。

簡単なサンプル

検証用のDatabase inspection を作成します。

CREATE DATABASE inspection WITH OWNER postgres;

ちゃんと作成されたか一覧を表示してみます。

    Name    |  Owner   | Encoding | Locale Provider |  Collate   |   Ctype    | ICU Locale | ICU Rules |   Access privileges   
------------+----------+----------+-----------------+------------+------------+------------+-----------+-----------------------
 inspection | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | 
 postgres   | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | 
 template0  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
            |          |          |                 |            |            |            |           | postgres=CTc/postgres
 template1  | postgres | UTF8     | libc            | en_US.utf8 | en_US.utf8 |            |           | =c/postgres          +
            |          |          |                 |            |            |            |           | postgres=CTc/postgres
(4 rows)

作成されてそうです。早速 inspection Databaseに接続して以下のRLSを作成してみたいと思います。


$ postgres=> \c inspection
You are now connected to database "inspection" as user "postgres".

$ inspection=> CREATE TABLE accounts (manager text, company text, contact_email text);
$ inspection=> ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;
$ inspection=> CREATE POLICY account_managers ON accounts TO managers
    USING (manager = current_user);

👆の例だと manager が current_user = (managers) の行のみ参照・更新ができるはずです。早速試してみます。

# サンプルデータを登録
$ inspection=> INSERT INTO accounts (manager, company, contact_email) VALUES
    ('managers', 'company1', 'company1@email'),
    ('admin', 'company2', 'company2@email'),
    ('managers', 'company3', 'company3@email'),
    ('admin', 'company4', 'company4@email');
# このままだとrole: managersがaccountsテーブルにアクセスできない為、権限を付与
$ inspection= GRANT SELECT ON accounts TO managers;
# managersに切り替え
$ inspection= SET ROLE managers;
SET
$ inspection=> SELECT current_user;
 current_user 
--------------
 managers
# アクセスしてみるとmanager=managersのものしか取得できていない
$ inspection=> SELECT * FROM accounts;
 manager  | company  | contact_email  
----------+----------+----------------
 managers | company1 | company1@email
 managers | company3 | company3@email
(2 rows)

設定したPolicy通りに manager が managers の者だけ取得できています。

試しに managers ではない行に対して、参照や更新を行ってみます。

$ inspection=> SELECT * FROM accounts WHERE company = 'company2';
 manager | company | contact_email 
---------+---------+---------------
(0 rows)
$ inspection=> UPDATE accounts SET company = 'company99' WHERE manager = 'admin';
ERROR:  permission denied for table accounts

参照はできず、更新は permission error になっています。

current_setting を利用したポリシーの作成

組み込み関数の current_setting と set_config を使って、アクセスしたユーザーの識別子を判定する方法になります。

https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SET

早速 inspection Databaseに接続して以下のRLSを作成してみます。

inspection= CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
inspection= CREATE TABLE users (
  id uuid default uuid_generate_v4() primary key,
  name text,
  company text,
  contact_email text
);
inspection= ALTER TABLE users ENABLE ROW LEVEL SECURITY;
inspection= CREATE POLICY user_policy ON users
USING (id = current_setting('app.user_id')::uuid);

適当に何件かデータを登録します。

inspection= INSERT INTO users (name, company, contact_email) VALUES
  ('user1', 'company1', 'user1@example.com'),
  ('user2', 'company2', 'user2@example.com'),
  ('user3', 'company3', 'user3@example.com');
inspection= SELECT * FROM users;
                  id                  | name  | company  |   contact_email   
--------------------------------------+-------+----------+-------------------
 c3076642-2e59-4f0c-8109-7fdb28530e7f | user1 | company1 | user1@example.com
 1815d9ce-8b0c-4ef0-bce8-0c13d429145e | user2 | company2 | user2@example.com
 17d43dfb-b858-4404-bf16-924f43d01777 | user3 | company3 | user3@example.com

ここで試しに user2 の id をset_config で設定してクエリしてみます。

inspection= SELECT set_config('app.user_id', '1815d9ce-8b0c-4ef0-bce8-0c13d429145e', false);
inspection= select current_setting('app.user_id');
           current_setting            
--------------------------------------
 1815d9ce-8b0c-4ef0-bce8-0c13d429145e

ちゃんと設定できています。ここで role が postgres だとテーブルのOwnerなのでPolicy関係なくアクセスできてしまうので、先ほど作成した managers ロールに切り替えてクエリしてみます。

inspection= GRANT SELECT ON users TO managers;
inspection= SET ROLE managers;
inspection= SELECT * FROM users;
                  id                  | name  | company  |   contact_email   
--------------------------------------+-------+----------+-------------------
 1815d9ce-8b0c-4ef0-bce8-0c13d429145e | user2 | company2 | user2@example.com

無事 user2 だけが取得できています ✨

リクエスト時の id を set_config にセットするには?

最後に、ここまではPostgreSQL内で操作して完結していましたが、実際にはバックエンド等でPostgreSQLにアクセスするなどのパターンの場合を考えてみます。

どうやるんだろうと調べていたら↓こちらの記事を発見しました ✨

https://zenn.dev/smallstall/articles/596d3981984587

どうやら set_config には第3引数の is_local が true の場合トランザクション内だけ適用されるフラグが存在するようで、

こちらを使ってリクエストしたユーザーの id をセットするのが良さそうです。

参考URL

https://zenn.dev/taxin/articles/postgresql-row-level-security-policy

https://dev.classmethod.jp/articles/postgresql-15-revoke-create-on-public-schema/

https://dev.classmethod.jp/articles/postgresql-organize-command/

https://supabase.com/docs/guides/database/postgres/row-level-security

Discussion