🐘 The PostgreSQL NULL Trap: When a Security Check Isn't Really a Security Check

How a seemingly harmless SQL behavior can undermine tenant isolation, why defense in depth is not always enough, and how to make security checks fail closed.

Some of the most interesting security bugs do not come from complicated algorithms, obscure race conditions, or sophisticated attackers.

They come from code that looks perfectly reasonable.

Consider this PostgreSQL constraint:

CHECK (organization_id = current_organization)

The intention seems obvious: a row can only be written if it belongs to the organization associated with the current transaction.

It looks like a reasonable security rule. It is easy to read, easy to explain, and easy to assume is correct.

But what happens when current_organization is missing?

The answer involves one of SQL's oldest features: three-valued logic. It is a good reminder that a database can enforce a rule exactly as designed while still failing to enforce the security property we intended.

Let us walk through the problem, using a hypothetical multi-tenant application, and explore what it teaches us about PostgreSQL, Row-Level Security, and fail-closed design.

1. The fundamentals: keeping tenants apart

Imagine a SaaS application used by hundreds of companies. All of them share the same PostgreSQL database.

Each row belonging to a customer carries an organization ID:

Document	Organization
Quarterly report	org-a
Employee handbook	org-b
Project plan	org-c

A user acting on behalf of org-a must not be able to read or modify data belonging to org-b.

This is called tenant isolation. It sounds simple, but enforcing it consistently across every query, background job, migration, and maintenance script is a substantial engineering responsibility.

PostgreSQL provides several mechanisms that can help.

Row-Level Security: which rows can you access?

Row-Level Security, or RLS, lets the database apply policies to individual rows. Instead of trusting every application query to remember a tenant filter, the database can enforce restrictions on which rows a role may read or modify.

Conceptually, an RLS policy might say:

You may access a row only if its organization ID matches the organization associated with your transaction.

That is a powerful additional layer of protection.

CHECK constraints: is this row valid?

A CHECK constraint validates a condition on a row being inserted or updated.

For example:

CHECK (price > 0)

This says that a product's price must be positive.

A similar constraint can compare a row's organization ID against the current tenant context.

The idea is to establish a database-level invariant: rows must satisfy the tenant rule before they can be stored.

RLS and CHECK constraints serve different purposes, and neither should be treated as a universal replacement for the other. RLS controls access to rows, while CHECK constraints validate row values.

The subtlety is in how CHECK constraints evaluate conditions.

2. The surprising part: SQL has three answers, not two

Most people think about conditions in terms of two outcomes:

• True
• False

SQL has a third: NULL.

In this context, NULL means that a value is missing or unknown. It is not the same as an empty string, zero, or false.

Consider the following comparisons:

SELECT 'org-a' = 'org-a'; -- TRUE
SELECT 'org-a' = 'org-b'; -- FALSE
SELECT 'org-a' = NULL;    -- NULL

The third comparison does not return false. The database cannot determine whether the values match because one of them is unknown.

This is called three-valued logic.

It is easy to overlook because many ordinary SQL queries seem to behave intuitively until an unknown value enters the picture.

For example, a condition such as price > 0 does not evaluate to false when price is NULL. It evaluates to NULL.

That distinction becomes important when the condition is used as a security boundary.

3. How a CHECK constraint can accept an unknown result

PostgreSQL CHECK constraints have an important rule:

A row violates a CHECK constraint when its expression evaluates to FALSE. If the expression evaluates to TRUE or NULL, the constraint does not reject the row.

Let us make that concrete.

Suppose we create a simplified table:

CREATE TABLE documents (
    organization_id text,
    title text,
    CHECK (
        organization_id =
        current_setting('app.organization_id', true)
    )
);

The application normally sets app.organization_id to identify the current tenant.

When that setting contains org-a, inserting a row for org-a satisfies the constraint. Inserting a row for org-b fails the comparison and is rejected.

Now imagine that no organization has been set.

The true argument to current_setting tells PostgreSQL to return NULL when the setting is missing rather than raising an error.

The comparison therefore becomes:

'org-b' = NULL

Its result is NULL.

And because a CHECK constraint accepts NULL, the constraint does not reject the row on that basis.

The constraint has not established that the write is authorized. It has simply failed to establish that the condition is false.

That is the trap.

Validation is not authorization

This distinction is worth pausing on.

A validation rule can be perfectly correct according to its defined semantics and still be unsuitable for enforcing a security invariant.

Our hypothetical constraint was intended to mean:

The row must belong to the current organization.

But its actual behavior was closer to:

Reject the row only when the comparison definitively says the organizations do not match.

Those statements are not equivalent.

When the value needed to make the decision is missing, the second rule can allow an operation that the first rule was supposed to prevent.

This is not a PostgreSQL bug. It is documented SQL behavior interacting with a security-sensitive design.

The lesson is to reason about what a security rule does for every possible input, including missing and unexpected values, not just the inputs present on the happy path.

4. Fail open versus fail closed

Security engineers often describe these situations using two terms: fail open and fail closed.

They are simpler than they sound.

Fail open means that when a check cannot determine whether an operation is allowed, the system ends up permitting it.

