Implementing Row-Level Security on Billion-Row Hash-Partitioned PostgreSQL Tables

How we enforced a 2-year data retention policy at the database level — without breaking query performance

The Problem

Our platform is a multi-tenant SaaS backup service serving thousands of customers across Google Workspace, Office 365, and similar cloud services. Our core data tables — docs_tbl, mails_tbl, cloud_mails_tbl — each hold billions of rows, hash-partitioned on userid across 74 partitions, spread across a 7-region RDS fleet managing over 500 TB.

We needed to enforce a strict 2-year data visibility window at the database layer. The requirement was simple: no application role should ever see records older than two years without an explicit DBA-level bypass. It had to be enforced at the database level — not in application code, not in the ORM, not in the API layer — because those layers can be bypassed, forgotten, or misconfigured.

Row-Level Security (RLS) was the right tool. What followed was an education in PostgreSQL’s policy evaluation model, its interaction with declarative partitioning, and what happens to your query plans when you bolt security onto a billion-row table.

A Primer on PostgreSQL RLS

Before diving into implementation, you need to understand one thing that trips up almost everyone: the difference between PERMISSIVE and RESTRICTIVE policies, and how PostgreSQL evaluates them.

PostgreSQL evaluates RLS using this formula:

Final visibility = (ANY permissive policy passes) AND (ALL restrictive policies pass)

Policy TypeLogicEffectPERMISSIVEOR across all permissive policiesAdds rows to the visible setRESTRICTIVEAND across all restrictive policiesRemoves rows from the visible set

The critical trap: If you define a RESTRICTIVE policy with no PERMISSIVE policy in place, the permissive side defaults to FALSE. The result? Zero rows returned for all users — not just old ones.

We hit this exact issue during our first iteration:

-- ❌ WRONG — this returns 0 rows for everyone
CREATE POLICY restrict_2yr ON dbo.docs_tbl
    AS RESTRICTIVE FOR ALL TO PUBLIC
    USING (createddate >= CURRENT_TIMESTAMP - INTERVAL '2 years');

Everything looked correct on paper. The policy was created. RLS was enabled. But queries returned empty result sets. The fix was counterintuitive: use a PERMISSIVE policy where the USING clause defines what is allowed. Rows that don't match the expression become silently invisible — no error, no warning.

Step 1 — Enable and Force RLS

ALTER TABLE dbo.docs_tbl ENABLE ROW LEVEL SECURITY;
ALTER TABLE dbo.docs_tbl FORCE ROW LEVEL SECURITY;

FORCE ROW LEVEL SECURITY is not optional if you want strict enforcement. Without it, the table owner bypasses all policies silently. Superusers always bypass RLS regardless — by design in PostgreSQL.

Note for partitioned tables: ENABLE ROW LEVEL SECURITY on the parent table does not automatically propagate to existing partitions. You must enable it explicitly on each partition, or use ON ONLY semantics carefully (covered in Step 4).

Step 2 — The 2-Year Visibility Policy

CREATE POLICY rls_restrict_older_than_2years
    ON dbo.docs_tbl

Two things worth calling out:

Why PERMISSIVE? As explained above — this defines the allowed set. Rows older than 2 years simply fall out of the visible set.

Why AND createddate IS NOT NULL? In a production dataset of billions of rows, there will be records with a missing createddate — perhaps from early migrations, legacy imports, or API-side timestamp bugs. Without this guard, those undated records become permanently visible to all roles, which violates the retention intent.

Step 3 — DBA Bypass Policy

Operations, archival jobs, and compliance queries legitimately need to read old data. We created a bypass policy for privileged roles:

CREATE POLICY rls_admin_bypass
    ON dbo.docs_tbl
    AS PERMISSIVE
    FOR ALL
    TO app_dba_role, app_archive_role
    USING (true);

The policy evaluation table after both policies are in place:

RoleRows VisiblePUBLIC / app rolescreateddate >= NOW() - 2 yearsapp_dba_roleAll rows (via USING (true))app_archive_roleAll rows (via USING (true))Table owner (with FORCE RLS)createddate >= NOW() - 2 yearsSuperuserAll rows (PostgreSQL built-in bypass)

Step 4 — The Partition Problem

Here’s the part the PostgreSQL documentation mentions in passing but deserves more emphasis: RLS policies defined on a partitioned parent table using ON ONLY do not automatically propagate to existing partitions.

When you create a policy on a partitioned table, it is logically associated with the parent. But each child partition is its own heap. For RLS to be enforced at query time, the policy (or an equivalent one) needs to exist on each partition too.

For a table with 74 HASH partitions, this means:

-- Enable RLS on each partition
ALTER TABLE dbo.docs_tbl_0 ENABLE ROW LEVEL SECURITY;
ALTER TABLE dbo.docs_tbl_0 FORCE ROW LEVEL SECURITY;
-- ... repeat for all 74 partitions

And either propagate the policy explicitly, or verify that the planner is enforcing it at the parent level before partition pruning occurs. We scripted this across all partitions using pg_inherits to enumerate them:

