RBAC and a masked-view pattern — and the breach that wasn't

The screenshot that almost ended someone's career

A junior analyst at a healthtech I worked with was demoing a dashboard in a screenshare. The dashboard showed a table of recent signups — including their email, full name, and the medical condition they'd entered during onboarding.

Nobody in the meeting needed that data. The analyst just SELECT'd from users because that's what the BI tool's connector pointed at. The clip was almost emailed to a vendor. Compliance caught it three minutes before send.

The fix wasn't training; it was never giving the BI tool's account access to PII columns in the first place. The masked-view pattern below — one view per role, exposing only what that role needs — is the entire defense. It's mechanical, not vigilant.

Role × table grant matrix

                       │ oltp.       │ analytics.       │ analytics.
                       │ customers   │ customers_safe   │ customers_finance
  ─────────────────────┼─────────────┼──────────────────┼──────────────────
  analyst              │      ✗      │       ✓          │        ✗
  finance              │      ✗      │       ✓          │        ✓
  data_engineer        │      ✗      │       ✓          │        ✓
  pii_admin            │      ✓      │       ✓          │        ✓
  service_etl  (R/W)   │      ✓      │       ✗          │        ✗

  ✓ = SELECT granted          ✗ = REVOKE'd / never granted
  service_etl writes to oltp.customers and is what populates the views;
  no human authenticates as service_etl.

The defense isn't training; it's the matrix. The analyst's BI tool authenticates as analyst — there is no SQL it can write that returns PII, because the rows don't exist in that role's universe. Adding a role is one row in the table; granting a column is one line of SQL. Mechanical, not vigilant.

-- One view that exposes everything but the PII columns:
CREATE VIEW analytics.customers_safe AS
SELECT
  customer_id,
  -- name and email replaced with stable hashes — analysts can join on them
  -- without seeing them.
  encode(digest(name, 'sha256'), 'hex')  AS name_hash,
  encode(digest(email, 'sha256'), 'hex') AS email_hash,
  country,
  plan,
  created_at,
  loaded_at
FROM oltp.customers;

-- Permissions: analyst can only see the safe view, never the source.
GRANT SELECT ON analytics.customers_safe TO analyst;
REVOKE ALL ON oltp.customers FROM analyst;

-- A second view for finance, who needs the email for receipts:
CREATE VIEW analytics.customers_finance AS
SELECT customer_id, name, email, country, plan, created_at FROM oltp.customers;
GRANT SELECT ON analytics.customers_finance TO finance;

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY orders_tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id', /* missing_ok */ true)::bigint);

-- Set the tenant on session start (in your connection pooler):
SET app.tenant_id = '42';