Implementing automatic expiry for access whitelists

This guide shows how to make every drift-suppression whitelist entry time-bound by construction, so an approved exception stops suppressing the moment its window closes — and the underlying grant is actively revoked rather than left to linger.

A whitelist that never expires is a permanent hole in your drift posture. The temporary INSERT an engineer needed for a four-hour migration becomes a standing suppression rule that silences the exact alert you would want a year later when someone reuses that principal. The durable fix is to treat every exception as a lease with an expires_at, evaluate that lease at match time with fail-closed semantics, and run a sweep that fires an explicit revoke when the lease lapses. This extends the manifest model from exception routing and whitelisting with a lifecycle contract.

When to use this — and when not to

Reach for automatic expiry when:

• Your whitelist entries are operational grants that were always meant to be temporary — incident break-glass, migration windows, ephemeral CI accounts — and you have seen entries outlive their justification.
• You can attach a revoke action to each exception, so a lapsed lease can close the grant it once covered instead of merely un-suppressing the alert.
• Auditors expect proof that access removal is automated, not dependent on someone remembering to delete a row.

Skip it, or scope it down, when:

• The exception is a genuinely permanent policy decision (a service account that legitimately holds SELECT on a reporting schema forever). Model that as baseline, not as a whitelist entry.
• You cannot yet revoke safely — if the revoke path is not transaction-safe, wire it through rollback and recovery first, because a half-applied revoke is worse than a lingering grant.
• Your clocks are unsynchronized across the store and the evaluator. Fix time sync before you make expiry load-bearing, or leases will fire early or late.

Step-by-step implementation

Step 1 — Model the exception as a lease

Give every whitelist entry a mandatory expires_at and a revoke_action describing how to close the grant when the lease ends. A pydantic v2 model rejects entries with no expiry at parse time, so an unbounded exception can never enter the store.

from datetime import datetime, timezone
from pydantic import BaseModel, field_validator

def utcnow() -> datetime:
    return datetime.now(timezone.utc)

class WhitelistLease(BaseModel):
    principal: str          # canonical role name (env suffix stripped)
    privilege: str          # SELECT, INSERT, ALL PRIVILEGES, ...
    object_scope: str       # schema.table
    environment: str        # prod | staging | dev
    ticket: str             # change record backing the approval
    granted_at: datetime
    expires_at: datetime    # required — no permanent whitelist entries
    revoke_sql: str         # the DDL that closes this grant on lapse

    @field_validator("expires_at")
    @classmethod
    def must_be_future(cls, v: datetime, info) -> datetime:
        if v <= info.data.get("granted_at", utcnow()):
            raise ValueError("expires_at must be after granted_at")
        return v

    def is_active(self, now: datetime | None = None) -> bool:
        return (now or utcnow()) < self.expires_at

Verify the model refuses an unbounded or backwards lease:

import pytest
base = dict(principal="ci_migrator", privilege="INSERT",
            object_scope="orders.line_items", environment="staging",
            ticket="CHG-4471", granted_at=utcnow(),
            revoke_sql="REVOKE INSERT ON orders.line_items FROM ci_migrator_stg;")

with pytest.raises(ValueError):
    WhitelistLease(**base, expires_at=base["granted_at"])   # not in the future

Step 2 — Match with fail-closed semantics

A match may only suppress a delta when the store is fresh and the lease is active. Freshness is a heartbeat: the loader stamps loaded_at, and if the snapshot is older than a hard staleness bound — or the load raised at all — matching returns “no match” so the delta is scored and routed normally. Never let an unreachable store default to suppression.

from datetime import timedelta

STALENESS_BOUND = timedelta(minutes=10)

class ExceptionStore:
    def __init__(self, leases: list[WhitelistLease], loaded_at: datetime):
        self.leases = leases
        self.loaded_at = loaded_at

    def is_fresh(self, now: datetime | None = None) -> bool:
        return (now or utcnow()) - self.loaded_at < STALENESS_BOUND

def match(delta, store: ExceptionStore | None,
          now: datetime | None = None) -> WhitelistLease | None:
    now = now or utcnow()
    # Fail closed: no store, or a stale snapshot, suppresses nothing.
    if store is None or not store.is_fresh(now):
        return None
    for lease in store.leases:
        if (lease.is_active(now)
                and lease.principal == delta.principal
                and lease.privilege == delta.privilege
                and lease.object_scope == delta.object_scope
                and lease.environment == delta.environment):
            return lease
    return None

Verify that both an expired lease and a stale store fall through to no-match:

class D:  # minimal delta stand-in
    principal, privilege = "ci_migrator", "INSERT"
    object_scope, environment = "orders.line_items", "staging"

fresh = ExceptionStore([], loaded_at=utcnow())
stale = ExceptionStore([], loaded_at=utcnow() - timedelta(minutes=30))
assert match(D, None) is None            # unreachable store → fail closed
assert match(D, stale) is None           # stale snapshot → fail closed
assert match(D, fresh) is None           # no leases → nothing to suppress

Step 3 — Sweep lapsed leases and fire the revoke

The sweep is a separate periodic job. It selects leases whose expires_at has passed, runs each revoke_sql inside a transaction, and removes the lease only after the revoke commits. Because a revoke of an already-absent grant is a no-op in PostgreSQL, the sweep is idempotent: re-running after a crash converges to the same state.

import psycopg   # psycopg 3

def sweep_expired(conn: psycopg.Connection,
                  store: ExceptionStore,
                  now: datetime | None = None) -> list[str]:
    now = now or utcnow()
    fired: list[str] = []
    for lease in list(store.leases):
        if lease.is_active(now):
            continue                       # still within its window
        with conn.transaction():           # revoke and de-list atomically
            conn.execute(lease.revoke_sql)
            store.leases.remove(lease)
        fired.append(lease.ticket)
    return fired

