Retiring Orphaned IAM Keys: A Practical Query and Ownership Model

Recent guidance highlights that AWS IAM user access keys never expire on their own, so a key created for a short‑lived agent can linger indefinitely. The change is the introduction of a concrete, platform‑agnostic query that extracts such keys from the credential report and the recommendation to assign an accountable owner to every credential at discovery. Practitioners need to act because an abandoned key is a persistent entry point that attackers can exploit faster than any annual audit.

Identify Stale Keys

The core of the solution is a SQL statement that runs against the aws_iam_credential_reports table synced by CloudQuery. It normalises the two possible key slots per user, filters out the root account, and selects keys that are both older than 90 days and have not been used in the last 90 days. The query works for both ClickHouse and PostgreSQL dialects.

WITH latest_reports AS (
  SELECT _cq_platform_id, arn, "user" AS principal,
         access_key1_active, access_key_1_last_rotated, access_key_1_last_used_date,
         access_key2_active, access_key_2_last_rotated, access_key_2_last_used_date
  FROM aws_iam_credential_reports
  ORDER BY _cq_sync_group_id DESC, _cq_sync_time DESC
  LIMIT 1 BY _cq_platform_id
),
keys AS (
  SELECT _cq_platform_id, arn, principal, 1 AS key_slot,
         access_key1_active AS active,
         access_key_1_last_rotated AS created,
         access_key_1_last_used_date AS last_used
  FROM latest_reports
  UNION ALL
  SELECT _cq_platform_id, arn, principal, 2 AS key_slot,
         access_key2_active AS active,
         access_key_2_last_rotated AS created,
         access_key_2_last_used_date AS last_used
  FROM latest_reports
)
SELECT _cq_platform_id, arn, principal, key_slot, created, last_used
FROM keys
WHERE principal != ''
  AND active = true
  AND created < now() - INTERVAL 90 DAY
  AND (last_used IS NULL OR last_used < now() - INTERVAL 90 DAY)
ORDER BY last_used NULLS FIRST, principal, key_slot;

Running this query on a quarterly schedule yields a prioritized list of credentials that merit review. A null last_used value signals a need for investigation rather than an automatic deletion.

Owner‑Centric Lifecycle

OWASP’s 2025 Top 10 for non‑human identities places improper off‑boarding at the top, reinforcing the need for an explicit owner for every credential. The process should be:

• Assign an owner when the key is first created (often the team that provisions the agent).
• Re‑assign ownership before the responsible person leaves the organization.
• Retire the key when the associated agent’s task ends, or when the review query flags it as stale.

This model separates the lifecycle of the VM (or container) from the lifecycle of the credential it uses, preventing a forgotten VM cost from masking a forgotten access risk.

Operational Implications

Integrating the query into existing CI/CD or platform‑as‑code pipelines turns a manual audit into an automated control. Teams can embed the result as a gate that blocks deployments using keys flagged as stale. Because the query works on any cloud that provides a comparable credential report (e.g., Entra ID service principals), the pattern can be extended beyond AWS without assuming identical APIs.

From a security perspective, the approach reduces the window of exposure demonstrated by the September 2026 GreyNoise AI‑orchestrated attack, where an attacker moved from an empty workspace to remote code execution in under four hours. By shrinking the inventory lag, organizations limit the time an attacker can weaponise a forgotten key.

Related CloudNinjas coverage: DevOps.

What This Means For Practitioners

Adopt the following short‑term actions:

1. Deploy CloudQuery (or an equivalent sync) to populate aws_iam_credential_reports in your analytics store.
2. Schedule the provided query to run at least quarterly and route the output to a ticketing system for owner review.
3. Document ownership for every non‑root IAM user key at creation time and update the record whenever personnel change.
4. Retire or rotate keys that appear in the stale list, and verify that no production workload depends on them.

Long‑term, embed credential ownership checks into your platform’s onboarding and off‑boarding playbooks to keep agent lifecycles in sync with their access rights.