Fail closed means that when authorization cannot be established, the system denies the operation.

Imagine an office door controlled by a badge reader.

• Valid badge: the door unlocks.
• Invalid badge: the door stays locked.
• Badge reader cannot determine who you are: the door stays locked.

The third case is fail-closed behavior.

A fail-open design would unlock the door when the badge reader could not make a decision. Convenient during an outage, perhaps, but not ideal for a secure office.

The same principle applies to tenant isolation.

Tenant context	Expected behavior
Known tenant matches the row	Allow
Known tenant does not match the row	Deny
Tenant context is missing	Deny

The central rule is straightforward:

If the system cannot prove that a tenant-owned write is authorized, it must not allow the write.

That does not mean every error in every system must result in a total shutdown. It means that security-sensitive decisions should not silently grant access when the information needed to authorize the operation is unavailable.

5. Why Row-Level Security does not automatically solve this

A natural reaction is: why not rely on RLS to prevent the write?

RLS is an important protection, but PostgreSQL has specific exceptions to its enforcement.

Superusers and roles with the BYPASSRLS attribute bypass RLS. Table owners normally bypass it too, unless the table uses FORCE ROW LEVEL SECURITY.

These exceptions are documented PostgreSQL behavior, not evidence that RLS is inherently unsafe.

They matter because database operations do not always run under the same role.

An application request might use a restricted role subject to RLS. A migration, administrative script, or maintenance operation might execute under a different role with different privileges.

Consider this hypothetical setup:

1. An ordinary application connection is subject to RLS.
2. A table owner can bypass RLS.
3. A CHECK constraint is expected to enforce the tenant boundary even when RLS is bypassed.
4. That CHECK constraint accepts an unknown result when tenant context is missing.

The layers now have a gap.

The CHECK constraint may work as expected for known tenant values, and RLS may work as expected for restricted roles. Yet the overall security design still has a failure mode when a privileged role bypasses RLS and the constraint receives missing context.

This is why defense in depth requires more than counting security mechanisms.

Each layer needs a clearly defined responsibility, and the combined design must remain safe when one layer is bypassed.

For more detail, see the PostgreSQL documentation on Row Security Policies.

6. NULL is not the only missing-value problem

There is another subtlety worth understanding when tenant context is stored in PostgreSQL settings.

A setting can sometimes be represented as an empty string ('') rather than NULL, depending on its history and how it was reset.

These are different values.

• NULL means the value is unknown or missing.
• '' is a known string containing zero characters.

This distinction can become important when database connections are reused.

A connection pool is a shared resource

Imagine a taxi that serves one passenger and then another. You would not want the next passenger to inherit the previous passenger's identity or permissions.

Connection pooling creates a similar engineering concern. The same underlying database connection can serve multiple transactions over time.

Tenant context must be established correctly for each transaction and must not leak across requests.

If one policy treats NULL as missing while another treats '' differently, the system can behave inconsistently when no usable tenant context exists.

A robust design normalizes both representations into one consistent state before making authorization decisions.

For example, PostgreSQL's NULLIF function can convert an empty string to NULL:

NULLIF(current_setting('app.organization_id', true), '')

This is a normalization step, not a complete authorization check. The resulting NULL must still be handled safely by the logic that decides whether the operation is permitted.

That last part is important. Normalizing missing values helps, but it does not automatically make an authorization rule fail closed.

7. Designing a fail-closed authorization check

The fix is not simply to add another layer of SQL. It is to make the intended security behavior explicit.

For a tenant-owned row, the authorization decision should have a definite result:

• Allow the write when the tenant is known and the row belongs to that tenant.
• Reject the write when the tenant is known but the row belongs to someone else.
• Reject the write when the tenant is missing or unusable.

In PostgreSQL, COALESCE is useful when an expression might produce NULL and a safe fallback is needed.

For example:

COALESCE(
    organization_id = current_organization,
    FALSE
)

Conceptually, this means:

Evaluate the comparison. If its result is unknown, treat it as false.

This illustrates the core principle, but it is not a complete drop-in security solution. A production implementation must also handle how the current organization is obtained, normalize empty values, define any legitimate system-level operations, and account for intentionally shared rows.

For a real schema, one approach is to centralize these decisions in a small set of carefully designed helper functions rather than duplicating authorization expressions across dozens of policies and constraints.

Centralization offers several advantages:

• The security rule has one consistent definition.
• Missing-context behavior is easier to audit.
• Changes can be reviewed in one place.
• Tests can focus on the intended authorization invariant.

But shared helpers are not automatically secure simply because they are shared. Their function privileges, name resolution, and behavior under different database roles still require careful review.

The objective is not to hide complexity in a function. It is to make the security contract explicit and difficult to implement inconsistently.

What about legitimate system operations?

A stricter authorization rule can affect operations that legitimately write data outside an ordinary tenant request.

Database migrations, data-repair scripts, and restore jobs are examples.

These operations should have an explicit, controlled authorization path rather than relying on missing tenant context to permit writes.

Think of it as issuing a separate, auditable access badge to a maintenance engineer instead of leaving the front door unlocked.

