Go to Qiita Advent Calendar 2024 Top
LoginSignup
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@nao-United92

PostgreSQL Row Level Security 検証

Last updated at Posted at 2023-06-30

はじめに

実務にて、以下のリプレースがありました。

  • MySQL ⇨ PostgreSQL へのデータベース変更
  • PostgreSQL の Row Level Security を導入

Row Level Security の概要・効果について、PostgreSQL 操作を交えながら検証手順を残しておきます。

目次

  1. PostgreSQL とは？
  2. マルチテナントデータベース とは？
  3. Row Level Security とは？
  4. Row Level Security を検証
  5. 参考文献

PostgreSQL とは？

  • 読み方は、ポストグレスキューエル(略してポスグレ)

  • オープンソースのリレーショナルデータベース管理システム(RDBMS)
    オープンソース

    • ソースコードが公開されていて改変できる。
    • ライセンス不要かつ無償で使用可能

    RDBMS

    • データを保管する箱の集まり
    • 各箱は列と行で構成された表の形式でデータを扱う
    • 表同士を関連付けることができる(リレーショナルデータベース＝RDB)

  • データベースのテーブルデータを追加、削除、更新、検索(全文検索)できる。

  • Row Level Security 、トランザクション、ストアドプロシージャも使える。

  • 無料なのに他のデータベースに機能面で劣らない。

マルチテナントデータベース とは？

マルチテナントデータベースは、複数企業のデータを１つのデータベースで管理します。

データベースが１つのため、金銭的コストが少なくて済むというメリットが挙げられます。
ただ一方で、企業数が数千単位になってくると以下の懸念が出てくるでしょう。

  • マイグレーション
    • １テーブルで管理するデータ量が膨大になりがちなため、テーブルカラムの名称変更や追加といった小規模な SQL 実行でも時間がかかってしまう
    • 機能拡張や改修によって、既存データへの影響が大きくなる
  • 安全性
    • 企業跨ぎでのデータ操作から情報漏洩等の問題発生に繋がる可能性が高い

今回紹介する Row Level Security はこの「安全性」を高めてくれる機能です。

Row Level Security とは？

Row Level Securityは、テーブルの行へのアクセスを制御することができます。

例えば、以下２つのテーブルがあり、双方のテーブルで、company_id ： 企業IDという企業を特定できるデータを持っているとします。

  • company ： 企業情報を管理
  • user ： 企業に所属するユーザー情報を管理

この時、company_id ： １に所属するデータだけをuserテーブルから検索したい場合、通常は以下のような SQL をイメージするかと思います。

SELECT * 
FROM user
WHERE user.company_id = 1 -- company.company_id ： 1

ただ仮に、WHERE句の記述を忘れた場合は全企業のユーザー情報が見れてしまいます。
これを各ディベロッパーの責任の下、開発を続けるのは企業が増える程リスクにしかなりません。

Row Level Security は上記のような実装抜け防止は勿論、テーブルデータ操作における安全性を高めてくれます。

Row Level Security を検証

では、Row Level Security をどのように適用するのか、一緒に検証していきましょう。
長いので、ここからは Row Level Security ではなく RLS と略させてもらいます。

psql とは？

インタラクティブ(対話)にユーザー入力を PostgreSQL へ渡して結果を表示する CUI ツール
以降、ターミナルにてpsqlを使って PostgreSQL を操作していきます。

ここからは以下の流れで、RLS の効果を見ていきます。
　1. Docker × PostgreSQL 環境を用意
　2. PostgreSQL コンテナを立ち上げる
　3. 立ち上げた PostgreSQL コンテナ内へ入る
　4. PostgreSQL へログイン
　5. データベースの作成
　6. テーブルの作成
　7. テーブルデータの追加
　8. ROW LEVEL SECURITY の有効化
　9. ロールの作成 ・ 権限付与 ・ 切り替え
　10. RLS を効かせたテーブルデータ操作

1. Docker × PostgreSQL 環境を用意

RLS 検証用ということで、構成はかなりシンプルです。

  • Dockerfile
    FROM postgres:11-alpine
ENV LANG ja_JP.utf8
    compose.yaml
    version: '3'
services:
  db:
    build: .
    ports:
      - 5433:5432
    environment:
      POSTGRES_USER: admin # PostgreSQL ログインユーザー
      POSTGRES_PASSWORD: admin # PostgreSQL ログインパスワード
    tty: true # true ： コンテナを継続起動、false ： コンテナ起動後、即停止
    volumes:
      - db_data:/var/lib/postgresql/data # PostgreSQL　の DB　データ保管場所
