Understanding RDB and RLS through Set Theory

Happy New Year.
I am K@zuki., who spent the year-end and New Year holidays studying Unity.
This article is a rewrite for my blog of the content I previously presented at an LT (Lightning Talk).

This article does not focus on PostgreSQL implementation or performance perspectives, but rather reinterprets them from a semantic standpoint. Therefore, it is mostly a paraphrase, and the intention is for you to feel that it is "obvious" once you finish reading.

When using RDBs, we perform "filtering" as naturally as breathing.
For multi-tenancy, it's WHERE tenant_id = ?. For logical deletion, it's WHERE deleted_at IS NULL.
Writing this every time is cumbersome, and forgetting to write it can lead to accidents.
Therefore, many frameworks and ORMs have a mechanism to "automatically append conditions" (Global Scope).

However, this feature is often disliked as an "anti-pattern."

On the other hand, Row-Level Security (RLS), which PostgreSQL and others possess, is also often introduced as a "mechanism to automatically append conditions," but it is considered a recommended and robust design.

"Automatic filtering in the application" vs. "RLS in the DB."
Although what they are doing is similar, why is the evaluation so different?

In this article, from the perspective of Set Theory, which is the foundation of RDBs, let's think about their definitive differences and the "SQL Semantics" that RLS protects.

RDB is Set Theory

In the relational model proposed by E.F. Codd in 1970, a database is not just a place to store data.
It is a system built on a mathematical foundation.

• Table = Set
  A table is a "set of tuples (rows)" and has no order.
• SQL = Relational Algebra
  The SQL we write is a declarative description for performing operations on sets.

Each clause of SQL can also be viewed as Syntactic Sugar for mathematical operators.

SQL	Relational Algebra	Symbol	Meaning
WHERE	Selection	$\sigma$	Retrieve "rows" that meet conditions
SELECT	Projection	$\pi$	Retrieve required "attributes (columns)"
JOIN	Join	$\Join$	Create pairs from sets

For example, a query like "I want the titles of documents with an ID greater than 1" can be interpreted not as a procedure, but as the definition of "which subset it is."

$$\pi_{title} ( \sigma_{id > 1} ( documents ) )$$

Assuming this "world of set theory," the true nature of RLS becomes clear.

RLS is a Redefinition of the Domain

Row-Level Security (RLS) is a mechanism that transparently applies a selection operation ($\sigma$) to all queries based on policy functions.

Expressing this mathematically, it looks like this:

$$T' = \sigma_{policy}(T)$$

• $T$: The entire physical set managed by the DB
• $T'$: The logical subset presented to the user

In other words, enabling RLS is synonymous with redefining the Domain itself to "the subset $T'$ that I can see," rather than rewriting queries.

-- The world after RLS is enabled
SELECT * FROM documents;

When you issue this query, the user is not seeing a "filtered result." They are seeing a "world where only $T'$ exists from the start."
In contrast, application-side control (whether manual or automatic) involves applying a WHERE filter to the entire $T$.

-- When filtering with WHERE
SELECT * FROM documents WHERE tenant_id = X;

Although it references the same documents table as RLS, it differs in whether it filters down from the entire set or queries a subset that has been narrowed down from the beginning.

Why is "Application-Side Scope" Disliked?

Here we return to the question at the beginning.
The mechanism of automatically adding a scope on the application side is also similar in that it automatically appends a WHERE clause.
Why is adding it via WHERE disliked?

It is because the mathematical approach (semantics) is fundamentally different.

Composition by Logical Conjunction (Syntactic Filtering)

Control on the application side is an approach where the developer composes the "original desired query conditions ($Q$)" with an "authorization filter ($F$)" using a logical AND (conjunction).

$$Result = \sigma_{Q \land F}(T)$$

In SQL, it looks like this:

-- Q (id > 5) AND F (tenant_id = 1)
SELECT * FROM documents WHERE id > 5 AND tenant_id = 1;

This approach is merely an intervention at the "Syntax" level.
If a developer accidentally forgets $F$, the query becomes a simple $\sigma_{Q}(T)$, and the result returned includes data from other tenants (information leakage).

Safety depends on the procedural effort of "constantly composing $F$," and easily collapses with things like unscoped.

Reduction of the Domain (Semantic Domain Definition)

On the other hand, the RLS approach is a "reduction of the Domain."

First, a logical set $T'$ is defined by the policy function.

$$T' = \sigma_{F}(T)$$

The query $Q$ issued by the application is executed against this $T'$.

$$Result = \sigma_{Q}(T')$$

-- No need to write filter F
SELECT * FROM documents WHERE id > 5;

Even if a developer forgets $F$, because the target of access has already been reduced to $T'$, it behaves as if data from other tenants does not physically exist.

To summarize succinctly:

• Application Control - Manually cutting out a safe part from the entire set $T$ every time.
• RLS - Only a safe subset $T'$ is provided from the start.

You can think of it as these being the differences.

Self-Defensive Sets

This difference creates a decisive gap in scenarios involving Table Joins (JOIN).

Application Control is Post-hoc Filtering

When controlling on the application side, it tends to take the form of applying filters to the "joined result," the "join operation," or "individual tables."

$$Result = \sigma_{F}(A \Join B)$$

or

$$Result = \sigma_{F}(A) \Join \sigma_{F}(B)$$

In this case, there is a higher risk than with RLS that data that should not be visible becomes a subject of calculation during the joining process, or that the filter is bypassed depending on the conditions of a LEFT JOIN.

RLS is a Self-Defensive Set

In an RLS environment, $\sigma$ is executed independently on each table (set) before the join operation ($\Join$) is performed.

$$A' \Join B' \Join C'$$

$$Result = (\sigma_{F} A) \Join (\sigma_{F} B) \Join (\sigma_{F} C)$$

Each table joins the process only after being reduced to "data that it is allowed to show." In this article, I call this a "Self-Defensive Set."

Because the join occurs between already-filtered subsets, it not only prevents unnecessary intermediate generation but also mathematically eliminates any room for "data that should not be visible" to be mixed in, regardless of what kind of join query is written.

Summary

The biggest advantage of introducing RLS is not just improved security; it is "the ability to return SQL to a declarative description." Do you remember the documents query from the beginning?

• Before: Describing noise (filter conditions) like "where ID is > 1 and tenant_id is X..." every time
  
  $$\pi_{title}(\sigma_{id \gt 1 \land tenant\_id = 1}(documents))$$
• After: Describing only the conditions for the "desired data"
  
  $$\pi_{title}(\sigma_{id \gt 1}(documents'))$$

Instead of rewriting queries, execute queries against a "correctly defined set $documents'$." This removes the noise from the application code, allowing developers to concentrate on pure business logic (set operations).

"Both RDB and RLS exist within the same world of set theory."

That is precisely why they are highly compatible and can create a robust architecture.