Auditing access to sensitive location data

This page builds the audit trail a spatial pipeline needs when its coordinates identify people: an inventory of who can read which geometry, a log of who actually did, a sensitivity classification that follows lineage downstream, and an alert when someone reads precise coordinates instead of the masked copy.

When to use this approach

  • Your geometry identifies individuals. Home locations, trip origins, device pings and delivery addresses all do, however aggregated the dashboard looks.
  • A policy exists but nobody can evidence compliance with it. Grants are a statement of intent; the query log is the evidence. The masking side is in masking precise coordinates for privacy.
  • A review or incident is coming. “Who could have seen this?” and “who did?” are different questions and both need answering.

Prerequisites

  • Query-log access: pg_stat_statements plus log_statement on PostGIS, QUERY_HISTORY on Snowflake, INFORMATION_SCHEMA.JOBS on BigQuery.
  • A separated schema layout, so precise and masked copies are distinguishable by grant rather than by convention.
  • A sensitivity classification agreed with whoever owns the policy — the audit implements a decision, it does not make one.
  • Somewhere to retain audit output for the period the policy requires.

Step-by-step instructions

1. Classify the columns, in metadata

yaml
# models/staging/schema.yml
models:
  - name: stg_trip_pings
    meta:
      sensitivity: restricted
      contains_personal_location: true
      lawful_basis: "legitimate interest, DPIA 2026-04"
    columns:
      - name: geom
        description: Precise device position. Restricted; use the masked mart unless you have a documented need.
        meta:
          sensitivity: restricted
          masking_available: mart_pings_masked.geom_h3

Verify every geometry column in the project carries a classification:

bash
dbt parse
python - <<'PY'
import json
m = json.load(open('target/manifest.json'))
gaps = []
for n in m['nodes'].values():
    if n['resource_type'] != 'model':
        continue
    for cname, c in (n.get('columns') or {}).items():
        if 'geom' in cname and not (c.get('meta') or {}).get('sensitivity'):
            gaps.append(f"{n['name']}.{cname}")
print('unclassified geometry columns:', gaps)
PY
Sensitivity tiers and who is granted each one Three tiers of the same location data. Restricted precise coordinates in the staging schema are granted only to the pipeline role. Internal grid-cell aggregates in the mart schema are granted to analysts. Public zone-level counts in the serving schema are granted to the API role and dashboards. An arrow shows the intended path of a question, moving down the tiers, and a note says an analyst reaching the top tier is the event the audit exists to detect. restricted staging · precise device coordinates pipeline role only no human grants internal marts · grid cells and aggregates analyst role named individuals shareable serving · zone-level counts API and dashboards broad grants An analyst querying the top band is exactly the event this audit exists to surface.

2. Inventory who can read what

Grants drift. An inventory query run on a schedule turns “we granted that once” into a current fact.

sql
-- models/ops/int_geometry_grants.sql
select
    t.table_schema,
    t.table_name,
    c.column_name,
    g.grantee,
    g.privilege_type
from information_schema.columns c
join information_schema.tables t
  on t.table_schema = c.table_schema and t.table_name = c.table_name
join information_schema.role_table_grants g
  on g.table_schema = t.table_schema and g.table_name = t.table_name
where c.udt_name in ('geometry', 'geography')
  and g.grantee not in ('postgres', 'dbt_runner')
order by t.table_schema, t.table_name, g.grantee
sql
-- tests/assert_no_broad_grants_on_restricted.sql
select table_schema, table_name, grantee
from {{ ref('int_geometry_grants') }}
where table_schema = 'staging'
  and grantee not in ('dbt_runner', 'pipeline_role')

Verify the test fails when it should, by granting SELECT to a test role and running it. A control that has never fired is not a control.

3. Log and attribute the reads

sql
-- models/ops/int_sensitive_access_log.sql
{{ config(materialized = 'incremental', unique_key = ['queryid', 'captured_at']) }}

select
    s.queryid,
    current_timestamp                              as captured_at,
    r.rolname                                      as db_user,
    s.calls,
    s.total_exec_time,
    s.query
from pg_stat_statements s
join pg_roles r on r.oid = s.userid
where s.query ilike '%staging.stg_trip_pings%'
   or s.query ilike '%staging.stg_device_home%'
{% if is_incremental() %}
  and current_timestamp > (select max(captured_at) from {{ this }})
{% endif %}

On a warehouse the equivalent is far richer — Snowflake’s ACCESS_HISTORY names the columns each query touched, and BigQuery’s audit logs do the same — so prefer those where available and keep the text-matching approach for PostGIS.

sql
-- Snowflake: attribute access by column rather than by query text
select
    query_start_time, user_name,
    f.value:"objectName"::string   as object_name,
    c.value:"columnName"::string   as column_name
from snowflake.account_usage.access_history,
     lateral flatten(base_objects_accessed) f,
     lateral flatten(f.value:"columns") c
where c.value:"columnName"::string ilike '%geom%'
  and query_start_time > dateadd(day, -7, current_timestamp())

Verify the log captures a read you make deliberately — query the restricted table as a test user and confirm the row appears within the collection interval.

