Row-Level Security Is a Predicate, Not a One-Time Check

Row-Level Security (RLS) is a powerful way to enforce tenant isolation directly inside YugabyteDB.

A typical multi-tenant policy might look like this:

				
					CREATE POLICY tenant_isolation_policy
ON tenant_orders
USING (
    tenant_id =
      NULLIF(current_setting('app.tenant_id', true), '')::bigint
);

At first glance, it can be tempting to think of the policy as an authorization check that YugabyteDB performs once when the query starts:

Can this user access tenant 101?
- Yes → Run the query
- No → Reject the query

But that isn’t the right mental model.

An RLS policy becomes part of the query’s row-filtering logic. Conceptually, YugabyteDB is evaluating:

Can this row be seen?
Can this row be seen?
Can this row be seen?
…

That distinction becomes very important when designing RLS policies for large tables.

The source material behind this tip highlights exactly this behavior: RLS expressions operate at the row level, which can turn expensive parsing or functions with side effects into a significant multiplier.

RLS Lives in the Query’s Hot Path

Consider:

				
					SELECT COUNT(*)
FROM tenant_orders;

If RLS applies to the querying role, rows must satisfy the RLS predicate before they are visible to the query.

That means anything placed inside the policy potentially becomes part of a very frequently executed code path.

For example, this is relatively lightweight:

				
					USING (
    tenant_id =
      NULLIF(current_setting('app.tenant_id', true), '')::bigint
);

But consider this:

				
					USING (
    tenant_id = ANY (
        string_to_array(
            current_setting('app.tenant_ids', true),
            ','
        )::bigint[]
    )
);

Or something even more expensive:

				
					USING (
    some_expensive_function(tenant_id)
);

Or a function with a side effect:

				
					USING (
    log_access_and_return_true()
);

The more work placed inside the RLS predicate, the more important its execution characteristics become.

⚡ Think of an RLS USING clause as hot-path code.
Keep the predicate simple, deterministic where possible, and index-friendly. Small amounts of extra work can become expensive when applied repeatedly across many rows.

There Isn’t a Separate “RLS Result Cache”

It is better not to think of RLS like this:

Evaluate policy once

↓

Cache TRUE

↓

Process the entire query

RLS predicates participate in normal PostgreSQL/YSQL query planning and execution.

The optimizer may be able to:

● Simplify expressions
● Reorder or push down predicates
● Use indexes
● Avoid unnecessary work
● Optimize function evaluation based on volatility

But you should not design an RLS policy assuming that YugabyteDB simply evaluates the policy once per SQL statement and caches the answer.

This is where PostgreSQL function volatility becomes especially important.

VOLATILE vs. STABLE vs. IMMUTABLE

PostgreSQL and YSQL functions can be classified as:

● VOLATILE
● STABLE
● IMMUTABLE

These classifications are more than documentation.

They tell the query optimizer what assumptions it is allowed to make about the function.

Here is a useful summary:

Classification	Meaning	RLS Consideration
VOLATILE	Result may change every time the function is called.	Potentially expensive inside frequently evaluated RLS predicates, especially when it performs writes or other side effects.
STABLE	Result is guaranteed not to change within a single SQL statement.	Often appropriate for functions reading transaction/session context such as tenant GUCs.
IMMUTABLE	The same arguments must always produce the same result.	Allows aggressive optimization, but should not be used for session-dependent values unless that promise is actually true.

Using a STABLE Function for Tenant Context

Suppose your application supports access to multiple tenants and stores the allowed tenant IDs in a custom GUC:

				
					SET LOCAL app.tenant_ids = '101,102,105';

Instead of embedding all of the parsing logic directly in the RLS policy:

				
					CREATE POLICY tenant_isolation_policy
ON tenant_orders
USING (
    tenant_id = ANY (
        string_to_array(
            current_setting('app.tenant_ids', true),
            ','
        )::bigint[]
    )
);

you can encapsulate the tenant-context logic:

				
					CREATE OR REPLACE FUNCTION current_tenant_ids()
RETURNS bigint[]
LANGUAGE sql
STABLE
AS $$
    SELECT
      string_to_array(
        NULLIF(current_setting('app.tenant_ids', true), ''),
        ','
      )::bigint[];
$$;

Then simplify the RLS policy:

				
					CREATE POLICY tenant_isolation_policy
ON tenant_orders
USING (
    tenant_id = ANY(current_tenant_ids())
);

This has several benefits.

It keeps complex parsing out of the policy itself, gives the optimizer useful information about the function’s volatility, and makes the security policy easier to read and maintain.

