Optimize aws_s3_bucket: Skip hydrate calls for buckets outside requested region qualifier #2737

When querying aws_s3_bucket with a WHERE region = 'ap-southeast-2' qualifier, Steampipe still runs all 14 hydrate API calls on every bucket across all regions before filtering. This causes severe timeouts for accounts with a large number of buckets.

Our scenario:

• Account has 272 S3 buckets spread across ap-south-1 (182), ap-southeast-1 (66), us-east-1 (20), me-south-1 (2), eu-west-1 (2)
• 0 buckets exist in the requested region (ap-southeast-2)
• Despite this, Steampipe fires ~3,800 API calls (272 buckets × 14 hydrate calls each)
• 2 buckets in me-south-1 caused TCP-level i/o timeout on all hydrate calls, each retrying 9 times with exponential backoff
• Query got stuck for 12+ minutes at 270/272 rows before hitting DeadlineExceeded
• The final result was 0 rows — all that work for nothing

The flow today:

1. ListBuckets (global API) → returns all 272 buckets (no region info)
2. HeadBucket on each → determines region
3. All 14 hydrate calls on ALL 272 buckets (ACL, Policy, Tags, Encryption, Versioning, Lifecycle, Logging, Website, ObjectLock, OwnershipControls, EventNotification, PublicAccessBlock, PolicyStatus, ARN)
4. Region filter applied after all hydrate calls complete
5. Returns only matching rows

Hydrate calls per bucket:

Describe the solution you'd like

After HeadBucket determines a bucket's region, skip all remaining hydrate calls if the bucket's region does not match the WHERE region = ? qualifier.

Proposed flow:

1. ListBuckets → returns all buckets
2. HeadBucket on each → determines region
3. If region doesn't match qualifier → skip hydrate calls, don't stream this row
4. Only run the 13 remaining hydrate calls on buckets that match the region
5. Return matching rows

In our case, this would reduce ~3,800 API calls to ~272 (just HeadBucket), and the query would return instantly instead of timing out.

Context

I'm aware that PR #2536 attempted this optimization but was reverted in #2545 due to InvalidToken errors. This request is to re-attempt this optimization with a fix for the credential scoping issue.

Query that triggers this

SELECT account_id, acl, akas, arn, block_public_acls, block_public_policy, 
       bucket_policy_is_public, creation_date, event_notification_configuration, 
       ignore_public_acls, lifecycle_rules, logging, name, object_lock_configuration, 
       object_ownership_controls, partition, policy, policy_std, region, 
       restrict_public_buckets, server_side_encryption_configuration, tags, tags_src, 
       title, versioning_enabled, versioning_mfa_delete, website_configuration 
FROM aws_s3_bucket 
WHERE account_id = '742465441725' AND region = 'ap-southeast-2'

Error

Error: rpc error: code = DeadlineExceeded desc = aws: exceeded allowed timeout (SQLSTATE HV000)

From plugin logs, the final timeout was caused by:

GetBucketNotificationConfiguration on bi-global-reports-01.s3.me-south-1.amazonaws.com
→ dial tcp 52.95.172.40:443: i/o timeout
→ exceeded maximum number of attempts, 9
A bucket in me-south-1 (not even the requested region) caused the entire query to fail.

Environment

Steampipe v2.3.5
steampipe-plugin-aws (latest)
Account with 272 S3 buckets across multiple regions
Connection configured with single region