Row-Level Security (RLS)¶

Doable enforces Row-Level Security at the PostgreSQL layer for all sensitive multi-tenant tables. This means the database itself, not just application code, refuses to return rows the requesting user has no right to see.

What is RLS?¶

PostgreSQL Row-Level Security lets you attach USING and WITH CHECK policies directly to a table. Every SELECT, INSERT, UPDATE, and DELETE automatically passes through those policies before any rows are touched. There is no way to bypass them from application code without switching to a privileged database role.

Doable's policies follow a single pattern:

If no user context is set (background jobs, migrations, WebSocket service), all rows are visible: existing internal operations are unaffected.
If a user context is set, only the rows that user is allowed to see are returned. Rows that fail the policy are silently filtered, not errored. A missing row looks identical to a non-existent one.

Why?¶

Defense in depth. Application-layer WHERE workspace_id = $1 checks are correct today, but they are one forgotten clause away from leaking data. RLS is a second, independent enforcement point at the database level: even if a route handler omits a filter, the database policy blocks the leak automatically.

The threat this directly addresses: cross-tenant data exposure. Without RLS, a bug that accidentally queries the wrong workspace ID could return another tenant's projects, integrations, or AI messages. With RLS active, the query returns zero rows regardless of the bug.

What's protected¶

Table category	Tables
Users	`users` (see own row + workspace co-members)
Projects	`projects`, `project_api_keys`, `ai_sessions`, `ai_messages`
Workspaces	`workspaces`, `workspace_members`
Integrations	`integration_connections`
GitHub	`github_connections`
AI providers	`ai_providers`, `credit_balances`
Auth	`refresh_tokens`

Policies were introduced progressively across migrations 045, 071, 076, 077, and 078. Each migration extends coverage while preserving the permissive-when-unset fallback for internal services.

How it works in Doable¶

The session variable¶

All policies rely on a single PostgreSQL session variable:

doable.current_user_id   -- UUID of the authenticated user, or empty or unset

A helper function reads it safely:

CREATE OR REPLACE FUNCTION doable_current_user_id() RETURNS uuid AS $$
BEGIN
  RETURN NULLIF(current_setting('doable.current_user_id', true), '')::uuid;
EXCEPTION WHEN OTHERS THEN
  RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;

When this returns NULL, every policy falls through to "allow all", the safe default for jobs and services that don't have a user identity.

Setting the context per request¶

The authMiddlewareWithRls middleware (in services/api/src/middleware/rls.ts) handles the context injection:

authMiddleware verifies the JWT and extracts the user UUID.
The middleware opens a PostgreSQL transaction with sql.begin().
Inside that transaction it runs:
```
SET LOCAL "doable.current_user_id" = '<uuid>';
```
SET LOCAL scopes the variable to the current transaction. It is automatically cleared when the transaction ends, so there is no risk of leaking one user's context into a subsequent request on the same connection.
The transaction object is stashed in an AsyncLocalStorage context (txAls). The sql proxy in db/index.ts dispatches every subsequent query in the request through that transaction transparently; route handlers need no changes.

The policy shape¶

A typical permissive policy looks like this:

-- projects: visible to workspace members
CREATE POLICY projects_workspace_member ON projects
  USING (
    doable_current_user_id() IS NULL        -- internal / background
    OR visibility = 'public'                -- public projects
    OR EXISTS (
      SELECT 1 FROM workspace_members wm
      WHERE wm.workspace_id = projects.workspace_id
        AND wm.user_id = doable_current_user_id()
    )
  )
  WITH CHECK (
    doable_current_user_id() IS NULL
    OR EXISTS (
      SELECT 1 FROM workspace_members wm
      WHERE wm.workspace_id = projects.workspace_id
        AND wm.user_id = doable_current_user_id()
    )
  );

USING controls read visibility; WITH CHECK controls write eligibility. Both gates are present on every table.

SECURITY DEFINER helpers¶

Some policies need to look up workspace membership to decide visibility, but querying workspace_members from inside a policy on workspace_members would cause infinite recursion. Doable resolves this with SECURITY DEFINER helper functions that bypass RLS for their own body:

CREATE OR REPLACE FUNCTION doable_user_workspace_ids(uid uuid) RETURNS SETOF uuid AS $$
  SELECT workspace_id FROM workspace_members WHERE user_id = uid;
$$ LANGUAGE sql STABLE SECURITY DEFINER;

These helpers are the only code that reads the membership table without RLS enforcement, and they are read-only functions with no side effects.

Live since¶

RLS was activated in production with deploy 64afcf7 on 2026-05-11. The initial set of tables (users, projects, ai_sessions, ai_messages, integration_connections, github_connections, refresh_tokens) shipped in migration 045. Phase 2 (migration 071) extended coverage to workspace_members, ai_providers, credit_balances, and project_api_keys. Migrations 076 to 078 further tuned the users visibility policy and added workspace-level policies.

Limitations and admin bypass¶

Admin / platform routes use a privileged database role (or operate without a user context set) and are therefore not filtered by RLS. They rely on explicit application-layer authorization checks instead. The same checks exist on those routes regardless of RLS.
Streaming endpoints (chat, AI, SSE) are explicitly excluded from authMiddlewareWithRls. A streaming response can hold a connection for minutes; keeping a transaction open that long would exhaust the connection pool. Those routes still enforce workspace membership at the application layer.
WebSocket service runs without a user context (doable.current_user_id unset), so it uses the permissive fallback. The WS service performs its own session-level authorization before processing any message.
RLS policies are additive. They narrow what the application can see, but they cannot grant access beyond what the application's own role (doable) is already allowed by standard PostgreSQL privileges.