Weekly ClickHouse Cloud security and access review email

Every Friday at 9am, run a security and access review of my ClickHouse Cloud organization and email a prioritized brief to my security team. This needs judgement about which keys, members, and events actually matter, so weigh each finding and explain why it made the list.

First gather the current state of the organization. Use ClickHouse Cloud List API Keys to pull every API key with its role and metadata, List Organization Members to pull the current members, and List Organization Activities to pull the recent audit trail (scope it to the past week, since these activities accept a from_date and to_date). ClickHouse Cloud resources are org-scoped, so resolve the organization first if needed.

Then produce a prioritized brief with three sections. Section one, API keys worth rotating: flag admin-role keys first, because an admin key can create and delete services and other keys, and flag keys that look stale or unused. Note that a key's secret is only shown once at creation, so unknown or long-idle keys are a genuine risk. Section two, membership changes: list members who were added or removed this week, based on the audit trail. Section three, high-risk audit events: call out things like a service being deleted, a new admin key being created, or a member removed.

Sort every item across the brief by risk, highest first, and give each one a single-line reason explaining why it matters and what to do about it (for example, rotate, review, or confirm the change was intended). Keep it scannable so the reader can act quickly.

Finally, send the brief to my security team using Gmail Send a Message, with a clear subject line that includes the date range covered. Let the recipient email address and the risk thresholds be configurable: the recipient defaults to my security team's address, and the thresholds control things like how many days without use makes a key count as stale and which activity types are treated as high risk.