Expected shape of a sweep run, where one of two leases has lapsed:

>>> sweep_expired(conn, store, now=utcnow())
['CHG-4471']          # the lapsed lease was revoked and de-listed
>>> [l.ticket for l in store.leases]
['CHG-4490']          # the still-active lease remains

Worked example: a four-hour migration grant on PostgreSQL 15

Scenario: PostgreSQL 15, three environments. At 09:00 UTC an engineer is approved for INSERT on orders.line_items in staging under ticket CHG-4471, expiring at 13:00 UTC. The lease is created with its own revoke DDL. While active, a drift delta for that exact grant is suppressed. The 13:05 sweep finds the lapsed lease and closes the grant.

lease = WhitelistLease(
    principal="ci_migrator", privilege="INSERT",
    object_scope="orders.line_items", environment="staging",
    ticket="CHG-4471",
    granted_at=datetime(2026, 7, 18, 9, 0, tzinfo=timezone.utc),
    expires_at=datetime(2026, 7, 18, 13, 0, tzinfo=timezone.utc),
    revoke_sql="REVOKE INSERT ON orders.line_items FROM ci_migrator_stg;",
)
store = ExceptionStore([lease], loaded_at=datetime(2026, 7, 18, 11, 0, tzinfo=timezone.utc))

t_noon = datetime(2026, 7, 18, 12, 0, tzinfo=timezone.utc)
t_after = datetime(2026, 7, 18, 13, 5, tzinfo=timezone.utc)

print(match(D, store, now=t_noon) is lease)   # within window → suppressed
print(lease.is_active(t_after))                # past 13:00 → False

Expected output:

True
False

At 12:00 the store is fresh (loaded 11:00, inside the 10-minute bound only if reloaded — in production the loader re-stamps loaded_at each cycle) and the lease is active, so the delta is suppressed against ticket CHG-4471. After 13:00 the lease reports inactive; the next sweep_expired runs REVOKE INSERT ON orders.line_items FROM ci_migrator_stg; and removes the entry. From that moment, an identical grant reappearing is no longer whitelisted and will score and route on its own merits, exactly as covered in threshold tuning for alerts.

Gotchas and engine-specific notes

Store all timestamps in UTC. A lease written with a naive local time will expire hours early or late depending on the evaluator’s zone. The pydantic model above forces timezone-aware UTC; reject naive datetimes at the boundary rather than coercing them silently.

Freshness is not the same as active. A lease can be active but read from a snapshot loaded an hour ago — that snapshot may be missing a revocation someone made in the meantime. The staleness bound protects against acting on outdated data; keep it well under your reload interval so a fresh snapshot is always in hand at match time.

PostgreSQL vs MySQL revoke shape. In PostgreSQL a REVOKE ... FROM role on a grant that no longer exists succeeds as a no-op, which is what makes the sweep idempotent. MySQL 8 raises ERROR 3523 when you REVOKE a privilege the account does not hold, so wrap the MySQL revoke to tolerate the not-found case, and remember that removing a role edge from mysql.role_edges does not deactivate a role that is still pinned via SET ROLE or mysql.default_roles.

Fire the revoke, then de-list — never the reverse. If you remove the lease first and the revoke then fails, you have lost the record of what still needs closing. The transaction in Step 3 keeps de-listing and revoking atomic so a crash leaves the lease in place for the next sweep to retry.

Clock skew fails leases early. If the sweep host runs ahead of the database, a lease can be revoked minutes before its intended window ends. Drive the sweep from the database’s now() rather than the host clock when the two can drift, and align the underlying grant lifecycle with the same catalog contract described in privilege scope mapping.

Compliance note

Automatic expiry produces the evidence auditors ask for under SOC 2 CC6.1 and PCI-DSS Requirement 7.2: proof that elevated access is removed on a defined schedule without human intervention. Each lease carries its approving ticket, its expires_at, and the exact revoke DDL, and each sweep emits a record of which tickets were closed and when — a direct artifact for NIST SP 800-53 AC-2(3) (disable/remove accounts) and AC-2(4) (automated audit actions). Because the fail-closed match path never suppresses on a stale store, the pipeline cannot silently hide drift during an outage, which is the control weakness a permanent whitelist would introduce.

Frequently asked questions

What happens to a suppressed alert when its lease expires? Nothing is retroactively un-suppressed, but from the expiry moment forward the match returns no lease, so an identical grant is scored and routed normally. The sweep also fires the revoke, so in the common case the grant itself is gone before the next diff runs.

Why fail closed instead of caching the last good whitelist? A cached whitelist can suppress an alert for a grant that was already revoked out-of-band, hiding real drift. Failing closed means the worst case is a benign, already-approved grant paging once during an outage — noisy, but never silent on a genuine escalation.

Should the sweep or the match evaluate expiry? Both, for different reasons. The match uses expires_at so suppression stops instantly at the boundary even if the sweep is late. The sweep uses it to actively revoke the underlying grant. Relying on only one leaves either a lingering grant or a lingering suppression.

How do I revoke safely if the grant is inside a busy transaction path? Route the revoke through a transaction-safe batch path rather than issuing ad-hoc DDL. The patterns in rollback and recovery keep a failed revoke from leaving the role in a half-closed state.

Related

• Exception Routing and Whitelisting — the whitelist framework this lifecycle extends
• Automating Exception Routing for Temporary Access Grants — how leases enter the pipeline in the first place
• Configuring Drift Thresholds for Staging vs Production — what a lapsed exception falls back to
• Rollback and Recovery — making the lapse-time revoke transaction-safe

Up: Exception Routing and Whitelisting