Open6ヶ月前にコメント追加3

SteampipeでEC2インスタンスのSSH設定を可視化する

AWS

Junichi Hashimoto

インスタンスにアタッチされたセキュリティグループを列挙する。

> select
  instance_id,
  sg ->> 'GroupId' as group_id,
  sg ->> 'GroupName' as group_name
from
  aws_ec2_instance
  cross join jsonb_array_elements(security_groups) as sg
+---------------------+----------------------+--------------------------------------------+
| instance_id         | group_id             | group_name                                 |
+---------------------+----------------------+--------------------------------------------+
| i-00f770f71905be9f3 | sg-0ee4dbfc4b325064a | Ec2Stack-Ec2SgF43886FE-E9ZcTVJk2Rje        |
| i-00f770f71905be9f3 | sg-0d71c4598fc6da809 | Ec2Stack-Ec2BastionSg1229C2E0-UOHiVMPERJ60 |
+---------------------+----------------------+--------------------------------------------+

参考:

Junichi Hashimoto

SSHおよびRDPを許可するセキュリティグループルールのリスト。

> select
  security_group_rule_id,
  group_id,
  ip_protocol,
  from_port,
  to_port,
  cidr_ipv4
from
  aws_vpc_security_group_rule
where
  not is_egress
  and (
    (
      ip_protocol = '-1' -- all traffic
      and from_port is null
    )
    or (
      from_port <= 22
      and to_port >= 22
    )
    or (
      from_port <= 3389
      and to_port >= 3389
    )
  );
+------------------------+----------------------+-------------+-----------+---------+-----------+
| security_group_rule_id | group_id             | ip_protocol | from_port | to_port | cidr_ipv4 |
+------------------------+----------------------+-------------+-----------+---------+-----------+
| sgr-0db01e8004e6d7c67  | sg-0ee4dbfc4b325064a | tcp         | 22        | 22      | 0.0.0.0/0 |
+------------------------+----------------------+-------------+-----------+---------+-----------+

Junichi Hashimoto

インスタンスに許可されたSSHとRDPのリスト。

> select
  i.instance_id as "インスタンスID",
  i.tags ->> 'Name' as "インスタンス名",
  sg ->> 'GroupId' as "セキュリティグループID",
  sg ->> 'GroupName' as "セキュリティグループ名",
  sgr.ip_protocol as "プロトコル",
  sgr.from_port as "許可ポート",
  sgr.cidr_ipv4 as "ソース",
  sgr.description as "説明"
from
  aws_ec2_instance as i,
  jsonb_array_elements(i.security_groups) as sg,
  aws_vpc_security_group_rule as sgr
where
  sg ->> 'GroupId' = sgr.group_id
  and sgr.type = 'ingress'
  and sgr.ip_protocol = 'tcp'
  and sgr.from_port in (22, 3389)
order by
  i.instance_id,
  sgr.from_port;

+---------------------+----------------+------------------------+--------------------------------------------+------------+------------+-----------+---------------------------------+
| インスタンスID      | インスタンス名 | セキュリティグループID | セキュリティグループ名                     | プロトコル | 許可ポート | ソース    | 説明                            |
+---------------------+----------------+------------------------+--------------------------------------------+------------+------------+-----------+---------------------------------+
| i-09df5c1c7d4aec5cc | ec2Web         | sg-035a1688a45623a02   | Ec2Stack-Ec2SgF43886FE-cEnQWY1ORvxi        | tcp        | 22         | 0.0.0.0/0 | allow ssh traffic from anywhere |
| i-09df5c1c7d4aec5cc | ec2Web         | sg-0b8d7b472a2b9dae3   | Ec2Stack-Ec2BastionSg1229C2E0-0cZax68KedCT | tcp        | 3389       | 0.0.0.0/0 | <null>                          |
+---------------------+----------------+------------------------+--------------------------------------------+------------+------------+-----------+---------------------------------+