RBAC Drift Detection & Compliance Sync

Configuring drift thresholds for staging vs production

This page shows how to run a single RBAC drift detector against multiple environments while applying a different alert threshold to each one — so staging’s constant, expected looseness never buries the on-call queue, and production’s zero-tolerance posture still catches the one grant that matters — using one versioned manifest per environment, an environment-aware evaluator, and a dry-run replay before the policy goes live.

Per-environment thresholding is where an abstract intuition (“staging is noisy, prod is not”) becomes a concrete, machine-verifiable number attached to each tier. It is the calibration layer of Threshold Tuning for Alerts: it consumes the composite severity produced by Rule-Based Drift Scoring, reads the per-tier snapshots produced by Environment Comparison Workflows, and decides, per environment, which scored deltas cross the line into an alert.

When to configure per-environment thresholds — and when not to

Use the workflow on this page when:

  • One detector fans out across dev, staging, and production and a single global threshold either floods you in the lower tiers or lets real drift slip under the line in the top tier.
  • Your lower environments legitimately hold looser grants — CI service accounts with broad SELECT, ephemeral test roles, synthetic-data schemas — that would score as violations under a production-calibrated policy.
  • An auditor needs evidence that production is held to a stricter access-control bar than staging, and you need a versioned artifact that proves the two policies differ deliberately.

Do not reach for this when:

  • You run one environment only. A single threshold manifest is simpler and per-environment machinery is pure overhead.
  • The “noise” in staging is actually unscored drift. Fix Rule-Based Drift Scoring first — thresholding over an unscored delta stream is just a delta counter, and no per-environment tuning will rescue it.
  • The looseness you want to tolerate is a small set of known, pre-approved grants. Those belong in Exception Routing and Whitelisting, not encoded as a slacker threshold that also lets unknown drift through.

Step 1: Express one threshold policy per environment as versioned config

Keep the policy out of code. Declare a table of environments, each with its own alert cutoff on the 0.0–1.0 composite drift index, the persistence it demands before firing, and the transport tier it routes to. A tomllib-loadable, pydantic-validated manifest gives you review-via-PR and startup validation for free.

# thresholds.toml — one section per environment, checked into version control
schema_version = 1

[environments.staging]
alert_at        = 0.40   # composite drift index that raises an alert
clear_at        = 0.25   # hysteresis floor: an open alert clears below this
debounce_cycles = 2      # must persist this many detection cycles
ttl_seconds     = 14400  # informational until drift outlives this (4h)
tier            = "digest"

[environments.production]
alert_at        = 0.15
clear_at        = 0.10
debounce_cycles = 1      # fire on first breach — no waiting
ttl_seconds     = 0      # zero tolerance: no grace window
tier            = "page"
import tomllib
from pydantic import BaseModel, model_validator
from typing import Self

class EnvThreshold(BaseModel):
    alert_at: float
    clear_at: float
    debounce_cycles: int
    ttl_seconds: int
    tier: str

    @model_validator(mode="after")
    def _hysteresis_gap(self) -> Self:
        if self.clear_at >= self.alert_at:
            raise ValueError("clear_at must be strictly below alert_at")
        return self

class ThresholdPolicy(BaseModel):
    schema_version: int
    environments: dict[str, EnvThreshold]

def load_policy(path: str) -> ThresholdPolicy:
    with open(path, "rb") as fh:
        return ThresholdPolicy.model_validate(tomllib.load(fh))

Verification: load_policy("thresholds.toml").environments["production"].alert_at returns 0.15, and inverting any band (clear_at >= alert_at) raises a ValidationError at load time rather than misfiring at 3 a.m.

Step 2: Calibrate each environment’s cutoff against a real baseline

Do not pick alert_at by feel. Measure the routine per-principal grant volume in each tier under a read-only transaction, and set the staging cutoff above its normal churn and the production cutoff just above zero. This calibration query never writes — it is executed by a detection principal with only pg_monitor / read visibility.

-- Per-grantee object-grant volume, PostgreSQL. Run against each environment.
SELECT
    grantee                         AS role_name,
    count(*)                        AS grant_count,
    count(*) FILTER (WHERE is_grantable = 'YES') AS delegable
FROM information_schema.role_table_grants
WHERE grantee NOT IN ('postgres', 'PUBLIC')
GROUP BY grantee
ORDER BY grant_count DESC;

Feed a week of these snapshots into a small calibrator that expresses the cutoff as a percentile of observed drift index rather than a guessed constant, so the threshold tracks each tier’s actual behaviour:

from statistics import quantiles

def suggest_alert_at(historical_indices: list[float], keep_top_pct: float) -> float:
    """Suggest an alert_at so only the top keep_top_pct of drift fires.

    keep_top_pct=0.05 -> alert on roughly the noisiest 5% of cycles (staging);
    keep_top_pct=0.40 -> alert on the top 40% (production, near zero-tolerance).
    """
    if not historical_indices:
        return 0.15
    cut = quantiles(sorted(historical_indices), n=100)[round((1 - keep_top_pct) * 100) - 1]
    return round(cut, 3)

Verification: on a staging history whose drift index rarely exceeds 0.35, suggest_alert_at(history, 0.05) returns a value near 0.40 — comfortably above routine churn. Run it on the production history and it should collapse toward 0.15 because production drift is near-zero by design.

Step 3: Evaluate scored deltas against the environment’s own band

Each scored delta already carries its environment tag from the snapshot stage. Select the matching threshold, apply hysteresis and debounce statefully per (environment, grantee, rule) key, and emit a structured event — never DDL.

from dataclasses import dataclass, field

@dataclass
class ScoredDelta:
    environment: str          # "staging" | "production"
    grantee: str
    rule: str
    drift_index: float        # composite severity, 0.0-1.0
    statement: str            # e.g. 'GRANT ALL ON schema public TO svc_x'

@dataclass
class KeyState:
    firing: bool = False
    consecutive: int = 0

def evaluate(delta: ScoredDelta,
             policy: ThresholdPolicy,
             state: dict[tuple[str, str, str], KeyState]) -> dict | None:
    band = policy.environments[delta.environment]
    key = (delta.environment, delta.grantee, delta.rule)
    st = state.setdefault(key, KeyState())

    if delta.drift_index >= band.alert_at:
        st.consecutive += 1
    elif delta.drift_index < band.clear_at:
        st.consecutive = 0
        st.firing = False           # hysteresis: only clear below clear_at
        return None
    else:
        return None                 # inside the hysteresis gap: hold, no change

    if st.consecutive >= band.debounce_cycles and not st.firing:
        st.firing = True
        return {
            "environment": delta.environment,
            "grantee": delta.grantee,
            "rule": delta.rule,
            "drift_index": delta.drift_index,
            "tier": band.tier,
            "statement": delta.statement,
        }
    return None

Verification: feed the evaluator a drift_index of 0.30 tagged staging (below staging’s 0.40) and it returns None; feed the identical value tagged production (above prod’s 0.15) and after debounce_cycles it returns a page-tier event. Same delta, opposite outcome — that difference is the entire point of per-environment thresholds.

Step 4: Dry-run the new policy by replaying history before it goes live

Never promote a threshold change straight to the live pager. Replay recorded deltas through the candidate policy in shadow mode and diff the alert set against what the current policy would have produced. This is a read-only exercise — no database mutation, no transport dispatched.

def replay(deltas: list[ScoredDelta], policy: ThresholdPolicy) -> list[dict]:
    """Shadow-execute a candidate policy over recorded deltas. Emits no pages."""
    state: dict[tuple[str, str, str], KeyState] = {}
    fired = [evaluate(d, policy, state) for d in deltas]
    return [event for event in fired if event is not None]

def regression(deltas, current: ThresholdPolicy, candidate: ThresholdPolicy) -> dict:
    now = {(e["environment"], e["grantee"], e["rule"]) for e in replay(deltas, current)}
    new = {(e["environment"], e["grantee"], e["rule"]) for e in replay(deltas, candidate)}
    return {"newly_alerting": sorted(new - now), "newly_silenced": sorted(now - new)}

Verification: replay a set of past deltas that includes a real production GRANT ALL incident and confirm it appears in newly_alerting under the candidate (or was already firing), while a batch of known-benign staging CI grants appears in newly_silenced. If a real production incident lands in newly_silenced, the candidate loosened production — stop and re-tune before promoting.

One delta, two environment thresholds, opposite outcomes A single scored delta with a composite drift index of 0.30 is evaluated against two per-environment threshold lanes drawn as number lines from 0.0 to 1.0. The staging lane places its alert_at cutoff at 0.40 and shades the region above it as the alert zone; the 0.30 delta sits to the left of that cutoff, inside the safe zone, so it produces no event and the digest queue stays quiet. The production lane places its alert_at cutoff at 0.15 and shades a much wider alert zone above it; the same 0.30 delta now sits to the right of the cutoff, inside the alert zone, so after the debounce it fires a page-tier event. The identical delta value routes to opposite outcomes purely because each environment carries its own cutoff. Scored delta index = 0.30 one value, both tiers STAGING 0.0 1.0 alert_at 0.40 0.30 alert zone no event digest stays quiet PRODUCTION 0.0 1.0 alert_at 0.15 0.30 alert zone page · fires routed to on-call same 0.30 · opposite outcome — the cutoff is the only difference

