Postgres Multi-Tenant Attempt 🌟

When we talk about multi-tenancy, we can imagine it as an apartment building. 🏢 In this building, there are many different apartments🏠 (tenants), each apartment is independent, with its own space and privacy, but they share the same building infrastructure (such as elevators, utilities, etc.). In the software domain, multi-tenancy refers to an architectural pattern that allows multiple different users (tenants) to be served within the same software system, with isolation between each user, just like the residents in an apartment building.

Why does PostgreSQL need multi-tenancy? 🤔

PostgreSQL needs to support multi-tenancy because many applications need to serve multiple users, and these users require independent data storage space. 🛠️ Through a multi-tenant architecture, PostgreSQL can achieve isolated storage of different users' data, ensuring data security and privacy. 🔒

Why not create a separate PostgreSQL for each user? 🤨

Creating a separate PostgreSQL database for each user would lead to resource waste and increased management complexity. 📈 Through a multi-tenant architecture, database resources can be utilized more efficiently, reducing costs and simplifying management. 🔄 Additionally, a multi-tenant architecture can also improve system scalability and flexibility. 💡

Why does SAAS need multi-tenancy? 🌐

In the Software as a Service (SAAS) model, service providers need to offer unified software services to multiple clients, each of whom may be an organization, business, or individual. 🤝 Using a multi-tenant architecture allows for data isolation and security between different clients while avoiding the maintenance of separate system instances for each client, thus reducing operational complexity and costs. 💻

Prerequisites 📚

Have Docker, previously learned PG, have a certain SQL foundation, understand what triggers are, and what functions are.
Trigger

Setting Up the Test Environment#

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

docker exec -it mt bash

Connect to the database using psql -U postgres

CREATE DATABASE testmt;

Assume there are tables User and 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
);

Create a Trigger#

Get the current inserted user's id
Insert a record in the access table

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();

Test

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;

Everything seems fine.

Start Creating Multi-Tenancy#

Create a trigger
When inserting Accesses, create a new user and create a schema bound to it.

Create a User for Each 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();

Test

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)

Use \du to view users

\du

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

Everything seems fine.

DELETE FROM Users WHERE id = 3;

Create Schema and Grant Permissions#

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();

Test

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

Use \dn to view schemas

\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}

You can try logging in yourself, I won't elaborate further here.

When Accesses are updated, synchronize the user password#

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();

You can try logging in yourself; just make sure the old password doesn't work.

When Accesses delete records, synchronize user deletion#

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();

Test

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

Conclusion#

This is just a simple implementation of multi-tenancy; in actual applications, it may be more complex, such as requiring more permission controls, data isolation, etc. 🌟 However, through this example, you can understand how to use PostgreSQL triggers and functions to implement the most basic multi-tenant functionality. If you want to delve deeper, you can look at the following features: