— 1. 创建租户模式
CREATE SCHEMA tenant_001;
CREATE SCHEMA tenant_002;
CREATE SCHEMA tenant_003;

— 2. 为每个租户创建相同结构的表
CREATE TABLE tenant_001.users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE tenant_002.users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE tenant_003.users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL
);

— 3. 创建租户角色并授权
CREATE ROLE tenant_001_role;
CREATE ROLE tenant_002_role;
CREATE ROLE tenant_003_role;

GRANT USAGE ON SCHEMA tenant_001 TO tenant_001_role;
GRANT ALL ON ALL TABLES IN SCHEMA tenant_001 TO tenant_001_role;

GRANT USAGE ON SCHEMA tenant_002 TO tenant_002_role;
GRANT ALL ON ALL TABLES IN SCHEMA tenant_002 TO tenant_002_role;

GRANT USAGE ON SCHEMA tenant_003 TO tenant_003_role;
GRANT ALL ON ALL TABLES IN SCHEMA tenant_003 TO tenant_003_role;

— 4. 创建租户用户并分配角色
CREATE USER tenant_001_user WITH PASSWORD ‘Tenant001@2026’;
CREATE USER tenant_002_user WITH PASSWORD ‘Tenant002@2026’;
CREATE USER tenant_003_user WITH PASSWORD ‘Tenant003@2026’;

GRANT tenant_001_role TO tenant_001_user;
GRANT tenant_002_role TO tenant_002_user;
GRANT tenant_003_role TO tenant_003_user;

— 5. 测试租户访问
— 以tenant_001_user身份登录
SET ROLE tenant_001_user;

— 只能访问自己的模式
SELECT * FROM tenant_001.users; — 成功
SELECT * FROM tenant_002.users; — 失败：权限被拒绝

— 重置角色
RESET ROLE;

— 1. 创建应用模块模式
CREATE SCHEMA auth; — 认证模块
CREATE SCHEMA user; — 用户模块
CREATE SCHEMA product; — 产品模块
CREATE SCHEMA order; — 订单模块
CREATE SCHEMA payment; — 支付模块

— 2. 创建各模块的表
— 认证模块
CREATE TABLE auth.sessions (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL,
token VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
expires_at TIMESTAMP NOT NULL
);

— 用户模块
CREATE TABLE user.profiles (
id SERIAL PRIMARY KEY,
user_id INTEGER UNIQUE NOT NULL,
full_name VARCHAR(100) NOT NULL,
avatar VARCHAR(255),
bio TEXT
);

— 产品模块
CREATE TABLE product.categories (
id SERIAL PRIMARY KEY,
name VARCHAR(50) UNIQUE NOT NULL,
description TEXT
);

CREATE TABLE product.items (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
category_id INTEGER REFERENCES product.categories(id),
price DECIMAL(10,2) NOT NULL,
stock INTEGER NOT NULL
);

— 订单模块
CREATE TABLE order.orders (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL,
total_amount DECIMAL(10,2) NOT NULL,
status VARCHAR(20) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order.order_items (
id SERIAL PRIMARY KEY,
order_id INTEGER REFERENCES order.orders(id),
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL,
price DECIMAL(10,2) NOT NULL
);

— 支付模块
CREATE TABLE payment.transactions (
id SERIAL PRIMARY KEY,
order_id INTEGER REFERENCES order.orders(id),
amount DECIMAL(10,2) NOT NULL,
payment_method VARCHAR(50) NOT NULL,
status VARCHAR(20) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 3. 创建模块角色并授权
CREATE ROLE auth_role;
CREATE ROLE user_role;
CREATE ROLE product_role;
CREATE ROLE order_role;
CREATE ROLE payment_role;

GRANT ALL ON SCHEMA auth TO auth_role;
GRANT ALL ON ALL TABLES IN SCHEMA auth TO auth_role;

GRANT ALL ON SCHEMA user TO user_role;
GRANT ALL ON ALL TABLES IN SCHEMA user TO user_role;

GRANT ALL ON SCHEMA product TO product_role;
GRANT ALL ON ALL TABLES IN SCHEMA product TO product_role;

GRANT ALL ON SCHEMA order TO order_role;
GRANT ALL ON ALL TABLES IN SCHEMA order TO order_role;

GRANT ALL ON SCHEMA payment TO payment_role;
GRANT ALL ON ALL TABLES IN SCHEMA payment TO payment_role;

— 4. 创建应用用户并分配角色
CREATE USER fgapp_user WITH PASSWORD ‘AppUser@2026’;
GRANT auth_role, user_role, product_role, order_role, payment_role TO fgapp_user;

— 5. 测试模块访问
SET ROLE fgapp_user;

— 访问各模块的表
SELECT * FROM auth.sessions;
SELECT * FROM user.profiles;
SELECT * FROM product.items;
SELECT * FROM order.orders;
SELECT * FROM payment.transactions;

— 重置角色
RESET ROLE;

— 1. 创建不同权限级别的模式
CREATE SCHEMA public_data; — 公开数据
CREATE SCHEMA internal_data; — 内部数据
CREATE SCHEMA admin_data; — 管理员数据

— 2. 创建各模式的表
CREATE TABLE public_data.products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL
);

CREATE TABLE internal_data.sales (
id SERIAL PRIMARY KEY,
product_id INTEGER REFERENCES public_data.products(id),
quantity INTEGER NOT NULL,
amount DECIMAL(10,2) NOT NULL,
sale_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE admin_data.users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
password VARCHAR(100) NOT NULL,
role VARCHAR(20) NOT NULL
);

— 3. 创建不同角色
CREATE ROLE public_role;
CREATE ROLE internal_role;
CREATE ROLE admin_role;

— 4. 授权
— 公开数据：所有人可访问
GRANT USAGE ON SCHEMA public_data TO public_role;
GRANT SELECT ON ALL TABLES IN SCHEMA public_data TO public_role;

— 内部数据：内部用户可访问
GRANT USAGE ON SCHEMA internal_data TO internal_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA internal_data TO internal_role;

— 管理员数据：仅管理员可访问
GRANT USAGE ON SCHEMA admin_data TO admin_role;
GRANT ALL ON ALL TABLES IN SCHEMA admin_data TO admin_role;

— 5. 创建用户并分配角色
CREATE USER public_user WITH PASSWORD ‘PublicUser@2026’;
CREATE USER internal_user WITH PASSWORD ‘InternalUser@2026’;
CREATE USER admin_user WITH PASSWORD ‘AdminUser@2026’;

GRANT public_role TO public_user;
GRANT public_role, internal_role TO internal_user;
GRANT public_role, internal_role, admin_role TO admin_user;

— 6. 测试权限
— 以public_user身份登录
SET ROLE public_user;
SELECT * FROM public_data.products; — 成功
SELECT * FROM internal_data.sales; — 失败：权限被拒绝
SELECT * FROM admin_data.users; — 失败：权限被拒绝

— 以internal_user身份登录
SET ROLE internal_user;
SELECT * FROM public_data.products; — 成功
SELECT * FROM internal_data.sales; — 成功
SELECT * FROM admin_data.users; — 失败：权限被拒绝

— 以admin_user身份登录
SET ROLE admin_user;
SELECT * FROM public_data.products; — 成功
SELECT * FROM internal_data.sales; — 成功
SELECT * FROM admin_data.users; — 成功

— 重置角色
RESET ROLE;