Overview #

1. Identify Vulnerabilities: Knowing which vulnerabilities affect your ECR images helps you prioritize security efforts, especially for images in use.
2. Current Usage Tracking: Determine which of your ECR images are currently in use by your ECS clusters or other services, ensuring that any identified vulnerabilities are addressed promptly.
3. Compliance and Audit: Maintain a compliant and auditable environment by having a clear view of your ECR images, their vulnerabilities, and their usage status.
4. Resource Optimization: Optimize ECR resource management by identifying and possibly removing unused images that may still have vulnerabilities.

Required Tables #

• aws_ecr_repository_images: Contains metadata about ECR repository images.
• aws_ecr_repository_image_scan_findings: Stores scan findings of ECR repository images.
• aws_inspector2_findings: Holds detailed findings from Amazon Inspector, including vulnerability information.

How to get a comprehensive view of your AWS ECR images and their security statuses #

WITH unnested_findings AS (
    SELECT
        findings.arn,
        findings.aws_account_id,
        findings.description,
        findings.inspector_score,
        findings.severity,
        findings.status,
        findings.title,
        findings.package_vulnerability_details,
        findings.resources,
        findings.request_region,
        resource -> 'details' -> 'awsEcrContainerImage' ->> 'imageHash' AS image_hash
    FROM
        aws_inspector2_findings AS findings,
        JSONB_ARRAY_ELEMENTS(findings.resources) AS resource
    WHERE
     resource ->> 'type' = 'AWS_ECR_CONTAINER_IMAGE'
)
SELECT
    images.account_id AS image_account_id,
    images.image_digest,
    images.image_pushed_at,
    images.image_size_in_bytes,
    images.image_tags,
    images.repository_name,
    images.region AS image_region,
    scan_findings.image_scan_findings,
    scan_findings.image_scan_status AS scan_status,
    findings.arn AS finding_arn,
    findings.aws_account_id AS finding_account_id,
    findings.description AS finding_description,
    findings.inspector_score,
    findings.severity AS finding_severity,
    findings.status AS finding_status,
    findings.title AS finding_title,
    findings.package_vulnerability_details,
    findings.resources
FROM aws_ecr_repository_images AS images
LEFT JOIN aws_ecr_repository_image_scan_findings AS scan_findings
ON images.image_digest = scan_findings.image_digest
LEFT JOIN unnested_findings AS findings
ON images.image_digest = findings.image_hash
ORDER BY images.image_pushed_at DESC;

Steps

1. Unnesting Findings:

   • The unnested_findings CTE (Common Table Expression) extracts and unnests the findings from AWS Inspector.
   • It uses the JSONB_ARRAY_ELEMENTS function to handle the JSON array in the resources column, which includes details about ECR container images.
   • This step ensures that each finding related to an ECR container image is represented as a separate row.
2. Joining Tables:

   • The main query selects relevant columns from the aws_ecr_repository_images, aws_ecr_repository_image_scan_findings, and unnested_findings tables.
   • The LEFT JOIN clause is used to combine these tables:

     • First Join: The aws_ecr_repository_images table is joined with the aws_ecr_repository_image_scan_findings table using the image_digest column. This join retrieves the scan findings associated with each image.
     • Second Join: The unnested_findings CTE is then joined with the aws_ecr_repository_images table using the image_digest and image_hash columns. This join associates the detailed vulnerability findings with the respective images.

Example Result #

Customization #

Summary #