But there is an important caveat.

💡 STABLE does not mean “execute exactly once.”
STABLE tells the PostgreSQL/YSQL optimizer that the function’s result will not change during the statement. That gives the optimizer additional freedom, but you should still inspect the execution plan rather than assume a particular number of function calls.

Be Careful Using IMMUTABLE with Tenant GUCs

You may encounter techniques that wrap current_setting() inside an IMMUTABLE function to encourage more aggressive optimization.

For example:

				
					CREATE OR REPLACE FUNCTION get_tenant_ids()
RETURNS bigint[]
LANGUAGE sql
IMMUTABLE
AS $$
    SELECT
      string_to_array(
        current_setting('app.tenant_ids'),
        ','
      )::bigint[];
$$;

This is attractive because IMMUTABLE gives the optimizer much stronger guarantees.

But think carefully about what IMMUTABLE actually promises:

The result cannot change.

A session setting clearly can.

For example:

				
					BEGIN;

SET LOCAL app.tenant_id = '101';

-- queries for tenant 101

COMMIT;

and then:

				
					BEGIN;

SET LOCAL app.tenant_id = '205';

-- queries for tenant 205

COMMIT;

The value returned by current_setting() changed.

This distinction becomes especially important when using:

● Prepared statements
● Reused query plans
● Connection pools
● YSQL Connection Manager
● Applications switching tenant context between transactions

Labeling a function as IMMUTABLE when it really depends on session state violates the contract you are giving the optimizer.

⚠️ Don’t mark a function IMMUTABLE just to make it faster.
Function volatility is a contract with the optimizer. For tenant context obtained from current_setting(), STABLE generally better describes the function’s semantics because the value can change between statements or transactions.

The More Dangerous RLS Trap: Side Effects

Repeated CPU work is one concern.

Functions that perform writes are potentially much more dangerous.

This connects directly to a previous YugabyteDB Tip:

💡 Audit SELECTs on a Table to an Audit Table Using RLS

That technique takes advantage of RLS to simulate something PostgreSQL/YSQL does not provide directly: an AFTER SELECT trigger.

The policy can invoke a function such as:

				
					CREATE POLICY audit_me_select
ON audit_me
FOR SELECT
USING (
    log_audit_me_select()
);

And the function might perform:

				
					INSERT INTO audit_me_audit (...);

It works.

But there is an important consequence.

A function that modifies database state must be treated as VOLATILE.

Now consider:

				
					SELECT COUNT(*)
FROM audit_me;

If 100,000 protected rows must pass through the policy, the workload should not be mentally modeled as:

1 SELECT + 1 audit INSERT

It can instead behave much more like:

1 SELECT + many policy function calls + many audit INSERT

Potentially one for each row evaluated by that policy function.

A policy function with side effects can create an audit storm, where a single read query generates a large number of writes.

CPU Multiplier vs. I/O Multiplier

There are therefore two different types of RLS performance problems.

Type	Examples	Risk
CPU Multiplier	`string_to_array()`, JSON processing, regex, complex PL/pgSQL, authorization calculations	Repeated computation can increase query latency as more rows are evaluated.
I/O Multiplier	Audit inserts, updates, logging functions, functions with other side effects	A read query can generate a large amount of additional write activity.

But “Per Row” Does NOT Necessarily Mean “Scan Every Row”

This is an extremely important distinction.

Saying that an RLS predicate applies at the row level does not mean YugabyteDB must always read every row in the table and then throw most of them away.

Consider a table designed like this:

				
					CREATE TABLE tenant_orders (
    tenant_id    BIGINT NOT NULL,
    order_id     BIGINT NOT NULL,
    order_detail TEXT,
    PRIMARY KEY (tenant_id, order_id)
);

And a policy:

				
					CREATE POLICY tenant_isolation_policy
ON tenant_orders
USING (
    tenant_id =
      NULLIF(current_setting('app.tenant_id', true), '')::bigint
);

Now:

				
					BEGIN;

SET LOCAL app.tenant_id = '101';

SELECT *
FROM tenant_orders;

COMMIT;

The ideal result is for the tenant predicate to participate in the access path so YugabyteDB can retrieve the relevant tenant’s rows efficiently rather than scanning unrelated tenants.

This is one reason the shape of your RLS predicate matters just as much as the cost of evaluating it.

A simple predicate like:

				
					tenant_id = <tenant>

aligned with an appropriate primary key or index is much easier to optimize than complex policy logic involving functions, parsing, joins, or subqueries.

