It seems to be a deadlock between the refresh connection query and the benchmark query
2023-07-03 14:23:32.002 UTC [INFO] hub: StartScan for table: aws_s3_bucket, cache enabled: false, iterator 0xc000163980, 0 quals (1688394211353)
2023-07-03 14:23:32.216 UTC [INFO] hub: StartScan for table: aws_iam_policy, cache enabled: false, iterator 0xc000154fc0, 1 quals (1688394211576)
2023-07-03 14:23:32.295 UTC [35831] ERROR: deadlock detected at character 268
2023-07-03 14:23:32.295 UTC [35831] DETAIL: Process 35831 waits for AccessShareLock on relation 48689 of database 16384; blocked by process 35827.
Process 35827 waits for AccessExclusiveLock on relation 48923 of database 16384; blocked by process 35831.
Process 35831: select
-- Required Columns
u.arn as resource,
case
when count(k.*) > 1 then 'alarm'
else 'ok'
end as status,
u.name || ' has ' || count(k.*) || ' active access key(s).' as reason
-- Additional Dimensions
from
aws_iam_user as u
left join aws_iam_access_key as k on u.name = k.user_name and u.account_id = k.account_id
where
k.status = 'Active' or k.status is null
group by
u.arn,
u.name,
u.account_id,
u.tags,
u._ctx;
Process 35827: drop schema if exists "aws_001" cascade;
create schema "aws_001";
comment on schema "aws_001" is 'steampipe plugin: hub.steampipe.io/plugins/turbot/aws@latest';
grant usage on schema "aws_001" to steampipe_users;
alter default privileges in schema "aws_001" grant select on tables to steampipe_users;
grant select on all tables in schema "aws_001" to steampipe_users;
import foreign schema "hub.steampipe.io/plugins/turbot/aws@latest" from server steampipe into "aws_001";
2023-07-03 14:23:32.295 UTC [35831] HINT: See server log for query details.
2023-07-03 14:23:32.295 UTC [35831] STATEMENT: select
-- Required Columns
u.arn as resource,
case
when count(k.*) > 1 then 'alarm'
else 'ok'
end as status,
u.name || ' has ' || count(k.*) || ' active access key(s).' as reason
-- Additional Dimensions
from
aws_iam_user as u
left join aws_iam_access_key as k on u.name = k.user_name and u.account_id = k.account_id
where
k.status = 'Active' or k.status is null
group by
u.arn,
u.name,
u.account_id,
u.tags,
u._ctx;
2023-07-03 14:23:32.415 UTC [INFO] hub: goFdwBeginForeignScan, connection 'aws_001', table 'aws_s3_bucket', explain: false
2023-07-03 14:23:32.416 UTC [INFO] hub: --------
2023-07-03 14:23:32.416 UTC [INFO] hub: no quals
2023-07-03 14:23:32.416 UTC [INFO] hub: --------
2023-07-03 14:23:32.416 UTC [INFO] hub: goFdwBeginForeignScan, connection 'aws_001', table 'aws_s3_account_settings', explain: false
2023-07-03 14:23:32.417 UTC [INFO] hub: --------
2023-07-03 14:23:32.417 UTC [INFO] hub: no quals
2023-07-03 14:23:32.417 UTC [INFO] hub: --------
2023-07-03 14:23:32.417 UTC [INFO] hub: StartScan for table: aws_s3_bucket, cache enabled: false, iterator 0xc000b10fc0, 0 quals (1688394212941)
2023-07-03 14:23:34.552 UTC [INFO] hub: StartScan for table: aws_s3_bucket, cache enabled: false, iterator 0xc0001592c0, 0 quals (168839421194)
2023-07-03 14:23:34.554 UTC [INFO] hub: StartScan for table: aws_macie2_classification_job, cache enabled: false, iterator 0xc000163c80, 0 quals (1688394212574)
2023-07-03 14:23:35.379 UTC [INFO] hub: StartScan for table: aws_s3_account_settings, cache enabled: false, iterator 0xc000b112c0, 0 quals (1688394212735)
2023-07-03 14:23:41.368 UTC [35831] LOG: duration: 8951.437 ms execute stmtcache_41: select
-- Required Columns
arn as resource,
case
when (bucket.block_public_acls or s3account.block_public_acls)
and (bucket.block_public_policy or s3account.block_public_policy)
and (bucket.ignore_public_acls or s3account.ignore_public_acls)
and (bucket.restrict_public_buckets or s3account.restrict_public_buckets)
then 'ok'
else 'alarm'
end as status,
case
when (bucket.block_public_acls or s3account.block_public_acls)
and (bucket.block_public_policy or s3account.block_public_policy)
and (bucket.ignore_public_acls or s3account.ignore_public_acls)
and (bucket.restrict_public_buckets or s3account.restrict_public_buckets)
then name || ' all public access blocks enabled.'
else name || ' not enabled for: ' ||
concat_ws(', ',
case when not (bucket.block_public_acls or s3account.block_public_acls) then 'block_public_acls' end,
case when not (bucket.block_public_policy or s3account.block_public_policy) then 'block_public_policy' end,
case when not (bucket.ignore_public_acls or s3account.ignore_public_acls) then 'ignore_public_acls' end,
case when not (bucket.restrict_public_buckets or s3account.restrict_public_buckets) then 'restrict_public_buckets' end
) || '.'
end as reason
-- Additional Dimensions
from
aws_s3_bucket as bucket,
aws_s3_account_settings as s3account
where
s3account.account_id = bucket.account_id;
2023-07-03 14:23:41.628 UTC [35828] LOG: duration: 9760.837 ms execute stmtcache_39: select
-- Required Columns
arn as resource,
case
when versioning_mfa_delete then 'ok'
else 'alarm'
end status,
case
when versioning_mfa_delete then name || ' MFA delete enabled.'
else name || ' MFA delete disabled.'
end reason
-- Additional Dimensions
from
aws_s3_bucket;
2023-07-03 14:23:41.777 UTC [35830] LOG: duration: 9988.768 ms execute stmtcache_37: select
-- Required Columns
arn as resource,
case
when server_side_encryption_configuration is not null then 'ok'
else 'alarm'
end status,
case
when server_side_encryption_configuration is not null then name || ' default encryption enabled.'
else name || ' default encryption disabled.'
end reason
-- Additional Dimensions
from
aws_s3_bucket;
2023-07-03 14:23:41.780 UTC [35829] LOG: duration: 9946.066 ms execute stmtcache_38: with ssl_ok as (
select
distinct name,
arn,
'ok' as status
from
aws_s3_bucket,
jsonb_array_elements(policy_std -> 'Statement') as s,
jsonb_array_elements_text(s -> 'Principal' -> 'AWS') as p,
jsonb_array_elements_text(s -> 'Action') as a,
jsonb_array_elements_text(s -> 'Resource') as r,
jsonb_array_elements_text(
s -> 'Condition' -> 'Bool' -> 'aws:securetransport'
) as ssl
where
p = '*'
and s ->> 'Effect' = 'Deny'
and ssl :: bool = false
)
select
-- Required Columns
b.arn as resource,
case
when ok.status = 'ok' then 'ok'
else 'alarm'
end status,
case
when ok.status = 'ok' then b.name || ' bucket policy enforces HTTPS.'
else b.name || ' bucket policy does not enforce HTTPS.'
end reason
-- Additional Dimensions
from
aws_s3_bucket as b
left join ssl_ok as ok on ok.name = b.name;
2023-07-03 14:23:41.789 UTC [INFO] hub: goFdwBeginForeignScan, connection 'aws_001', table 'aws_cloudtrail_trail', explain: false
2023-07-03 14:23:41.791 UTC [WARN] hub: RestrictionsToQuals: failed to convert 1 restriction to quals
2023-07-03 14:23:41.792 UTC [INFO] hub: --------
2023-07-03 14:23:41.792 UTC [INFO] hub: no quals
2023-07-03 14:23:41.792 UTC [INFO] hub: --------
2023-07-03 14:23:41.792 UTC [INFO] hub: StartScan for table: aws_cloudtrail_trail, cache enabled: false, iterator 0xc0009bcc00, 0 quals (1688394221798)
2023-07-03 14:23:41.797 UTC [35832] LOG: duration: 9794.955 ms execute stmtcache_40: with bucket_list as (
select
trim(b::text, '"' ) as bucket_name
from
aws_macie2_classification_job,
jsonb_array_elements(s3_job_definition -> 'BucketDefinitions') as d,
jsonb_array_elements(d -> 'Buckets') as b
)
select
-- Required Columns
b.arn as resource,
case
when b.region = any(array['us-gov-east-1', 'us-gov-west-1']) then 'skip'
when l.bucket_name is not null then 'ok'
else 'alarm'
end status,
case
when b.region = any(array['us-gov-east-1', 'us-gov-west-1']) then b.title || ' not protected by Macie as Macie is not supported in ' || b.region || '.'
when l.bucket_name is not null then b.title || ' protected by Macie.'
else b.title || ' not protected by Macie.'
end reason
-- Additional Dimensions
from
aws_s3_bucket as b
left join bucket_list as l on b.name = l.bucket_name;
2023-07-03 14:23:42.788 UTC [35830] ERROR: deadlock detected at character 1035
2023-07-03 14:23:42.788 UTC [35830] DETAIL: Process 35830 waits for AccessShareLock on relation 49016 of database 16384; blocked by process 35827.
Process 35827 waits for AccessExclusiveLock on relation 49901 of database 16384; blocked by process 35830.
Process 35830: with event_selectors_trail_details as (
select
distinct account_id
from
aws_cloudtrail_trail,
jsonb_array_elements(event_selectors) as e
where
(is_logging and is_multi_region_trail and e ->> 'ReadWriteType' = 'All')
),
advanced_event_selectors_trail_details as (
select
distinct account_id
from
aws_cloudtrail_trail,
jsonb_array_elements_text(advanced_event_selectors) as a
where
-- when readOnly = true, then it is readOnly, when readOnly = false then it is writeOnly, if advanced_event_selectors is not null then it is both ReadWriteType
(is_logging and is_multi_region_trail and advanced_event_selectors is not null and (not a like '%readOnly%'))
)
select
-- Required Columns
a.title as resource,
case
when d.account_id is null and ad.account_id is null then 'alarm'
else 'ok'
end as status,
case
when d.account_id is null and ad.account_id is null then 'cloudtrail disabled.'
else 'cloudtrail enabled.'
end as reason
-- Additional Dimensions
Process 35827: drop schema if exists "aws_001" cascade;
create schema "aws_001";
comment on schema "aws_001" is 'steampipe plugin: hub.steampipe.io/plugins/turbot/aws@latest';
grant usage on schema "aws_001" to steampipe_users;
alter default privileges in schema "aws_001" grant select on tables to steampipe_users;
grant select on all tables in schema "aws_001" to steampipe_users;
import foreign schema "hub.steampipe.io/plugins/turbot/aws@latest" from server steampipe into "aws_001";
2023-07-03 14:23:42.788 UTC [35830] HINT: See server log for query details.
2023-07-03 14:23:42.788 UTC [35830] STATEMENT: with event_selectors_trail_details as (
select
distinct account_id
from
aws_cloudtrail_trail,
jsonb_array_elements(event_selectors) as e
where
(is_logging and is_multi_region_trail and e ->> 'ReadWriteType' = 'All')
),
advanced_event_selectors_trail_details as (
select
distinct account_id
from
aws_cloudtrail_trail,
jsonb_array_elements_text(advanced_event_selectors) as a
where
-- when readOnly = true, then it is readOnly, when readOnly = false then it is writeOnly, if advanced_event_selectors is not null then it is both ReadWriteType
(is_logging and is_multi_region_trail and advanced_event_selectors is not null and (not a like '%readOnly%'))
)
select
-- Required Columns
a.title as resource,
case
when d.account_id is null and ad.account_id is null then 'alarm'
else 'ok'
end as status,
case
when d.account_id is null and ad.account_id is null then 'cloudtrail disabled.'
else 'cloudtrail enabled.'
end as reason
-- Additional Dimensions
from
aws_account as a
left join event_selectors_trail_details as d on d.account_id = a.account_id
left join advanced_event_selectors_trail_details as ad on ad.account_id = a.account_id;