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 INSTEAD THIS IS JUST FOR DEMO
    name VARCHAR(50) NOT NULL
);

CREATE TABLE Accesses (
    id SERIAL PRIMARY KEY, -- UUID INSTEAD THIS IS JUST FOR DEMO
    user_id INTEGER NOT NULL,
    access_name VARCHAR(50) NOT NULL,
    access_secret VARCHAR(50) NOT NULL,
    -- FOR CASCADE DELETE AND UPDATE
    FOREIGN KEY (user_id) REFERENCES Users(id) ON DELETE CASCADE ON UPDATE CASCADE
);

新建 trigger#

獲取當前 insert 的用戶 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;

看起來沒問題

開始創建多租戶#

新建 trigger
當插入 Accesses 時，創建一個新的用戶，並創建一個與之綁定的 schema

為每個 access 創建一個用戶#

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;

創建 schema，並授權#

CREATE FUNCTION create_schema() RETURNS trigger AS $trigger_bound$
BEGIN
    EXECUTE FORMAT('CREATE SCHEMA IF NOT EXISTS schema_%s', NEW.user_id);
    -- main user has all privileges
    -- sub users only have select, insert for main user's tables
    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;
    -- set search path
    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查看 schema

\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
    -- revoke all privileges on schema
    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;
    -- delete schema if the user is dead
    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 的觸發器和函數來實現最基礎的多租戶功能，若想繼續深究可以去看下面的功能：