Postgresqlのltreeを活用した階層構造の便利な利用法 - RAKUS Developers Blog | ラクス エンジニアブログ

RAKUS Developers Blog | ラクス エンジニアブログ

株式会社ラクスのITエンジニアによる技術ブログです。

トップ > PostgreSQL > Postgresqlのltreeを活用した階層構造の便利な利用法

Postgresqlのltreeを活用した階層構造の便利な利用法

PostgreSQL

はじめに

こんにちは！
エンジニア2年目のTKDSです！
今回はltreeについて調べ、その活用法を考えてみました。
ltreeについて、ltreeの活用法の2段構成です。

ltreeとは

階層ツリー構造を模した構造を格納する機能を提供する拡張機能です。
詳しくはドキュメントをみてください。

ltree型

階層ツリー構造を表す型です。
例）`Company.Department.Team1 ドット区切りで大文字小文字は区別しないようです。
各データはラベルと呼びます（上記でのCompany、Department、Team）。

ltreeの操作

よく使いそうな操作だけ２つ挙げます。

  • ltree @> ltree : 左辺の引数が右辺の親要素（か同じ）かどうか
    例）SELECT name FROM organization WHERE path @> 'Top.IT.Software';
    テーブルのpathがTop.IT.Softwareの親に該当する要素をすべて探します。

  • ltree <@ ltree : 左辺の引数が右辺の子要素（か同じ）かどうか
    例）SELECT name FROM organization WHERE path <@ 'Top.IT';
    テーブルのpathがTop.ITを親に持つ要素を探します。
    このSELECT文では、Top.ITを親に持つ要素をすべて探します。

  • ~ :一致するパスの検索

右辺に指定したパスに一致するltreeを探します。

SELECT name FROM organization WHERE path ~ 'Top.IT.*';
SELECT name FROM organization WHERE path ~ 'Top.I*.*';

活用法

2種類ほど活用例を考えてみました。

1. 承認フローの構築

ltreeを使って、承認フローに使えるテーブルを構築してみます。

事前準備

ces:
  db:
    image: postgres:16.4-bullseye
    container_name: db
    environment:
      POSTGRES_USER: postgres
      POSTGRES_DB: postgres
      POSTGRES_PASSWORD: postgres
    ports:
      - "127.0.0.1:5432:5432"
    volumes:
      - db_data:/var/lib/postgresql/data  
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql

    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres -d postgres"]
      interval: 30s
      timeout: 10s
      retries: 5
      start_period: 10s

volumes:
  db_data:

-- ltree拡張の有効化
CREATE EXTENSION IF NOT EXISTS ltree;

テーブル作成

CREATE TABLE approval_flow (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    flow_path ltree UNIQUE,         -- 階層構造を表現するためのltreeカラム
    status text,             -- 各承認ステップのステータスを格納
    approver text            -- 承認者（ユーザーIDや名前）
);

データ追加

INSERT INTO approval_flow (flow_path, status, approver)
VALUES
    ('1.approver.userA', 'open', 'userA'),
    ('2.approver.userB', 'pending', 'userB'),
    ('2.approver.userC', 'pending', 'userC'),
    ('3.approver.userD', 'closed', 'userD');

ここまででデータ投入までできました。

では、2の承認者でpendingのひとを検索してみましょう。

SELECT approver 
FROM approval_flow 
WHERE flow_path ~ '2.approver.*' 
  AND status = 'pending';

userCをapprovedにしてみましょう。

UPDATE approval_flow
SET status = 'approved'
WHERE flow_path = '2.approver.userC';

1ユーザー検索結果から消えてるのがわかります。

階層構造を表現できるので、1列で承認が必要な人を管理できて便利です。

2. テーブルに細かくアクセス制御をかける

次に活用例２です。
もともと考えてたのはこの使い方でした。
スキーマよりも細かく、グループを区切ってアクセス制御できないかなと考えたことがありました。
そのときに、調べてみつけたのが階層構造を扱うltreeでした。
この活用方法では、ltreeでグループを作り、Row Level Security (行セキュリティポリシー)の有効化で参照単位を制限してアクセス制御を実現します。

テーブルの関連のイメージは以下の図です。
基本的にすべてのテーブルをテナントIDで紐付けて、事故ってテナント外のデータを参照しないようにしました。

では、実際に環境を構築してきます。

事前準備

1のときと同じです。
docker compose down -v、docker compose upで環境作り直しておきます。　
docker exec -it db psql -U postgresでログインします。

ltreeの有効化

-- ltree拡張の有効化
CREATE EXTENSION IF NOT EXISTS ltree;

ついでにtestスキーマを作って参照するようにしておきます。
ltreeの設定はpublicスキーマにインストールされるようなので、publicも追加しておきます。
publicを含めないとltreeが参照できず、ltree型がないエラーになります。

CREATE SCHEMA test;
set search_path to test, public ;

テーブル作成

  • tenantテーブル
CREATE TABLE test.tenants (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL
);
  • usersテーブル
CREATE TABLE test.users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  username TEXT NOT NULL,
  email TEXT NOT NULL,
  tenant_id BIGINT NOT NULL,
  CONSTRAINT fk_tenant FOREIGN KEY (tenant_id) REFERENCES test.tenants(id)
);
  • rolesテーブル

ロールの名前を設定するテーブル

CREATE TABLE test.roles (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  tenant_id BIGINT NOT NULL,
  CONSTRAINT fk_tenant FOREIGN KEY (tenant_id) REFERENCES test.tenants(id),
  CONSTRAINT unique_role_name_per_tenant UNIQUE (name, tenant_id)
);
  • role_permissionsテーブル：ロールに紐づく権限を設定するテーブル
    ltreeでアクセス制限を設定します。
CREATE TABLE test.role_permissions (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  role_id BIGINT NOT NULL,
  access_ltree LTREE NOT NULL,
  tenant_id BIGINT NOT NULL,
  CONSTRAINT fk_role FOREIGN KEY (role_id) REFERENCES test.roles(id),
  CONSTRAINT fk_tenant FOREIGN KEY (tenant_id) REFERENCES test.tenants(id)
);
  • user_rolesテーブル：ユーザーとロールの紐付けを管理するテーブル
CREATE TABLE test.user_roles (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id BIGINT NOT NULL,
  role_name TEXT NOT NULL,
  tenant_id BIGINT NOT NULL,
  CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES test.users(id),
  CONSTRAINT fk_tenant FOREIGN KEY (tenant_id) REFERENCES test.tenants(id)
);
  • documentsテーブル：アクセス制限するデータを投入するテーブル
CREATE TABLE test.documents (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  tenant_id BIGINT NOT NULL,
  access_ltree LTREE NOT NULL,
  CONSTRAINT fk_tenant FOREIGN KEY (tenant_id) REFERENCES test.tenants(id)
);

すべて作成したらテーブル一覧を確認してみます

\dt test.*

ポリシー作成

アクセス制御を実現するためのルールを作っていきます。

行セキュリティポリシーの有効化

ALTER TABLE test.documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE test.documents FORCE ROW LEVEL SECURITY;

有効になっているか確認します。

SELECT relrowsecurity, relforcerowsecurity
FROM pg_class
WHERE relname = 'documents';

ポリシーの設定

詳しくはドキュメントをみてください

CREATE POLICY tenant_rbac_ltree_policy ON test.documents
  USING (
    EXISTS (
      SELECT 1
      FROM test.user_roles ur
      JOIN test.roles r ON ur.role_name = r.name AND ur.tenant_id = r.tenant_id
      JOIN test.role_permissions rp ON r.id = rp.role_id
      WHERE ur.user_id = current_setting('session.authorization_user_id')::BIGINT
        AND rp.tenant_id = test.documents.tenant_id
        AND rp.access_ltree @> test.documents.access_ltree
    )
  );

CREATE POLICY tenant_rbac_ltree_policy_insert_update ON test.documents
  WITH CHECK (
    EXISTS (
      SELECT 1
      FROM test.user_roles ur
      JOIN test.roles r ON ur.role_name = r.name AND ur.tenant_id = r.tenant_id
      JOIN test.role_permissions rp ON r.id = rp.role_id
      WHERE ur.user_id = current_setting('session.authorization_user_id')::BIGINT
        AND rp.tenant_id = test.documents.tenant_id
        AND rp.access_ltree @> test.documents.access_ltree
    )
  );

session.authorization_user_idを設定することで実際のアクセス制御を実現します。

データを追加

  • テナントデータの投入
INSERT INTO tenants (name) VALUES ('Tenant A');
INSERT INTO tenants (name) VALUES ('Tenant B');
  • ロールデータの投入
-- Tenant Aのロール
INSERT INTO roles (name, tenant_id) VALUES ('Admin', 1);
INSERT INTO roles (name, tenant_id) VALUES ('User', 1);

-- Tenant Bのロール
INSERT INTO roles (name, tenant_id) VALUES ('Admin', 2);
INSERT INTO roles (name, tenant_id) VALUES ('User', 2);
  • ロール権限の投入
-- Tenant AのAdminロールの権限 (Sales部門全体にアクセス可能)
INSERT INTO role_permissions (role_id, access_ltree, tenant_id) 
VALUES (1, 'TenantA.Sales', 1);  -- Admin role for Tenant A (Sales部門全体)

-- Tenant AのUserロールの権限 (Sales部門のTeam 2のみアクセス可能)
INSERT INTO role_permissions (role_id, access_ltree, tenant_id) 
VALUES (2, 'TenantA.Sales.Team2', 1); -- User role for Tenant A (Team 2のみ)

-- Tenant BのAdminロールの権限 (Marketing部門全体にアクセス可能)
INSERT INTO role_permissions (role_id, access_ltree, tenant_id) 
VALUES (3, 'TenantB.Marketing', 2);  -- Admin role for Tenant B (Marketing部門全体)

-- Tenant BのUserロールの権限 (Marketing部門のTeam 2のみアクセス可能)
INSERT INTO role_permissions (role_id, access_ltree, tenant_id) 
VALUES (4, 'TenantB.Marketing.Team2', 2); -- User role for Tenant B (Team 2のみ)
  • ユーザーデータの投入
-- Tenant Aのユーザー
INSERT INTO users (username, email, tenant_id) VALUES ('user_a1', 'user_a1@example.com', 1);
INSERT INTO users (username, email, tenant_id) VALUES ('user_a2', 'user_a2@example.com', 1);

-- Tenant Bのユーザー
INSERT INTO users (username, email, tenant_id) VALUES ('user_b1', 'user_b1@example.com', 2);
INSERT INTO users (username, email, tenant_id) VALUES ('user_b2', 'user_b2@example.com', 2);

ユーザーとロールの紐付け

-- Tenant Aのユーザーにロールを割り当て
INSERT INTO user_roles (user_id, role_name, tenant_id) VALUES (1, 'Admin', 1);  -- user_a1 is Admin in Tenant A
INSERT INTO user_roles (user_id, role_name, tenant_id) VALUES (2, 'User', 1);   -- user_a2 is User in Tenant A

-- Tenant Bのユーザーにロールを割り当て
INSERT INTO user_roles (user_id, role_name, tenant_id) VALUES (3, 'Admin', 2);  -- user_b1 is Admin in Tenant B
INSERT INTO user_roles (user_id, role_name, tenant_id) VALUES (4, 'User', 2);   -- user_b2 is User in Tenant B
  • ドキュメントデータの投入
-- Tenant Aのドキュメント (会社=Tenant A, 部門=Sales, チーム=Team 1)
INSERT INTO documents (title, content, tenant_id, access_ltree) 
VALUES ('Document A1', 'Content of Document A1', 1, 'TenantA.Sales.Team1');
INSERT INTO documents (title, content, tenant_id, access_ltree) 
VALUES ('Document A2', 'Content of Document A2', 1, 'TenantA.Sales.Team2');

-- Tenant Bのドキュメント (会社=Tenant B, 部門=Marketing, チーム=Team 1)
INSERT INTO documents (title, content, tenant_id, access_ltree) 
VALUES ('Document B1', 'Content of Document B1', 2, 'TenantB.Marketing.Team1');
INSERT INTO documents (title, content, tenant_id, access_ltree) 
VALUES ('Document B2', 'Content of Document B2', 2, 'TenantB.Marketing.Team2');

ユーザー作成

postgresユーザーのままだとすべてのレコードがみえてしまうので、別のユーザーを作成します。

CREATE USER access_user WITH PASSWORD 'password';
-- スキーマへのアクセス権限（USAGE）を付与
GRANT USAGE ON SCHEMA test TO access_user;

-- テーブルに対するCRUD操作権限を付与
GRANT INSERT, SELECT, UPDATE, DELETE ON ALL TABLES IN SCHEMA test TO access_user;

試す

最初に全データをpostgresユーザーでみておきましょう。

各記事にaccess_ltreeが設定されているのがわかります。
この権限通りにアクセス制限できているか確認していきます。

ログインします。
docker exec -it db psql -U access_user -d postgres
どの権限でアクセスするかは、session.authorization_user_idで決められます。
まずは設定なしでやってみます。
権限がないのでみれません。

次はuser_id=1でやってみます。
権限はTenantAのSales部門全体にアクセス可能です。

次はuser_id=2でやってみます。
権限はTenantAのSales部門のTeam 2のみアクセス可能です。

次はuser_id=3でやってみます。
権限はTenantBのMarketing部門全体にアクセス可能です。

次はuser_id=4でやってみます。
権限はTenantBのMarketing部門のTeam 2のみアクセス可能です。

次はuser_id=5でやってみます。
紐付けられるユーザーはいません。

しっかり操作権限が絞られているのが確認できました！

まとめ

ltreeについて紹介と活用方法を考えてみました。
階層構造を簡単に扱えて、パターンマッチで検索もできるので非常に便利です。
記事をみてくださったかたもぜひ活用方法を考えてみてください！（社内のかたはこっそり教えていただけるとありがたいです）
ここまで読んでいただきありがとうございました！

ラクスの技術 / デザイン情報サイト
Copyright © RAKUS Co., Ltd. All rights reserved.