Create two AWS ECR repositories

Oct 13, 2025 by thesweetdesigns.com 112 views

Issue Overview

There's a significant behavior change in the Steampipe AWS plugin between versions 1.4.0 and > 1.4.0 affecting the aws_ecr_image_scan_finding table. The query planner no longer correctly applies filters for image_tag in join conditions, leading to incorrect and inflated results when querying scan findings. The original issue is reported here: https://github.com/turbot/steampipe-plugin-aws/issues/2446

Root Cause

In version > 1.4.0, the image_tag column was changed from a required qualifier to an optional qualifier in the aws_ecr_image_scan_finding table:

KeyColumns: []*plugin.KeyColumn{
    {Name: "repository_name", Require: plugin.Required},
    {Name: "image_tag", Require: plugin.Optional}, // Changed from Required to Optional
    {Name: "image_digest", Require: plugin.Optional},
},

This change affects how the query planner generates execution plans for SQL queries involving this table.

Observed Behavior

When executing a query that joins the aws_ecr_image table with the aws_ecr_image_scan_finding table using both repository_name and image_tag as join conditions:

Query:

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;

In version 1.4.0:
- The query planner correctly applies BOTH repository_name AND image_tag filters during the foreign scan.
- This results in accurate filtering of scan findings by repository and tag.
In version 1.5.0:
- The query planner only applies the repository_name filter during the foreign scan.
- The image_tag filter is ignored in the join condition.
- This leads to inflated results as all findings for a repository are returned regardless of the image tag.

Reproduction Steps:

Environment Setup:

Create two AWS ECR repositories, each containing images with different tags.
Ensure that each image tag has an associated scanning report.
Run the above query to retrieve image scanning findings exclusively for the latest images only based on pushed at time.

Impact

The change in the query planner's behavior has a significant impact on the accuracy and performance of queries involving the aws_ecr_image_scan_finding table. Inaccurate filtering of scan findings can lead to incorrect conclusions and decisions, while inflated results can cause performance issues and increased costs.

Workaround

To work around this issue, you can modify the query to use a subquery that applies the image_tag filter before joining with the aws_ecr_image_scan_finding table. This will ensure that the image_tag filter is applied correctly, even in version > 1.4.0.

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 (
                            select
                                repository_name,
                                image_tag
                            from
                                myawsaccount.aws_ecr_image_scan_finding
                            where
                                repository_name = l.repository_name
                        ) 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;

Conclusion

Q&A

Q: What is the issue with the query planner in version `> 1.4.0`?

A: The query planner in version > 1.4.0 ignores to apply the filter for the image_tag column in the aws_ecr_image_scan_finding table, leading to incorrect and inflated results when querying scan findings.

Q: What is the root cause of this issue?

A: The root cause of this issue is the change in the image_tag column from a required qualifier to an optional qualifier in the aws_ecr_image_scan_finding table.

Q: How does this issue affect the accuracy and performance of queries?

A: This issue affects the accuracy and performance of queries involving the aws_ecr_image_scan_finding table. Inaccurate filtering of scan findings can lead to incorrect conclusions and decisions, while inflated results can cause performance issues and increased costs.

Q: How can I work around this issue?

A: You can modify the query to use a subquery that applies the image_tag filter before joining with the aws_ecr_image_scan_finding table.

Q: Is there a workaround for this issue in version `> 1.4.0`?

A: Yes, you can use a subquery to apply the image_tag filter before joining with the aws_ecr_image_scan_finding table.

Q: What is the recommended solution to this issue?

A: The recommended solution is to upgrade to the latest version of the Steampipe AWS plugin to ensure that the query planner behaves correctly.

Q: Can I reproduce this issue?

A: Yes, you can reproduce this issue by following the reproduction steps outlined in the article.

Q: What are the reproduction steps for this issue?

A: The reproduction steps for this issue are:

Create two AWS ECR repositories, each containing images with different tags.
Ensure that each image tag has an associated scanning report.
Run the query to retrieve image scanning findings exclusively for the latest images only based on pushed at time.

Q: What is the impact of this issue on the accuracy and performance of queries?

A: The impact of this issue on the accuracy and performance of queries is significant. Inaccurate filtering of scan findings can lead to incorrect conclusions and decisions, while inflated results can cause performance issues and increased costs.

Q: Can I use a subquery to work around this issue?

A: Yes, you can use a subquery to work around this issue.

Q: What is the recommended query to use a subquery to work around this issue?

A: The recommended query to use a subquery to work around this issue is:

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 (
                            select
                                repository_name,
                                image_tag
                            from
                                myawsaccount.aws_ecr_image_scan_finding
                            where
                                repository_name = l.repository_name
                        ) 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;

Q: What is the recommended solution to this issue?

A: The recommended solution is to upgrade to the latest version of the Steampipe AWS plugin to ensure that the query planner behaves correctly.