turbot/aws

GitHub
steampipe plugin install awssteampipe plugin install aws
aws_accessanalyzer_analyzeraws_accountaws_account_alternate_contactaws_account_contactaws_acm_certificateaws_amplify_appaws_api_gateway_api_authorizeraws_api_gateway_api_keyaws_api_gateway_authorizeraws_api_gateway_rest_apiaws_api_gateway_stageaws_api_gateway_usage_planaws_api_gatewayv2_apiaws_api_gatewayv2_domain_nameaws_api_gatewayv2_integrationaws_api_gatewayv2_routeaws_api_gatewayv2_stageaws_appautoscaling_targetaws_appconfig_applicationaws_appstream_imageaws_athena_query_executionaws_athena_workgroupaws_auditmanager_assessmentaws_auditmanager_controlaws_auditmanager_evidenceaws_auditmanager_evidence_folderaws_auditmanager_frameworkaws_availability_zoneaws_backup_frameworkaws_backup_legal_holdaws_backup_planaws_backup_protected_resourceaws_backup_recovery_pointaws_backup_selectionaws_backup_vaultaws_cloudcontrol_resourceaws_cloudformation_stackaws_cloudformation_stack_resourceaws_cloudfront_cache_policyaws_cloudfront_distributionaws_cloudfront_functionaws_cloudfront_origin_access_identityaws_cloudfront_origin_request_policyaws_cloudfront_response_headers_policyaws_cloudsearch_domainaws_cloudtrail_channelaws_cloudtrail_event_data_storeaws_cloudtrail_importaws_cloudtrail_queryaws_cloudtrail_trailaws_cloudtrail_trail_eventaws_cloudwatch_alarmaws_cloudwatch_log_eventaws_cloudwatch_log_groupaws_cloudwatch_log_metric_filteraws_cloudwatch_log_resource_policyaws_cloudwatch_log_streamaws_cloudwatch_log_subscription_filteraws_cloudwatch_metricaws_cloudwatch_metric_data_pointaws_cloudwatch_metric_statistic_data_pointaws_codeartifact_domainaws_codeartifact_repositoryaws_codebuild_buildaws_codebuild_projectaws_codebuild_source_credentialaws_codecommit_repositoryaws_codedeploy_appaws_codedeploy_deployment_configaws_codedeploy_deployment_groupaws_codepipeline_pipelineaws_config_aggregate_authorizationaws_config_configuration_recorderaws_config_conformance_packaws_config_retention_configurationaws_config_ruleaws_cost_by_account_dailyaws_cost_by_account_monthlyaws_cost_by_record_type_dailyaws_cost_by_record_type_monthlyaws_cost_by_service_dailyaws_cost_by_service_monthlyaws_cost_by_service_usage_type_dailyaws_cost_by_service_usage_type_monthlyaws_cost_by_tagaws_cost_forecast_dailyaws_cost_forecast_monthlyaws_cost_usageaws_dax_clusteraws_dax_parameteraws_dax_parameter_groupaws_dax_subnet_groupaws_directory_service_directoryaws_dlm_lifecycle_policyaws_dms_replication_instanceaws_docdb_clusteraws_docdb_cluster_instanceaws_drs_jobaws_drs_recovery_instanceaws_drs_recovery_snapshotaws_drs_source_serveraws_dynamodb_backupaws_dynamodb_global_tableaws_dynamodb_metric_account_provisioned_read_capacity_utilaws_dynamodb_metric_account_provisioned_write_capacity_utilaws_dynamodb_tableaws_dynamodb_table_exportaws_ebs_snapshotaws_ebs_volumeaws_ebs_volume_metric_read_opsaws_ebs_volume_metric_read_ops_dailyaws_ebs_volume_metric_read_ops_hourlyaws_ebs_volume_metric_write_opsaws_ebs_volume_metric_write_ops_dailyaws_ebs_volume_metric_write_ops_hourlyaws_ec2_amiaws_ec2_ami_sharedaws_ec2_application_load_balanceraws_ec2_application_load_balancer_metric_request_countaws_ec2_application_load_balancer_metric_request_count_dailyaws_ec2_autoscaling_groupaws_ec2_capacity_reservationaws_ec2_classic_load_balanceraws_ec2_client_vpn_endpointaws_ec2_gateway_load_balanceraws_ec2_instanceaws_ec2_instance_availabilityaws_ec2_instance_metric_cpu_utilizationaws_ec2_instance_metric_cpu_utilization_dailyaws_ec2_instance_metric_cpu_utilization_hourlyaws_ec2_instance_typeaws_ec2_key_pairaws_ec2_launch_configurationaws_ec2_launch_templateaws_ec2_launch_template_versionaws_ec2_load_balancer_listeneraws_ec2_managed_prefix_listaws_ec2_network_interfaceaws_ec2_network_load_balanceraws_ec2_network_load_balancer_metric_net_flow_countaws_ec2_network_load_balancer_metric_net_flow_count_dailyaws_ec2_regional_settingsaws_ec2_reserved_instanceaws_ec2_spot_priceaws_ec2_ssl_policyaws_ec2_target_groupaws_ec2_transit_gatewayaws_ec2_transit_gateway_routeaws_ec2_transit_gateway_route_tableaws_ec2_transit_gateway_vpc_attachmentaws_ecr_imageaws_ecr_image_scan_findingaws_ecr_repositoryaws_ecrpublic_repositoryaws_ecs_clusteraws_ecs_cluster_metric_cpu_utilizationaws_ecs_cluster_metric_cpu_utilization_dailyaws_ecs_cluster_metric_cpu_utilization_hourlyaws_ecs_container_instanceaws_ecs_serviceaws_ecs_taskaws_ecs_task_definitionaws_efs_access_pointaws_efs_file_systemaws_efs_mount_targetaws_eks_addonaws_eks_addon_versionaws_eks_clusteraws_eks_fargate_profileaws_eks_identity_provider_configaws_eks_node_groupaws_elastic_beanstalk_applicationaws_elastic_beanstalk_environmentaws_elasticache_clusteraws_elasticache_parameter_groupaws_elasticache_redis_metric_cache_hits_hourlyaws_elasticache_redis_metric_curr_connections_hourlyaws_elasticache_redis_metric_engine_cpu_utilization_dailyaws_elasticache_redis_metric_engine_cpu_utilization_hourlyaws_elasticache_redis_metric_get_type_cmds_hourlyaws_elasticache_redis_metric_list_based_cmds_hourlyaws_elasticache_redis_metric_new_connections_hourlyaws_elasticache_replication_groupaws_elasticache_reserved_cache_nodeaws_elasticache_subnet_groupaws_elasticsearch_domainaws_emr_block_public_access_configurationaws_emr_clusteraws_emr_cluster_metric_is_idleaws_emr_instanceaws_emr_instance_fleetaws_emr_instance_groupaws_eventbridge_busaws_eventbridge_ruleaws_fsx_file_systemaws_glacier_vaultaws_globalaccelerator_acceleratoraws_globalaccelerator_endpoint_groupaws_globalaccelerator_listeneraws_glue_catalog_databaseaws_glue_catalog_tableaws_glue_connectionaws_glue_crawleraws_glue_data_catalog_encryption_settingsaws_glue_data_quality_rulesetaws_glue_dev_endpointaws_glue_jobaws_glue_security_configurationaws_guardduty_detectoraws_guardduty_filteraws_guardduty_findingaws_guardduty_ipsetaws_guardduty_memberaws_guardduty_publishing_destinationaws_guardduty_threat_intel_setaws_health_affected_entityaws_health_eventaws_iam_access_advisoraws_iam_access_keyaws_iam_account_password_policyaws_iam_account_summaryaws_iam_actionaws_iam_credential_reportaws_iam_groupaws_iam_policyaws_iam_policy_attachmentaws_iam_policy_simulatoraws_iam_roleaws_iam_saml_provideraws_iam_server_certificateaws_iam_service_specific_credentialaws_iam_useraws_iam_virtual_mfa_deviceaws_identitystore_groupaws_identitystore_useraws_inspector2_coverageaws_inspector2_coverage_statisticsaws_inspector2_findingaws_inspector2_memberaws_inspector_assessment_runaws_inspector_assessment_targetaws_inspector_assessment_templateaws_inspector_exclusionaws_inspector_findingaws_kinesis_consumeraws_kinesis_firehose_delivery_streamaws_kinesis_streamaws_kinesis_video_streamaws_kinesisanalyticsv2_applicationaws_kms_aliasaws_kms_keyaws_lambda_aliasaws_lambda_functionaws_lambda_function_metric_duration_dailyaws_lambda_function_metric_errors_dailyaws_lambda_function_metric_invocations_dailyaws_lambda_layeraws_lambda_layer_versionaws_lambda_versionaws_lightsail_instanceaws_macie2_classification_jobaws_media_store_containeraws_mgn_applicationaws_msk_clusteraws_msk_serverless_clusteraws_neptune_db_clusteraws_networkfirewall_firewallaws_networkfirewall_firewall_policyaws_networkfirewall_rule_groupaws_oam_linkaws_oam_sinkaws_opensearch_domainaws_organizations_accountaws_organizations_policyaws_pinpoint_appaws_pipes_pipeaws_pricing_productaws_pricing_service_attributeaws_ram_principal_associationaws_ram_resource_associationaws_rds_db_clusteraws_rds_db_cluster_parameter_groupaws_rds_db_cluster_snapshotaws_rds_db_event_subscriptionaws_rds_db_instanceaws_rds_db_instance_automated_backupaws_rds_db_instance_metric_connectionsaws_rds_db_instance_metric_connections_dailyaws_rds_db_instance_metric_connections_hourlyaws_rds_db_instance_metric_cpu_utilizationaws_rds_db_instance_metric_cpu_utilization_dailyaws_rds_db_instance_metric_cpu_utilization_hourlyaws_rds_db_instance_metric_read_iopsaws_rds_db_instance_metric_read_iops_dailyaws_rds_db_instance_metric_read_iops_hourlyaws_rds_db_instance_metric_write_iopsaws_rds_db_instance_metric_write_iops_dailyaws_rds_db_instance_metric_write_iops_hourlyaws_rds_db_option_groupaws_rds_db_parameter_groupaws_rds_db_proxyaws_rds_db_snapshotaws_rds_db_subnet_groupaws_rds_reserved_db_instanceaws_redshift_clusteraws_redshift_cluster_metric_cpu_utilization_dailyaws_redshift_event_subscriptionaws_redshift_parameter_groupaws_redshift_snapshotaws_redshift_subnet_groupaws_redshiftserverless_namespaceaws_redshiftserverless_workgroupaws_regionaws_resource_explorer_indexaws_resource_explorer_searchaws_resource_explorer_supported_resource_typeaws_route53_domainaws_route53_health_checkaws_route53_recordaws_route53_resolver_endpointaws_route53_resolver_ruleaws_route53_traffic_policyaws_route53_traffic_policy_instanceaws_route53_zoneaws_s3_access_pointaws_s3_account_settingsaws_s3_bucketaws_s3_multi_region_access_pointaws_s3_objectaws_sagemaker_appaws_sagemaker_domainaws_sagemaker_endpoint_configurationaws_sagemaker_modelaws_sagemaker_notebook_instanceaws_sagemaker_training_jobaws_secretsmanager_secretaws_securityhub_action_targetaws_securityhub_findingaws_securityhub_finding_aggregatoraws_securityhub_hubaws_securityhub_insightaws_securityhub_memberaws_securityhub_productaws_securityhub_standards_controlaws_securityhub_standards_subscriptionaws_securitylake_data_lakeaws_securitylake_subscriberaws_serverlessapplicationrepository_applicationaws_service_discovery_instanceaws_service_discovery_namespaceaws_service_discovery_serviceaws_servicecatalog_portfolioaws_servicecatalog_productaws_servicequotas_default_service_quotaaws_servicequotas_service_quotaaws_servicequotas_service_quota_change_requestaws_ses_domain_identityaws_ses_email_identityaws_sfn_state_machineaws_sfn_state_machine_executionaws_sfn_state_machine_execution_historyaws_simspaceweaver_simulationaws_sns_topicaws_sns_topic_subscriptionaws_sqs_queueaws_ssm_associationaws_ssm_documentaws_ssm_document_permissionaws_ssm_inventoryaws_ssm_inventory_entryaws_ssm_maintenance_windowaws_ssm_managed_instanceaws_ssm_managed_instance_complianceaws_ssm_managed_instance_patch_stateaws_ssm_parameteraws_ssm_patch_baselineaws_ssoadmin_account_assignmentaws_ssoadmin_instanceaws_ssoadmin_managed_policy_attachmentaws_ssoadmin_permission_setaws_sts_caller_identityaws_tagging_resourceaws_vpcaws_vpc_customer_gatewayaws_vpc_dhcp_optionsaws_vpc_egress_only_internet_gatewayaws_vpc_eipaws_vpc_eip_address_transferaws_vpc_endpointaws_vpc_endpoint_serviceaws_vpc_flow_logaws_vpc_flow_log_eventaws_vpc_internet_gatewayaws_vpc_nat_gatewayaws_vpc_network_aclaws_vpc_peering_connectionaws_vpc_routeaws_vpc_route_tableaws_vpc_security_groupaws_vpc_security_group_ruleaws_vpc_subnetaws_vpc_verified_access_endpointaws_vpc_verified_access_groupaws_vpc_verified_access_instanceaws_vpc_verified_access_trust_provideraws_vpc_vpn_connectionaws_vpc_vpn_gatewayaws_waf_rate_based_ruleaws_waf_ruleaws_waf_rule_groupaws_waf_web_aclaws_wafregional_ruleaws_wafregional_rule_groupaws_wafregional_web_aclaws_wafv2_ip_setaws_wafv2_regex_pattern_setaws_wafv2_rule_groupaws_wafv2_web_aclaws_wellarchitected_answeraws_wellarchitected_check_detailaws_wellarchitected_check_summaryaws_wellarchitected_consolidated_reportaws_wellarchitected_lensaws_wellarchitected_lens_reviewaws_wellarchitected_lens_review_improvementaws_wellarchitected_lens_review_reportaws_wellarchitected_lens_shareaws_wellarchitected_milestoneaws_wellarchitected_notificationaws_wellarchitected_share_invitationaws_wellarchitected_workloadaws_wellarchitected_workload_shareaws_workspaces_workspace

