@app.get("/projects")
async def list_projects(user: User, db: Session = Depends(get_db)):
return db.query(Project).filter(Project.user_id == user.id).all()

Permissions drift is real: scattered checks, forgotten filters, and data leaks. PostgreSQL’s Row Level Security (RLS) flips the script: pushing verification into the database for stronger safety, but with trade-offs in debugging and performance.

Permissions are among the most complex parts of building any application. Access control is a fundamental system design requirement, and most implementations begin by enforcing checks at the application layer.

For instance:

@app.get("/projects")
async def list_projects(user: User, db: Session = Depends(get_db)):
return db.query(Project).filter(Project.user_id == user.id).all()

Queries quickly accumulate explicit WHERE clauses, middleware enforces constraints, and guard logic is scattered throughout the codebase. While functional, this approach is prone to inconsistencies, omissions, and vulnerabilities.

Over time, no one is entirely sure where the real source of truth resides. Confusion emerges, permissions drift, and a single forgotten clause can lead to data leaking into the wrong hands.

Fortunately, there are tools to address this uncertainty. One such tool is PostgreSQL’s Row Level Security (RLS), which provides a first-class mechanism for enforcing permissions. In the context of a FastAPI service, RLS reframes the broader question:

Where should permission verification actually live: in the application, or in the database?

Why Push Permissions into the Database?

Pushing permissions into the database may initially feel counterintuitive. Application frameworks like FastAPI already offer middleware, dependency injection, and hooks for checks. Why involve PostgreSQL?

The answer rests on two pillars:

Safety: With RLS, the database itself refuses to return unauthorized rows, regardless of how inconsistent or error-prone the application code might be. Forgotten filters are no longer a risk.

Consistency: Policies are defined in the database schema and are not scattered across application code. Every query, whether via ORM, raw SQL, or reporting tools, is subject to the same guardrails. This effectively transforms PostgreSQL from a storage layer into an enforcer of trust.

Traditionally, application code performs the checks, as seen in the previous code snippet:

db.query(Project).filter(Project.user_id == user.id).all()

That means:

Every endpoint or service must remember to apply filters.

Business and security logic become intertwined.

One oversight can bypass access rules entirely. RLS flips this convention. The application simply sets a session parameter (equivalent to SET LOCAL app.current_user_id with FastAPI) and executes queries; PostgreSQL enforces the policies.

With RLS, the database enforces this rule directly:

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_can_view_projects
ON projects
FOR SELECT
USING (
EXISTS (
SELECT 1
FROM memberships m
WHERE m.project_id = projects.id
AND m.user_id = current_setting('app.current_user_id')::uuid
)
);

This raises an architectural question:

Should the application remain the primary decision-maker, treating the database as a dumb store?

Or should the database take on a share of the business logic? In practice, the trade-off is between control and safety. Application-level checks are explicit but fragile; database-level checks are implicit but robust.

Development Workflow Implications

Adopting RLS is not only a technical change but also a cultural one. Developers accustomed to debugging FastAPI endpoints must now recognize that “empty results” may be the outcome of a database policy, not a faulty filter in Python. Debugging shifts from “why is this filter wrong?” to “what policy just applied?”

In FastAPI, this boils down tp passing the current user’s identity into PostgreSQL:

async def get_db(user: User):
async with async_session() as session:
# Assign current user for RLS
await session.execute(
text("SET LOCAL app.current_user_id = :uid"),
{"uid": str(user.id)}
)
yield session

Once this is set, all queries in the transaction are evaluated against RLS policies.

Then, with RLS enabled, the endpoint no longer needs explicit filters and can be designed this way:

@app.get("/projects")
async def list_projects(db: AsyncSession = Depends(get_db)):
return db.query(Project).all()

Even though the query has no WHEREclause, PostgreSQL ensures only the authorized rows are returned.

Now, developers can query tables naturally, without having to enforce permissions themselves directly in the endpoint. This new structure evidently implies some necessary adjustments and reflections:

Session Management: Connection pooling can introduce risks. If SET LOCAL is misapplied, user identifiers may persist across pooled connections, exposing data. Correct transaction scoping is critical.

Testing: Python unit tests alone are insufficient. Database-level test cases must verify which rows each user can and cannot access.

Onboarding: Developers new to RLS often find it unintuitive. Training and documentation are essential for adoption.

Third-Party Access: Reporting tools or analysts must also respect RLS, which requires consistently setting session parameters outside the application. This adds friction but guarantees uniform enforcement. In short, RLS does not remove complexity; it relocates it, moving responsibility from the application code to the PostgreSQL schema design.

