Implementing Row-Level Security for Geospatial Data

This page shows you how to restrict which geometries a query can return based on a user’s permitted zone — by compiling spatial predicates into your dbt models at build time, pre-filtering with a bounding-box operator, and confirming an exact ST_Intersects topology check, so no unauthorized coordinate ever reaches the BI layer.

Standard row-level security filters on a scalar key — a tenant ID, a region code, a classification tier. Spatial row-level security (RLS) filters on topology: a row is visible only if its geometry falls inside, touches, or sits within a buffer distance of a polygon the user is entitled to see. That single difference — comparing geometries instead of scalars — is what forces every decision below: coordinate reference system (CRS) alignment, spatial indexing, and the order in which predicates evaluate.

When to use this approach

Spatial RLS is the right tool when visibility depends on where a record is, not who owns it. Reach for it — versus simpler alternatives — when:

• Visibility is defined by physical boundaries, not a foreign key. If access maps cleanly to a tenant_id or region_code, use ordinary column-based scoping from the parent Data Security & Scoping Rules guide instead — topology evaluation is pure overhead when a scalar equality would do.
• A user’s grant is a polygon or a radius. Service territories, jurisdictional zones, and proximity buffers around critical infrastructure all need point-in-polygon or ST_DWithin containment that no WHERE tenant = ... clause can express.
• The filter must be version-controlled and tested with the pipeline. Compiling the predicate into dbt — rather than relying solely on warehouse-side CREATE POLICY — keeps scoping in code review and CI. The two are complementary; this page covers the dbt-compiled layer and notes where to hand off to a database policy.

This pattern assumes geometries are already normalized to one CRS. If your feeds arrive in mixed projections, resolve that first with spatial reference system management — an RLS predicate that compares geometries in different SRIDs silently passes rows it should exclude.

Prerequisites

• dbt Core >= 1.6 (or dbt Cloud on the equivalent runtime) so var() defaults resolve consistently across environments.
• Adapter / extension: dbt-postgres >= 1.6 with PostGIS >= 3.1 for stable GiST cost estimates on ST_Intersects. DuckDB users need the spatial extension — see the DuckDB spatial extension integration guide for CI parity. The engine itself is provisioned in setting up PostGIS with dbt.
• Grants: the dbt service role needs SELECT on the access-zone source and CREATE/USAGE on the target schema. If you also enforce a warehouse policy, the role that creates the policy must differ from the role that queries through it.
• Environment variables: never inline a user identifier or SRID. Resolve session context through env_var() and run-level scope through var(), so the same models deploy unchanged from staging to production.

Step-by-step instructions

1. Materialize a deterministic access-zone table

Map each user (or role) to the polygon they may see. Materialize as a table so it can carry a persistent spatial index and resolve joins fast. Validate every boundary at build time and normalize its SRID — an invalid or mis-projected polygon is the most common cause of a filter that silently leaks.

-- models/staging/stg_user_access_zones.sql
{{ config(materialized='table') }}

SELECT
  user_id,
  access_level,
  ST_SetSRID(ST_GeomFromText(zone_boundary_wkt), {{ var('canonical_srid', 4326) }}) AS access_geometry
FROM {{ source('identity', 'user_access_zones') }}
WHERE is_active = TRUE
  AND ST_IsValid(ST_GeomFromText(zone_boundary_wkt))

Verify no row was dropped for invalidity and every geometry shares one SRID:

SELECT count(*) AS zones, count(DISTINCT ST_SRID(access_geometry)) AS distinct_srids
FROM {{ ref('stg_user_access_zones') }};
-- expect distinct_srids = 1

2. Add a GiST index with a post-hook

Without a spatial index the engine computes exact topology for every candidate row. Add the index after the table materializes, then ANALYZE so the planner prefers an index scan.

-- config block for stg_user_access_zones.sql
{{ config(
    materialized='table',
    post_hook=[
      "CREATE INDEX IF NOT EXISTS idx_access_geom ON {{ this }} USING GIST (access_geometry)",
      "ANALYZE {{ this }}"
    ]
) }}

Confirm the index exists:

SELECT indexname FROM pg_indexes WHERE tablename = 'stg_user_access_zones';
-- expect idx_access_geom in the result