Table: aws_iam_role

An IAM role is an AWS Identity and Access Management (IAM) entity with permissions to make AWS service requests.

Examples

List of IAM roles with no inline policy

select
name,
create_date
from
aws_iam_role
where
inline_policies is null;

List the policies attached to the roles

select
name,
description,
split_part(policy, '/', 3) as attached_policy
from
aws_iam_role
cross join jsonb_array_elements_text(attached_policy_arns) as policy;

Permission boundary information for each role

select
name,
description,
permissions_boundary_arn,
permissions_boundary_type
from
aws_iam_role;

Find all roles that allow *

select
r.name as role_name,
p.name as policy_name
from
aws_iam_role as r,
jsonb_array_elements_text(r.attached_policy_arns) as policy_arn,
aws_iam_policy as p,
jsonb_array_elements(p.policy_std -> 'Statement') as stmt,
jsonb_array_elements_text(stmt -> 'Action') as action
where
policy_arn = p.arn
and stmt ->> 'Effect' = 'Allow'
and action = '*'
order by
r.name;

Find any roles that allow wildcard actions

select
r.name as role_name,
p.name as policy_name,
stmt ->> 'Sid' as statement,
action
from
aws_iam_role as r,
jsonb_array_elements_text(r.attached_policy_arns) as policy_arn,
aws_iam_policy as p,
jsonb_array_elements(p.policy_std -> 'Statement') as stmt,
jsonb_array_elements_text(stmt -> 'Action') as action
where
r.name = 'owner'
and policy_arn = p.arn
and (
action like '%*%'
or action like '%?%'
);

