access-review

Access review

A policy document and an effective permission are not the same thing, and the gap between them is where breaches live. This skill is about closing that gap before a human is asked to sign anything.

The rule that governs everything below: never report what a policy says. Report what a principal can do, computed by simulation against a shadow copy of the account. If you cannot simulate it, say you could not, and do not guess.

---

1. The three questions

For every principal in the change:

1. What can they reach after this that they could not reach before? The delta, not the total. An approver cannot judge a 400-line policy document; they can judge four new verbs.
2. What is the worst thing reachable through this grant? Not the intended use — the maximum. Read access to public.* includes the table nobody remembered was there.
3. When does it end? A grant with no expiry is refused by policy. There is no such thing as temporary access that nobody scheduled to remove.

---

2. Effective permissions, not declared ones

Four mechanisms make the declared policy an underestimate. Check each.

Transitive role membership

-- Postgres: everything prod_reader actually inherits, transitively
WITH RECURSIVE m AS (
  SELECT oid, rolname FROM pg_roles WHERE rolname = 'prod_reader'
  UNION
  SELECT r.oid, r.rolname
    FROM pg_auth_members am
    JOIN pg_roles r ON r.oid = am.roleid
    JOIN m ON m.oid = am.member
)
SELECT rolname FROM m;

A role granted a role granted a role is three documents away from the grant you are reviewing. \du shows one level. Recurse.

PUBLIC grants

GRANT ... TO PUBLIC is invisible in a per-role audit and applies to the new principal immediately.

SELECT table_schema, table_name, privilege_type
  FROM information_schema.role_table_grants
 WHERE grantee = 'PUBLIC';

Default privileges

ALTER DEFAULT PRIVILEGES means the grant covers tables that do not exist yet. This is the one people miss: the review passes, and six weeks later a new table containing card data is readable by a role nobody re-reviewed.