Postgres マルチテナントの試み🌟

多テナントについて話すとき、アパートのビルを想像できます。 🏢 このビルには、さまざまなアパート🏠（テナント）があり、それぞれのアパートは独立しており、自分のスペースとプライバシーを持っていますが、同じビルのインフラ（エレベーター、水道、電気など）を共有しています。ソフトウェアの分野では、多テナントは、同じソフトウェアシステム内で複数の異なるユーザー（テナント）にサービスを提供することを可能にするアーキテクチャパターンを指します。各ユーザーは相互に隔離されており、アパートの住人のようです。

なぜ PostgreSQL は多テナントを必要とするのか？ 🤔

PostgreSQL は多テナントの能力をサポートする必要があります。なぜなら、多くのアプリケーションが複数のユーザーにサービスを提供する必要があり、これらのユーザーは独立したデータストレージスペースを必要とするからです。 🛠️ 多テナントアーキテクチャを通じて、PostgreSQL は異なるユーザーのデータを隔離して保存し、データの安全性とプライバシーを確保できます。 🔒

なぜ各ユーザーに PostgreSQL を開設しないのか？ 🤨

各ユーザーに独立した PostgreSQL データベースを開設すると、リソースの浪費と管理の複雑性が増します。 📈 多テナントアーキテクチャを使用することで、データベースリソースをより効率的に利用し、コストを削減し、管理を簡素化できます。 🔄 さらに、多テナントアーキテクチャはシステムのスケーラビリティと柔軟性を向上させることができます。 💡

なぜ SAAS は多テナントを必要とするのか？ 🌐

ソフトウェア・アズ・ア・サービス（SAAS）モデルでは、サービスプロバイダーは複数のクライアントに統一されたソフトウェアサービスを提供する必要があります。各クライアントは組織、企業、または個人である可能性があります。 🤝 多テナントアーキテクチャを使用することで、異なるクライアント間のデータの隔離と安全性を実現し、各クライアントのために個別のシステムインスタンスを維持する必要がなくなり、運用の複雑性とコストを削減できます。 💻

予備知識 📚

手元に docker があり、以前に PG を学んだことがあり、一定の SQL 基礎があり、トリガーとは何か、関数とは何かを理解しています。
トリガー

テスト環境の構築#

docker run --name mt -e POSTGRES_PASSWORD=114514 -d postgres:16

docker exec -it mt bash

psql -U postgres を使用してデータベースに接続します。

CREATE DATABASE testmt;

User と Access テーブルがあると仮定します#

CREATE TABLE Users (
    id SERIAL PRIMARY KEY, -- UUID の代わりにこれはデモ用です
    name VARCHAR(50) NOT NULL
);

CREATE TABLE Accesses (
    id SERIAL PRIMARY KEY, -- UUID の代わりにこれはデモ用です
    user_id INTEGER NOT NULL,
    access_name VARCHAR(50) NOT NULL,
    access_secret VARCHAR(50) NOT NULL,
    -- カスケード削除と更新のため
    FOREIGN KEY (user_id) REFERENCES Users(id) ON DELETE CASCADE ON UPDATE CASCADE
);

トリガーの新規作成#

現在の挿入ユーザーの ID を取得します。
access テーブルにレコードを挿入します。

CREATE FUNCTION insert_default_access() RETURNS trigger AS $trigger_bound$
BEGIN
    INSERT INTO Accesses (user_id, access_name, access_secret)
    VALUES (NEW.id, NEW.id, 'random_secret');
    RETURN NEW;
END;
$trigger_bound$
LANGUAGE plpgsql;

CREATE TRIGGER create_default_access_for_new_user
AFTER INSERT ON Users
FOR EACH ROW
EXECUTE PROCEDURE insert_default_access();

テスト

INSERT INTO Users (name) VALUES ('Alice');
SELECT * FROM Users;
--  id | name
-- ----+-------
--   2 | Alice
-- (1 row)
SELECT * FROM Accesses;
--  id | user_id | access_name | access_secret
-- ----+---------+-------------+---------------
--   2 |       2 | 2           | random_secret
-- (1 row)
DELETE FROM Users WHERE id = 2;

問題なさそうです。

多テナントの作成を開始します#

トリガーを新規作成します。
Accesses に挿入する際に、新しいユーザーを作成し、それにバインドされたスキーマを作成します。

各アクセスのためにユーザーを作成します#

CREATE FUNCTION create_pg_account() RETURNS trigger AS $trigger_bound$
BEGIN
    EXECUTE FORMAT('CREATE USER user_%s_%s WITH PASSWORD %L', NEW.user_id, NEW.access_name, NEW.access_secret);
    RETURN NEW;
END;
$trigger_bound$
LANGUAGE plpgsql;

CREATE TRIGGER auto_create_pg_account
AFTER INSERT ON Accesses
FOR EACH ROW
EXECUTE PROCEDURE create_pg_account();