List higher-level permissions for any specific role

select
r.name,
a.action,
a.access_level,
a.description
from
aws_iam_role as r,
jsonb_array_elements_text(r.attached_policy_arns) as pol_arn,
aws_iam_policy as p,
jsonb_array_elements(p.policy_std -> 'Statement') as stmt,
jsonb_array_elements_text(stmt -> 'Action') as action_glob,
glob(action_glob) as action_regex
join aws_iam_action as a on a.action like action_regex
where
pol_arn = p.arn
and stmt ->> 'Effect' = 'Allow'
and r.name = 'AWSServiceRoleForRDS'
and access_level not in ('List', 'Read')
order by
action;

List all actions (with level) in role2, not in role1

with roles as (
select
name,
attached_policy_arns
from
aws_iam_role
where
name in ('AWSServiceRoleForSSO', 'AWSServiceRoleForRDS')
),
policies as (
select
name,
arn,
policy_std
from
aws_iam_policy
),
role1_permissions as (
select
r.name,
a.action,
a.access_level,
a.description
from
roles as r,
jsonb_array_elements_text(r.attached_policy_arns) as pol_arn,
policies as p,
jsonb_array_elements(p.policy_std -> 'Statement') as stmt,
jsonb_array_elements_text(stmt -> 'Action') as action_glob,
glob (action_glob) as action_regex
join aws_iam_action a on a.action like action_regex
where
pol_arn = p.arn
and stmt ->> 'Effect' = 'Allow'
and r.name = 'AWSServiceRoleForSSO'
),
role2_permissions as (
select
r.name,
a.action,
a.access_level,
a.description
from
roles as r,
jsonb_array_elements_text(r.attached_policy_arns) as pol_arn,
policies as p,
jsonb_array_elements(p.policy_std -> 'Statement') as stmt,
jsonb_array_elements_text(stmt -> 'Action') as action_glob,
glob (action_glob) as action_regex
join aws_iam_action a on a.action like action_regex
where
pol_arn = p.arn
and stmt ->> 'Effect' = 'Allow'
and r.name = 'AWSServiceRoleForRDS'
)
select
*
from
role2_permissions
where
action not in (
select
action
from
role1_permissions
)
order by
action;

