Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

PR#2357 (aws plugin 1.5.0) breaks aws_ecr_image_scan_finding - 'List' call for table 'aws_ecr_image_scan_finding' is missing 1 required qual: column:'repository_name' #2367

Open
captainfalcon23 opened this issue Jan 6, 2025 · 5 comments · May be fixed by #2376
Open

PR#2357 (aws plugin 1.5.0) breaks aws_ecr_image_scan_finding - 'List' call for table 'aws_ecr_image_scan_finding' is missing 1 required qual: column:'repository_name' #2367

captainfalcon23 opened this issue Jan 6, 2025 · 5 comments · May be fixed by #2376
Assignees
@ParthaI
Labels
bug Something isn't working

Comments

@captainfalcon23
Copy link

captainfalcon23 commented Jan 6, 2025 •
edited
Loading

Describe the bug
When using AWS provider 1.4.0, the following query works fine and returns results with no errors as expected and works fine. However, after upgrading to aws plugin 1.5.0, we get the following error:

Error: rpc error: code = Internal desc = myawsaccount: rpc error: code = Internal desc = 'List' call for table 'aws_ecr_image_scan_finding' is missing 1 required qual: column:'repository_name' operator: =
 (SQLSTATE HV000)

select
    distinct REPLACE(
        REPLACE(innerq."AWS_ACCOUNT_NAME", 'aws_', ''),
        '_',
        '-'
    ) as "AWS_ACCOUNT_NAME",
    innerq.region as "AWS_REGION",
    innerq.repository_name as "AWS_REPO",
    innerq.image_tag as "ECR_IMAGE_TAG",
    innerq.name as "COMMON_VULN_ID",
    innerq.uri as "VULN_INFO_URL",
    innerq.severity as "VULN_SEVERITY",
    innerq.package_version as "VULN_PACKAGE_VERSION",
    innerq.package_name as "VULN_PACKAGE_NAME"
