Find public S3 buckets

Queries & Insights
,
1

Public S3 buckets can expose sensitive data, leading to breaches. Here’s a PostgreSQL query that you can use to find all public S3 buckets from your AWS CloudQuery sync.

WITH policy_allow_public AS (
    SELECT arn, COUNT(*) as statement_count
    FROM (
        SELECT 
            arn,
            jsonb_array_elements_text(
                COALESCE(bp.policy_json::jsonb, '{}'::jsonb) -> 'Statement'
            ) as statement
        FROM aws_s3_buckets b
        INNER JOIN aws_s3_bucket_policies bp
            ON b.arn = bp.bucket_arn
        WHERE
            (COALESCE(bp.policy_json::jsonb, '{}'::jsonb) -> 'Statement') IS NOT NULL
    ) statements
    WHERE
        statement::jsonb ->> 'Effect' = 'Allow' AND (
            statement::jsonb ->> 'Principal' = '*' OR
            statement::jsonb -> 'Principal' ->> 'AWS' = '*' OR
            EXISTS (
                SELECT 1 
                FROM jsonb_array_elements_text(
                    CASE 
                        WHEN jsonb_typeof(statement::jsonb -> 'Principal' -> 'AWS') = 'array' 
                        THEN statement::jsonb -> 'Principal' -> 'AWS'
                        ELSE jsonb_build_array(statement::jsonb -> 'Principal' -> 'AWS')
                    END
                ) as aws_principal
                WHERE aws_principal = '*'
            )
        )
    GROUP BY arn
),
non_blocked AS (
    SELECT b.arn
    FROM aws_s3_buckets b
    LEFT JOIN aws_s3_bucket_public_access_blocks bpab
        ON bpab._cq_parent_id = b._cq_id
    LEFT JOIN policy_allow_public pap
        ON pap.arn = b.arn
    LEFT JOIN aws_s3_bucket_grants bg
        ON bg._cq_parent_id = b._cq_id
    WHERE
        (
            COALESCE(bpab.public_access_block_configuration::jsonb, '{}'::jsonb) ->> 'BlockPublicAcls' != 'true'
            AND (
                COALESCE(bg.grantee::jsonb, '{}'::jsonb) ->> 'URI' = 'http://acs.amazonaws.com/groups/global/AllUsers'
                AND bg.permission IN ('READ_ACP', 'FULL_CONTROL')
            )
        )
        OR
        (
            COALESCE(bpab.public_access_block_configuration::jsonb, '{}'::jsonb) ->> 'BlockPublicPolicy' != 'true'
            AND pap.statement_count > 0
        )
)
SELECT
    b.account_id,
    b.region,
    b.name,
    b.tags
FROM aws_s3_buckets b
JOIN non_blocked USING(arn)
GROUP BY b.account_id, b.region, b.name, b.tags;
1 Like