Skip to content

feat: Updated query of security_groups_with_access_to_unauthorized_ports#13855

Merged
kodiakhq[bot] merged 3 commits intomainfrom
private/ron/EC2_18
Oct 11, 2023
Merged

feat: Updated query of security_groups_with_access_to_unauthorized_ports#13855
kodiakhq[bot] merged 3 commits intomainfrom
private/ron/EC2_18

Conversation

@ronsh12
Copy link
Copy Markdown
Contributor

@ronsh12 ronsh12 commented Sep 13, 2023

A change was made because duplicate rows were inserted into the aws_policy_results table. Additionally, the IP condition should be combined with port conditions using parentheses for proper logical grouping.

@cq-bot cq-bot added the aws label Sep 13, 2023
@ronsh12 ronsh12 mentioned this pull request Sep 13, 2023
Closed
2 tasks
@ronsh12 ronsh12 linked an issue Sep 13, 2023 that may be closed by this pull request
bug: ec2/security_groups_with_access_to_unauthorized_ports.sql is missing parenthesis for where statement #13766
Closed
2 tasks
@ronsh12 ronsh12 requested review from jsonpr and removed request for hermanschaaf and yevgenypats September 26, 2023 12:22
jsonpr
jsonpr suggested changes Oct 9, 2023
View reviewed changes
plugins/source/aws/policies/queries/ec2/security_groups_with_access_to_unauthorized_ports.sql Outdated
end
end as status
FROM view_aws_security_group_ingress_rules
GROUP BY account_id, resource_id, status
Copy link
Copy Markdown
Contributor

@jsonpr jsonpr Oct 9, 2023

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

What happens here when a Security Group has multiple rules - some that pass and some that fail? Will the SG show up twice - where there will be multiple rows for the same SG (but each SG will have 2 rows - one for a pass and one for a fail).

Copy link
Copy Markdown
Contributor Author

@ronsh12 ronsh12 Oct 10, 2023

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

You're correct; I've modified it so that if any single rule fails, the entire SG is considered to have failed.

@jsonpr jsonpr mentioned this pull request Oct 9, 2023
Merged
jsonpr
jsonpr approved these changes Oct 11, 2023
View reviewed changes
plugins/source/aws/policies/queries/ec2/security_groups_with_access_to_unauthorized_ports.sql
account_id,
resource_id,
CASE
WHEN SUM(CASE WHEN status = 'fail' THEN 1 ELSE 0 END) > 0 THEN 'fail'
Copy link
Copy Markdown
Contributor

@jsonpr jsonpr Oct 11, 2023

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Approving this for now - let's choose a consistent way across our logic to do aggregate checks like this.