volumes:
  db_data: {}

2. PostgreSQL コンテナを立ち上げる

  • docker compose up -d

3. 立ち上げた PostgreSQL コンテナ内へ入る

psqlを使用する(PostgreSQL 操作を行う)には、コンテナ内へ入る必要があります。

  • docker compose exec db bash

4. PostgreSQL へログイン

コンテナ内へ入ったので、ここからは PostgreSQL を操作していきましょう。
まずは PostgreSQL へログインします。

  • フォーマット
    psql -U ユーザー名

    psql -U admin

※ psql -U ユーザー名 -d データベース名で一気にデータベースへのログインも可能です。

5. データベースの作成

  • データベースの作成
    RLS 検証用のデータベースを作成します。

    フォーマット
    create database データベース名;

    create database rls_verification;
    実行結果
    CREATE DATABASE

  • データベースの一覧表示
    データベースが作成されたかどうかは以下で確認できます。

    psql \l

    rls_verification追加されていますね。

    実行結果
                                 List of databases
       Name       | Owner | Encoding |  Collate   |   Ctype    | Access privileges 
------------------+-------+----------+------------+------------+-------------------
 admin            | admin | UTF8     | ja_JP.utf8 | ja_JP.utf8 | 
 postgres         | admin | UTF8     | ja_JP.utf8 | ja_JP.utf8 | 
 rls_verification | admin | UTF8     | ja_JP.utf8 | ja_JP.utf8 | 
 template0        | admin | UTF8     | ja_JP.utf8 | ja_JP.utf8 | =c/admin         +
                  |       |          |            |            | admin=CTc/admin
 template1        | admin | UTF8     | ja_JP.utf8 | ja_JP.utf8 | =c/admin         +
                  |       |          |            |            | admin=CTc/admin

6. テーブルの作成

  • データベースへの接続

    \connect rls_verification

    以下は、adminユーザーでrls_verificationへ接続できたことを示しています。

    実行結果
    You are now connected to database "rls_verification" as user "admin".

  • テーブルの作成
    検証用のテーブルを2つ作成します。

  • フォーマット
    CREATE TABLE テーブル名 (
    カラム名1 serial primary key,
    カラム名2 型([データ長]) NOT NULL UNIQUE,
    ・・・
);

    • 企業情報を管理するcompanyテーブルを作成

      CREATE TABLE company (
    company_id serial primary key,
    company_code varchar(20) NOT NULL UNIQUE,
    company_name varchar(255) NOT NULL
);
      実行結果
      CREATE TABLE

    • 企業内のユーザー情報を管理するuserテーブルを作成

      CREATE TABLE "user" (
    user_id serial primary key,
    company_id integer NOT NULL, -- row level security で using 句に利用するカラム
    user_name varchar(255) NOT NULL,
    email varchar(255) NOT NULL,
    FOREIGN KEY (company_id) REFERENCES company(company_id)
);
      実行結果
      CREATE TABLE

  • テーブルの一覧表示
    テーブルが作成されたかどうかは以下で確認できます。
    \dtは簡易表示、\dは詳細表示

    • 全テーブル内容の簡易表示

      \dt
      実行結果
              List of relations
 Schema |  Name   | Type  | Owner 
--------+---------+-------+-------
 public | company | table | admin
 public | user    | table | admin

    • userテーブル内容の簡易表示

      \dt user

             List of relations
 Schema | Name | Type  | Owner 
--------+------+-------+-------
 public | user | table | admin

    • 全テーブル内容の詳細表示
      詳細表示の場合、sequenceの情報も参照できます。

      \d
      実行結果
                       List of relations
 Schema |          Name          |   Type   | Owner 
--------+------------------------+----------+-------
 public | company                | table    | admin
 public | company_company_id_seq | sequence | admin
 public | user                   | table    | admin
 public | user_user_id_seq       | sequence | admin

    • userテーブル内容の詳細表示
      テーブルを指定した場合、Null許容、Indexes、Foreign-keyの情報も参照できます。

      \d user

                                              Table "public.user"
   Column   |          Type          | Collation | Nullable |                Default                
------------+------------------------+-----------+----------+---------------------------------------
 user_id    | integer                |           | not null | nextval('user_user_id_seq'::regclass)
 company_id | integer                |           | not null | 
 user_name  | character varying(255) |           | not null | 
 email      | character varying(255) |           | not null | 
Indexes:
    "user_pkey" PRIMARY KEY, btree (user_id)
Foreign-key constraints:
    "user_company_id_fkey" FOREIGN KEY (company_id) REFERENCES company(company_id)

