Menu

1 / 2

Multi-tenant PostgreSQL: row-level security vs schema-per-tenant & when to use which

DEV Community: sass·Jayanth·6 months ago
#daYCrRj9
#dev#tenant#schema#enter#article
Reading 0:00
15s threshold

If you're building a multi-tenant SaaS, this is the first real architecture

decision that will haunt you if you get it wrong.

I've implemented both approaches in production. Here's the honest trade-off.

Option A: Shared schema with row-level security (RLS)

Every tenant's data lives in the same tables. A tenant_id column on every

row. PostgreSQL RLS policies enforce that queries only ever return rows

belonging to the current tenant.

-- Enable RLS on the table ALTER TABLE orders ENABLE ROW LEVEL SECURITY ;

-- Policy: users only see their tenant's rows CREATE POLICY tenant_isolation ON orders USING ( tenant_id = current_setting ( 'app.current_tenant_id' ):: uuid );

Enter fullscreen mode

Exit fullscreen mode

# Set the tenant context before every query async def set_tenant ( conn , tenant_id : str ): await conn . execute ( " SELECT set_config( ' app.current_tenant_id ' , $1, true) " , tenant_id )

Enter fullscreen mode

Exit fullscreen mode

Works well when: You have many small tenants.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.