Security by Architecture

Pop quiz: how many places in your codebase check whether a user can access a specific record?

If you have to count — that's the problem. Every endpoint, every query, every service method that touches tenant data... you're relying on every developer remembering to add the check. Every time. In every file.

One missed check and user A sees user B's data. That's not a bug. That's a headline.

The architectural approach

Instead of trusting every developer to remember security checks, make it impossible to forget. Enforce security at the database layer, not the application layer.

Row-Level Security (RLS)

PostgreSQL's Row-Level Security lets you define policies that automatically filter every query. The database itself ensures users only see their own data — even if your application code has a bug.

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

-- Create a policy: users only see their workspace's projects
CREATE POLICY workspace_isolation ON projects
  FOR ALL TO authenticated_users
  USING (
    workspace_id = current_setting('app.current_workspace_id')::uuid
  );

Every query — SELECT, INSERT, UPDATE, DELETE — now automatically includes this filter. There's no way to accidentally query projects from another workspace. The database won't return them.

Session-based context

On every authenticated request, your middleware sets the session context before any query runs:

# In your middleware:
await session.execute(
    text(f"SET app.current_workspace_id = '{workspace_id}'")
)
await session.execute(
    text(f"SET app.current_user_id = '{user_id}'")
)
await session.execute(text("SET ROLE authenticated_users"))

The fail-safe default

What happens when the session variables aren't set? In a good system: nothing. No data is returned. This is the fail-safe default — deny everything when context is unclear.

CASE
    WHEN current_setting('app.current_workspace_id', true) = ''
    THEN false  -- No context? No data.
    ELSE workspace_id = current_setting('app.current_workspace_id')::uuid
END

This is fundamentally different from application-level checks, where the default is usually "return everything unless something stops it." Database-level security inverts that: return nothing unless everything checks out.

Three layers of defense

1. Authentication at middleware — Who are you? (JWT verification)
2. Authorization at policy — What can you do? (Role-based access)
3. Isolation at database — What data can you see? (RLS policies)

Even if layers 1 and 2 have bugs, layer 3 prevents data from leaking across tenants. Defense in depth means any single failure point doesn't compromise the system.

Common pitfalls

• UUID casting on empty strings: PostgreSQL evaluates all parts of AND conditions. If app.current_workspace_id is empty, casting it to UUID crashes. Always use CASE statements to check before casting.
• Forgetting to reset session: After each request, reset the role and session variables. Without this, connection pooling can leak context between requests.
• Testing in superuser mode: Your tests need to actually switch roles and set session variables. Testing as a superuser bypasses RLS entirely — you're not testing what production actually does.