from
    (
        with latest_image_ts as (
            select
                repository_name,
                max(image_pushed_at) as image_pushed_at
            from
                myawsaccount.aws_ecr_image
            group by
                repository_name
        ),
        images_with_tags as (
            select
                _ctx,
                region,
                repository_name,
                image_pushed_at,
                jsonb_array_elements_text(image_tags) :: text as image_tag
            from
                myawsaccount.aws_ecr_image
        )
        select
            i._ctx ->> 'connection_name' as "AWS_ACCOUNT_NAME",
            i.region,
            i.repository_name,
            i.image_pushed_at,
            i.image_tag,
            f.name,
            f.uri,
            f.severity,
            f.description,
            (
                jsonb_path_query(f.attributes, '$[*] ? (@.Key == "package_name")') -> 'Value' #>>'{}')::text as package_name,
                (
                    jsonb_path_query(
                        f.attributes,
                        '$[*] ? (@.Key == "package_version")'
                    ) -> 'Value' #>>'{}')::text as package_version
                    from
                        images_with_tags i
                        join latest_image_ts l on (l.repository_name, l.image_pushed_at) = (i.repository_name, i.image_pushed_at)
                        join myawsaccount.aws_ecr_image_scan_finding f on (f.repository_name, f.image_tag) = (l.repository_name, i.image_tag)
                    order by
                        repository_name,
                        image_tag,
                        severity,
                        name,
                        package_name
                ) innerq;

Steampipe version (steampipe -v)
Steampipe v1.0.1

Plugin version (steampipe plugin list)
hub.steampipe.io/plugins/turbot/[email protected] | 1.4.0
hub.steampipe.io/plugins/turbot/aws@latest | 1.5.0

To reproduce
Run the query above using aws plugin 1.40 and 1.5.0

Expected behavior
Query should continue to work, as from what I can see in the PR, only an additional qual was added, which I wouldn't have expected would affect this functionaility.

Additional context
Refer #2356 and #2357
@captainfalcon23 captainfalcon23 added the bug Something isn't working label Jan 6, 2025
@captainfalcon23
Copy link
Author

pinging @ParthaI as you worked on the PR and other issue

ParthaI added a commit that referenced this issue Jan 15, 2025
@ParthaI
Add parent hydrate support in the table aws_ecr_image_scan_finding to…
5091360 
… manage the complex join queries Closes #2367
@ParthaI ParthaI linked a pull request Jan 15, 2025 that will close this issue
Add parent hydrate support for the table aws_ecr_image_scan_finding to manage the complex join queries Closes #2367 #2376
Open
@ParthaI
Copy link
Contributor

ParthaI commented Jan 15, 2025

Hello @captainfalcon23, I’ve raised a draft PR with some updates to the table design to better handle complex queries. However, the design hasn’t been finalized yet. If you have some time, could you please try it out on the PR branch and share your feedback? Thank you!

@captainfalcon23
Copy link
Author

Hey @ParthaI sorry for the delay. I tested this today. So good news and bad news. Good news is the query now runs to completion. Bad news is that vulnerabilities for ALL image tags in a repo are being returned, instead of just the image specified in the join here:

  join aws_all.aws_ecr_image_scan_finding f on (f.repository_name,f.image_tag) = (l.repository_name,i.image_tag)

On another topic, while the old plugin works, I noticed a while ago that due to the same above join, I assume aws_ecr_image_scan_finding has a parent hydrate using listAwsEcrImageTags. What happens is, this parent hydrate doesn't pass the context of which account it is from, so when using an aggregator, it tries all accounts defined, so the logs fill with:

1737498382703: aws_ecr_image.listAwsEcrImageTags: api_error="operation error ECR: DescribeImages, https response error StatusCode: 400, RequestID: 0adb282c-eeec-4cc1-8429-04463389a531, RepositoryNotFoundException: The repository with name 'blah' does not exist in the registry with id '0000000'"

Just thought to mention it while working on this particular table.

@ParthaI
Copy link
Contributor

ParthaI commented Feb 6, 2025

Hello @captainfalcon23,

Apologies for the delay. I have cross-checked the code in the PR you mentioned, and the results appear to be returning as expected.

It seems like there is some issue while using the aggregator connection with CTE expression. While the rows are not being populated for all the accounts we have configured in our connection config. I am digging into it.

The RepositoryNotFoundException error you are encountering occurs because the repository is not available in the specified account while using the aggregator connection. By design, when using an aggregator connection, results from multiple accounts are combined and displayed together.

In the Common Table Expressions (CTEs), if a repository belongs to a specific account but is not available in another, the query will return RepositoryNotFoundException for the account where the repository does not exist. Similarly, the same behavior applies to regions within an account where the repository is not present.

Additionally, I have already handled the "Not Found" error code for the parent hydrate function in the table.

I have also updated the query by adding account_id and region conditions in the WHERE clause. Could you please try again by pulling the latest changes from the issue-2367 branch and rebuilding the plugin locally?

Thank you for being so patient!

@ParthaI
Copy link
Contributor

ParthaI commented Feb 7, 2025

Hello @captainfalcon23, I cross-verified the result it is working as expected. I think we are good over there.

Query result:

with images_with_tags AS (
    SELECT
        _ctx ->> 'connection_name' AS AWS_ACCOUNT_NAME,
        account_id,
        region,
        repository_name,
        image_pushed_at,
        jsonb_array_elements_text(image_tags) AS image_tag
    FROM
        myawsaccount.aws_ecr_image
), 
findings as (
    select
        f._ctx ->> 'connection_name' AS AWS_ACCOUNT_NAME,
        f.account_id,
        f.region,
        f.repository_name,
        f.severity,
        f.attributes
    from 
       myawsaccount.aws_ecr_image_scan_finding as f,
       images_with_tags as t
    where f.repository_name = t.repository_name
)
select
    f._ctx ->> 'connection_name',
    f.image_tag,
    f.name AS COMMON_VULN_ID,
    f.uri AS VULN_INFO_URL,
    f.severity AS VULN_SEVERITY,
    jsonb_path_query(f.attributes, '$[*] ? (@.Key == "package_name")') ->> 'Value' AS VULN_PACKAGE_NAME,
    jsonb_path_query(f.attributes, '$[*] ? (@.Key == "package_version")') ->> 'Value' AS VULN_PACKAGE_VERSION
FROM
    myawsaccount.aws_ecr_image_scan_finding as f,
    images_with_tags as t
where
    f.repository_name = t.repository_name
    and f.image_tag = t.image_tag
    and f.account_id = t.account_id
    and f.region = t.region
+--------------------+-----------+----------------+--------------------------------------------------------------+---------------+-------------------+-----------------------------------+
| ?column?           | image_tag | common_vuln_id | vuln_info_url                                                | vuln_severity | vuln_package_name | vuln_package_version              |
+--------------------+-----------+----------------+--------------------------------------------------------------+---------------+-------------------+-----------------------------------+
| aws_zero_intg_test | latest    | CVE-2025-0395  | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2025-0395  | MEDIUM        | glibc             | 2.31-0ubuntu9.16                  |
| aws_zero_intg_test | latest    | CVE-2025-23022 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2025-23022 | MEDIUM        | freetype          | 2.10.1-2ubuntu0.3                 |
| aws_zero_intg_test | latest    | CVE-2024-50602 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2024-50602 | MEDIUM        | expat             | 2.2.9-1ubuntu0.7                  |
| aws_zero_intg_test | latest    | CVE-2017-11164 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2017-11164 | INFORMATIONAL | pcre3             | 2:8.39-12ubuntu0.1                |
| aws_zero_intg_test | latest    | CVE-2024-10041 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2024-10041 | MEDIUM        | pam               | 1.3.1-5ubuntu4.7                  |
| aws_zero_intg_test | latest    | CVE-2023-2004  | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2023-2004  | MEDIUM        | freetype          | 2.10.1-2ubuntu0.3                 |
| aws_zero_intg_test | latest    | CVE-2022-49043 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2022-49043 | MEDIUM        | libxml2           | 2.9.10+dfsg-5ubuntu0.20.04.7      |
| aws_zero_intg_test | latest    | CVE-2023-50495 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2023-50495 | LOW           | ncurses           | 6.2-0ubuntu2.1                    |
| aws_zero_intg_test | latest    | CVE-2023-26604 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2023-26604 | LOW           | systemd           | 245.4-4ubuntu3.24                 |
| aws_zero_intg_test | latest    | CVE-2022-41409 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2022-41409 | LOW           | pcre2             | 10.34-7ubuntu0.1                  |
| aws_zero_intg_test | latest    | CVE-2023-29383 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2023-29383 | LOW           | shadow            | 1:4.8.1-1ubuntu5.20.04.5          |
| aws_zero_intg_test | latest    | CVE-2023-45918 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2023-45918 | LOW           | ncurses           | 6.2-0ubuntu2.1                    |
| aws_zero_intg_test | latest    | CVE-2024-56433 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2024-56433 | MEDIUM        | shadow            | 1:4.8.1-1ubuntu5.20.04.5          |
| aws_zero_intg_test | latest    | CVE-2023-7008  | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2023-7008  | LOW           | systemd           | 245.4-4ubuntu3.24                 |
| aws_zero_intg_test | latest    | CVE-2024-6716  | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2024-6716  | LOW           | tiff              | 4.1.0+git191117-2ubuntu0.20.04.14 |
| aws_zero_intg_test | latest    | CVE-2022-32325 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2022-32325 | LOW           | libjpeg-turbo     | 2.0.3-0ubuntu1.20.04.3            |
| aws_zero_intg_test | latest    | CVE-2016-20013 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2016-20013 | INFORMATIONAL | glibc             | 2.31-0ubuntu9.16                  |
| aws_zero_intg_test | latest    | CVE-2013-4235  | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2013-4235  | LOW           | shadow            | 1:4.8.1-1ubuntu5.20.04.5          |
| aws_zero_intg_test | latest    | CVE-2022-3857  | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2022-3857  | LOW           | libpng1.6         | 1.6.37-2                          |
| aws_zero_intg_test | latest    | CVE-2016-2781  | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2016-2781  | LOW           | coreutils         | 8.30-3ubuntu2                     |
| aws_zero_intg_test | latest    | CVE-2022-3219  | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2022-3219  | LOW           | gnupg2            | 2.2.19-3ubuntu2.2                 |
| aws_zero_intg_test | latest    | CVE-2024-34459 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2024-34459 | LOW           | libxml2           | 2.9.10+dfsg-5ubuntu0.20.04.7      |
| aws_zero_intg_test | latest    | CVE-2015-9019  | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2015-9019  | LOW           | libxslt           | 1.1.34-4ubuntu0.20.04.1           |
| aws_zero_intg_test | latest    | CVE-2024-2236  | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2024-2236  | LOW           | libgcrypt20       | 1.8.5-5ubuntu1.1                  |
| aws_zero_intg_test | latest    | CVE-2024-50602 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2024-50602 | MEDIUM        | expat             | 2.2.9-1ubuntu0.7                  |
| aws_zero_intg_test | latest    | CVE-2022-49043 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2022-49043 | MEDIUM        | libxml2           | 2.9.10+dfsg-5ubuntu0.20.04.7      |
| aws_zero_intg_test | latest    | CVE-2015-9019  | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2015-9019  | LOW           | libxslt           | 1.1.34-4ubuntu0.20.04.1           |
| aws_zero_intg_test | latest    | CVE-2024-2236  | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2024-2236  | LOW           | libgcrypt20       | 1.8.5-5ubuntu1.1                  |
| aws_zero_intg_test | latest    | CVE-2023-2004  | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2023-2004  | MEDIUM        | freetype          | 2.10.1-2ubuntu0.3                 |
| aws_zero_intg_test | latest    | CVE-2017-11164 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2017-11164 | INFORMATIONAL | pcre3             | 2:8.39-12ubuntu0.1                |
| aws_zero_intg_test | latest    | CVE-2025-0395  | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2025-0395  | MEDIUM        | glibc             | 2.31-0ubuntu9.16                  |
| aws_zero_intg_test | latest    | CVE-2022-32325 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2022-32325 | LOW           | libjpeg-turbo     | 2.0.3-0ubuntu1.20.04.3            |
| aws_zero_intg_test | latest    | CVE-2024-10041 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2024-10041 | MEDIUM        | pam               | 1.3.1-5ubuntu4.7                  |
| aws_zero_intg_test | latest    | CVE-2025-23022 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2025-23022 | MEDIUM        | freetype          | 2.10.1-2ubuntu0.3                 |
| aws_zero_intg_test | latest    | CVE-2016-2781  | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2016-2781  | LOW           | coreutils         | 8.30-3ubuntu2                     |
| aws_zero_intg_test | latest    | CVE-2023-29383 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2023-29383 | LOW           | shadow            | 1:4.8.1-1ubuntu5.20.04.5          |
| aws_zero_intg_test | latest    | CVE-2022-3857  | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2022-3857  | LOW           | libpng1.6         | 1.6.37-2                          |
| aws_zero_intg_test | latest    | CVE-2022-3219  | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2022-3219  | LOW           | gnupg2            | 2.2.19-3ubuntu2.2                 |
| aws_zero_intg_test | latest    | CVE-2023-7008  | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2023-7008  | LOW           | systemd           | 245.4-4ubuntu3.24                 |
| aws_zero_intg_test | latest    | CVE-2024-56433 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2024-56433 | MEDIUM        | shadow            | 1:4.8.1-1ubuntu5.20.04.5          |
| aws_zero_intg_test | latest    | CVE-2024-34459 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2024-34459 | LOW           | libxml2           | 2.9.10+dfsg-5ubuntu0.20.04.7      |
| aws_zero_intg_test | latest    | CVE-2023-45918 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2023-45918 | LOW           | ncurses           | 6.2-0ubuntu2.1                    |
| aws_zero_intg_test | latest    | CVE-2016-20013 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2016-20013 | INFORMATIONAL | glibc             | 2.31-0ubuntu9.16                  |
| aws_zero_intg_test | latest    | CVE-2023-50495 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2023-50495 | LOW           | ncurses           | 6.2-0ubuntu2.1                    |
| aws_zero_intg_test | latest    | CVE-2022-41409 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2022-41409 | LOW           | pcre2             | 10.34-7ubuntu0.1                  |
| aws_zero_intg_test | latest    | CVE-2023-26604 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2023-26604 | LOW           | systemd           | 245.4-4ubuntu3.24                 |
| aws_zero_intg_test | latest    | CVE-2024-6716  | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2024-6716  | LOW           | tiff              | 4.1.0+git191117-2ubuntu0.20.04.14 |
| aws_zero_intg_test | latest    | CVE-2013-4235  | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2013-4235  | LOW           | shadow            | 1:4.8.1-1ubuntu5.20.04.5          |
| aws                | latest    | CVE-2016-2781  | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2016-2781  | LOW           | coreutils         | 8.30-3ubuntu2                     |
| aws                | latest    | CVE-2020-22916 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2020-22916 | MEDIUM        | xz-utils          | 5.2.4-1ubuntu1.1                  |
| aws                | latest    | CVE-2017-11164 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2017-11164 | INFORMATIONAL | pcre3             | 2:8.39-12ubuntu0.1                |
| aws                | latest    | CVE-2023-45918 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2023-45918 | LOW           | ncurses           | 6.2-0ubuntu2.1                    |
| aws                | latest    | CVE-2023-29383 | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2023-29383 | LOW           | shadow            | 1:4.8.1-1ubuntu5.20.04.5          |
| aws                | latest    | CVE-2023-7008  | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2023-7008  | LOW           | systemd           | 245.4-4ubuntu3.23                 |
| aws                | latest    | CVE-2022-3219  | http://people.ubuntu.com/~ubuntu-security/cve/CVE-2022-3219  | LOW           | gnupg2            | 2.2.19-3ubuntu2.2                 |

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@ParthaI ParthaI

Labels
bug Something isn't working
Projects
None yet
Milestone
No milestone
2 participants
@ParthaI @captainfalcon23