Table: aws_acmpca_certificate_authority - Query AWS ACM PCA Certificate Authorities using SQL

The aws_acmpca_certificate_authority table provides detailed information about AWS Certificate Manager Private Certificate Authority (ACM PCA) certificate authorities. These entities enable you to securely issue and manage your private certificates. This table allows for querying configurations, statuses, key storage standards, and more for each certificate authority within your AWS account.

Table Usage Guide

This table can be utilized to monitor the configuration and operational health of your private certificate authorities managed through AWS ACM PCA. It enables security analysts, compliance auditors, and cloud administrators to assess the certificate authorities' compliance with policies, investigate issuance metadata, and understand the security standards being applied.

Examples

Basic information

Retrieve basic details about your ACM PCA Certificate Authorities.

select
  arn,
  status,
  created_at,
  not_before,
  not_after,
  key_storage_security_standard,
  failure_reason
from
  aws_acmpca_certificate_authority;

select
  arn,
  status,
  datetime(created_at) AS created_at,
  datetime(not_before) AS not_before,
  datetime(not_after) AS not_after,
  key_storage_security_standard,
  failure_reason
from
  aws_acmpca_certificate_authority;

Certificate authorities with specific key storage security standards

List certificate authorities that comply with a specific key storage security standard.

select
  arn,
  status,
  key_storage_security_standard
from
  aws_acmpca_certificate_authority
where
  key_storage_security_standard = 'FIPS_140_2_LEVEL_3_OR_HIGHER';

select
  arn,
  status,
  key_storage_security_standard
from
  aws_acmpca_certificate_authority
where
  key_storage_security_standard = 'FIPS_140_2_LEVEL_3_OR_HIGHER';

Certificate authorities by status

Find certificate authorities by their operational status, e.g., ACTIVE, DISABLED.

select
  arn,
  status,
  created_at,
  last_state_change_at
from
  aws_acmpca_certificate_authority
where
  status = 'ACTIVE';

select
  arn,
  status,
  datetime(created_at) AS created_at,
  datetime(last_state_change_at) AS last_state_change_at
from
  aws_acmpca_certificate_authority
where
  status = 'ACTIVE';

Tagged Certificate Authorities

Identify certificate authorities tagged with specific key-value pairs for organizational purposes.

select
  arn,
  tags
from
  aws_acmpca_certificate_authority
where
  (tags ->> 'Project') = 'MyProject';

select
  arn,
  json_extract(tags, '$.Project') AS project_tag
from
  aws_acmpca_certificate_authority
where
  project_tag = 'MyProject';

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- aws

You can pass the configuration to the command with the --config argument:

steampipe_export_aws --config '<your_config>' aws_acmpca_certificate_authority