Performance Considerations

RLS is evaluated for every row accessed by a query. While overhead is minimal in simple cases, complex joins can trigger unexpected query plans.

For example:

USING (
EXISTS (
SELECT 1
FROM memberships
WHERE memberships.project_id = projects.id
AND memberships.user_id = current_setting('app.current_user_id')::uuid
)
)

This appears straightforward, but:

On large datasets, it can generate nested subqueries for every row.

Missing indexes on memberships can severely degrade performance.

Complex joins (e.g., projects → tasks → comments) multiply the cost. Best practices include:

Designing indexes aligned with policy predicates.

Keeping policy logic minimal and delegating heavy computation to precomputed views.

Periodically reviewing execution plans with EXPLAIN under RLS conditions. RLS, therefore, enforces security and forces developers to approach schema design with a database engineer’s mindset.

Operational Challenges

Implementing RLS at scale introduces several subtleties:

Session Scope: Always use SET LOCAL inside transactions; never use SET globally. Otherwise, one user’s ID may leak into another’s request through a pooled connection.

Migration Complexity: Adding RLS late in a project can be disruptive. Queries that previously “just worked” may suddenly return empty results until session variables are set correctly. Early adoption is simpler.

Third-Party Access: Admin dashboards, reporting tools, and data scientists must also set session variables. This constraint can be inconvenient but ensures consistency across all access paths.

When RLS Is the Wrong Tool

RLS is powerful but not universal. Specific scenarios are better handled in application logic:

Access-control list: e.g., “admins can edit project metadata, but only owners can delete it.”

Feature flags: Conditional feature exposure is easier to manage in middleware.

Complex workflows: Multi-step approval processes or rules not tied directly to rows/columns are awkward to encode as SQL policies. RLS is best reserved for structural visibility rules (“users may only see their own rows”), not for the entire permission system. It maps well to a multi-tenant database that requires proper isolation at the storage rather than the application level, improving security.

The Trade-off Matrix

As with every engineering decision, this can be boiled down to a trade-off between multiple dimensions.

| | Application-Level Filtering | Row Level Security | Safety | Transparency | Performance | Debugging | Portability | Adaptation Cost | | | ————————— | —————— | —— | ———— | ———– | ——— | ———– | ————— | | ⚠️ Prone to omission | ✅ Guaranteed by the databaes | | ✅ Explicit in application code | ✅ Implicit in schema | | ⚠️ Potential over-fetching | ⚠️ Avoid over-fetching, but can prevent some optimizations | | ✅ Familiar to application devs | ⚠️ Requires database expertise | | ✅ Works with any database | ⚠️ PostgreSQL specific | | ✅ Low (default pattern) | ⚠️ Requires schema design and team training |

Our Own Two Cents

At Mergify, we build tools and products to help teams create reliable CI pipelines, increase merge rates, and reduce the overall costs associated with these workflows. Achieving this requires a large number of API routes, each interacting with various resources in our PostgreSQL database.

As the number of endpoints grew — along with our user base, product use cases, and database activity — we began enforcing stricter permissions across existing routes. This quickly became a long and complex effort of refactoring endpoints one by one, by adding the necessary permission filters everywhere, and juggling with unexpected edge cases.

Obviously, the experience raised an important question. Since we had no plans to move away from PostgreSQL, and given how easy it is for developers to overlook permission details, should access control really remain at the application level? Or would it be more effective to leverage PostgreSQL’s Row Level Security (RLS), reducing both the cognitive load on developers and the risk of mistakes, along with reducing the load on the database with more optimized queries?

As it was necessary, the refactor made these issues painfully clear. It not only increased code complexity but also hurt readability. At the same time, our database schema was already solid and well-suited for tools like RLS. The trade-off, in our context, then quickly became evident and we made the switch.

Conclusion

PostgreSQL’s Row Level Security provides a robust mechanism for delegating access control to the database engine. In applications, it offers stronger safety guarantees than application-level filtering but introduces new challenges in schema design, debugging, and operational workflows.

RLS should not be treated as a universal replacement for application logic. Its value lies in eliminating a specific class of vulnerabilities: unauthorized row leakage caused by missing filters.

The most significant challenges are cultural and operational rather than purely technical. Teams must adapt their testing practices, debugging workflows, and database literacy. Performance tuning and careful session management are essential for success.

Ultimately, adopting RLS is less about syntax than architectural philosophy: deciding whether access control belongs primarily in the application layer or in the database itself. RLS provides a compelling solution for systems that prioritize safety, consistency, and regulatory compliance.