テスト

INSERT INTO Users (name) VALUES ('Bob');
SELECT * FROM Users;
--  id | name
-- ----+------
--   3 | Bob
-- (1 row)
SELECT * FROM Accesses;
--  id | user_id | access_name | access_secret
-- ----+---------+-------------+---------------
--   3 |       3 | 3           | random_secret
-- (1 row)

\du を使用してユーザーを確認します。

\du

--                              List of roles
--  Role name |                         Attributes
-- -----------+------------------------------------------------------------
--  postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS
--  user_3_3  |

問題なさそうです。

DELETE FROM Users WHERE id = 3;

スキーマを作成し、権限を付与します#

CREATE FUNCTION create_schema() RETURNS trigger AS $trigger_bound$
BEGIN
    EXECUTE FORMAT('CREATE SCHEMA IF NOT EXISTS schema_%s', NEW.user_id);
    -- メインユーザーはすべての権限を持つ
    -- サブユーザーはメインユーザーのテーブルに対してのみ選択、挿入権限を持つ
    IF CAST(NEW.user_id AS VARCHAR(50)) = NEW.access_name THEN
        EXECUTE FORMAT('GRANT ALL PRIVILEGES ON SCHEMA schema_%s TO user_%s_%s', NEW.user_id, NEW.user_id, NEW.access_name);
    ELSE
        EXECUTE FORMAT('GRANT SELECT, INSERT ON SCHEMA schema_%s TO user_%s_%s', NEW.user_id, NEW.user_id, NEW.access_name);
    END IF;
    -- 検索パスを設定
    EXECUTE FORMAT('ALTER USER user_%s_%s SET search_path TO schema_%s', NEW.user_id, NEW.access_name, NEW.user_id);
    RETURN NEW;
END;
$trigger_bound$
LANGUAGE plpgsql;

CREATE TRIGGER auto_create_schema_for_access
AFTER INSERT ON Accesses
FOR EACH ROW
EXECUTE PROCEDURE create_schema();

テスト

INSERT INTO Users (name) VALUES ('Charlie');
SELECT * FROM Users;

\dn を使用してスキーマを確認します。

\dn

SELECT * FROM pg_user;
--  usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil |       useconfig
-- ----------+----------+-------------+----------+---------+--------------+----------+----------+------------------------
--  postgres |       10 | t           | t        | t       | t            | ******** |          |
--  user_5_5 |    16439 | f           | f        | f       | f            | ******** |          | {search_path=schema_5}

自分でログインしてみることができますが、ここでは詳しくは説明しません。

Accesses が更新されると、ユーザーパスワードを同期更新します#

CREATE FUNCTION update_pg_account() RETURNS trigger AS $trigger_bound$
BEGIN
    EXECUTE FORMAT('ALTER USER user_%s_%s WITH PASSWORD %L', NEW.user_id, NEW.access_name, NEW.access_secret);
    RETURN NEW;
END;
$trigger_bound$
LANGUAGE plpgsql;

CREATE TRIGGER auto_update_pg_account
AFTER UPDATE ON Accesses
FOR EACH ROW
EXECUTE PROCEDURE update_pg_account();

自分でログインしてみて、古いパスワードでは入れないことを確認してください。

Accesses のレコードが削除されると、ユーザーを同期削除します#

CREATE FUNCTION delete_pg_account() RETURNS trigger AS $trigger_bound$
BEGIN
    -- スキーマのすべての権限を取り消します
    EXECUTE FORMAT('REVOKE ALL PRIVILEGES ON SCHEMA schema_%s FROM user_%s_%s', OLD.user_id, OLD.user_id, OLD.access_name);
    EXECUTE FORMAT('DROP USER IF EXISTS user_%s_%s', OLD.user_id, OLD.access_name);
    RETURN OLD;
    -- ユーザーが存在しない場合はスキーマを削除します
    IF CAST(OLD.user_id AS VARCHAR(50)) = OLD.access_name THEN
        EXECUTE FORMAT('DROP SCHEMA IF EXISTS schema_%s CASCADE', OLD.user_id);
    END IF;
END;
$trigger_bound$
LANGUAGE plpgsql;

CREATE TRIGGER auto_delete_pg_account
AFTER DELETE ON Accesses
FOR EACH ROW
EXECUTE PROCEDURE delete_pg_account();

テスト

DELETE FROM Accesses WHERE id = 5;
\du
\dn

結論#

これは単純な多テナントの実装に過ぎません。実際のアプリケーションでは、より複雑になる可能性があります。たとえば、より多くの権限管理、データ隔離などが必要です。 🌟 しかし、この例を通じて、PostgreSQL のトリガーと関数を使用して最も基本的な多テナント機能を実現する方法を理解できるでしょう。さらに深く掘り下げたい場合は、以下の機能を確認してください。