Row-Level Security (RLS)Supported

Row-Level Security (RLS) is a PostgreSQL feature that restricts which rows users can access based on their session characteristics. It's the recommended approach for multi-tenant applications, providing security at the database level without relying solely on application logic.

Enabling Row-Level Security#

Use enableRlsDdl to enable RLS on a table. This is the first step before creating any policies.

import { postgres } from '@zmdb/postgres';
import { enableRlsDdl, UnsupportedFeatureError } from '@zmdb/sql/schema-objects';

const ddl = enableRlsDdl('orders', postgres);
console.log(ddl);

ALTER TABLE "orders" ENABLE ROW LEVEL SECURITY

Creating RLS Policies#

Use createPolicyDdl to define a policy. The policy specifies which rows are visible based on a USING expression.

import { postgres } from '@zmdb/postgres';
import { createPolicyDdl, type RlsPolicy } from '@zmdb/sql/schema-objects';

const policy: RlsPolicy = {
  name: 'users_can_see_own_orders',
  table: 'orders',
  using: 'user_id = current_user_id()',
  command: 'SELECT',
};

const ddl = createPolicyDdl(policy, postgres);
console.log(ddl);

CREATE POLICY "users_can_see_own_orders" ON "orders" FOR SELECT USING (user_id = current_user_id())

Policy Commands#

Policies can be scoped to specific SQL commands: SELECT, INSERT, UPDATE, DELETE, or ALL (default).

// Policy for all operations
const allPolicy = {
  name: 'tenant_isolation_all',
  table: 'documents',
  using: 'tenant_id = current_tenant_id()',
  command: 'ALL',
};

const selectOnlyPolicy = {
  name: 'read_only_access',
  table: 'reports',
  using: 'true', // everyone can read
  command: 'SELECT',
};

CREATE POLICY "tenant_isolation_all" ON "documents" FOR ALL USING (tenant_id = current_tenant_id())
CREATE POLICY "read_only_access" ON "reports" FOR SELECT USING (true)

Multi-Tenant Isolation#

The most common use case for RLS is multi-tenant data isolation. Each tenant's data is protected at the database level.

// Complete RLS setup for a multi-tenant table
const policies = [
  // Enable RLS on the table
  enableRlsDdl('tenants', 'postgres'),

  // Policy for SELECT - users can only see their tenant
  createPolicyDdl(
    {
      name: 'tenant_select',
      table: 'tenants',
      using: "id = current_setting('app.tenant_id', true)::uuid",
      command: 'SELECT',
    },
    'postgres',
  ),

  // Policy for INSERT - can only insert for their tenant
  createPolicyDdl(
    {
      name: 'tenant_insert',
      table: 'tenants',
      using: "id = current_setting('app.tenant_id', true)::uuid",
      command: 'INSERT',
    },
    'postgres',
  ),

  // Policy for UPDATE - can only update their tenant
  createPolicyDdl(
    {
      name: 'tenant_update',
      table: 'tenants',
      using: "id = current_setting('app.tenant_id', true)::uuid",
      command: 'UPDATE',
    },
    'postgres',
  ),

  // Policy for DELETE - can only delete their tenant
  createPolicyDdl(
    {
      name: 'tenant_delete',
      table: 'tenants',
      using: "id = current_setting('app.tenant_id', true)::uuid",
      command: 'DELETE',
    },
    'postgres',
  ),
];

console.log(policies.join(';\n'));

ALTER TABLE "tenants" ENABLE ROW LEVEL SECURITY;
CREATE POLICY "tenant_select" ON "tenants" FOR SELECT USING (id = current_setting('app.tenant_id', true)::uuid);
CREATE POLICY "tenant_insert" ON "tenants" FOR INSERT USING (id = current_setting('app.tenant_id', true)::uuid);
CREATE POLICY "tenant_update" ON "tenants" FOR UPDATE USING (id = current_setting('app.tenant_id', true)::uuid);
CREATE POLICY "tenant_delete" ON "tenants" FOR DELETE USING (id = current_setting('app.tenant_id', true)::uuid)

Bypass for Service Accounts#

Some operations (like batch imports or admin tools) may need to bypass RLS. Use FORCE to make policies mandatory or bypass them with BYPASS.

// Admin role bypass (run as superuser or owner)
const bypassPolicy = {
  name: 'admin_bypass',
  table: 'orders',
  using: "current_user = 'admin'",
  command: 'ALL',
};

// Note: BYPASS requires superuser or BYPASSRLS attribute
// This is typically handled at the role level, not in the policy

CREATE POLICY "admin_bypass" ON "orders" FOR ALL USING (current_user = 'admin')

Disabling RLS#

If you need to temporarily disable RLS (for migrations, etc.), use DISABLE ROW LEVEL SECURITY.

const disableRlsDdl = `ALTER TABLE "orders" DISABLE ROW LEVEL SECURITY`;

ALTER TABLE "orders" DISABLE ROW LEVEL SECURITY

Related#

• Schemas & Namespaces — organizing RLS-protected tables
• Indexes & Constraints — performance considerations for RLS
• Relations — relationship handling with RLS enabled