If you are utilizing AWS S3 in your cloud environments, you may want to know which buckets are not configured correctly to block public access. Use CloudQuery to export your data from AWS to PostgreSQL and ensure that the aws_s3_buckets and aws_s3_bucket_public_access_blocks tables are included in your AWS source configuration. Once you have synced this data to your PostgreSQL database, here’s how to view any potentially publicly accessible S3 buckets:
select
b.account_id,
b.arn,
b.name as bucket_name,
b.creation_date,
b.region as bucket_region,
b.tags,
CASE when
-- check that all public access block configurations are true
((pab.public_access_block_configuration -> 'BlockPublicAcls')::boolean
or (pab.public_access_block_configuration -> 'IgnorePublicAcls')::boolean
or (pab.public_access_block_configuration -> 'BlockPublicPolicy')::boolean
or (pab.public_access_block_configuration -> 'RestrictPublicBuckets')::boolean)
-- check if s3 bucket policy status 'IsPublic' is set to false
or b.policy_status ->> 'IsPublic' = 'false'
THEN true ELSE false
END AS public_access_blocked
FROM
aws_s3_buckets b
LEFT JOIN
aws_s3_bucket_public_access_blocks pab on pab._cq_parent_id = b._cq_id
This query will yield a result that looks like this:
| account_id | arn | bucket_name | creation_date | bucket_region | tags | public_access_blocked |
|---|
| 1234567890 | arn:aws:s3:::test_bucket_1 | test_bucket_1 | 2024-03-01 15:34:05 | us-east-1 | {"tag":"test-value"} | true |
| 1234567890 | arn:aws:s3:::test_bucket_2 | test_bucket_2 | 2023-05-03 11:42:07 | us-east-1 | {"tag":"test-value"} | false |
This query leverages PostgreSQL built-in JSON functions to parse the AWS configuration fields which are stored as jsonb data types. It first checks that all public access blocks are configured properly and then checks that the S3 bucket policy is not public. If all public access blocks are configured then the query will return true in the public_access_blocked field, meaning you can target only those that return false when reviewing your cloud security configurations.
You can also use this query to search for specific S3 buckets by tag, to do that we will keep the above query and after the final line we can add a WHERE clause to target specifically tagged buckets. For example, let’s say you want to grab all buckets tagged with a specific EUID, you can use the following query:
select
b.account_id,
b.arn,
b.name as bucket_name,
b.creation_date,
b.region as bucket_region,
b.tags,
CASE when
-- check that all public access block configurations are true
((pab.public_access_block_configuration -> 'BlockPublicAcls')::boolean
or (pab.public_access_block_configuration -> 'IgnorePublicAcls')::boolean
or (pab.public_access_block_configuration -> 'BlockPublicPolicy')::boolean
or (pab.public_access_block_configuration -> 'RestrictPublicBuckets')::boolean)
-- check if s3 bucket policy status 'IsPublic' is set to false
or b.policy_status ->> 'IsPublic' = 'false'
THEN false ELSE true
END AS public_access_blocked
FROM
aws_s3_buckets b
LEFT JOIN
aws_s3_bucket_public_access_blocks pab on pab._cq_parent_id = b._cq_id
WHERE
b.tags ->> 'EUID' = 'EUID1'
This query will yield a slightly different result, note the EUID in the tags field:
| account_id | arn | bucket_name | creation_date | bucket_region | tags | public_access_blocked |
|---|
| 7890123456 | arn:aws:s3:::euid_bucket_1 | euid_bucket_1 | 2024-03-01 15:34:05 | eu-central-1 | {"EUID":"EUID1"} | true |
Want to see CloudQuery in action? Schedule a demo with our team or check out the platform documentation to learn more.
Try CloudQuery yourself
Self-hosted, open source, and free to start. Or schedule a demo to see the managed version.