Always Check the Execution Plan

Don’t guess.

Test the policy with:

				
					EXPLAIN (ANALYZE, COSTS OFF)
SELECT *
FROM tenant_orders;

But make sure you test as the actual application role.

Testing as one of these can give you very misleading results:

● Superuser
● Table owner
● Role with BYPASSRLS

because those users may bypass RLS.

For example:

				
					SET ROLE tenant_app;

BEGIN;

SET LOCAL app.tenant_id = '101';

EXPLAIN (ANALYZE, COSTS OFF)
SELECT *
FROM tenant_orders;

ROLLBACK;

Look for whether YugabyteDB can use the tenant predicate efficiently as part of the access path rather than processing significantly more data than necessary.

A Good RLS Pattern

For applications where each transaction operates on one tenant, a simple pattern is difficult to beat:

				
					CREATE POLICY tenant_isolation_policy
ON tenant_orders
FOR ALL
TO tenant_app
USING (
    tenant_id =
      NULLIF(current_setting('app.tenant_id', true), '')::bigint
)
WITH CHECK (
    tenant_id =
      NULLIF(current_setting('app.tenant_id', true), '')::bigint
);

Then establish the tenant context at the beginning of the transaction:

				
					BEGIN;

SET LOCAL app.tenant_id = '101';

-- Application queries

COMMIT;

This gives you:

Simple RLS predicate
+
Indexable tenant_id
+
Fail-closed tenant context
+
Transaction-scoped GUC
+
No policy side effects

Why `NULLIF()` Matters

Notice that the policy uses:

				
					NULLIF(current_setting('app.tenant_id', true), '')::bigint

rather than simply:

				
					current_setting('app.tenant_id')::bigint

This helps the policy fail closed if the tenant context is missing or empty.

current_setting(..., true) returns NULL when the setting does not exist, while:

				
					NULLIF(..., '')

also converts an empty string to NULL.

The resulting comparison:

				
					tenant_id = NULL

does not evaluate to TRUE.

So instead of accidentally exposing another tenant’s rows, the policy returns no matching rows.

🔒 Security and performance can reinforce each other.
A fail-closed tenant GUC combined with a simple, indexable tenant_id predicate gives YugabyteDB both a strong security boundary and a predicate the query engine has a good chance of executing efficiently.

How This Fits with Previous YugabyteDB Tips

There are two related YugabyteDB Tips worth reading with this one.

Tip	Why It’s Relevant
Safely Use Dynamic Tenant GUCs with RLS and YSQL Connection Manager	Shows how to safely establish dynamic tenant context, especially when connections are reused.
Audit SELECTs on a Table to an Audit Table Using RLS	Demonstrates the clever RLS auditing technique—and provides a perfect example of why side effects inside a row-level predicate must be used carefully.

Final Takeaway

The most important thing to remember about RLS performance is this:

RLS is a predicate, not a one-time authorization check.

Treat the policy expression like code running in the hot path of your query.

Prefer:

				
					tenant_id =
  NULLIF(current_setting('app.tenant_id', true), '')::bigint

over complicated parsing, lookup, or side-effect logic whenever possible.

The practical rules are:

✔ Keep RLS predicates simple.
✔ Make tenant predicates index-friendly.
✔ Use fail-closed tenant context.
✔ Use the correct function volatility classification.
✔ Treat VOLATILE functions inside policies with caution.
✔ Avoid writes from RLS policies on high-volume reads unless the per-row behavior is explicitly what you want.
✔ Don’t assume STABLE means “executed exactly once.”
✔ Don’t declare something IMMUTABLE merely to encourage caching.
✔ Use EXPLAIN ANALYZE with the actual application role.
✔ Remember that row-level evaluation does not necessarily mean a full-table scan when the predicate can be used efficiently as an access condition.

💡 The goal isn’t to avoid RLS.
RLS is an excellent security mechanism for multi-tenant applications. The goal is to design the policy so that the row-level security check is cheap, predictable, and aligned with the way YugabyteDB accesses the data.

Have Fun!

I’m in Sunnyvale, CA this week for an offsite with the YugabyteDB engineering team. After a full day of meetings, a colleague and I grabbed dinner at Black Bear Diner.

What a cool place! It brought back some great memories of the classic diners back home around Pittsburgh, PA. The food was incredible, the atmosphere was fun, and yeah… they definitely lean into the bear theme inside. 🐻😂

Great food, great company, and a little unexpected taste of home while 2,500 miles away.