7. テーブルデータの追加

作成したcompany、userテーブルへ RLS 検証用データを追加していきます。

  • フォーマット
    INSERT INTO テーブル名 (カラム名1, カラム名2) values
(データ1, データ２,・　・・);

  • 企業データを追加

    INSERT INTO company (company_code, company_name) values
('test_code1', 'テスト株式会社１'),
('test_code2', 'テスト株式会社２'),
('test_code3', 'テスト株式会社３');

    INSERT 0 3

  • ユーザーデータを追加

    INSERT INTO "user" (user_id, company_id, user_name, email) values
(1,1,'user1','user1@ex.com'),
(2,1,'user2','user2@ex.com'),
(3,1,'user3','user3@ex.com'),
(4,2,'user4','user4@ex.com'),
(5,2,'user5','user5@ex.com'),
(6,3,'user6','user6@ex.com'),
(7,3,'user7','user7@ex.com'),
(8,3,'user8','user8@ex.com'),
(9,3,'user9','user9@ex.com');

    INSERT 0 9

  • テーブルデータの一覧表示
    データが追加されたかどうかは以下で確認できます。

    フォーマット
    select * from テーブル名;

    select * from "user";

    問題なく追加されていますね。

    実行結果
     user_id | company_id | user_name |    email     
---------+------------+-----------+--------------
       1 |          1 | user1     | user1@ex.com
       2 |          1 | user2     | user2@ex.com
       3 |          1 | user3     | user3@ex.com
       4 |          2 | user4     | user4@ex.com
       5 |          2 | user5     | user5@ex.com
       6 |          3 | user6     | user6@ex.com
       7 |          3 | user7     | user7@ex.com
       8 |          3 | user8     | user8@ex.com
       9 |          3 | user9     | user9@ex.com
(9 rows)

8. ROW LEVEL SECURITY の有効化

次にuserテーブルに対して RLS を適用するための設定を行い、テーブル行へのアクセスを制御します。

  • RLS の有効化

    フォーマット
    ALTER TABLE テーブル名 ENABLE ROW LEVEL SECURITY;

    ALTER TABLE "user" ENABLE ROW LEVEL SECURITY;
    実行結果
    ALTER TABLE

  • ポリシーの作成

    • RLS 検証用ポリシーの作成

      フォーマット
      CREATE POLICY rls_policy ON テーブル名
 USING(テーブルカラム名[::text] = current_setting('app.テーブルカラム名'[::text]));

      CREATE POLICY rls_policy ON "user"
 USING(company_id = current_setting('app.company_id'));

      エラーになりました。

      実行結果
      ERROR:  operator does not exist: integer = text
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

    • 暗黙の型変換
      正確には上記フォーマットにある::textの通り、text 扱いになっていてUSINGで指定するデータカラムはtext型である必要があります。
      company_idはinteger型のため、ポリシー使用の際にtext型へキャストされるよう以下を実行します。

      CREATE CAST (integer AS text) WITH INOUT AS IMPLICIT;
      実行結果
      CREATE CAST

      再度、ポリシー作成を行うと、問題なく作成できました。

      実行結果
      CREATE POLICY

  • テーブル情報を参照

    \d user

    userテーブルの情報を見てみると、RLS 用ポリシーの情報が追加されています。

                                                Table "public.user"
   Column   |          Type          | Collation | Nullable |                Default                
------------+------------------------+-----------+----------+---------------------------------------
 user_id    | integer                |           | not null | nextval('user_user_id_seq'::regclass)
 company_id | integer                |           | not null | 
 user_name  | character varying(255) |           | not null | 
 email      | character varying(255) |           | not null | 
Indexes:
    "user_pkey" PRIMARY KEY, btree (user_id)
Foreign-key constraints:
    "user_company_id_fkey" FOREIGN KEY (company_id) REFERENCES company(company_id)
Policies:
    POLICY "rls_policy"
      USING (((company_id)::text = current_setting('app.company_id'::text)))

※ RLS の有効化設定とポリシーによって、テーブルの行へのアクセス制御が可能となります。

9. ロールの作成 ・ 権限付与 ・ 切り替え

