Intermediate LevelPostgreSQL
How does Row-Level Security (RLS) enforce multi-tenant authorization policies?
❓ Interview Prompt: Explain Row-Level Security (RLS), `ALTER TABLE ... ENABLE ROW LEVEL SECURITY`, `CREATE POLICY`, `USING` clause, and tenant isolation.
Row-Level Security (RLS) restricts which rows in a table are visible or modifiable based on the database user role or session variables:
Multi-Tenant Pattern:
1
Enable RLS: 'ALTER TABLE orders ENABLE ROW LEVEL SECURITY;'
2
Create Policy: 'CREATE POLICY tenant_isolation_policy ON orders USING (tenant_id = current_setting('app.current_tenant')::INT);'
•
Automatically filters all queries to the tenant ID set in application session variables!
Code Implementation Example
sql
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY order_tenant_policy ON orders
FOR ALL
USING (tenant_id = NULLIF(current_setting('app.current_tenant', true), '')::INT);
-- In application connection session:
SET LOCAL app.current_tenant = '42';
SELECT * FROM orders; -- Automatically returns ONLY tenant 42 rows!💡Configuring Row-Level Security policies for multi-tenant isolation.
🎯 Interview Key Takeaway
Use Row-Level Security (RLS) to enforce multi-tenant authorization policies at the database layer.
#RLS#Row-Level Security#Multi-Tenancy#Security#Intermediate
Back to all PostgreSQL Questions→