@jsonpr jsonpr added the automerge Automatically merge once required checks pass label Oct 11, 2023
@kodiakhq kodiakhq bot merged commit efa9e34 into main Oct 11, 2023
@kodiakhq kodiakhq bot deleted the private/ron/EC2_18 branch October 11, 2023 12:09
@cq-bot cq-bot mentioned this pull request Oct 11, 2023
Merged
erezrokah added a commit that referenced this pull request Oct 13, 2023
@cq-bot @erezrokah
chore(main): Release plugins-source-aws v22.15.0 (#14402)
c163eb2 
🤖 I have created a release *beep* *boop*
---


##
[22.15.0](plugins-source-aws-v22.14.0...plugins-source-aws-v22.15.0)
(2023-10-13)


### Features

* Deleted query origin_access_identity_enabled.sql
([#13921](#13921))
([ec77ff8](ec77ff8))
* Introduce spec JSON schema
([#14296](#14296))
([c35f473](c35f473))
* **services:** Support newly added regions
([#14481](#14481))
([672772c](672772c))
* Updated query api_gw_cache_encrypted.sql
([#13860](#13860))
([dca3fe0](dca3fe0))
* Updated query of security_groups_with_access_to_unauthorized_ports
([#13855](#13855))
([efa9e34](efa9e34))
* Updated query of security_groups_with_open_critical_ports
([#13854](#13854))
([6834ee9](6834ee9))
* Updated query rds_databases_and_clusters_should_not_use_a_datab…
([#13936](#13936))
([810078f](810078f))
* Updated query secrets_configured_with_automatic_rotation_should…
([#13934](#13934))
([8b1293c](8b1293c))
* Updated query unused_acls.sql
([#13859](#13859))
([f47df0c](f47df0c))


### Bug Fixes

* Added check for empty string health_status
([#13861](#13861))
([37d8875](37d8875))
* Changed ssm.2 query to reduce redundent rows
([#13933](#13933))
([cee1fab](cee1fab))
* **deps:** Update github.com/cloudquery/arrow/go/v14 digest to d401686
([#14459](#14459))
([7ce40f8](7ce40f8))
* **deps:** Update module github.com/cloudquery/cloudquery-api-go to
v1.2.6 ([#14475](#14475))
([83fe7ca](83fe7ca))
* **deps:** Update module github.com/cloudquery/cloudquery-api-go to
v1.2.8 ([#14503](#14503))
([4056593](4056593))
* **deps:** Update module github.com/cloudquery/plugin-sdk/v4 to v4.12.2
([#14378](#14378))
([a2e0c46](a2e0c46))
* **deps:** Update module github.com/cloudquery/plugin-sdk/v4 to v4.12.3
([#14436](#14436))
([d529e2d](d529e2d))
* **deps:** Update module github.com/cloudquery/plugin-sdk/v4 to v4.12.4
([#14489](#14489))
([9bb45dc](9bb45dc))
* **deps:** Update module github.com/cloudquery/plugin-sdk/v4 to v4.12.5
([#14516](#14516))
([2d905bf](2d905bf))
* **deps:** Update module golang.org/x/net to v0.17.0 [SECURITY]
([#14500](#14500))
([9e603d5](9e603d5))
* Fallback to `eu-west-1` if a given buckets LocationConstraint is `EU`
([#14476](#14476))
([f6433e7](f6433e7))
* Lowercase policy statement in query for KMS.1
([#13858](#13858))
([b161fe1](b161fe1))
* Proper schema for `CustomECSListTasksInput.MaxResults`
([#14502](#14502))
([cdaaa99](cdaaa99))
* Redact Code Location and Fall back to other APIs to fully resolve
`aws_lambda_functions`
([#14381](#14381))
([bf402f4](bf402f4))

---
This PR was generated with [Release
Please](https://github.com/googleapis/release-please). See
[documentation](https://github.com/googleapis/release-please#release-please).

Co-authored-by: Erez Rokah <erezrokah@users.noreply.github.com>
hydratim pushed a commit to hydratim/cloudquery that referenced this pull request Oct 20, 2023
@ronsh12 @hydratim
feat: Updated query of security_groups_with_access_to_unauthorized_po…
1269308 
…rts (cloudquery#13855)

A change was made because duplicate rows were inserted into the aws_policy_results table. Additionally, the IP condition should be combined with port conditions using parentheses for proper logical grouping.
hydratim pushed a commit to hydratim/cloudquery that referenced this pull request Oct 20, 2023
@cq-bot @erezrokah @hydratim
chore(main): Release plugins-source-aws v22.15.0 (cloudquery#14402)
adb2aba 
🤖 I have created a release *beep* *boop*
---


##
[22.15.0](cloudquery/cloudquery@plugins-source-aws-v22.14.0...plugins-source-aws-v22.15.0)
(2023-10-13)


### Features

* Deleted query origin_access_identity_enabled.sql
([cloudquery#13921](cloudquery#13921))
([ec77ff8](cloudquery@ec77ff8))
* Introduce spec JSON schema
([cloudquery#14296](cloudquery#14296))
([c35f473](cloudquery@c35f473))
* **services:** Support newly added regions
([cloudquery#14481](cloudquery#14481))
([672772c](cloudquery@672772c))
* Updated query api_gw_cache_encrypted.sql
([cloudquery#13860](cloudquery#13860))
([dca3fe0](cloudquery@dca3fe0))
* Updated query of security_groups_with_access_to_unauthorized_ports
([cloudquery#13855](cloudquery#13855))
([efa9e34](cloudquery@efa9e34))
* Updated query of security_groups_with_open_critical_ports
([cloudquery#13854](cloudquery#13854))
([6834ee9](cloudquery@6834ee9))
* Updated query rds_databases_and_clusters_should_not_use_a_datab…
([cloudquery#13936](cloudquery#13936))
([810078f](cloudquery@810078f))
* Updated query secrets_configured_with_automatic_rotation_should…
([cloudquery#13934](cloudquery#13934))
([8b1293c](cloudquery@8b1293c))
* Updated query unused_acls.sql
([cloudquery#13859](cloudquery#13859))
([f47df0c](cloudquery@f47df0c))


### Bug Fixes

* Added check for empty string health_status
([cloudquery#13861](cloudquery#13861))
([37d8875](cloudquery@37d8875))
* Changed ssm.2 query to reduce redundent rows
([cloudquery#13933](cloudquery#13933))
([cee1fab](cloudquery@cee1fab))
* **deps:** Update github.com/cloudquery/arrow/go/v14 digest to d401686
([cloudquery#14459](cloudquery#14459))
([7ce40f8](cloudquery@7ce40f8))
* **deps:** Update module github.com/cloudquery/cloudquery-api-go to
v1.2.6 ([cloudquery#14475](cloudquery#14475))
([83fe7ca](cloudquery@83fe7ca))
* **deps:** Update module github.com/cloudquery/cloudquery-api-go to
v1.2.8 ([cloudquery#14503](cloudquery#14503))
([4056593](cloudquery@4056593))
* **deps:** Update module github.com/cloudquery/plugin-sdk/v4 to v4.12.2
([cloudquery#14378](cloudquery#14378))
([a2e0c46](cloudquery@a2e0c46))
* **deps:** Update module github.com/cloudquery/plugin-sdk/v4 to v4.12.3
([cloudquery#14436](cloudquery#14436))
([d529e2d](cloudquery@d529e2d))
* **deps:** Update module github.com/cloudquery/plugin-sdk/v4 to v4.12.4
([cloudquery#14489](cloudquery#14489))
([9bb45dc](cloudquery@9bb45dc))
* **deps:** Update module github.com/cloudquery/plugin-sdk/v4 to v4.12.5
([cloudquery#14516](cloudquery#14516))
([2d905bf](cloudquery@2d905bf))
* **deps:** Update module golang.org/x/net to v0.17.0 [SECURITY]
([cloudquery#14500](cloudquery#14500))
([9e603d5](cloudquery@9e603d5))
* Fallback to `eu-west-1` if a given buckets LocationConstraint is `EU`
([cloudquery#14476](cloudquery#14476))
([f6433e7](cloudquery@f6433e7))
* Lowercase policy statement in query for KMS.1
([cloudquery#13858](cloudquery#13858))
([b161fe1](cloudquery@b161fe1))
* Proper schema for `CustomECSListTasksInput.MaxResults`
([cloudquery#14502](cloudquery#14502))
([cdaaa99](cloudquery@cdaaa99))
* Redact Code Location and Fall back to other APIs to fully resolve
`aws_lambda_functions`
([cloudquery#14381](cloudquery#14381))
([bf402f4](cloudquery@bf402f4))

---
This PR was generated with [Release
Please](https://github.com/googleapis/release-please). See
[documentation](https://github.com/googleapis/release-please#release-please).

Co-authored-by: Erez Rokah <erezrokah@users.noreply.github.com>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

1 more reviewer

@jsonpr jsonpr jsonpr approved these changes

Reviewers whose approvals may not affect merge requirements

Assignees

No one assigned

Labels

automerge Automatically merge once required checks pass

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

bug: ec2/security_groups_with_access_to_unauthorized_ports.sql is missing parenthesis for where statement

3 participants

@ronsh12 @jsonpr @cq-bot