IAM Least Privilege for a Pipeline, by Audit
Evaluate a policy set the way the platform does (default deny, explicit deny wins), audit granted-but-unused permissions, and explain the canonical Athena Access Denied from policy data alone.
Roles, not keys
The first thing to get right is what identity a pipeline runs as. A junior instinct is to create an access key, put it in a config file, and move on. Nobody does that in a reviewed system, because a key is a long-lived secret that leaks into a repo, a log, or a laptop. The pattern is a role: an identity with permissions attached and a trust policy naming who may assume it. The Glue job, the Lambda, the EMR cluster assume that role and receive short-lived credentials that rotate on their own. Nothing to leak, nothing to rotate by hand.
How a request is evaluated
Policy evaluation is a fixed order, and interviewers ask for it because it is short and candidates still get it backwards.
- Default denyno statement, no access
- Any explicit Deny?if yes, denied, full stop
- Any matching Allow?principal + action + resource
- Decisionallowed only if an Allow matched and no Deny did
Three consequences worth saying out loud:
- Adding an Allow can never override a Deny. If a request is denied and there is an explicit Deny anywhere in the evaluated set, adding permissions does nothing. Find the Deny.
- A statement must match all three parts. A grant of
s3:GetObjectons3://lake/raw/does nothing for a request tos3://lake/curated/, and nothing at all fors3:PutObject. - Resources are prefixes.
s3://lake/covers everything under it, which is how a well-meaning "just give it read access to the lake" quietly includes the PII prefix.
Least privilege for a pipeline, said concretely: the ingest role needs s3:GetObject on the raw prefix, s3:PutObject on the curated prefix, and glue:UpdateTable on its own tables. Nothing else. Not delete. Not write to raw.
Least privilege is an audit, not an intention
Everyone agrees with least privilege and almost nobody can prove they have it, because a policy document only tells you what is permitted. What was used is in the access log (CloudTrail on AWS). Put the two beside each other and least privilege becomes a query with an answer: every allow with no matching successful call in the log is a revoke candidate. That is the audit you run before a security review, and it is an anti-join.
Read the result with judgment before you revoke. A permission a quarterly job needs will look unused in a week of logs, so you widen the window or check the schedule. But "granted three years ago, never once used" is a finding, and the write permission into a tier the job has no business writing to is a finding with a blast radius.
The canonical Access Denied
The failure every junior meets on AWS: Athena queries fail with Access Denied, and the query itself is fine. Athena writes every result set to a results bucket before you can read it, so the role needs s3:PutObject on that bucket, not only s3:GetObject. A role that can read results but not write them starts a query, does the work, and dies at the last step. The seed here holds exactly that: one role that can read s3://athena-results/ and was never granted write, and one role whose write grant is scoped to a prefix Athena does not use.
Common mistake: treating a denied attempt in the log as evidence that a permission is used. It is evidence of the opposite. When you audit grants against usage, count only the calls that succeeded, or a broken permission will look busy and keep its grant.
Interview nuance: asked "how would you secure this pipeline", the strong answer moves from principle to evidence in one breath: roles with short-lived credentials rather than keys, the minimum action set per tier, and then "and I would audit it by joining the policy set to CloudTrail to find grants nobody has used". The second half is what makes the first half credible.
On a real platform this differs. Here you evaluate a small
iam_policiestable in SQLite. Real IAM composes identity policies, resource policies, permission boundaries, service control policies (SCPs), resource control policies (RCPs), and session policies, and they do not all combine the same way. Boundaries, SCPs, RCPs, and session policies are filters: they can only subtract, so the effective permission is an intersection with each of them. Identity and resource policies are additive within one account: either one can grant on its own, so a bucket policy naming a principal authorizes it even with no matching identity policy. Across accounts you need both. And any explicit Deny, in any of them, wins over everything. Lake Formation layers column, row, and cell grants on top for the catalog. Azure expresses the same audit as an RBAC role-assignment review against activity logs. The evaluation order and the grants-versus-usage audit transfer unchanged.
CREATE TABLE access_log (
event_id TEXT,
principal TEXT,
action TEXT,
resource TEXT,
event_time TEXT,
error_code TEXT -- NULL when the call succeeded, otherwise the denial reason
);
INSERT INTO access_log (event_id, principal, action, resource, event_time, error_code) VALUES
('e-001', 'role/ingest-etl', 's3:GetObject', 's3://lake/raw/events/dt=2026-03-02/part-0000.parquet', '2026-03-02 03:05:11', NULL),
('e-002', 'role/ingest-etl', 's3:GetObject', 's3://lake/raw/events/dt=2026-03-02/part-0001.parquet', '2026-03-02 03:05:14', NULL),
('e-003', 'role/ingest-etl', 's3:GetObject', 's3://lake/raw/orders/dt=2026-03-02/part-0000.parquet', '2026-03-02 03:06:02', NULL),
('e-004', 'role/ingest-etl', 's3:GetObject', 's3://lake/raw/orders/dt=2026-03-02/part-0001.parquet', '2026-03-02 03:06:05', NULL),
('e-005', 'role/ingest-etl', 's3:PutObject', 's3://lake/curated/events/dt=2026-03-02/part-0000.parquet', '2026-03-02 03:12:40', NULL),
('e-006', 'role/ingest-etl', 's3:PutObject', 's3://lake/curated/orders/dt=2026-03-02/part-0000.parquet', '2026-03-02 03:13:02', NULL),
('e-007', 'role/ingest-etl', 's3:PutObject', 's3://lake/curated/orders/dt=2026-03-02/part-0001.parquet', '2026-03-02 03:13:05', NULL),
('e-008', 'role/ingest-etl', 'glue:UpdateTable', 'arn:glue:table/analytics/curated_events', '2026-03-02 03:14:00', NULL),
('e-009', 'role/curated-etl', 's3:GetObject', 's3://lake/curated/events/dt=2026-03-02/part-0000.parquet', '2026-03-02 04:01:20', NULL),
('e-010', 'role/curated-etl', 's3:PutObject', 's3://lake/published/daily_revenue/dt=2026-03-02/part-0.parquet','2026-03-02 04:09:55', NULL),
('e-011', 'role/analyst-athena', 'athena:StartQueryExecution','arn:athena:workgroup/analytics', '2026-03-02 10:22:31', NULL),
('e-012', 'role/analyst-athena', 's3:GetObject', 's3://lake/published/daily_revenue/dt=2026-03-02/part-0.parquet','2026-03-02 10:22:40', NULL),
('e-013', 'role/analyst-athena', 's3:PutObject', 's3://athena-results/query-abc/result.csv', '2026-03-02 10:22:45', 'AccessDenied'),
('e-014', 'role/analyst-athena', 'athena:StartQueryExecution','arn:athena:workgroup/analytics', '2026-03-02 11:04:02', NULL),
('e-015', 'role/analyst-athena', 's3:PutObject', 's3://athena-results/query-abd/result.csv', '2026-03-02 11:04:09', 'AccessDenied'),
('e-016', 'role/analyst-athena', 's3:PutObject', 's3://athena-results/query-abe/result.csv', '2026-03-02 14:37:50', 'AccessDenied'),
('e-017', 'role/analyst-athena', 's3:GetObject', 's3://athena-results/query-9f0/result.csv', '2026-03-02 09:15:00', NULL),
('e-018', 'role/bi-reader', 'athena:StartQueryExecution','arn:athena:workgroup/bi', '2026-03-02 09:59:00', NULL),
('e-019', 'role/bi-reader', 's3:GetObject', 's3://lake/published/daily_revenue/dt=2026-03-02/part-0.parquet','2026-03-02 10:00:12', NULL),
('e-020', 'role/bi-reader', 's3:PutObject', 's3://athena-results/query-def/result.csv', '2026-03-02 10:00:20', 'AccessDenied'),
('e-021', 'role/bi-reader', 's3:PutObject', 's3://athena-results/query-deg/result.csv', '2026-03-02 15:31:44', 'AccessDenied'),
('e-022', 'user/contractor-jo', 's3:GetObject', 's3://lake/published/daily_revenue/dt=2026-03-02/part-0.parquet','2026-03-02 13:40:05', NULL),
('e-023', 'user/contractor-jo', 's3:GetObject', 's3://lake/raw/pii/customers.csv', '2026-03-02 02:14:09', 'AccessDenied'),
('e-024', 'user/contractor-jo', 's3:GetObject', 's3://lake/raw/pii/customers.csv', '2026-03-02 23:47:31', 'AccessDenied'),
('e-025', 'user/contractor-jo', 's3:GetObject', 's3://lake/published/daily_revenue/dt=2026-03-01/part-0.parquet','2026-03-02 22:58:00', NULL),
('e-026', 'role/curated-etl', 's3:PutObject', 's3://lake/raw/backfill/part-0000.parquet', '2026-03-02 11:15:00', 'AccessDenied');-- The trail at a glance: who is calling, and who is bouncing off a missing permission.
SELECT principal,
COUNT(*) AS events,
SUM(CASE WHEN error_code = 'AccessDenied' THEN 1 ELSE 0 END) AS denied
FROM access_log
GROUP BY principal
ORDER BY denied DESC, principal;Apply
Your turn
The task this lesson builds to.
Write a query that returns every allow grant the access log shows nobody ever used, as (principal, action, resource_prefix), ordered by principal then action, over iam_policies(policy_id, principal, effect, action, resource_prefix) and access_log(event_id, principal, action, resource, event_time, error_code). This is the revoke candidate list.
A grant counts as used when access_log holds a successful event (error_code IS NULL) for the same principal and the same action on a resource that starts with the grant's resource_prefix. Deny statements grant nothing, so leave them out.
3 hints and 1 automated check are waiting in the workspace.
Practice
Make it stick
A second problem on the same idea, plus 3 bonus drills.
Write a query that returns each principal hitting Access Denied on the Athena results bucket, with how many times and whether any allow could have covered it, as (principal, denied_events, has_results_bucket_allow), most denials first, over access_log and iam_policies.
Count only events with error_code = 'AccessDenied' on a resource under s3://athena-results/. has_results_bucket_allow is 1 when that principal holds an Allow statement for the same action on a resource_prefix under s3://athena-results/, otherwise 0. A 1 here does not mean the call should have worked: it means an allow exists but did not match the path Athena wrote to.
2 hints and 1 automated check are waiting in the workspace.