Explain how PostgreSQL row level security works. I want to use it in a Remix app with…
- User
Explain how PostgreSQL row level security works. I want to use it in a Remix app with Drizzle ORM to isolate tenants.
Assistant
Of course! This is an excellent use case for PostgreSQL's Row Level Security (RLS). It's a powerful feature that moves your data access logic from your application code into the database itself, making your multi-tenant application more secure and less prone to bugs.
Let's break down how it works, from the database concepts to the practical implementation in a Remix app with Drizzle.
Part 1: How PostgreSQL Row Level Security (RLS) Works
Think of RLS as a security guard standing in front of your data tables. By default, this guard lets no one see any rows. You have to give the guard a set of rules (called Policies) that tell them which users are allowed to see or modify which specific rows.
RLS is built on three core components:
-
The Master Switch:
ALTER TABLE ... ENABLE ROW LEVEL SECURITY- This command "activates" the security guard for a specific table.
- Crucially, once enabled, the default behavior is DENY. If there are no policies that grant access, nobody (except the table owner and superusers) can see or modify any rows in that table.
-
The Rules (Policies):
CREATE POLICY ...- A policy is a rule attached to a table that defines who can access which rows. A policy has several parts:
FOR [COMMAND]: Specifies which SQL command the policy applies to (e.g.,SELECT,INSERT,UPDATE,DELETE, orALL).TO [ROLE]: Specifies which database role(s) the policy applies to. You typically have anapp_userrole that your application connects with.USING (expression): This is the most important part for reading data. It's a boolean expression. If the expression returnstruefor a given row, the user can see it (SELECT) or modify it (UPDATE/DELETE).WITH CHECK (expression): This is for writing data. When a user tries toINSERTa new row orUPDATEan existing one, this expression is evaluated for the new data. If it returnstrue, the operation is allowed.
-
The Context (Session Variables):
current_setting()- How does the
USINGorWITH CHECKexpression know who the current user is? The policy is in the database, but the user is in your Remix app. - The bridge is a session variable. Your application, right before running a query, tells the database, "For this transaction, the current tenant ID is 'abc-123'".
- You do this with the
SETcommand (e.g.,SET app.current_tenant_id = 'abc-123'). - The policy then accesses this value using the
current_setting('app.current_tenant_id')function.
- How does the
The Flow of a Request
- A user logs into your Remix app.
- Your Remix server authenticates the user and gets their
tenantIdfrom their session/token. - The user navigates to a page that needs data (a Remix
loaderruns). - Your server code starts a database transaction.
- Inside the transaction, you first run:
SET LOCAL app.current_tenant_id = 'the-users-tenant-id';- Using
SET LOCALis important because the setting is automatically discarded at the end of the transaction, preventing data from leaking between requests.
- Using
- You then run your Drizzle query, like
db.select().from(projects). - PostgreSQL receives the query. It sees that RLS is enabled on the
projectstable. - It checks the RLS policy. The policy's
USINGclause might betenant_id = current_setting('app.current_tenant_id')::uuid. - PostgreSQL evaluates this condition for every row in the
projectstable, effectively adding aWHERE tenant_id = 'the-users-tenant-id'clause automatically and securely at the database level. - Only the rows belonging to that tenant are returned to your application.
- The transaction commits, the
app.current_tenant_idsetting is cleared, and the request is finished.
Part 2: Implementation with Remix and Drizzle
Let's build a simple multi-tenant app where tenants have
projects.Step 1: Database Setup (SQL)
First, let's set up our database roles and tables. We'll create a dedicated role for our application to use, which has limited permissions.
-- 1. Create a role that our Remix application will use to connect to the DB. -- NOLOGIN means it can't be used to log in directly. CREATE ROLE app_user NOLOGIN; -- NOTE: The user/role you connect with in your app's connection string -- will need to be granted this role. -- For example, if you connect as 'my_remix_app', you would run: -- GRANT app_user TO my_remix_app; -- 2. Create the tenants and projects tables CREATE TABLE tenants ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name TEXT NOT NULL ); CREATE TABLE projects ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), name TEXT NOT NULL, tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE ); -- 3. Grant basic permissions to our application role -- The app can't do anything without this. GRANT SELECT, INSERT, UPDATE, DELETE ON tenants, projects TO app_user; -- 4. Enable Row Level Security on the projects table ALTER TABLE projects ENABLE ROW LEVEL SECURITY; -- 5. Create the policy to isolate tenants -- This is the core of our multi-tenant security. CREATE POLICY tenant_isolation_policy ON projects FOR ALL -- Applies to SELECT, INSERT, UPDATE, DELETE TO app_user -- Only for our application role USING (tenant_id = current_setting('app.current_tenant_id')::uuid) WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);What this policy does:
USING (...): When youSELECTprojects, you will only see rows where thetenant_idcolumn matches theapp.current_tenant_idwe set.WITH CHECK (...): When youINSERTorUPDATEa project, the database enforces that thetenant_idof the new/updated row must matchapp.current_tenant_id. You can't accidentally create a project for another tenant.
Step 2: Drizzle Schema
Now, define your schemas in your Remix project.
app/db/schema.tsimport { pgTable, text, uuid } from 'drizzle-orm/pg-core'; export const tenants = pgTable('tenants', { id: uuid('id').primaryKey().defaultRandom(), name: text('name').notNull(), }); export const projects = pgTable('projects', { id: uuid('id').primaryKey().defaultRandom(), name: text('name').notNull(), tenantId: uuid('tenant_id') .notNull() .references(() => tenants.id, { onDelete: 'cascade' }), });Step 3: Setting the RLS Context in Remix
The best way to manage setting the
tenant_idis to create a helper function that wraps your database calls in a transaction. This ensures theSET LOCALcommand and your query run together and the setting is automatically cleaned up.app/db/drizzle.server.tsimport { drizzle } from 'drizzle-orm/node-postgres'; import { Pool } from 'pg'; import * as schema from './schema'; import { sql } from 'drizzle-orm'; const pool = new Pool({ connectionString: process.env.DATABASE_URL, }); export const db = drizzle(pool, { schema }); // This is our RLS-aware transaction helper! export async function dbWithTenant<T>( tenantId: string, callback: (tx: typeof db) => Promise<T> ) { // Drizzle's transaction function gives us a transaction-specific `tx` object return db.transaction(async (tx) => { // 1. Set the tenant_id for the current transaction await tx.execute(sql`SET LOCAL app.current_tenant_id = ${tenantId}`); // 2. Run the user's database logic with the `tx` object const result = await callback(tx); // 3. The transaction commits, and `app.current_tenant_id` is automatically cleared. return result; }); }Step 4: Using the Helper in Remix Loaders and Actions
Now, in your Remix routes, you can use this helper to ensure all database access is tenant-isolated.
Example
loader(Reading data):app/routes/projects.tsximport { json, LoaderFunctionArgs } from '@remix-run/node'; import { useLoaderData } from '@remix-run/react'; import { dbWithTenant } from '~/db/drizzle.server'; import { projects } from '~/db/schema'; import { getTenantIdFromSession } from '~/session.server'; // Your auth logic export async function loader({ request }: LoaderFunctionArgs) { // Assume you have a function to get the tenantId from the user's session const tenantId = await getTenantIdFromSession(request); // Use our helper to fetch projects. // RLS is handled automatically! const projectsForTenant = await dbWithTenant(tenantId, (tx) => { // Notice we DON'T need a .where({ tenantId: ... }) clause here! return tx.select().from(projects); }); return json({ projectsForTenant }); } export default function ProjectsRoute() { const { projectsForTenant } = useLoaderData<typeof loader>(); // ... render projects }Example
action(Writing data):app/routes/projects.new.tsximport { ActionFunctionArgs, json, redirect } from '@remix-run/node'; import { dbWithTenant } from '~/db/drizzle.server'; import { projects } from '~/db/schema'; import { getTenantIdFromSession } from '~/session.server'; // Your auth logic export async function action({ request }: ActionFunctionArgs) { const tenantId = await getTenantIdFromSession(request); const formData = await request.formData(); const newProjectName = formData.get('name') as string; await dbWithTenant(tenantId, (tx) => { return tx.insert(projects).values({ name: newProjectName, // We still provide the tenantId here. The `WITH CHECK` // in our policy will FAIL this insert if we try to provide // a tenantId that doesn't match the one in our session variable. // This is our safety net. tenantId: tenantId, }); }); return redirect('/projects'); } // ... form componentWhy is This Approach So Powerful?
- Bulletproof Security: The security logic lives in the database. A developer can't forget a
where('tenant_id', ...)clause in their application code and accidentally leak data. The database enforces the rule on every single query. - Cleaner Application Code: Your data-fetching logic becomes much simpler. You don't have to manually pass
tenantIdto every database call and add aWHEREclause. You set the context once, and then you can just query your data. - Centralized Logic: Your tenancy rule is defined in one place (the SQL policy). If you need to change it, you change it there, and it applies everywhere instantly.
- Future-Proof: If you later add a reporting tool, an admin interface, or another service that connects to the same database, they will all automatically be subject to the same row-level security rules, provided they use the same
app_userrole.
-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.