Stop Writing Authorization Middleware: Why Your Database Should Be the One Enforcing Your Permissions

If you’ve ever pushed a feature only to realize an hour later that a user could access someone else's private data by simply changing an ID in a URL, you’ve felt the specific, gut-wrenching anxiety of a Broken Object Level Authorization (BOLA) vulnerability. It’s the top risk on the OWASP API Security Top 10, and despite our best efforts with middleware, we keep making the same mistakes.

We’ve been taught to treat the database as a "dumb" bucket and the application code as the "smart" layer. We write layers of middleware, decorators, and policy checkers to ensure that User A can’t see User B’s invoices. But this approach is fundamentally fragile. It relies on every single developer, on every single route, for every single new feature, remembering to apply the correct check.

It’s time to move the security perimeter closer to the data. By leveraging Row-Level Security (RLS) directly in your database, you can make it physically impossible for your application to return data that the current user isn't authorized to see.

The Swiss Cheese Model of Middleware

Application-level authorization is like a series of gates. You have your requireAuth middleware, then perhaps a checkOwnership wrapper, and finally a database query.

// A typical Express route prone to human error
app.get('/api/invoices/:id', async (req, res) => {
  const invoice = await db.invoices.findById(req.params.id);
  
  // What if the developer forgets these two lines?
  if (invoice.userId !== req.user.id) {
    return res.status(403).send('Unauthorized');
  }

  res.json(invoice);
});

In a codebase with hundreds of routes, someone will eventually forget those lines. Or they’ll write a search endpoint that returns a list of objects and forget to add the .where('user_id', req.user.id) clause to the query.

This is the "Swiss Cheese" problem: security depends on the perfect alignment of every layer of your application logic. If one layer has a hole, the data leaks. When you move authorization to the database, you aren't just adding a gate; you're changing the physics of the data layer itself.

What is Row-Level Security (RLS)?

Row-Level Security is a feature in databases like PostgreSQL that allows you to define policies on a table. These policies act as a persistent filter. When a query is executed, the database engine automatically appends the policy's logic to the WHERE clause.

If a user tries to SELECT * FROM invoices, and an RLS policy says they can only see rows where user_id matches their own, the database simply pretends the other rows don't exist. The application doesn't have to do anything.

Setting Up RLS in PostgreSQL

Let's look at a concrete example. Suppose we have a multi-tenant application with users and documents.

1. The Schema

First, we define our table and enable RLS. By default, even if you enable RLS, the owner of the table (usually the migration user) can see everything. We want to restrict access for the "application" user.

CREATE TABLE documents (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT,
  owner_id UUID NOT NULL
);

-- Crucial: You must explicitly enable RLS on the table
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

2. Defining a Policy

Now we need a way for the database to know who the "current user" is. PostgreSQL doesn't inherently know about your application's JWTs or session cookies. We usually handle this by setting a session variable.

-- Create a policy that restricts access based on a session variable
CREATE POLICY document_owner_policy ON documents
FOR ALL -- Applies to SELECT, INSERT, UPDATE, DELETE
TO app_user -- The role your application uses to connect
USING (owner_id = (current_setting('app.current_user_id'))::uuid);

The USING clause is the heart of the policy. It’s a boolean expression. If it returns true, the row is visible/modifiable. If false, it's as if the row isn't there.

Integrating with Your Application Layer

This is where developers often get stuck. How do you pass the user_id from your Node.js or Python backend into the Postgres session?

The most common pattern is to wrap your database calls in a transaction and set a local variable that lasts only for the duration of that transaction. This ensures that one request's user ID doesn't leak into another request handled by the same connection pool.

Here is how you might do it in a Node.js environment using a library like pg:

async function getDocuments(userId) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    
    // Set the session variable for this transaction only
    await client.query(`SET LOCAL app.current_user_id = '${userId}'`);
    
    // The RLS policy is now automatically applied
    const res = await client.query('SELECT * FROM documents');
    
    await client.query('COMMIT');
    return res.rows;
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
}

Now, even if you write SELECT * FROM documents without a WHERE clause, the database will only return documents belonging to that userId. You have effectively eliminated the possibility of a developer "forgetting" the authorization check.

Handling More Complex Permissions

Ownership is simple, but what about roles? What if a manager needs to see their team's documents?

RLS policies can be as complex as you need. They can include subqueries and joins. Let’s say we have a memberships table that defines who belongs to which organization.

CREATE TABLE memberships (
  user_id UUID,
  org_id UUID,
  role TEXT -- 'admin', 'member'
);

ALTER TABLE documents ADD COLUMN org_id UUID;

CREATE POLICY org_access_policy ON documents
FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM memberships
    WHERE memberships.org_id = documents.org_id
    AND memberships.user_id = (current_setting('app.current_user_id'))::uuid
  )
);

This policy is incredibly powerful. It ensures that any query against documents is automatically scoped to the organizations the user is actually a part of.

The Performance "Tax"

A common objection to RLS is performance. If the database is running a subquery for every row it evaluates, won't it crawl to a halt?

In my experience, the overhead is negligible for most applications, provided you index your foreign keys. Postgres is very good at optimizing these policies. In many cases, it’s actually *faster* than doing the same checks in your application code because the database engine can optimize the execution plan globally.

However, there is a "gotcha" with subqueries in policies. If you use a subquery like the EXISTS example above, it might be executed for every row. To optimize this, you can use functional indexes or security definer functions that cache the user's permissions in a temporary table or a session-level variable.

The "Superuser" Trap

When testing RLS, you might find it’s not working—specifically, that you can still see everything.

This usually happens because you are connecting to the database as the table owner or a superuser. RLS is bypassed by default for table owners. This is a safety feature to prevent you from accidentally locking yourself out of your own data.

In production, your application should connect via a dedicated role (e.g., web_anon or authenticated_user) that does not own the tables.

-- Create a restricted role
CREATE ROLE app_user WITH LOGIN PASSWORD 'very_secure_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

Why This Matters for Modern Architectures

If you are building a serverless application or using a tool like PostgREST or Supabase, RLS isn't just a nice-to-have; it's the foundation of the entire architecture.

In these setups, the frontend often communicates directly with the database via a thin API layer. Without RLS, you would have zero security. By moving the logic to the database, you can safely expose your database schema to the internet, knowing that the data is protected by the engine itself.

Debugging RLS Policies

One downside of RLS is that it can be harder to debug. When a query returns zero rows, is it because the data doesn't exist, or because the policy blocked it?

I’ve found that the best way to debug is to use the EXPLAIN command. Postgres will show you the rewritten query, including the conditions added by the RLS policies.

SET app.current_user_id = 'some-uuid';
EXPLAIN SELECT * FROM documents;

You'll see something like Filter: (owner_id = 'some-uuid'::uuid) in the output, confirming that the policy is active and what it’s doing.

Moving Toward a "Secure by Default" Mindset

Moving authorization to the database is a shift in mindset. It’s about moving away from "I will check permissions here" to "The system will not allow unauthorized access anywhere."

I'm not suggesting you delete all your application-layer checks. Defense in depth is still valuable. Validating inputs and handling high-level business logic in your code is still necessary. But the final word on who can see what data should live as close to the bytes on the disk as possible.

Stop writing brittle middleware that breaks the moment someone forgets a line of code. Enable RLS, write your policies once, and sleep better knowing your database is doing the heavy lifting for you.