The privileged path must be restricted to trusted operations. If ordinary application requests can declare themselves privileged, the exception defeats the security rule.

There is also an important distinction between tenant-owned data and genuinely shared reference data. A row with no organization ID might be valid in a shared lookup table, but that should be an explicit part of the data model and authorization policy, not an accidental consequence of NULL handling.

8. Audit the schema you actually have

Security rules tend to evolve over time.

One developer adds a CHECK constraint. Another introduces an RLS policy. A later migration changes how tenant context is represented. Eventually, the same underlying rule may exist in several slightly different forms.

That inconsistency makes auditing harder.

A useful approach is to inspect PostgreSQL's live system catalogs, which describe the constraints, policies, and tables that actually exist in the database.

Why not just search the migration files?

Because migrations describe the intended sequence of schema changes. The live catalog tells you what the current schema contains. Historical changes, manual interventions, and differences between environments can make those two views diverge.

For a tenant-isolation audit, review at least:

• CHECK constraints involving tenant identifiers.
• RLS policies and the expressions they use.
• Whether RLS is enabled and whether it is forced on relevant tables.
• The roles used by applications, migrations, and maintenance jobs.
• How missing tenant context is represented and handled.
• Defaults that derive identity or tenant information from database settings.

Group similar rules by their behavior, not just by their SQL spelling. Two expressions can look different but implement the same rule. Two expressions that look almost identical can have materially different behavior around NULL.

When automating a rewrite, generate changes from recognized patterns and stop on unfamiliar ones. A tool should not guess how to transform a security expression it does not understand.

Automation can reduce repetitive work, but uncertainty should lead to review rather than an optimistic edit.

9. Test the security property, not just the happy path

A test that proves an ordinary application request cannot access another tenant's row is useful. It does not necessarily prove that a CHECK constraint will enforce the boundary when RLS is bypassed.

To test that specific property, use an appropriately controlled test environment and a role exempt from RLS.

The test should attempt a tenant-owned write with no tenant context and assert that the database rejects it because the CHECK constraint is violated.

The test should demonstrate two states:

Before the fix: the unsafe write is accepted, so the test detects the bug.

After the fix: the unsafe write is rejected, so the test passes.

Then deliberately restore the vulnerable expression and run the test again. It should fail.

This is a powerful testing technique because it shows that the test actually detects the failure mode it was designed to catch.

Otherwise, a passing test can provide false confidence. Perhaps RLS rejected the write before the CHECK constraint was evaluated. Perhaps the test never exercised a missing tenant context. Perhaps the test ran under a role that did not reflect the intended threat model.

The question is not just, "Does the test pass?"

It is, "Would this test catch the security regression we care about?"

For security-sensitive code, that is a much more valuable question.

10. Where AI-assisted engineering fits

An audit like this can involve repetitive work: inspecting catalog metadata, grouping similar expressions, generating migration scripts, and running regression tests.

Those tasks can benefit from automation, including AI coding agents.

However, the security contract must remain explicit and human-reviewed.

An agent can help identify constraints that match a known pattern. It can generate a migration and report expressions it cannot recognize. It can run tests and help analyze failures.

It should not independently decide whether a shared row is legitimate, whether a maintenance operation deserves privileged access, or what the application's tenant-isolation guarantee should be.

A useful division of responsibility is:

• Humans define and review the security invariant.
• Automation performs repeatable transformations.
• Tests demonstrate that the resulting system enforces the invariant.

For schema-wide changes, a reproducible generator is often safer than asking an agent to edit hundreds of SQL expressions manually. The output can be reviewed as a normal migration, and the generator can be rerun to verify that it produces consistent results.

The more security-sensitive the transformation, the more important it is for unexpected inputs to stop the process rather than trigger a guess.

11. The bigger lesson

The underlying issue is a small SQL semantic detail with potentially significant security consequences.

NULL is not false. A PostgreSQL CHECK constraint does not reject an expression merely because its result is unknown. RLS has documented exemptions. Connection reuse can complicate how missing context is represented.

None of these behaviors is inherently wrong.

The engineering challenge is ensuring that the system's security guarantees account for all of them.

Here are the principles worth carrying into your next database design review:

Make uncertainty explicit. Decide what happens when tenant context is missing, empty, invalid, or inconsistent.

Fail closed on authorization. If a tenant-owned operation cannot be authorized, deny it rather than treating an unknown result as permission.

Understand the boundaries of each control. Know which roles are subject to RLS and what CHECK constraints actually guarantee.

Audit the live schema. Do not assume every table and policy still matches the original design.

Test the failure mode directly. Make sure your regression test catches the original bug, including when other security layers are bypassed.

Keep security logic consistent. Centralize shared invariants, review exceptions explicitly, and avoid letting different parts of the schema develop subtly different interpretations of the same rule.

The most dangerous security assumptions are often the ones that seem too obvious to question.

A comparison that looks like an authorization check may not actually establish authorization. A second security layer may not apply to every database role. And a passing test may be proving something other than what you think it is.

In security engineering, the difference between "not proven false" and "proven allowed" matters.