4. Alert on the pattern that matters

Volume of access is not the signal. The signal is the kind: a human role reading precise geometry when a masked equivalent exists.

sql
-- tests/assert_no_unmasked_human_access.sql
select
    db_user,
    count(*) as reads,
    max(captured_at) as last_read
from {{ ref('int_sensitive_access_log') }}
where captured_at > current_date - interval '1 day'
  and db_user not in ('dbt_runner', 'pipeline_role')
group by db_user
having count(*) > 0
yaml
models:
  - name: int_sensitive_access_log
    description: >
      Reads of restricted geometry, by role. Any row with a human role is an
      exception requiring a documented reason; the pipeline role is expected.
    tests:
      - dbt_utils.expression_is_true:
          expression: "captured_at > current_date - interval '400 days'"

Verify the alert routes somewhere with an owner. An access alert that lands in a shared channel becomes background noise faster than any other kind.

Two questions an audit must answer, and the evidence for each On the left, the question of who could have read the data, answered by the grant inventory from the catalog, which is a statement about permissions. On the right, the question of who did read it, answered by the query or access history, which is a statement about events. A note beneath explains that a review needs both, because grants without logs prove nothing happened and logs without grants cannot show what was possible. "who could have read it?" role_table_grants a statement about permissions re-inventoried on a schedule, because grants drift "who actually did?" access_history · pg_stat_statements a statement about events retained for the policy's period, not the log's default Grants without logs prove nothing happened; logs without grants cannot show what was possible.

5. Make the safe path the easy one

An audit that only catches people is a poor control. The complementary move is to make the masked copy so convenient that reaching for the precise one is a deliberate act.

sql
-- models/marts/mart_pings_masked.sql
{{ config(
    materialized = 'table',
    post_hook = "GRANT SELECT ON {{ this }} TO ROLE analyst"
) }}

select
    ping_id,
    trip_id,
    date_trunc('hour', observed_at)          as observed_hour,
    {{ h3_cell('geom', var('privacy_resolution', 8)) }} as geom_h3,
    zone_id
from {{ ref('int_pings_zoned') }}

At H3 resolution 8 a cell is roughly three quarters of a square kilometre, which supports density analysis and does not identify a household. Suppress cells below a count threshold as well, so a single event in a sparse area cannot be re-identified by inspection.

Verify the masked mart answers the questions people actually ask, by checking what the restricted table is being read for — if the answer is “something the masked copy cannot do”, the masking resolution is wrong rather than the analyst.

What a masked copy preserves and what it removes The same twelve events shown twice. On the left, precise points scattered across a residential block, where a single point identifies a household. On the right, the same events snapped to grid cells with counts, where a cell holding one event is suppressed entirely. Labels note that the density pattern survives the masking while the individual position does not. precise coordinates one point identifies a household grid cells with suppression 3 4 3 — — density survives; cells below the threshold are dropped

Configuration reference

Element Where Note
meta.sensitivity model and column meta Machine-checkable classification that travels with lineage
grant inventory ops model, scheduled Catches drift between the policy and reality
ACCESS_HISTORY / pg_stat_statements ops model Who read what, retained per policy
privacy_resolution project var H3 r8 ≈ 0.7 km²; tune to the re-identification risk
count suppression masked mart Stops a lone event identifying someone
audit retention ops model The policy’s period, which usually exceeds the log’s default

Gotchas & edge cases

  • pg_stat_statements normalises and can truncate query text, so table names may be missing from long queries. Use it as a signal, not as complete evidence, and enable statement logging where completeness is required.
  • A view over restricted data inherits its sensitivity, not its grants. Someone with access to the view reads the underlying coordinates; classify views explicitly.
  • Aggregation is not anonymisation. A count of one in a cell identifies a person exactly as well as the coordinate did; suppression thresholds are part of the masking, not an optional extra.
  • Audit tables are themselves sensitive. A log of who queried home locations is a map of investigative interest; grant it as narrowly as the data it protects.
  • Row-level security and column grants interact. Test the combination rather than each separately, following implementing row-level security for geospatial data.

FAQ

Is a query log enough, or do I need column-level access history?

Column-level is much better where the engine offers it — Snowflake and BigQuery both do — because it answers the question directly instead of by inference from query text. On PostGIS, text matching over pg_stat_statements is the pragmatic substitute, with statement logging for the tables that most need certainty.

How long should access logs be retained?

For the period the governing policy specifies, which is usually longer than the database’s own log retention — hence copying them into a model you control. A year is a common floor; the important thing is that the retention is a decision rather than a default nobody chose.

What masking resolution is defensible?

The one that supports the analysis while making re-identification implausible, which depends on population density and on what else is published. Grid cells around a square kilometre with a suppression threshold work for most urban mobility analysis; sparse rural data needs coarser cells for the same protection.

Should the audit block access or only record it?

Record, and let grants do the blocking. An audit that tries to prevent access duplicates the permission system badly; its job is to show that the permission system is configured as intended and that the exceptions were deliberate.

Up: Part of Data Security & Scoping Rules.