Table: aws_s3_object

select
  key,
  arn,
  bucket_name,
  last_modified,
  storage_class,
  version_id
from
  aws_s3_object
where
  bucket_name = 'steampipe-test';

select
  key,
  arn,
  bucket_name,
  last_modified,
  storage_class,
  version_id
from
  aws_s3_object
where
  bucket_name = 'steampipe-test'
  and prefix = 'test/logs/2021/03/01/12';

select
  key,
  arn,
  bucket_name,
  last_modified,
  storage_class,
  version_id
from
  aws_s3_object
where
  bucket_name = 'steampipe-test'
  and prefix = 'test/logs/2021/03/01/12/abc.txt';

select
  key,
  arn,
  bucket_name,
  last_modified,
  storage_class,
  version_id
from
  aws_s3_object
where
  bucket_name = 'steampipe-test'
  and sse_kms_key_id is not null;

select
  key,
  arn,
  bucket_name,
  last_modified,
  storage_class,
  version_id
from
  aws_s3_object
where
  bucket_name = 'steampipe-test'
  and last_modified < current_date - interval '3 months';

select
  key,
  bucket_name,
  owner,
  acl_grant -> 'Grantee' as grantee,
  acl_grant ->> 'Permission' as permission
from
  aws_s3_object,
  jsonb_array_elements(aws_s3_object.acl -> 'Grants') as acl_grant
where
  bucket_name = 'steampipe-test'
  and acl_grant ->> 'Permission' = 'FULL_CONTROL'
  and acl_grant -> 'Grantee' ->> 'ID' != aws_s3_object.owner ->> 'ID';

select
  key,
  bucket_name,
  object_lock_legal_hold_status
from
  aws_s3_object
where
  bucket_name = 'steampipe-test'
  and object_lock_legal_hold_status = 'ON';

select
  key,
  bucket_name,
  object_lock_retain_until_date,
  object_lock_mode,
  object_lock_legal_hold_status
from
  aws_s3_object
where
  bucket_name = 'steampipe-test'
  and object_lock_mode = 'GOVERNANCE';

select
  key,
  bucket_name,
  object_lock_retain_until_date,
  object_lock_mode,
  object_lock_legal_hold_status
from
  aws_s3_object
where
  bucket_name = 'steampipe-test'
  and object_lock_retain_until_date > current_date + interval '1 year';

select
  key,
  bucket_name,
  jsonb_pretty(tags) as tags
from
  aws_s3_object
where
  bucket_name = 'steampipe-test'
  and tags ->> 'application' is not null;

select
  key,
  o.arn as object_arn,
  bucket_name,
  last_modified,
  bucket_key_enabled
from
  aws_s3_object as o,
  aws_s3_bucket as b
where
  o.bucket_name = b.name
  and not bucket_key_enabled;

select
  key,
  arn,
  bucket_name,
  last_modified,
  storage_class
from
  aws_s3_object
where
  bucket_name in (
    select
      name
    from
      aws_s3_bucket
    where
      not block_public_acls
      or not block_public_policy
      or not ignore_public_acls
      or not restrict_public_buckets
  );

select
  key,
  b ->> 'awsAccountId' as account_id,
  b ->> 'digestEndTime' as digest_end_time,
  b ->> 'digestPublicKeyFingerprint' as digest_public_key_fingerprint,
  b ->> 'digestS3Bucket' as digest_s3_bucket,
  b ->> 'digestStartTime' as digest_start_time
from
  aws_s3_object,
  jsonb_array_elements(body :: jsonb) as b
where
  bucket_name = 'steampipe-test'
  and prefix = 'test1/log_text.txt';