Gold Fig blog post queries, code snippets, and examples.
Last active
May 28, 2021 20:00
-
-
Save vikrum/57b8d02327d5989c5095272cb685d166 to your computer and use it in GitHub Desktop.
Gold Fig blog code snippets
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE FUNCTION is_rfc1918block (block cidr) | |
RETURNS boolean | |
LANGUAGE 'sql' | |
STRICT IMMUTABLE | |
AS $BODY$ | |
SELECT | |
('192.168.0.0/16' >>= block) | |
OR ('172.16.0.0/12' >>= block) | |
OR ('10.0.0.0/8' >>= block); | |
$BODY$; | |
SELECT | |
Zone.uri AS hosted_zone_arn, | |
(Record.value ->> 'Value')::inet AS ip_address, | |
-- This could be moved to the WHERE clause to include or exclude internal IP addresses | |
is_rfc1918block ((Record.value ->> 'Value')::cidr) AS is_internal | |
FROM | |
aws_route53_hostedzone AS Zone | |
CROSS JOIN LATERAL jsonb_array_elements(Zone.resourcerecordsets) AS RecordSet | |
CROSS JOIN LATERAL jsonb_array_elements(RecordSet.value -> 'ResourceRecords') AS Record | |
WHERE | |
RecordSet.value ->> 'Type' = 'A' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH activity AS ( | |
SELECT | |
U._id, | |
P.idle_since | |
FROM | |
aws_iam_user AS U | |
CROSS JOIN LATERAL user_password_activity (U) AS P | |
WHERE | |
P.is_active | |
UNION | |
SELECT | |
U._id, | |
P.idle_since | |
FROM | |
aws_iam_user AS U | |
CROSS JOIN LATERAL user_access_key_1_activity (U) AS P | |
WHERE | |
P.is_active | |
UNION | |
SELECT | |
U._id, | |
P.idle_since | |
FROM | |
aws_iam_user AS U | |
CROSS JOIN LATERAL user_access_key_2_activity (U) AS P | |
WHERE | |
P.is_active | |
) | |
SELECT | |
U.uri, | |
U.username, | |
U.createdate, | |
MAX(A.idle_since) AS idle, | |
age(MAX(A.idle_since)) AS idle_time | |
FROM | |
aws_iam_user AS U | |
INNER JOIN activity AS A ON U._id = A._id | |
GROUP BY | |
U._id | |
HAVING | |
age(MAX(A.idle_since)) > '3 months'::interval |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
username, | |
createdate, | |
age(createdate) AS age, | |
(access_key_1_active = TRUE | |
AND (age((accesskeys -> 0 ->> 'CreateDate')::timestamp with time zone) > '3 months'::interval | |
AND access_key_1_last_used_date IS NULL) | |
OR age(access_key_1_last_used_date) > '3 months'::interval) AS access_key_1_stale, | |
(accesskeys -> 0 ->> 'CreateDate')::timestamp with time zone AS access_key_1_create_date, | |
age((accesskeys -> 0 ->> 'CreateDate')::timestamp with time zone) AS access_key_1_age, | |
access_key_1_last_used_date, | |
age(COALESCE(access_key_1_last_used_date, (accesskeys -> 0 ->> 'CreateDate')::timestamp with time zone)) AS access_key_1_idle_time, | |
(access_key_2_active = TRUE | |
AND (age((accesskeys -> 1 ->> 'CreateDate')::timestamp with time zone) > '3 months'::interval | |
AND access_key_2_last_used_date IS NULL) | |
OR age(access_key_2_last_used_date) > '3 months'::interval) AS access_key_2_stale, | |
(accesskeys -> 1 ->> 'CreateDate')::timestamp with time zone AS access_key_2_create_date, | |
age((accesskeys -> 1 ->> 'CreateDate')::timestamp with time zone) AS access_key_2_age, | |
access_key_2_last_used_date, | |
age(COALESCE(access_key_2_last_used_date, (accesskeys -> 1 ->> 'CreateDate')::timestamp with time zone)) AS access_key_2_idle_time | |
FROM | |
aws_iam_user | |
WHERE (access_key_2_active = TRUE | |
AND (age((accesskeys -> 1 ->> 'CreateDate')::timestamp with time zone) > '3 months'::interval | |
AND access_key_2_last_used_date IS NULL) | |
OR age(access_key_2_last_used_date) > '3 months'::interval) | |
OR (access_key_1_active = TRUE | |
AND (age((accesskeys -> 0 ->> 'CreateDate')::timestamp with time zone) > '3 months'::interval | |
AND access_key_1_last_used_date IS NULL) | |
OR age(access_key_1_last_used_date) > '3 months'::interval) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
F.functionname, | |
A.account_id AS allowed_accounts | |
FROM | |
aws_lambda_function AS F | |
CROSS JOIN LATERAL jsonb_array_elements(F._policy -> 'Statement') AS S | |
CROSS JOIN LATERAL allowed_account_ids(S.value) AS A |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment