turbot/alicloud
steampipe plugin install alicloud

Table: alicloud_ecs_security_group - Query Alibaba Cloud ECS Security Groups using SQL

An Alibaba Cloud ECS Security Group is a logical group that segregates ECS instances in different security domains. It acts as a virtual firewall to control inbound and outbound traffic for one or more ECS instances. It is a crucial component for managing the security of Alibaba Cloud ECS instances.

Table Usage Guide

The alicloud_ecs_security_group table provides insights into the security configurations of Alibaba Cloud ECS instances. As a security analyst, you can use this table to explore the security group settings for each ECS instance, including inbound and outbound rules, and associated metadata. Use this table to identify instances with potentially risky security settings, such as open ports or unrestricted IP access.

Examples

List of security groups where all instances within the security group are isolated from each other

Determine the areas in which security groups are configured such that all instances within them are isolated from each other. This can be useful for identifying potential security risks and ensuring robust access control.

select
name,
security_group_id,
type,
inner_access_policy
from
alicloud_ecs_security_group
where
inner_access_policy = 'drop';
select
name,
security_group_id,
type,
inner_access_policy
from
alicloud_ecs_security_group
where
inner_access_policy = 'drop';

Get the security group rules of each security group

Explore the security settings of your system by identifying the rules of each security group. This is useful for auditing security measures and ensuring appropriate access controls are in place.

select
name,
security_group_id,
p ->> 'IpProtocol' as ip_protocol_type,
p ->> 'PortRange' as port_range,
p ->> 'Direction' as direction,
p ->> 'SourceCidrIp' as source_cidr_ip,
p ->> 'SourcePortRange' as source_port_range
from
alicloud_ecs_security_group,
jsonb_array_elements(permissions) as p;
select
name,
security_group_id,
json_extract(p.value, '$.IpProtocol') as ip_protocol_type,
json_extract(p.value, '$.PortRange') as port_range,
json_extract(p.value, '$.Direction') as direction,
json_extract(p.value, '$.SourceCidrIp') as source_cidr_ip,
json_extract(p.value, '$.SourcePortRange') as source_port_range
from
alicloud_ecs_security_group,
json_each(permissions) as p;

List of all enterprise security groups

Explore which enterprise-level security groups are active in various regions. This can be useful for maintaining oversight of your security measures and ensuring they align with your company's standards.

select
name,
security_group_id,
region_id,
type
from
alicloud_ecs_security_group
where
type = 'enterprise';
select
name,
security_group_id,
region_id,
type
from
alicloud_ecs_security_group
where
type = 'enterprise';

Count of security groups by VPC ID

Analyze the settings to understand the distribution of security groups across different VPCs. This can aid in managing network access and security configurations more effectively.

select
vpc_id,
count(*) as count
from
alicloud_ecs_security_group
group by
vpc_id;
select
vpc_id,
count(*) as count
from
alicloud_ecs_security_group
group by
vpc_id;

Get the security group rules that allow inbound public access to all tcp or udp ports

This query helps to identify security group rules that could potentially expose your system to threats by allowing unrestricted inbound public access via any TCP or UDP port. This information is crucial for assessing potential vulnerabilities and taking steps to enhance your system's security.

select
name,
security_group_id,
p ->> 'IpProtocol' as ip_protocol_type,
p ->> 'PortRange' as port_range,
p ->> 'Direction' as direction,
p ->> 'SourceCidrIp' as source_cidr_ip,
p ->> 'SourcePortRange' as source_port_range
from
alicloud_ecs_security_group,
jsonb_array_elements(permissions) as p
where
p ->> 'IpProtocol' in ('TCP', 'UDP', 'ALL')
and p ->> 'Direction' = 'ingress'
and p ->> 'SourceCidrIp' = '0.0.0.0/0'
and (
p ->> 'PortRange' = '-1/-1'
or p ->> 'PortRange' = '1/65535'
);
select
name,
security_group_id,
json_extract(p.value, '$.IpProtocol') as ip_protocol_type,
json_extract(p.value, '$.PortRange') as port_range,
json_extract(p.value, '$.Direction') as direction,
json_extract(p.value, '$.SourceCidrIp') as source_cidr_ip,
json_extract(p.value, '$.SourcePortRange') as source_port_range
from
alicloud_ecs_security_group,
json_each(permissions) as p
where
json_extract(p.value, '$.IpProtocol') in ('TCP', 'UDP', 'ALL')
and json_extract(p.value, '$.Direction') = 'ingress'
and json_extract(p.value, '$.SourceCidrIp') = '0.0.0.0/0'
and (
json_extract(p.value, '$.PortRange') = '-1/-1'
or json_extract(p.value, '$.PortRange') = '1/65535'
);

Get the security group rules that allow inbound public access to all tcp or udp ports, along with instances attached to them

This query is useful for identifying potential security vulnerabilities in your system. It reveals the security group rules that permit unrestricted inbound public access to all TCP or UDP ports, and also lists the instances associated with these rules. This can help in strengthening your system's security by pinpointing areas of potential weakness.

select
i.name,
i.instance_id,
sg.name,
sg.security_group_id,
p ->> 'IpProtocol' as ip_protocol_type,
p ->> 'PortRange' as port_range,
p ->> 'Direction' as direction,
p ->> 'SourceCidrIp' as source_cidr_ip,
p ->> 'SourcePortRange' as source_port_range
from
alicloud_ecs_security_group as sg,
jsonb_array_elements(permissions) as p,
alicloud_ecs_instance as i,
jsonb_array_elements_text(i.security_group_ids) as instance_sg
where
p ->> 'IpProtocol' in ('TCP', 'UDP', 'ALL')
and p ->> 'Direction' = 'ingress'
and p ->> 'SourceCidrIp' = '0.0.0.0/0'
and (
p ->> 'PortRange' = '-1/-1'
or p ->> 'PortRange' = '1/65535'
)
and instance_sg = sg.security_group_id;
select
i.name,
i.instance_id,
sg.name,
sg.security_group_id,
json_extract(p.value, '$.IpProtocol') as ip_protocol_type,
json_extract(p.value, '$.PortRange') as port_range,
json_extract(p.value, '$.Direction') as direction,
json_extract(p.value, '$.SourceCidrIp') as source_cidr_ip,
json_extract(p.value, '$.SourcePortRange') as source_port_range
from
alicloud_ecs_security_group as sg,
json_each(permissions) as p,
alicloud_ecs_instance as i,
json_each(i.security_group_ids) as instance_sg
where
json_extract(p.value, '$.IpProtocol') in ('TCP', 'UDP', 'ALL')
and json_extract(p.value, '$.Direction') = 'ingress'
and json_extract(p.value, '$.SourceCidrIp') = '0.0.0.0/0'
and (
json_extract(p.value, '$.PortRange') = '-1/-1'
or json_extract(p.value, '$.PortRange') = '1/65535'
)
and instance_sg.value = sg.security_group_id;

Schema for alicloud_ecs_security_group

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
account_idtext=, !=, ~~, ~~*, !~~, !~~*The alicloud Account ID in which the resource is located.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
arntextThe Alibaba Cloud Resource Name (ARN) of the ECS security group.
creation_timetimestamp with time zoneThe time when the security group was created.
descriptiontextThe description of the security group.
inner_access_policytextThe description of the security group.
nametextThe name of the security group.
permissionsjsonbDetails about the security group rules.
regiontextThe name of the region where the resource belongs.
resource_group_idtextThe ID of the resource group to which the security group belongs.
security_group_idtext=The ID of the security group.
service_idbigintThe ID of the distributor to which the security group belongs.
service_managedbooleanIndicates whether the user is an Alibaba Cloud service or a distributor.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
tagsjsonbA map of tags for the resource.
tags_srcjsonbA list of tags attached with the security group.
titletextTitle of the resource.
typetextThe type of the security group. Possible values are: normal, and enterprise.
vpc_idtexthe ID of the VPC to which the security group belongs.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- alicloud

You can pass the configuration to the command with the --config argument:

steampipe_export_alicloud --config '<your_config>' alicloud_ecs_security_group