List role with wildcard principal in trust policy(maintenance-role) and role(admin-role) that have trust relationship with maintenance-role

Refer here

select
maintenance.name,
admin.name,
jsonb_pretty(maintenance_stmt),
jsonb_pretty(admin_stmt)
from
-- use the account to get the organization_id
aws_account as a,
-- check any role as the "maintenance-role"
aws_iam_role as maintenance,
-- Combine via join with any role as the "admin-role"
aws_iam_role as admin,
jsonb_array_elements(maintenance.assume_role_policy_std -> 'Statement') as maintenance_stmt,
jsonb_array_elements(admin.assume_role_policy_std -> 'Statement') as admin_stmt
where
-- maintenance role can be assumed by any AWS principal
maintenance_stmt -> 'Principal' -> 'AWS' ? '*' -- maintenance role principal must be in same account
and maintenance_stmt -> 'Condition' -> 'StringEquals' -> 'aws:principalorgid' ? a.organization_id -- admin role specifically allow maintenance role
and admin_stmt -> 'Principal' -> 'AWS' ? maintenance.arn;

List the roles that might allow other roles/users to bypass their assigned IAM permissions.

select
r.name,
stmt
from
aws_iam_role as r,
jsonb_array_elements(r.assume_role_policy_std -> 'Statement') as stmt,
jsonb_array_elements_text(stmt -> 'Principal' -> 'AWS') as trust
where
trust = '*'
or trust like 'arn:aws:iam::%:role/%'

