You ship daily. Security review is three sprints behind.

-- checks/aws-s3/bucket-public-composite.sql
-- Check: KSP10011
-- Severity: CRITICAL
-- Description: S3 buckets should not be publicly accessible (composite: ACL + policy + public access block)

SELECT
    b.arn as resource_id,
    SPLIT_PART(b.arn, ':::', 2) as resource_name,
    CASE
        -- Pass if all public access is blocked
        WHEN pab.public_access_block_configuration IS NOT NULL
             AND jdecode(pab.public_access_block_configuration)->>'BlockPublicAcls' = 'true'
             AND jdecode(pab.public_access_block_configuration)->>'BlockPublicPolicy' = 'true'
             AND jdecode(pab.public_access_block_configuration)->>'IgnorePublicAcls' = 'true'
             AND jdecode(pab.public_access_block_configuration)->>'RestrictPublicBuckets' = 'true' THEN 'pass'
        -- Fail if ACL grants to AllUsers
        WHEN acl_public.bucket_arn IS NOT NULL THEN 'fail'
        -- Fail if policy allows public access (wildcard principal in an Allow statement)
        WHEN pol.policy IS NOT NULL AND EXISTS (
            SELECT 1 FROM jsonb_array_elements(
                CASE jsonb_typeof(jdecode(pol.policy)->'Statement')
                    WHEN 'array' THEN jdecode(pol.policy)->'Statement'
                    ELSE jsonb_build_array(jdecode(pol.policy)->'Statement')
                END
            ) AS stmt
            WHERE stmt->>'Effect' = 'Allow'
              AND (stmt->>'Principal' = '*' OR stmt->'Principal'->'AWS' @> '"*"'::jsonb)
        ) THEN 'fail'
        ELSE 'pass'
    END as status,
    CASE
        WHEN pab.public_access_block_configuration IS NOT NULL
             AND jdecode(pab.public_access_block_configuration)->>'BlockPublicAcls' = 'true'
             AND jdecode(pab.public_access_block_configuration)->>'BlockPublicPolicy' = 'true'
             AND jdecode(pab.public_access_block_configuration)->>'IgnorePublicAcls' = 'true'
             AND jdecode(pab.public_access_block_configuration)->>'RestrictPublicBuckets' = 'true'
            THEN 'All public access is blocked via public access block configuration'
        WHEN acl_public.bucket_arn IS NOT NULL
            THEN 'Bucket ACL grants access to AllUsers (internet) - enable public access block or remove AllUsers grants'
        WHEN pol.policy IS NOT NULL AND EXISTS (
            SELECT 1 FROM jsonb_array_elements(
                CASE jsonb_typeof(jdecode(pol.policy)->'Statement')
                    WHEN 'array' THEN jdecode(pol.policy)->'Statement'
                    ELSE jsonb_build_array(jdecode(pol.policy)->'Statement')
                END
            ) AS stmt
            WHERE stmt->>'Effect' = 'Allow'
              AND (stmt->>'Principal' = '*' OR stmt->'Principal'->'AWS' @> '"*"'::jsonb)
        )
            THEN 'Bucket policy allows public access (Principal: *) - restrict the policy or enable public access block'
        ELSE 'Bucket is not publicly accessible'
    END as reason
FROM aws_s3_buckets b
LEFT JOIN aws_s3_bucket_public_access_blocks pab
    ON b.arn = pab.bucket_arn
LEFT JOIN (
    SELECT DISTINCT bucket_arn
    FROM aws_s3_bucket_grants
    WHERE grantee_type = 'Group'
      AND jdecode(grantee)->>'URI' = 'http://acs.amazonaws.com/groups/global/AllUsers'
) acl_public ON b.arn = acl_public.bucket_arn
LEFT JOIN aws_s3_bucket_policies pol
    ON b.arn = pol.bucket_arn;