Skip to content
TheChatArchiveThe Chat Archive
Geminigemini-2.5-pro· GoogleHugging Face LMArena Human Preference 140K

Explain how PostgreSQL row level security works. I want to use it in a Remix app with…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with Claudeclaude-3-5-sonnet-20241022’s answer.
  1. User

    Explain how PostgreSQL row level security works. I want to use it in a Remix app with Drizzle ORM to isolate tenants.

  2. Gemini 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:

    1. 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.
    2. 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, or ALL).
      • TO [ROLE]: Specifies which database role(s) the policy applies to. You typically have an app_user role that your application connects with.
      • USING (expression): This is the most important part for reading data. It's a boolean expression. If the expression returns true for 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 to INSERT a new row or UPDATE an existing one, this expression is evaluated for the new data. If it returns true, the operation is allowed.
    3. The Context (Session Variables): current_setting()

      • How does the USING or WITH CHECK expression 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 SET command (e.g., SET app.current_tenant_id = 'abc-123').
      • The policy then accesses this value using the current_setting('app.current_tenant_id') function.

    The Flow of a Request

    1. A user logs into your Remix app.
    2. Your Remix server authenticates the user and gets their tenantId from their session/token.
    3. The user navigates to a page that needs data (a Remix loader runs).
    4. Your server code starts a database transaction.
    5. Inside the transaction, you first run: SET LOCAL app.current_tenant_id = 'the-users-tenant-id';
      • Using SET LOCAL is important because the setting is automatically discarded at the end of the transaction, preventing data from leaking between requests.
    6. You then run your Drizzle query, like db.select().from(projects).
    7. PostgreSQL receives the query. It sees that RLS is enabled on the projects table.
    8. It checks the RLS policy. The policy's USING clause might be tenant_id = current_setting('app.current_tenant_id')::uuid.
    9. PostgreSQL evaluates this condition for every row in the projects table, effectively adding a WHERE tenant_id = 'the-users-tenant-id' clause automatically and securely at the database level.
    10. Only the rows belonging to that tenant are returned to your application.
    11. The transaction commits, the app.current_tenant_id setting 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 you SELECT projects, you will only see rows where the tenant_id column matches the app.current_tenant_id we set.
    • WITH CHECK (...): When you INSERT or UPDATE a project, the database enforces that the tenant_id of the new/updated row must match app.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.ts

    import { 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_id is to create a helper function that wraps your database calls in a transaction. This ensures the SET LOCAL command and your query run together and the setting is automatically cleaned up.

    app/db/drizzle.server.ts

    import { 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.tsx

    import { 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.tsx

    import { 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 component
    

    Why is This Approach So Powerful?

    1. 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.
    2. Cleaner Application Code: Your data-fetching logic becomes much simpler. You don't have to manually pass tenantId to every database call and add a WHERE clause. You set the context once, and then you can just query your data.
    3. 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.
    4. 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_user role.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.