Verify the Trust policy of Role has validation conditions when used with GitHub Actions

select
iam.arn as resource,
iam.description,
iam.assume_role_policy_std,
case
when pstatement -> 'Condition' -> 'StringLike' -> 'token.actions.githubusercontent.com:sub' is not null
or pstatement -> 'Condition' -> 'StringEquals' -> 'token.actions.githubusercontent.com:sub' is not null then 'ok'
else 'alarm'
end as status,
case
when pstatement -> 'Condition' -> 'StringLike' -> 'token.actions.githubusercontent.com:sub' is not null
or pstatement -> 'Condition' -> 'StringEquals' -> 'token.actions.githubusercontent.com:sub' is not null then iam.arn || ' Condition Check Exists'
else iam.arn || ' Missing Condition Check'
end as reason
from
aws_iam_role as iam,
jsonb_array_elements(iam.assume_role_policy_std -> 'Statement') as pstatement
where
pstatement -> 'Action' ? & array [ 'sts:assumerolewithwebidentity' ]
and (pstatement -> 'Principal' -> 'Federated') :: text like '%token.actions.githubusercontent.com%'
order by
status asc

Query examples

Control examples

.inspect aws_iam_role

AWS IAM Role

NameTypeDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
account_idtextThe AWS Account ID in which the resource is located.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
arntextThe Amazon Resource Name (ARN) specifying the role.
assume_role_policyjsonbThe policy that grants an entity permission to assume the role.
assume_role_policy_stdjsonbContains the assume role policy in a canonical form for easier searching.
attached_policy_arnsjsonbA list of managed policies attached to the role.
create_datetimestamp with time zoneThe date and time when the role was created.
descriptiontextA user-provided description of the role.
inline_policiesjsonbA list of policy documents that are embedded as inline policies for the role..
inline_policies_stdjsonbInline policies in canonical form for the role.
instance_profile_arnsjsonbA list of instance profiles associated with the role.
max_session_durationbigintThe maximum session duration (in seconds) for the specified role. Anyone who uses the AWS CLI, or API to assume the role can specify the duration using the optional DurationSeconds API parameter or duration-seconds CLI parameter.
nametextThe friendly name that identifies the role.
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
pathtextThe path to the role.
permissions_boundary_arntextThe ARN of the policy used to set the permissions boundary for the role.
permissions_boundary_typetextThe permissions boundary usage type that indicates what type of IAM resource is used as the permissions boundary for an entity. This data type can only have a value of Policy.
regiontextThe AWS Region in which the resource is located.
role_idtextThe stable and unique string identifying the role.
role_last_used_datetimestamp with time zoneContains information about the last time that an IAM role was used. Activity is only reported for the trailing 400 days. This period can be shorter if your Region began supporting these features within the last year. The role might have been used more than 400 days ago.
role_last_used_regiontextContains the region in which the IAM role was used.
tagsjsonbA map of tags for the resource.
tags_srcjsonbA list of tags that are attached to the role.
titletextTitle of the resource.