Tạo Database Template chứa Schema mẫu
Chuẩn bị môi trường và tạo Template Database
Chúng ta cần tạo một database đặc biệt đóng vai trò là "mẫu" (template). Khi tạo schema mới cho tenant, PostgreSQL sẽ copy toàn bộ cấu trúc bảng, index và view từ database này. Database mẫu phải tồn tại trước khi bắt đầu tạo tenant.
Mục tiêu là tạo database có tên tenant_template_db. Database này sẽ chứa schema public với các bảng cơ bản của hệ thống như users, orders...
Tại sao cần làm vậy? Để đảm bảo mọi tenant mới đều có cùng cấu trúc dữ liệu đồng nhất, giảm thiểu lỗi cấu hình thủ công và tăng tốc độ onboard.
Kết quả mong đợi: Database tenant_template_db được tạo thành công, có cấu trúc bảng hoàn chỉnh và được đánh dấu là template để có thể copy.
Đầu tiên, đăng nhập vào user postgres và tạo database mẫu.
sudo -u postgres psql -c "CREATE DATABASE tenant_template_db;"
Kết quả: PostgreSQL trả về thông báo CREATE DATABASE.
Tiếp theo, định nghĩa cấu trúc bảng trong database mẫu. Chúng ta sẽ tạo schema public và các bảng cần thiết.
sudo -u postgres psql -d tenant_template_db -c "
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
total_amount DECIMAL(10, 2),
status VARCHAR(20) DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
"
Kết quả: PostgreSQL trả về CREATE TABLE cho cả hai bảng.
Quan trọng nhất: Đánh dấu database này là template để PostgreSQL cho phép tạo database mới từ nó.
sudo -u postgres psql -c "ALTER DATABASE tenant_template_db SET is_template = true;"
Kết quả: PostgreSQL trả về ALTER DATABASE.
Verify kết quả
Kiểm tra xem database có xuất hiện trong danh sách và có thuộc tính is_template = true không.
sudo -u postgres psql -c "\l | grep tenant_template_db"
Đầu ra phải hiển thị dòng chứa tên database và cột template có giá trị yes (hoặc t).
Viết Script SQL tự động tạo Schema từ Template
Logic tạo Schema mới cho Tenant
Trong mô hình Schema riêng, mỗi tenant sẽ có một schema riêng (ví dụ: tenant_acme, tenant_beta). Chúng ta sẽ viết một hàm SQL để copy cấu trúc từ public của template vào schema mới.
Tại sao không tạo database mới? Vì PostgreSQL giới hạn số lượng database và việc quản lý backup/restore trên nhiều database riêng biệt phức tạp hơn so với quản lý schema trong một database duy nhất (master tenant db).
Kết quả mong đợi: Một hàm SQL create_tenant_schema được tạo, cho phép tạo schema mới với đầy đủ bảng dữ liệu từ template.
Trước khi tạo hàm, chúng ta cần tạo một database "Master" nơi chứa tất cả các schema của tenant. Giả sử tên là multi_tenant_db.
sudo -u postgres psql -c "CREATE DATABASE multi_tenant_db;"
Kết quả: Database được tạo.
Chuyển sang database master và tạo hàm động để tạo schema. Hàm này sẽ tạo schema mới, sau đó copy từng object (table, index, sequence) từ schema public của template sang schema mới.
sudo -u postgres psql -d multi_tenant_db -c "
CREATE OR REPLACE FUNCTION create_tenant_schema(tenant_name TEXT)
RETURNS VOID AS \$\$
DECLARE
schema_name TEXT := tenant_name;
tbl RECORD;
sql_stmt TEXT;
BEGIN
-- 1. Tạo schema mới
EXECUTE format('CREATE SCHEMA %I', schema_name);
-- 2. Copy cấu trúc bảng từ template (schema public) sang schema mới
FOR tbl IN
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_type = 'BASE TABLE'
AND table_name NOT LIKE 'pg_%'
LOOP
-- Copy DDL của bảng (chỉ cấu trúc, không copy dữ liệu mẫu nếu muốn)
-- Nếu muốn copy dữ liệu mẫu từ template, cần logic clone table khác
EXECUTE format('
CREATE TABLE %I.%I (LIKE tenant_template_db.public.%I INCLUDING ALL);
', schema_name, tbl.table_name, tbl.table_name);
END LOOP;
-- 3. Copy Sequence (nếu có)
FOR tbl IN
SELECT sequence_name
FROM information_schema.sequences
WHERE sequence_schema = 'public'
AND sequence_name NOT LIKE 'pg_%'
LOOP
EXECUTE format('
CREATE SEQUENCE %I.%I (LIKE tenant_template_db.public.%I);
', schema_name, tbl.sequence_name, tbl.sequence_name);
END LOOP;
-- 4. Gán quyền sở hữu cho user tenant (nếu có user riêng) hoặc giữ nguyên
-- Ở đây ta chỉ gán quyền cho user postgres để demo
EXECUTE format('
ALTER SCHEMA %I OWNER TO postgres;
', schema_name);
RAISE NOTICE 'Schema %I created successfully from template.', schema_name;
END;
\$\$ LANGUAGE plpgsql;
"
Kết quả: PostgreSQL trả về CREATE FUNCTION.
Verify kết quả
Thử gọi hàm để tạo một schema mẫu tên tenant_test_01.
sudo -u postgres psql -d multi_tenant_db -c "SELECT create_tenant_schema('tenant_test_01');"
Kiểm tra xem schema có được tạo và có bảng bên trong không.
sudo -u postgres psql -d multi_tenant_db -c "\dn | grep tenant_test_01"
sudo -u postgres psql -d multi_tenant_db -c "\dt tenant_test_01.*"
Đầu ra phải hiển thị schema tenant_test_01 và danh sách bảng users, orders nằm trong schema đó.
Cấu hình quyền truy cập (GRANT/REVOKE) cho từng Schema
Chiến lược phân quyền Multi-Tenant
Vấn đề cốt lõi của mô hình Schema riêng là ngăn tenant A truy cập dữ liệu của tenant B. Mặc dù PostgreSQL tách biệt schema, nhưng nếu user có quyền ALL trên database, họ vẫn có thể query chéo schema.
Giải pháp: Tạo user riêng cho từng tenant (hoặc dùng 1 user chung nhưng giới hạn quyền) và chỉ GRANT quyền vào schema của chính họ.
Kết quả mong đợi: Tenant A chỉ thấy schema của mình, khi cố truy cập schema của Tenant B sẽ bị lỗi permission denied.
Trước hết, tạo user cho tenant mới (ví dụ: user_tenant_01) trong database master.
sudo -u postgres psql -d multi_tenant_db -c "
DO \$\$
DECLARE
tenant_name TEXT := 'tenant_test_01';
user_name TEXT := 'user_tenant_01';
BEGIN
-- Tạo user nếu chưa tồn tại
IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = user_name) THEN
EXECUTE format('CREATE ROLE %I WITH LOGIN PASSWORD ''secure_password_123''', user_name);
END IF;
-- Gán quyền sở hữu schema cho user này
EXECUTE format('GRANT ALL ON SCHEMA %I TO %I', tenant_name, user_name);
-- Gán quyền trên các bảng trong schema đó
FOR tbl IN
SELECT tablename
FROM pg_tables
WHERE schemaname = tenant_name
LOOP
EXECUTE format('GRANT ALL PRIVILEGES ON TABLE %I.%I TO %I', tenant_name, tbl.tablename, user_name);
END LOOP;
-- Gán quyền trên sequence
FOR seq IN
SELECT sequencename
FROM pg_sequences
WHERE schemaname = tenant_name
LOOP
EXECUTE format('GRANT ALL PRIVILEGES ON SEQUENCE %I.%I TO %I', tenant_name, seq.sequencename, user_name);
END LOOP;
END;
\$\$;
"
Kết quả: User và quyền được gán thành công.
Bước tiếp theo: Tách biệt search_path để user mặc định chỉ nhìn thấy schema của mình, tránh xung đột với schema public.
sudo -u postgres psql -d multi_tenant_db -c "ALTER ROLE user_tenant_01 SET search_path TO tenant_test_01, public;"
Kết quả: ALTER ROLE thành công. Khi user này login, họ sẽ ưu tiên tìm bảng trong schema tenant_test_01.
Verify kết quả
Thử login bằng user tenant và truy cập bảng.
sudo -u postgres psql -d multi_tenant_db -U user_tenant_01 -c "SELECT * FROM users LIMIT 1;"
Nhập password khi được hỏi. Kết quả: Trả về dữ liệu (nếu có) hoặc bảng rỗng (vừa tạo).
Thử truy cập schema của tenant khác (giả sử ta tạo thêm tenant_test_02 trước đó hoặc giả lập lỗi).
sudo -u postgres psql -d multi_tenant_db -U user_tenant_01 -c "SELECT * FROM tenant_test_02.users;"
Kết quả mong đợi: Lỗi permission denied for table users hoặc relation "tenant_test_02.users" does not exist nếu search_path bị chặn.
Xử lý xung đột tên bảng với Schema Public
Ngăn chặn xung đột trong môi trường đa tenant
Vấn đề: Nếu tenant tạo bảng users và schema public cũng có bảng users, việc query SELECT * FROM users sẽ gây nhầm lẫn hoặc lỗi nếu search_path không được cấu hình đúng.
Giải pháp: Luôn đặt schema public ở cuối search_path của tenant, hoặc tốt hơn là tạo schema riêng cho hệ thống (ví dụ system_schema) và giữ public trống hoặc chỉ dùng cho internal.
Kết quả mong đợi: Tenant chỉ truy cập bảng trong schema riêng, không bị nhiễu bởi bảng trong public.
Cấu hình search_path mặc định cho database master để ngăn user mới truy cập public nếu không cần thiết.
sudo -u postgres psql -d multi_tenant_db -c "ALTER DATABASE multi_tenant_db SET search_path TO \$user, public;"
Tuy nhiên, cách an toàn nhất là khi tạo tenant, đặt search_path cụ thể.
Chỉnh sửa hàm tạo tenant (ở phần trên) để thêm lệnh set search_path vào hàm create_tenant_schema hoặc tạo một hàm riêng configure_tenant_access.
sudo -u postgres psql -d multi_tenant_db -c "
CREATE OR REPLACE FUNCTION configure_tenant_access(tenant_name TEXT, user_name TEXT)
RETURNS VOID AS \$\$
BEGIN
-- Đặt search_path: Schema tenant đứng đầu, public đứng cuối
EXECUTE format('ALTER ROLE %I SET search_path TO %I, public', user_name, tenant_name);
-- Nếu muốn chặn hoàn toàn public, bỏ 'public' khỏi list này
-- EXECUTE format('ALTER ROLE %I SET search_path TO %I', user_name, tenant_name);
RAISE NOTICE 'Search path configured for %I.', user_name;
END;
\$\$ LANGUAGE plpgsql;
"
Áp dụng cho tenant đã tạo:
sudo -u postgres psql -d multi_tenant_db -c "SELECT configure_tenant_access('tenant_test_01', 'user_tenant_01');"
Kết quả: ALTER ROLE thành công.
Verify kết quả
Tạo một bảng users trong schema public để test xung đột.
sudo -u postgres psql -d multi_tenant_db -c "CREATE TABLE public.users_dummy (id INT);"
Đăng nhập user tenant và query users.
sudo -u postgres psql -d multi_tenant_db -U user_tenant_01 -c "SELECT table_name FROM information_schema.tables WHERE table_schema = current_schema();"
Kết quả: Chỉ hiển thị bảng users và orders thuộc schema tenant_test_01, không thấy users_dummy của public.
Tự động hóa quy trình tạo Tenant bằng Bash
Script Bash tích hợp SQL
Để vận hành thực tế, chúng ta cần một script bash để gọi các hàm SQL đã viết ở trên, nhận tham số tên tenant và tự động hóa toàn bộ quy trình: tạo schema, gán quyền, set search_path.
Tại sao dùng Bash? Để dễ dàng tích hợp vào CI/CD, cron jobs, hoặc gọi trực tiếp từ command line của Sysadmin.
Kết quả mong đợi: Một file script create_tenant.sh có thể chạy một lệnh để onboard tenant mới.
Tạo file script tại /usr/local/bin/create_tenant.sh.
cat > /usr/local/bin/create_tenant.sh > /var/log/multi_tenant_provision.log
EOF
Cấp quyền thực thi cho script.
chmod +x /usr/local/bin/create_tenant.sh
Kết quả: Script đã sẵn sàng để chạy.
Verify kết quả
Chạy script để tạo tenant mới tenant_demo_final.
/usr/local/bin/create_tenant.sh tenant_demo_final
Quan sát output: Script phải in ra thông tin user, password và trạng thái thành công.
Kiểm tra trong database xem schema và user đã tồn tại chưa.
sudo -u postgres psql -d multi_tenant_db -c "\dn | grep tenant_demo_final"
sudo -u postgres psql -d multi_tenant_db -c "\du | grep user_tenant_demo_final"
Đầu ra phải hiển thị cả schema và role tương ứng.
Thử login bằng user mới để đảm bảo quyền truy cập hoạt động.
sudo -u postgres psql -d multi_tenant_db -U user_tenant_demo_final -c "SELECT current_schema();"
Kết quả: Trả về tenant_demo_final.
Điều hướng series:
Mục lục: Series: Triển khai Database Multi-Tenant với PostgreSQL và Ubuntu 24.04
« Phần 3: Kiến trúc Multi-Tenant: So sánh Schema riêng và Database riêng
Phần 5: Triển khai mô hình Database riêng: Quản lý Tenant quy mô lớn »