Row-Level Security for PostGIS Lineage Tables

Part of: Access Control and Redaction for Lineage Records

Lineage tables carry facts about datasets a given user may have no right to know exist. Filtering those rows in application code works until somebody connects with a database client, writes a report against the same tables, or adds a second service. Row-level security moves the filter into the database, where every path to the data goes through it. This how-to applies it to lineage specifically, where the policies are unusual because a row’s sensitivity depends on entities in other tables.

The property that makes this worth the effort: row-level security is the only place a filter cannot be forgotten. Every application-layer filter is one code path away from being bypassed, and lineage is queried from more places than anyone anticipates.

Prerequisites

  • A role per access class, not a role per person, per Access Control and Redaction for Lineage Records.
  • A classification on each dataset that policies can key on.
  • Connection pooling that sets the session role reliably.
  • A test suite that runs as each role.

The Edge Table Is the Hard Part

Node visibility is easy; edge visibility is where the design decisions live, because an edge connects two entities that may have different classifications.

Three policies for a cross-classification edge Hiding the edge breaks the chain silently; masking the far end preserves the structure while withholding identity; showing it fully leaks existence. POLICY USER SEES LEAKS hide the edge strictest a dataset with no ancestry — looks like an origin nothing mask the far end usually right "derived from a restricted source" — chain intact that something exists, not what show it fully no policy on edges everything, including the restricted identifier the classification

The middle policy is right for most organisations because the top one is actively misleading. A user shown a dataset with no ancestry will conclude it is original data, and that conclusion will end up in a document. Telling them a source exists and is not visible to them is both more honest and less informative than they might guess.

Implement masking with a view rather than by weakening the policy. The base table policy hides the restricted node entirely; a view over the edge table substitutes a placeholder identifier where the far end is invisible. Two mechanisms, each doing one thing.

The disclosure in the middle policy needs signing off rather than assuming. “A restricted source exists” is itself information, and in some settings — a redaction that is meant to be undetectable — it is exactly what must not leak. Ask, and record the answer.

Policies Key on Classification, Not on Identity

A policy enumerating dataset identifiers is unmaintainable within a month, and it fails open for every dataset created afterwards.

Key the policy on a classification column carried by the entity table, and join to it from the lineage tables. New datasets then inherit a policy by virtue of their classification, and the only way to create an unprotected row is to leave the classification null.

Make the classification NOT NULL with no default. A default of “public” is the failure mode that produces a breach: somebody adds a dataset, forgets to classify it, and it is visible to everyone. A default of “restricted” fails safe but trains people to reclassify without thinking. No default forces a decision at insert.

Write the policy so that an unrecognised classification denies rather than permits. A new classification added by a migration should be invisible until a policy explicitly admits it, which turns an incomplete rollout into a support ticket instead of an incident.

Force the Policy on Everyone, Including the Owner

The default in PostgreSQL is that table owners bypass row-level security, and this surprises people during the incident rather than during the review.

Three routine bypasses of row-level security Ownership, superuser, and the BYPASSRLS role attribute each defeat the policy silently, and each has a specific mitigation. the table owner bypasses by default — the migration role is usually the owner, and often the app role too FORCE ROW LEVEL SECURITY superuser connections bypass unconditionally — backups, ad-hoc fixes, some monitoring agents no superuser in app paths roles with BYPASSRLS granted once for a data load and never revoked audit pg_roles on a schedule Every bypass is silent. The query returns more rows and reports no error.

The silence noted at the bottom is what makes these worth a scheduled check. A bypass produces no log entry, no warning and no failed test unless a test specifically asserts the row count as a restricted role — everything simply works, with more data than intended.

Use FORCE ROW LEVEL SECURITY on every lineage table without exception, and own the tables with a role that no application ever connects as. The separation costs one migration and removes the largest category of accidental bypass.

Audit pg_roles for rolbypassrls and rolsuper on a schedule, and alert on any change. Grants made during an incident are the ones nobody remembers to revoke.

Set the Role, Do Not Trust the Application

Row-level security is only as good as the session context, and pooled connections are where that context goes wrong.

Set the role or the session variable at the start of every transaction, not at connection time. A pooled connection returned to the pool retains whatever context the last user set, and the next borrower inherits it.

Prefer SET LOCAL so the setting reverts at transaction end automatically. It removes an entire class of bug in which an early return skips the reset.

Never derive the session identity from a parameter the client supplied. The role must come from the authenticated session on the server side; a client-supplied user identifier turns row-level security into a suggestion.

Fail closed when the context is unset. A policy that permits when the session variable is null is a policy that permits every connection that forgot to set it, which is the failure mode this section exists to prevent.

Performance Follows the Policy Predicate

A policy is a predicate appended to every query, and a badly shaped one turns indexed lookups into scans.

Keep the predicate a simple comparison against an indexed column wherever possible. A policy that calls a function per row, or performs a subquery join to a permissions table, multiplies the cost of every read against the table.

Where a join is unavoidable, materialise the permission set into a small table keyed for the lookup, and index it. The policy then joins against a table the planner understands rather than executing logic it cannot cost.

Measure with EXPLAIN as the restricted role, not as the owner. The plan the owner sees does not include the policy at all, which is exactly why the performance problem is discovered in production.

Write Policies Are as Important as Read Policies

Row-level security is usually introduced to stop the wrong people reading, and a read-only policy set leaves the more damaging half open.

All four commands need a policy A read-only policy set leaves insert, update and delete unrestricted, each of which corrupts the record in a different way. SELECT the one everyone implements unauthorised disclosure restricts which rows are visible INSERT needs a WITH CHECK clause fabricated provenance or any role can assert any lineage UPDATE needs USING and WITH CHECK history rewritten one bounds the rows, one the result DELETE usually should be denied outright evidence destroyed lineage is append-only in most designs

The UPDATE row carries the subtlety most often missed. USING decides which rows may be updated and WITH CHECK decides what they may become; a policy with only the first lets a role move a row it can see into a classification it cannot, and the row then vanishes from its own view while remaining changed.

Deny DELETE to every application role by default. Lineage that can be deleted is lineage that can be denied, and the cases needing removal — a retention expiry, a legal erasure — are rare enough to run as a controlled operation rather than a standing grant.

Policy INSERT even where only one service writes. The point is that a second writer added next year inherits the constraint rather than needing somebody to remember it existed.

Verification

Assert row counts per role against a fixture where every classification is represented. This is the core test and it must run as each role rather than as the owner.

Assert the fail-closed path by connecting with no session context set and confirming zero rows rather than all rows. This is the single most valuable assertion here.

Assert that a newly inserted row with an unrecognised classification is invisible to every role. It proves the deny-by-default branch exists.

Assert FORCE ROW LEVEL SECURITY is set on every lineage table by querying the catalogue in the test suite. A table added later without it is otherwise undetectable until it leaks.

Gotchas & edge cases

  • Aggregates leak counts. A user denied the rows can still learn how many exist if a count is computed before the policy is applied in some views. Check aggregate paths separately.
  • Foreign key errors leak existence. An insert failing because it references a row the user cannot see reports a constraint violation naming it. Validate references in the application first, and return a generic error.
  • COPY and logical replication bypass policies. Replication streams the whole table. A replica for reporting must carry the policies too, or it is an unprotected copy.
  • Policies do not apply to indexes. An index-only scan still respects the policy in PostgreSQL, but statistics and index metadata can reveal value distributions. Treat highly sensitive columns as needing more than a row policy.
  • Migrations run as the owner. A migration that copies data between tables can move restricted rows into an unrestricted one without any policy objecting. Review migrations touching lineage tables specifically.