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 的触发器和函数来实现最基础的多租户功能，若想继续深究可以去看下面的功能：