How To List All Expired SSL Certificates on AWS

SSL certificates are critical for securing cloud infrastructure, encrypting data in transit, and maintaining compliance. However, expired certificates can cause outages, security vulnerabilities, and compliance issues. This guide walks you through how to identify and analyze expired AWS SSL certificates using CloudQuery, ensuring your cloud security posture remains strong.

We will use CloudQuery, to extract SSL certificate data from AWS services and analyze expiration statuses with SQL queries. Specifically, we will:

• Sync AWS SSL Certificate Data from key services such as ACM, IAM, RDS, DocumentDB, MQ, and Transfer Family.
• Run ClickHouse SQL Queries to analyze certificate expiration dates.

How to Sync Your AWS Cloud Infrastructure Data with CloudQuery

Follow these simple steps to set up CloudQuery and sync your Azure data:

Sign Up for CloudQuery
Start by creating a CloudQuery account.

Set Up Your Data Sync
Configure a new sync using the AWS Source Integration. Be sure to sync the tables for the AWS services you are using. These tables collectively cover ACM, IAM, RDS, DocumentDB, MQ, and Transfer Family certificates. If you are only interested in ACM-issued certificates, then aws_acm_certificates alone should be sufficient.

• aws_acm_certificates – Contains details about Amazon Certificate Manager (ACM) managed certificates, including their expiration dates.
• aws_iam_server_certificates – Stores SSL/TLS certificates uploaded to IAM, including expiration dates.
• aws_acmpca_certificate_authorities – If using a private certificate authority (ACM PCA), this table stores information on issued certificates.
• aws_rds_certificates – Contains RDS SSL/TLS certificate expiration details.
• aws_docdb_certificates – Contains SSL/TLS certificate expiration details for Amazon DocumentDB.
• aws_transfer_certificates – Stores certificates used in AWS Transfer Family.

Run Your Data Sync
Click the “Run Sync” button to pull your AWS data into CloudQuery.

How to analyze your AWS SSL Certification data

With CloudQuery, you can run queries about your synced data directly in the integrated SQL Editor. You can run the following ClickHouse queries to give you a list of all your expired SSL Certificates on AWS.

Identifying Expired SSL Certificates in AWS Certificate Manager (ACM)

This query retrieves a list of SSL certificates managed by AWS Certificate Manager (ACM) and checks if they are expired. It pulls relevant details such as the AWS account ID, certificate ARN, domain name, expiration date, and current status. The query also calculates whether a certificate has expired by comparing its expiration date (not_after) with the current time.

SELECT
    account_id,
    arn,
    domain_name,
    not_after AS expiry_date,
    status,
    NOW() AS current_time,
    not_after < NOW() AS is_expired
FROM aws_acm_certificates
ORDER BY not_after ASC;

Checking Expired IAM Server Certificates in AWS

This query retrieves all IAM server certificates stored in AWS and checks if any have expired. It pulls key details such as the AWS account ID, certificate ARN, certificate name, and expiration date. By comparing the expiration date with the current time, it determines whether each certificate is still valid.

SELECT
    account_id,
    arn,
    server_certificate_name,
    expiration AS expiry_date,
    NOW() AS current_time,
    expiration < NOW() AS is_expired
FROM aws_iam_server_certificates
ORDER BY expiration ASC;

How to Find All Expired SSL Certificates Across AWS Services

This query consolidates expired SSL certificates from multiple AWS services into a single result set. It checks expiration dates across six key AWS certificate sources:

SELECT 'IAM Server' AS source, account_id, arn, expiration AS expiry_date FROM aws_iam_server_certificates WHERE expiration < NOW()
UNION ALL
SELECT 'ACM PCA' AS source, account_id, arn, not_after FROM aws_acmpca_certificate_authorities WHERE not_after < NOW()
UNION ALL
SELECT 'ACM' AS source, account_id, arn, not_after FROM aws_acm_certificates WHERE not_after < NOW()
UNION ALL
SELECT 'RDS' AS source, account_id, arn, valid_till FROM aws_rds_certificates WHERE valid_till < NOW()
UNION ALL
SELECT 'DocumentDB' AS source, account_id, arn, valid_till FROM aws_docdb_certificates WHERE valid_till < NOW()
UNION ALL
SELECT 'Transfer Family' AS source, account_id, arn, not_after_date FROM aws_transfer_certificates WHERE not_after_date < NOW()
ORDER BY expiry_date ASC;

Wrap Up

By syncing AWS infrastructure data with CloudQuery and running these SQL queries, you can:

• Quickly identify expired SSL certificates across multiple AWS services.
• Prevent outages and security risks caused by expired certificates.
• Ensure compliance by proactively managing certificate renewals.

Regularly running these queries will help keep your AWS cloud infrastructure secure and compliant.

CloudQuery simplifies cloud asset discovery and governance, making it easy to audit, monitor, and manage cloud environments at scale. If you haven’t already, get started with CloudQuery today to enhance your cloud visibility.

Have questions or need help? Join the CloudQuery Developer Community to connect with other users, share insights, and get support. Let us know how your team is tackling cloud security by engaging with us on LinkedIn, or X.

FAQs

1. Can I use CloudQuery with other cloud providers besides AWS?
   Yes, CloudQuery supports a wide range of cloud providers, including GCP, Azure, and more.
2. How do I authenticate CloudQuery to access my AWS resources?
   You’ll need AWS read-only credentials to authenticate. CloudQuery supports multiple authentication methods, such as environment variables, shared credentials files, and IAM roles.
3. Can I query other types of AWS resources besides SSL Certificates?
   Absolutely. CloudQuery allows you to query various AWS resources, including S3 buckets, security groups, IAM roles, and more.