SELECT inhrelid::regclass AS partition
FROM pg_inherits
WHERE inhparent = 'dbo.docs_tbl'::regclass
ORDER BY inhrelid::regclass::text;

Step 5 — The Index Strategy

This is where RLS gets expensive if you’re not careful.

Before adding RLS, our queries on docs_tbl typically anchored to the primary key (userid, docid) or the existing index on (userid, retentionstatus). After enabling RLS, the planner now injects the retention predicate createddate >= CURRENT_TIMESTAMP - INTERVAL '2 years' into every query — implicitly, invisibly.

An EXPLAIN (ANALYZE, BUFFERS) of a basic lookup revealed the damage:

Rows Removed by Filter: 56,094

On a LIMIT 10 query. The planner was scanning via the primary key, fetching rows, then discarding the ones that failed the RLS predicate as a post-scan filter. On a billion-row table with 74 partitions, this is catastrophic at scale.

The Fix: Composite Partial Index

CREATE INDEX ix_docs_tbl_userid_createddate_rls
    ON ONLY dbo.docs_tbl USING btree (userid, createddate DESC)
    WHERE retentionstatus = 0
      AND createddate IS NOT NULL;

Column ordering rationale:

• userid first — because the partition key is userid and nearly every query filters on it. This also enables partition pruning to kick in before the index scan.
• createddate DESC second — the RLS predicate is >= NOW() - 2yr, which is a range scan on recent rows. A descending index means the planner enters from the most recent end and exits early once it crosses the 2-year boundary.

Partial index conditions:

• WHERE retentionstatus = 0 — mirrors our existing index conventions and excludes soft-deleted records, keeping the index lean.
• AND createddate IS NOT NULL — matches the RLS policy guard exactly, so the planner can prove that any row in the index satisfies both conditions without re-evaluating them.

The ON ONLY caveat: Just like with policies, ON ONLY creates the index definition on the parent but does not automatically create it on existing partitions. You need a corresponding index on each partition. For new partitions created after the parent index exists, PostgreSQL will attach automatically — but existing partitions require a migration step.

-- Per-partition index (run for each of the 74 partitions)
CREATE INDEX ix_docs_tbl_0_userid_createddate_rls
    ON dbo.docs_tbl_0 USING btree (userid, createddate DESC)
    WHERE retentionstatus = 0
      AND createddate IS NOT NULL;

Performance Impact

After deploying both the RLS policies and the composite partial indexes across the partition fleet, we re-ran the same EXPLAIN (ANALYZE, BUFFERS) queries.

Before:

• Index used: Primary key (userid, docid)
• Rows removed by filter: ~56,000 per query
• Plan node: Seq Scan on filtered output + Filter step

After:

• Index used: ix_docs_tbl_userid_createddate_rls
• Rows removed by filter: 0
• Plan node: Index Scan with RLS predicate pushed into index condition
• Heap fetches: near zero (index-only scan eligible after VACUUM)

The key insight: when the index’s WHERE clause exactly matches the RLS policy's USING expression, PostgreSQL's planner can prove that any row returned by the index is already RLS-compliant. The filter disappears from the plan entirely.

Common Pitfalls Summary

PitfallWhat HappensFixRESTRICTIVE policy without a PERMISSIVE one0 rows returned for all usersUse PERMISSIVE to define what is allowedNo FORCE ROW LEVEL SECURITYTable owner silently bypasses RLSAlways set FORCE ROW LEVEL SECURITYPolicy defined ON ONLY parentPartitions not coveredEnable RLS and create policies per partitionIndex not matching RLS predicatePost-scan filter removes rows at query timeAlign index WHERE clause with RLS USING expressionNo NULL guard on date columnUndated rows visible to all rolesAdd AND createddate IS NOT NULL to the policy

Key Takeaways

1. RLS policy type matters more than you think. RESTRICTIVE-only with no PERMISSIVE policy silently blocks everything. Use PERMISSIVE to define what rows are visible, not what rows are blocked.
2. FORCE ROW LEVEL SECURITY is not optional if you need strict enforcement. Without it, the table owner role bypasses all policies.
3. Declarative partitioning does not inherit RLS policies or indexes automatically. Plan for a partition-level rollout script from the start.
4. RLS predicates become invisible query filters. If your indexes don’t align with the USING expression, you'll pay the cost on every query through post-scan filtering — at scale, this matters.
5. Partial indexes aligned to your RLS policy are the performance key. When the index WHERE clause matches the RLS USING expression, the planner eliminates the filter step entirely.

We run this pattern across multiple high-volume tables — each with 70+ hash partitions, hundreds of millions to billions of rows, across 7 AWS regions. The approach scales. The gotchas are real but survivable.

Implementing Row-Level Security on Billion-Row Hash-Partitioned PostgreSQL Tables was originally published in DataDrivenInvestor on Medium, where people are continuing the conversation by highlighting and responding to this story.