Gold Fig blog code snippets

README.md

Gold Fig blog post queries, code snippets, and examples.

goldfig_blog_20200414-find_internal_ips_route53.sql

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'

goldfig_blog_20200414-idle_users.sql

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

goldfig_blog_20200414-stale_access_keys.sql

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)

goldfig_blog_20210323-allowed_accounts.sql

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