Skip to content

Commit c9d7294

Browse files
jsonprjsonpr
authored
feat(aws-policies): Add in AWS security account contact query (#11729)
Adding Foundational Security Best Practice Policy - net new policy that we haven't covered for AWS Accounts. This check looks for the presence of a security contact for each account. This check tested the following use cases. These cases cover all expected pass and fail use cases for security contacts which are contained within the `aws_account_alternate_contact` table. These alternate tables can include operations, billing, or security: - Account with no alternate contacts (expected fail) - Account with only billing or operations and no security contact (expected fail) - Account with only a security contact (expected pass) - Account with a security contact and other contacts (expected positive) To do so, I joined this with a table that references the AWS Account. This is not an ideal join, but the query uses `aws_iam_accounts` to get an idea of the account we're pulling data from. Another option would be to use ` aws_account_contacts`, but that requires assuming that accounts will always have contact information (which should generally be the case). The other tables considered were `aws_organizations_accounts` but that may not return the data we want due to permissions within the organization and how accounts could be listed depending on visibility. A couple conditions - updating contact information in AWS requires all fields to be populated. Additionally, a lack of security contact will return no data - so we do a NULL check based off a left join (with iam_accounts as the left table). We remove the non-security contacts in the right join (nested select). Right now, we pull contact information from the calling AWS Account. It's possible to pull contact information from a delegated administrator or an administrator account. If we revisit that later, this query will need to be updated.
1 parent 30d415c commit c9d7294

File tree

6 files changed

+73
-1
lines changed

6 files changed

+73
-1
lines changed

‎plugins/source/aws/policies/foundational_security/account.sql‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,3 @@
1+
\set check_id 'Account.1'
2+
\echo "Executing check Account.1"
3+
\ir ../queries/account/security_account_information_provided.sql

‎plugins/source/aws/policies/foundational_security/policy.sql‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9,6 +9,7 @@ SELECT CASE
99
END AS "execution_time" \gset
1010
\set framework 'foundational_security'
1111
\ir ../create_aws_policy_results.sql
12+
\ir ./account.sql
1213
\ir ./acm.sql
1314
\ir ./apigateway.sql
1415
\ir ./autoscaling.sql

‎plugins/source/aws/policies/queries/account/security_account_information_provided.sql‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
insert into aws_policy_results
2+
select
3+
:'execution_time' as execution_time,
4+
:'framework' as framework,
5+
:'check_id' as check_id,
6+
'Security contact information should be provided for an AWS account' as title,
7+
aws_iam_accounts.account_id,
8+
case when
9+
alternate_contact_type is null
10+
then 'fail'
11+
else 'pass'
12+
end as status
13+
FROM aws_iam_accounts
14+
LEFT JOIN (
15+
SELECT * from aws_account_alternate_contacts
16+
WHERE alternate_contact_type = 'SECURITY'
17+
) as account_security_contacts
18+
ON aws_iam_accounts.account_id = account_security_contacts.account_id

‎website/pages/docs/plugins/sources/aws/policies.md‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -196,6 +196,7 @@ AWS Foundational Security Best Practices requires the following tables to be syn
196196
197197
```yaml
198198
tables:
199+
- aws_account_alternate_contacts
199200
- aws_acm_certificates
200201
- aws_apigateway_rest_api_stages
201202
- aws_apigateway_rest_apis
@@ -281,6 +282,7 @@ tables:
281282
### Queries
282283
283284
AWS Foundational Security Best Practices performs the following checks:
285+
- Security contact information should be provided for an AWS account
284286
- certificate has less than 30 days to be renewed
285287
- API Gateway REST and WebSocket API logging should be enabled
286288
- API Gateway REST API stages should be configured to use SSL certificates for backend authentication

‎website/tables/aws/aws_account_alternate_contacts.md‎

Lines changed: 28 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -19,4 +19,31 @@ The composite primary key for this table is (**account_id**, **alternate_contact
1919
|email_address|`utf8`|
2020
|name|`utf8`|
2121
|phone_number|`utf8`|
22-
|title|`utf8`|
22+
|title|`utf8`|
23+
24+
## Example Queries
25+
26+
These SQL queries are sampled from CloudQuery policies and are compatible with PostgreSQL.
27+
28+
### Security contact information should be provided for an AWS account
29+
30+
```sql
31+
SELECT
32+
'Security contact information should be provided for an AWS account' AS title,
33+
aws_iam_accounts.account_id,
34+
CASE WHEN alternate_contact_type IS NULL THEN 'fail' ELSE 'pass' END AS status
35+
FROM
36+
aws_iam_accounts
37+
LEFT JOIN (
38+
SELECT
39+
*
40+
FROM
41+
aws_account_alternate_contacts
42+
WHERE
43+
alternate_contact_type = 'SECURITY'
44+
)
45+
AS account_security_contacts ON
46+
aws_iam_accounts.account_id = account_security_contacts.account_id;
47+
```
48+
49+

‎website/tables/aws/aws_iam_accounts.md‎

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -47,6 +47,27 @@ The primary key for this table is **account_id**.
4747

4848
These SQL queries are sampled from CloudQuery policies and are compatible with PostgreSQL.
4949

50+
### Security contact information should be provided for an AWS account
51+
52+
```sql
53+
SELECT
54+
'Security contact information should be provided for an AWS account' AS title,
55+
aws_iam_accounts.account_id,
56+
CASE WHEN alternate_contact_type IS NULL THEN 'fail' ELSE 'pass' END AS status
57+
FROM
58+
aws_iam_accounts
59+
LEFT JOIN (
60+
SELECT
61+
*
62+
FROM
63+
aws_account_alternate_contacts
64+
WHERE
65+
alternate_contact_type = 'SECURITY'
66+
)
67+
AS account_security_contacts ON
68+
aws_iam_accounts.account_id = account_security_contacts.account_id;
69+
```
70+
5071
### S3 Block Public Access setting should be enabled
5172

5273
```sql

0 commit comments

Comments
 (0)