次は、ロールの作成・権限付与・切り替えです。
ロールと言われて最初は混乱しましたが、要はユーザーのことです。

  • ロールの一覧表示

    \du

    現状はadminのみですね。

    実行結果
                                           List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 admin     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

  • RLS 検証用ロールの作成(ログイン権限付与)
    ロール作成手順も兼ねてということで、別途ロールを用意します。
    ※ 管理者ロールのまま進めても問題ありません。

    フォーマット
    CREATE ROLE ロール名 WITH 与える操作権限(※WITH以降は記述なしでもOK);

    CREATE ROLE rls_test WITH LOGIN;
    実行結果
    CREATE ROLE

  • ロールの一覧表示、操作権限の確認
    ロールが作成されたかどうかは以下で確認できます。

    \du

    rls_test追加されていますね。

    実行結果
                                       List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 admin     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 rls_test  |                                                            | {}

    ちなみに、以下で確認することも可能です。

    select rolname, rolcreatedb from pg_roles;

  • ロールへの操作権限付与
    rls_testロールへuserテーブルの全ての操作権限を付与します。

    GRANT ALL PRIVILEGES ON "user" TO rls_test;
    実行結果
    GRANT

  • ロールの切り替え
    admin ⇨ rls_testへロールの切り替えを行います。

    フォーマット
    \connect - ロール名

    \connect - rls_test

    userの部分が、adminからrls_testへ切り替わりました。

    実行結果
    You are now connected to database "rls_verification" as user "rls_test".

10. RLS によるテーブルデータ操作

これでuserテーブルへ RLS が適用されました。
この後、CRUD 動作を見ていきましょう！と言いたい所ですが、１つ準備が必要です。

  • テーブル行へのアクセスを制御するデータをセッション値としてセット
    set_configは、SQL のSETコマンドと同じです。
    フォーマット
    SELECT set_config('app.テーブルカラム名', カラムデータ, 「カラムデータ」のパラメータ設定);
 
「カラムデータ」のパラメータ設定(true / false)
  ・ true ： カラムデータを現在のトランザクションに適用
  ・ false ： カラムデータを現在のセッションに適用

    SELECT set_config('app.company_id', 1, false);
    実行結果
     set_config 
------------
 1
(1 row)

※以降、userテーブルについてはcompany_id ： 1に紐づくデータのみにしかアクセスできません。set_configによってuserテーブルの行へのアクセスを制御しました。

これで準備は整いました。
では、実際に RLS による CRUD 動作を見ていきましょう。

  • データ検索
    フォーマット
    SELECT * FROM テーブル名;

    SELECT * FROM "user";

　WHERE句なしで、userデータの絞り込み検索がされました。
　検索されたのは、set_configでセットしたcompany_id ： １のデータのみです。

  • 実行結果
     user_id | company_id | user_name |    email     
---------+------------+-----------+--------------
       1 |          1 | user1     | user1@ex.com
       2 |          1 | user2     | user2@ex.com
       3 |          1 | user3     | user3@ex.com
(3 rows)

  • データ追加

    フォーマット
    INSERT INTO テーブル名 (カラム名1, カラム名2) values
(データ1, データ２,・　・・);

    追加データのcompany_idとset_configによるセッション値が同じ場合、登録成功

    INSERT INTO "user" (user_id, company_id, user_name, email) values
(10, 1,'user1','user10@ex.com');
    実行結果
    INSERT 0 1

    追加データのcompany_idとset_configによるセッション値が異なる場合、RLS エラー

    INSERT INTO "user" (user_id, company_id, user_name, email) values
(11, 3,'user11','user11@ex.com');
    実行結果
    ERROR:  new row violates row-level security policy for table "user"

  • データ更新
    フォーマット
    UPDATE テーブル名 SET カラム名 = カラムデータ WHERE 条件式;
    更新データのcompany_idとset_configによるセッション値が同じ場合、更新成功 
    UPDATE "user" SET user_name = 'user10' WHERE user_id = 10;
    実行結果
    UPDATE 1
    更新データのcompany_idとset_configによるセッション値が異なる場合、更新無効 
    UPDATE "user" SET user_name = 'user9Up' WHERE user_id = 9;
    実行結果
    UPDATE 0

  • データ削除

    フォーマット
    DELETE　FROM テーブル名 WHERE 条件式

    削除データのcompany_idとset_configによるセッション値が同じ場合、削除成功

    DELETE FROM "user" WHERE user_id = 10;
    実行結果
    DELETE 1

    削除データのcompany_idとset_configによるセッション値が異なる場合、削除無効

    DELETE FROM "user" WHERE user_id = 9;
    実行結果
    DELETE 0

参考文献

PostgreSQLとは？特徴や最新バージョンの機能を紹介！
PostgreSQLのRow Level Securityを使ってマルチテナントデータを安全に扱う
PostgreSQLの基本的な操作方法・コマンドまとめ
PostgreSQLでの暗黙の型変換メモ（文字列<->数値）
9.20. システム管理関数
Github

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?