Worked example: PostgreSQL 15, three environments, one CI service account

Scenario: the account ci_migrator is granted SELECT across the whole analytics schema in every environment. In staging that is expected — CI seeds and reads synthetic data constantly. In production the same broad grant is a least-privilege violation. One detector runs against all three tiers on a 15-minute cycle.

Scored deltas emitted by the snapshot + scoring stages for this account:

staging     | ci_migrator | broad_schema_select | 0.32 | GRANT SELECT ON ALL TABLES IN SCHEMA analytics
production  | ci_migrator | broad_schema_select | 0.32 | GRANT SELECT ON ALL TABLES IN SCHEMA analytics

Running Step 3 with thresholds.toml from Step 1:

  • staging — 0.32 < alert_at (0.40): no event. The grant sits inside expected staging churn and never reaches the queue.
  • production — 0.32 >= alert_at (0.15) and debounce_cycles = 1: an event fires immediately at tier = "page".

The production event routes to on-call while staging stays silent — from a single delta value, evaluated against two policies. If ci_migrator genuinely needs that grant in production, it does not belong in a looser threshold; register it as a time-bound exception via automating exception routing for temporary access grants so it clears automatically and unknown drift still pages.

PostgreSQL vs MySQL calibration gotchas

The evaluator is engine-agnostic — it operates on scored deltas, not raw catalogs — but the Step 2 calibration query and how you tag an environment’s baseline are not.

  • Calibration catalog source. PostgreSQL exposes per-object grants in information_schema.role_table_grants and membership in pg_auth_members. MySQL 8.0.16+ keeps the static grant graph in mysql.role_edges and per-user activation in mysql.default_roles; count baseline volume from mysql.tables_priv instead. A cutoff calibrated on one engine’s row counts is meaningless against the other’s.
  • Dormant roles skew the baseline. In MySQL a granted role can be inactive until SET ROLE or activate_all_roles_on_login, so a naive baseline under-counts effective grants and you will calibrate alert_at too low. Reconstruct effective grants from role_edges + default_roles before measuring, the same normalization Environment Comparison Workflows apply upstream.
  • PUBLIC and default privileges. PostgreSQL’s default PUBLIC grants on the public schema inflate every environment’s baseline equally; exclude them (as the Step 2 query does) or staging and production baselines both drift upward and your percentiles lose meaning.
  • Comparable identities across tiers. Only calibrate one environment against another once role identities are canonical — svc_etl_prod and svc_etl_stg must map to one logical role via privilege scope mapping, or a threshold tuned on staging volume will not describe production at all.

Compliance mapping and the audit artifact this produces

Running production at a strictly tighter threshold than staging is direct evidence for the access-restriction controls auditors probe: SOC 2 CC6.1 (logical access restricted to what each identity requires), PCI-DSS Requirement 7 (access limited to least privilege by role), and HIPAA §164.312(a)(1) (technical access control). The artifact is the pair of version-controlled thresholds.toml (showing production’s cutoff, debounce, and zero grace window versus staging’s) and the timestamped regression() report from Step 4 (showing the candidate policy still fires on real production drift and only silences known-benign lower-tier noise). Together they prove the stricter production posture is deliberate, reviewed, and validated before it shipped — the change-authorization trail those same controls expect.

Frequently asked questions

Should staging and production ever share one threshold policy? No. Lower environments legitimately hold looser grants and change constantly, so a production-tuned cutoff buries staging in noise, while a staging-relaxed cutoff lets real production drift slip under the line. Keep one versioned manifest section per environment and calibrate each against its own baseline.

If staging noise is the problem, why not just raise the staging threshold until it stops? Because raising a threshold silences all drift below the new line, including a genuine escalation. Tolerate specific known grants through Exception Routing and Whitelisting and reserve the threshold for the severity you truly do not care about in that tier. Raising the cutoff blindly is how real drift hides in staging.

What drift index should production alert at? Near zero — the Step 2 calibrator collapses production’s cutoff toward its baseline because production drift should be rare by design. A common starting point is 0.15 with debounce_cycles = 1 so the first breach pages, then tighten using the regression() replay rather than by guessing.

Does a per-environment threshold change ever touch the database? Never. The evaluator only emits a structured event, and both the calibration and replay steps are read-only. Even a page-tier production event routes the delta to a separate, explicitly invoked remediation stage — the thresholding layer holds no write or grant-option privileges.

Up: Threshold Tuning for Alerts