PostgreSQL Row-Level Security: Database-Level Access Control Explained

You're building a SaaS application. Multiple customers use your platform, each with their own data. You need to make absolutely sure Customer A never sees Customer B's data. Where do you enforce this rule?

Most developers start by adding filters in their application code. Every query gets a WHERE tenant_id = ? clause. It works, but it's fragile. Miss one filter in one endpoint, and you've got a data breach.

PostgreSQL Row-Level Security (RLS) offers a better approach: enforce access control directly in the database.

What is Row-Level Security?

Row-Level Security is a PostgreSQL feature that lets you define policies controlling which rows users can see, insert, update, or delete. These policies run automatically on every query, regardless of how the query originates.

Think of it as adding an invisible WHERE clause to every query based on who's asking. The difference? You define it once at the database level, and it applies everywhere.

-- Enable RLS on a table
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Create a policy
CREATE POLICY tenant_isolation ON documents
  FOR ALL
  USING (tenant_id = current_setting('app.current_tenant')::uuid);

Once this policy is active, users can only access rows where tenant_id matches their tenant. No application code needed.

Why RLS Matters

Application-level filtering has a fundamental problem: it's optional. A developer can forget to add the filter. A new junior dev might not know about the requirement. One missed WHERE clause in a reporting endpoint, and you're exposing data across tenants.

RLS makes access control mandatory. The database enforces it automatically. Even if your application code has bugs, the database acts as a security layer that prevents unauthorized access.

This is defense in depth done right.

Benefits Over Other Approaches

Compared to Application-Level Filtering:

Application filters require discipline. Every query needs the right WHERE clause. Every ORM call needs the right scope. RLS enforces this automatically.

You also get protection against SQL injection. Even if an attacker crafts a malicious query that bypasses your application logic, RLS policies still apply.

Compared to Other Databases:

MySQL doesn't have native row-level security. You'd need to implement it through views or application logic. MongoDB has field-level and document-level security, but it's not as mature or flexible as PostgreSQL's RLS.

Oracle and SQL Server have similar features, but PostgreSQL's implementation is more straightforward and better integrated with roles and permissions.

Real-World Use Cases

Multi-Tenant SaaS Applications:

The classic use case. Each customer (tenant) sees only their data. RLS enforces tenant isolation at the database level.

CREATE POLICY tenant_policy ON orders
  FOR ALL
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Your application sets app.tenant_id once per request, and all queries automatically filter by tenant.

Role-Based Access Control:

Users with different roles see different data. Managers see all team records, while employees see only their own.

CREATE POLICY manager_policy ON employee_records
  FOR SELECT
  USING (
    department_id = current_setting('app.user_department')::int
    AND (
      current_setting('app.user_role') = 'manager'
      OR employee_id = current_setting('app.user_id')::int
    )
  );

Data Privacy and Compliance:

For applications handling sensitive data (healthcare, finance), RLS helps enforce data access rules required by regulations like GDPR or HIPAA.

Development and Testing Environments:

Use RLS to give developers access to realistic test data while automatically filtering out sensitive information they shouldn't see.

When to Choose PostgreSQL with RLS

Choose RLS when:

You're building multi-tenant applications where data isolation is critical. The consequences of showing the wrong data to the wrong user are severe.

You want defense in depth. Application-level security is your first layer, but you want the database as a backup.

You need to audit and prove your security model. RLS policies are explicit, versioned in your schema, and easier to review than scattered application code.

Your team is growing, and you want to reduce the chance of security bugs from new developers.

Consider alternatives when:

Your access control rules are extremely complex and change frequently. RLS policies are powerful but can become hard to manage if you have dozens of interconnected rules.

You're using an ORM that doesn't play well with PostgreSQL-specific features. Some ORMs might fight against RLS or make it harder to set session variables.

Performance is absolutely critical, and you've profiled that RLS policies are causing issues. This is rare, but possible with very complex policies on very large tables.

Practical Implementation

Here's a realistic example showing RLS in a Node.js application:

// Set the tenant context at the start of each request
app.use(async (req, res, next) => {
  const tenantId = req.user.tenantId;
  
  await pool.query(
    'SET LOCAL app.tenant_id = $1',
    [tenantId]
  );
  
  next();
});

// Now all queries automatically filter by tenant
app.get('/api/documents', async (req, res) => {
  // This query only returns documents for the current tenant
  // No WHERE clause needed - RLS handles it
  const result = await pool.query('SELECT * FROM documents');
  res.json(result.rows);
});

The database schema:

CREATE TABLE documents (
  id uuid PRIMARY KEY,
  tenant_id uuid NOT NULL,
  title text,
  content text,
  created_at timestamptz DEFAULT now()
);

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON documents
  FOR ALL
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Exempt your admin role from RLS if needed
ALTER TABLE documents FORCE ROW LEVEL SECURITY;

Important Considerations

Superusers bypass RLS by default. If your application connects as a superuser, RLS won't work. Create a dedicated application user with appropriate permissions.

Use FORCE ROW LEVEL SECURITY if you want RLS to apply even to table owners.

Session variables persist. If you're using connection pooling, make sure to set session variables per transaction, not per connection. Use SET LOCAL instead of SET.

Test your policies thoroughly. Write tests that verify users can't access data they shouldn't. It's easy to write a policy with subtle bugs.

Performance: RLS adds overhead. For most applications, it's negligible. But if you have complex policies on large tables, profile and optimize as needed.

Conclusion

PostgreSQL Row-Level Security moves access control from your application code into the database where it belongs. It's not a replacement for application-level security, but a powerful additional layer.

For multi-tenant applications, RLS is almost a no-brainer. It prevents entire classes of security bugs and makes your codebase simpler by removing scattered filter logic.

The best part? Once you set it up, it just works. Your team can focus on building features instead of worrying whether they remembered to add WHERE tenant_id = ? to every single query.

If you're using PostgreSQL and handling sensitive data with complex access rules, RLS deserves a serious look.