-- Pattern 1: Shared schema with tenant_id column
CREATE TABLE tenants (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
slug VARCHAR(50) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE users (
id SERIAL PRIMARY KEY,
tenant_id INT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
UNIQUE (tenant_id, email)
);
CREATE INDEX idx_users_tenant ON users(tenant_id);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
tenant_id INT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
user_id INT NOT NULL REFERENCES users(id),
total DECIMAL(10,2),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_order_user_tenant
FOREIGN KEY (tenant_id, user_id)
REFERENCES users(tenant_id, id) -- Ensures same tenant
);
CREATE INDEX idx_orders_tenant ON orders(tenant_id);
-- All queries MUST include tenant_id
SELECT * FROM users
WHERE tenant_id = 123 AND email = 'user@example.com';
-- Set application context (connection-level)
SET app.current_tenant_id = '123';
-- Application ensures tenant_id in all queries
INSERT INTO users (tenant_id, username, email)
VALUES (
current_setting('app.current_tenant_id')::INT,
'newuser',
'new@example.com'
);
-- Pattern 2: Row-Level Security (automatic filtering)
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
-- Policy: Users only see their tenant's data
CREATE POLICY tenant_isolation_users ON users
USING (tenant_id = current_setting('app.current_tenant_id')::INT);
CREATE POLICY tenant_isolation_orders ON orders
USING (tenant_id = current_setting('app.current_tenant_id')::INT);
-- Now queries automatically filter by tenant
SET app.current_tenant_id = '123';
SELECT * FROM users; -- Only tenant 123's users
-- Superuser can see all (for admin queries)
CREATE POLICY admin_all_users ON users
FOR ALL
TO admin_role
USING (true);
-- Force RLS even for table owner
ALTER TABLE users FORCE ROW LEVEL SECURITY;
-- Pattern 3: Separate schema per tenant
-- Create schema for each tenant
CREATE SCHEMA tenant_123;
CREATE SCHEMA tenant_456;
-- Create tables in each schema
CREATE TABLE tenant_123.users (
id SERIAL PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100)
);
CREATE TABLE tenant_456.users (
id SERIAL PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100)
);
-- Set search path per connection
SET search_path TO tenant_123, public;
SELECT * FROM users; -- Queries tenant_123.users
-- Schema creation function
CREATE OR REPLACE FUNCTION create_tenant_schema(tenant_slug VARCHAR)
RETURNS VOID AS $$
BEGIN
EXECUTE format('CREATE SCHEMA %I', tenant_slug);
EXECUTE format('
CREATE TABLE %I.users (
id SERIAL PRIMARY KEY,
username VARCHAR(50),
email VARCHAR(100) UNIQUE
)', tenant_slug);
EXECUTE format('
CREATE TABLE %I.orders (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES %I.users(id),
total DECIMAL(10,2)
)', tenant_slug, tenant_slug);
-- Grant permissions
EXECUTE format('GRANT USAGE ON SCHEMA %I TO app_user', tenant_slug);
EXECUTE format('GRANT ALL ON ALL TABLES IN SCHEMA %I TO app_user', tenant_slug);
END;
$$ LANGUAGE plpgsql;
-- Create new tenant
INSERT INTO tenants (name, slug) VALUES ('Acme Corp', 'acme');
SELECT create_tenant_schema('acme');
-- Schema migrations for all tenants
DO $$
DECLARE
tenant_record RECORD;
BEGIN
FOR tenant_record IN SELECT slug FROM tenants LOOP
EXECUTE format('
ALTER TABLE %I.users ADD COLUMN IF NOT EXISTS phone VARCHAR(20)
', tenant_record.slug);
END LOOP;
END $$;
-- Tenant context middleware (application sets this per request)
/*
// Node.js example
app.use(async (req, res, next) => {
const tenantSlug = req.hostname.split('.')[0]; // subdomain
const tenant = await db.query(
'SELECT id FROM tenants WHERE slug = $1',
[tenantSlug]
);
if (!tenant.rows[0]) {
return res.status(404).send('Tenant not found');
}
// Set tenant context for this connection
await req.dbClient.query(
'SET LOCAL app.current_tenant_id = $1',
[tenant.rows[0].id]
);
next();
});
*/
-- Tenant-specific connection pooling
/*
const pools = {};
function getTenantPool(tenantSlug) {
if (!pools[tenantSlug]) {
pools[tenantSlug] = new Pool({
...dbConfig,
options: `-c search_path=${tenantSlug},public`
});
}
return pools[tenantSlug];
}
*/
-- Cross-tenant queries (admin only)
CREATE VIEW all_tenants_users AS
SELECT
t.name AS tenant_name,
t.slug AS tenant_slug,
u.id,
u.username,
u.email,
u.created_at
FROM tenants t
JOIN users u ON t.id = u.tenant_id;
-- Tenant metrics
SELECT
t.name AS tenant,
COUNT(DISTINCT u.id) AS user_count,
COUNT(DISTINCT o.id) AS order_count,
COALESCE(SUM(o.total), 0) AS total_revenue,
pg_size_pretty(
pg_total_relation_size('users') *
COUNT(DISTINCT u.id)::FLOAT /
NULLIF((SELECT COUNT(*) FROM users), 0)
) AS estimated_storage
FROM tenants t
LEFT JOIN users u ON t.id = u.tenant_id
LEFT JOIN orders o ON t.id = o.tenant_id
GROUP BY t.id, t.name
ORDER BY total_revenue DESC;
-- Tenant data export
COPY (
SELECT *
FROM users
WHERE tenant_id = 123
) TO '/backup/tenant_123_users.csv' WITH CSV HEADER;
-- Tenant data deletion (GDPR compliance)
BEGIN;
DELETE FROM orders WHERE tenant_id = 123;
DELETE FROM users WHERE tenant_id = 123;
DELETE FROM tenants WHERE id = 123;
COMMIT;
-- Or with CASCADE
DELETE FROM tenants WHERE id = 123; -- Cascades to users, orders
-- Tenant isolation verification
CREATE OR REPLACE FUNCTION verify_tenant_isolation()
RETURNS TABLE(table_name TEXT, missing_policy BOOLEAN) AS $$
BEGIN
RETURN QUERY
SELECT
t.tablename::TEXT,
NOT EXISTS (
SELECT 1 FROM pg_policies p
WHERE p.tablename = t.tablename
AND p.policyname LIKE 'tenant_isolation%'
) AS missing_policy
FROM pg_tables t
WHERE t.schemaname = 'public'
AND EXISTS (
SELECT 1 FROM information_schema.columns c
WHERE c.table_name = t.tablename
AND c.column_name = 'tenant_id'
);
END;
$$ LANGUAGE plpgsql;
-- Check for tables missing RLS policies
SELECT * FROM verify_tenant_isolation()
WHERE missing_policy = true;
-- Tenant-aware backup script
/*
#!/bin/bash
for tenant_id in $(psql -t -c "SELECT id FROM tenants"); do
pg_dump --data-only --table=users --table=orders --where="tenant_id=$tenant_id" mydb > "tenant_${tenant_id}_backup.sql"
done
*/
-- Schema-based tenant cleanup
DROP SCHEMA tenant_123 CASCADE;
DELETE FROM tenants WHERE slug = 'tenant_123';
-- Tenant onboarding
CREATE OR REPLACE FUNCTION onboard_tenant(
tenant_name VARCHAR,
tenant_slug VARCHAR,
admin_email VARCHAR
)
RETURNS INT AS $$
DECLARE
new_tenant_id INT;
new_user_id INT;
BEGIN
-- Create tenant
INSERT INTO tenants (name, slug)
VALUES (tenant_name, tenant_slug)
RETURNING id INTO new_tenant_id;
-- Create admin user
INSERT INTO users (tenant_id, username, email, role)
VALUES (new_tenant_id, 'admin', admin_email, 'admin')
RETURNING id INTO new_user_id;
-- Create default data
INSERT INTO settings (tenant_id, key, value)
VALUES
(new_tenant_id, 'theme', 'light'),
(new_tenant_id, 'timezone', 'UTC');
RETURN new_tenant_id;
END;
$$ LANGUAGE plpgsql;
-- Use
SELECT onboard_tenant('New Corp', 'newcorp', 'admin@newcorp.com');
-- Tenant query performance
-- Ensure composite indexes include tenant_id first
CREATE INDEX idx_orders_tenant_user ON orders(tenant_id, user_id);
CREATE INDEX idx_orders_tenant_date ON orders(tenant_id, created_at DESC);
-- Partition by tenant (for very large deployments)
CREATE TABLE orders (
id BIGSERIAL,
tenant_id INT NOT NULL,
user_id INT NOT NULL,
total DECIMAL(10,2),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id, tenant_id)
) PARTITION BY LIST (tenant_id);
CREATE TABLE orders_tenant_1 PARTITION OF orders
FOR VALUES IN (1);
CREATE TABLE orders_tenant_2 PARTITION OF orders
FOR VALUES IN (2);
-- Partition pruning benefits large tenants