3. Wrap the predicate in a reusable scope macro

Centralize the spatial filter so every model applies it identically. The macro emits a bounding-box pre-filter (&&) and an exact ST_Intersects, both reading the active user’s polygon. The && operator hits the GiST index and prunes the candidate set cheaply; ST_Intersects then confirms true topology only on survivors.

-- macros/apply_spatial_rls.sql
{% macro apply_spatial_rls(geometry_column, user_table, user_id_expr="current_setting('app.user_id')") %}
  EXISTS (
    SELECT 1
    FROM {{ user_table }} AS z
    WHERE z.user_id = {{ user_id_expr }}
      AND {{ geometry_column }} && z.access_geometry
      AND ST_Intersects({{ geometry_column }}, z.access_geometry)
  )
{% endmacro %}

Using EXISTS (rather than a scalar subquery joined into the FROM clause) prevents row multiplication when a user owns more than one zone, and lets the planner push the spatial predicate down.

4. Gate the fact model with the macro

Apply the macro in the WHERE clause of every model that exposes sensitive geometry. The session sets app.user_id before querying; the compiled predicate does the rest.

-- models/marts/fct_spatial_events.sql
{{ config(materialized='incremental', unique_key='event_id') }}

SELECT
  e.event_id,
  e.event_timestamp,
  e.location_geom,
  e.metric_value
FROM {{ ref('stg_raw_events') }} e
WHERE {{ apply_spatial_rls('e.location_geom', ref('stg_user_access_zones')) }}
{% if is_incremental() %}
  AND e.event_timestamp > (SELECT max(event_timestamp) FROM {{ this }})
{% endif %}

5. Verify index usage and scoping with the query plan

Set a user context and read the plan. You want an Index Scan on idx_access_geom, not a sequential scan, and a row count that matches only the events inside that user’s zone.

SET app.user_id = '42';
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM analytics.fct_spatial_events;
-- expect "Index Scan using idx_access_geom" and no rows outside zone 42

6. Add a dbt test that fails closed

A scoping bug should break the build, not leak quietly. Assert that no row in the secured mart escapes the union of all active zones.

-- tests/assert_no_unscoped_geometry.sql
SELECT f.event_id
FROM {{ ref('fct_spatial_events') }} f
WHERE NOT EXISTS (
  SELECT 1 FROM {{ ref('stg_user_access_zones') }} z
  WHERE f.location_geom && z.access_geometry
    AND ST_Intersects(f.location_geom, z.access_geometry)
)
-- returns 0 rows when scoping holds

Configuration reference

Gotchas & edge cases

• SRID drift passes rows silently. If location_geom is SRID 3857 and access_geometry is 4326, PostGIS raises an error; some engines instead return no match and the filter looks like it “works” while excluding everything. Pin one SRID in staging and assert it with the test from step 1.
• && is necessary but not sufficient. The bounding-box operator only checks envelopes, so a point in a polygon’s bounding box but outside its true shape passes &&. The exact ST_Intersects is what enforces real containment — never ship the pre-filter alone.
• Self-intersecting polygons void the predicate. An invalid access boundary makes ST_Intersects undefined and can throw mid-query. Run ST_IsValid in staging and repair with ST_MakeValid before the zone reaches the macro.
• Multi-zone users and EXISTS. A scalar subquery (= (SELECT ... LIMIT 1)) silently drops all but one zone for users who own several. EXISTS is the correct shape; keep it.
• Boundary points and ST_Intersects vs ST_Contains. ST_Intersects returns true for geometries that only touch the boundary. If a point exactly on a shared border must belong to one zone only, switch that predicate to ST_Contains and document the rule.
• NULL geometries. Rows with a NULL location_geom never satisfy ST_Intersects and are excluded — usually correct, but quarantine them to an error table rather than dropping them so the gap is auditable.

FAQ

Related

• Data Security & Scoping Rules — the column-based scoping, scope macros, and audited validation tests this row-level pattern extends.
• Automating CRS Conversions in dbt Pipelines — normalize SRIDs before any RLS predicate evaluates.
• Tracking Spatial Schema Changes Across Environments — keep access-zone and geometry schemas in lockstep as boundaries evolve.

Up: Part of Data Security & Scoping Rules.