Skip to content
AWS for Query.Farm

aws.acm_pca.list_certificate_authorities

Example SQL Queries

SELECT * FROM
aws.acm_pca.list_certificate_authorities;

Description

Lists the private certificate authorities that you created by using the CreateCertificateAuthority action.

Table Definition

Column NameColumn Data Type
resource_owner Input Column

Use this parameter to filter the returned set of certificate authorities based on their owner. The default is SELF.

VARCHAR
_aws_profile Input Column

The AWS profile defines the AWS identity used. It can be defined via credentials or by assuming a IAM role.

STRUCT(
    "type" VARCHAR,
    "name" VARCHAR,
    "account_id" VARCHAR,
    "via_profile_name" VARCHAR,
    "assumed_role_arn" VARCHAR,
    "organization" STRUCT(
        "account_name" VARCHAR,
        "id" VARCHAR,
        "tags" STRUCT(
            "key" VARCHAR,
            "value" VARCHAR
        )[],
        "master_account" STRUCT(
            "id" VARCHAR,
            "email" VARCHAR
        ),
        "parents" STRUCT(
            "type" VARCHAR,
            "id" VARCHAR,
            "name" VARCHAR,
            "tags" STRUCT(
                "key" VARCHAR,
                "value" VARCHAR
            )[]
        )[]
    )
)
Show child fields
_aws_profile.account_id

The AWS account id

_aws_profile.assumed_role_arn

The ARN of the assumed role

_aws_profile.name

The unique name of the profile.

_aws_profile.organization

Information about this profile's membership in the AWS organization.

Show child fields
_aws_profile.organization.account_name

The name of account speciifed by the organization

_aws_profile.organization.id

The organization id

_aws_profile.organization.master_account
Show child fields
_aws_profile.organization.master_account.email

The organization master account email address

_aws_profile.organization.master_account.id

The organization master account id

_aws_profile.organization.parents[]
Show child fields
_aws_profile.organization.parents[].id

The id of the parent

_aws_profile.organization.parents[].name

The name of the parent

_aws_profile.organization.parents[].tags[]
Show child fields
_aws_profile.organization.parents[].tags[].key
_aws_profile.organization.parents[].tags[].value
_aws_profile.organization.parents[].type

The type of parent can be an organization unit or a root

_aws_profile.organization.tags[]
Show child fields
_aws_profile.organization.tags[].key
_aws_profile.organization.tags[].value
_aws_profile.type

The type of profile, either 'credentials' or 'assumed_role'

_aws_profile.via_profile_name

This IAM role for this profile is assumed by first utilizing another profile with this name to obtain credentials.

_aws_region Input Column

The AWS region to use.

VARCHAR
arn

Amazon Resource Name (ARN) for your private certificate authority (CA). The format is 12345678-1234-1234-1234-123456789012 .

VARCHAR
certificate_authority_configuration

Your private CA configuration.

STRUCT(
    "key_algorithm" VARCHAR,
    "signing_algorithm" VARCHAR,
    "subject" STRUCT(
        "country" VARCHAR,
        "organization" VARCHAR,
        "organizational_unit" VARCHAR,
        "distinguished_name_qualifier" VARCHAR,
        "state" VARCHAR,
        "common_name" VARCHAR,
        "serial_number" VARCHAR,
        "locality" VARCHAR,
        "title" VARCHAR,
        "surname" VARCHAR,
        "given_name" VARCHAR,
        "initials" VARCHAR,
        "pseudonym" VARCHAR,
        "generation_qualifier" VARCHAR,
        "custom_attributes" STRUCT(
            "object_identifier" VARCHAR,
            "value" VARCHAR
        )[]
    ),
    "csr_extensions" STRUCT(
        "key_usage" STRUCT(
            "digital_signature" BOOLEAN,
            "non_repudiation" BOOLEAN,
            "key_encipherment" BOOLEAN,
            "data_encipherment" BOOLEAN,
            "key_agreement" BOOLEAN,
            "key_cert_sign" BOOLEAN,
            "crl_sign" BOOLEAN,
            "encipher_only" BOOLEAN,
            "decipher_only" BOOLEAN
        ),
        "subject_information_access" STRUCT(
            "access_method" STRUCT(
                "custom_object_identifier" VARCHAR,
                "access_method_type" VARCHAR
            ),
            "access_location" STRUCT(
                "other_name" STRUCT(
                    "type_id" VARCHAR,
                    "value" VARCHAR
                ),
                "rfc822_name" VARCHAR,
                "dns_name" VARCHAR,
                "directory_name" STRUCT(
                    "country" VARCHAR,
                    "organization" VARCHAR,
                    "organizational_unit" VARCHAR,
                    "distinguished_name_qualifier" VARCHAR,
                    "state" VARCHAR,
                    "common_name" VARCHAR,
                    "serial_number" VARCHAR,
                    "locality" VARCHAR,
                    "title" VARCHAR,
                    "surname" VARCHAR,
                    "given_name" VARCHAR,
                    "initials" VARCHAR,
                    "pseudonym" VARCHAR,
                    "generation_qualifier" VARCHAR,
                    "custom_attributes" STRUCT(
                        "object_identifier" VARCHAR,
                        "value" VARCHAR
                    )[]
                ),
                "edi_party_name" STRUCT(
                    "party_name" VARCHAR,
                    "name_assigner" VARCHAR
                ),
                "uniform_resource_identifier" VARCHAR,
                "ip_address" VARCHAR,
                "registered_id" VARCHAR
            )
        )[]
    )
)
Show child fields
certificate_authority_configuration.csr_extensions

Specifies information to be added to the extension section of the certificate signing request (CSR).

Show child fields
certificate_authority_configuration.csr_extensions.key_usage

Indicates the purpose of the certificate and of the key contained in the certificate.

Show child fields
certificate_authority_configuration.csr_extensions.key_usage.crl_sign

Key can be used to sign CRLs.

certificate_authority_configuration.csr_extensions.key_usage.data_encipherment

Key can be used to decipher data.

certificate_authority_configuration.csr_extensions.key_usage.decipher_only

Key can be used only to decipher data.

certificate_authority_configuration.csr_extensions.key_usage.digital_signature

Key can be used for digital signing.

certificate_authority_configuration.csr_extensions.key_usage.encipher_only

Key can be used only to encipher data.

certificate_authority_configuration.csr_extensions.key_usage.key_agreement

Key can be used in a key-agreement protocol.

certificate_authority_configuration.csr_extensions.key_usage.key_cert_sign

Key can be used to sign certificates.

certificate_authority_configuration.csr_extensions.key_usage.key_encipherment

Key can be used to encipher data.

certificate_authority_configuration.csr_extensions.key_usage.non_repudiation

Key can be used for non-repudiation.

certificate_authority_configuration.csr_extensions.subject_information_access[]
Show child fields
certificate_authority_configuration.csr_extensions.subject_information_access[].access_location

The location of AccessDescription information.

Show child fields
certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.directory_name

Contains information about the certificate subject. The Subject field in the certificate identifies the entity that owns or controls the public key in the certificate. The entity can be a user, computer, device, or service. The Subject must contain an X.500 distinguished name (DN). A DN is a sequence of relative distinguished names (RDNs). The RDNs are separated by commas in the certificate.

Show child fields
certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.directory_name.common_name

For CA and end-entity certificates in a private PKI, the common name (CN) can be any string within the length limit.

Note: In publicly trusted certificates, the common name must be a fully qualified domain name (FQDN) associated with the certificate subject.

certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.directory_name.country

Two-digit code that specifies the country in which the certificate subject located.

certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.directory_name.custom_attributes[]
Show child fields
certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.directory_name.custom_attributes[].object_identifier

Specifies the object identifier (OID) of the attribute type of the relative distinguished name (RDN).

certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.directory_name.custom_attributes[].value

Specifies the attribute value of relative distinguished name (RDN).

certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.directory_name.distinguished_name_qualifier

Disambiguating information for the certificate subject.

certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.directory_name.generation_qualifier

Typically a qualifier appended to the name of an individual. Examples include Jr. for junior, Sr. for senior, and III for third.

certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.directory_name.given_name

First name.

certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.directory_name.initials

Concatenation that typically contains the first letter of the GivenName, the first letter of the middle name if one exists, and the first letter of the Surname.

certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.directory_name.locality

The locality (such as a city or town) in which the certificate subject is located.

certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.directory_name.organization

Legal name of the organization with which the certificate subject is affiliated.

certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.directory_name.organizational_unit

A subdivision or unit of the organization (such as sales or finance) with which the certificate subject is affiliated.

certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.directory_name.pseudonym

Typically a shortened version of a longer GivenName. For example, Jonathan is often shortened to John. Elizabeth is often shortened to Beth, Liz, or Eliza.

certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.directory_name.serial_number

The certificate serial number.

certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.directory_name.state

State in which the subject of the certificate is located.

certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.directory_name.surname

Family name. In the US and the UK, for example, the surname of an individual is ordered last. In Asian cultures the surname is typically ordered first.

certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.directory_name.title

A title such as Mr. or Ms., which is pre-pended to the name to refer formally to the certificate subject.

certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.dns_name

Represents GeneralName as a DNS name.

certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.edi_party_name

Represents GeneralName as an EdiPartyName object.

Show child fields
certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.edi_party_name.name_assigner

Specifies the name assigner.

certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.edi_party_name.party_name

Specifies the party name.

certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.ip_address

Represents GeneralName as an IPv4 or IPv6 address.

certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.other_name

Represents GeneralName using an OtherName object.

Show child fields
certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.other_name.type_id

Specifies an OID.

certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.other_name.value

Specifies an OID value.

certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.registered_id

Represents GeneralName as an object identifier (OID).

certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.rfc822_name

Represents GeneralName as an RFC 822 email address.

certificate_authority_configuration.csr_extensions.subject_information_access[].access_location.uniform_resource_identifier

Represents GeneralName as a URI.

certificate_authority_configuration.csr_extensions.subject_information_access[].access_method

The type and format of AccessDescription information.

Show child fields
certificate_authority_configuration.csr_extensions.subject_information_access[].access_method.access_method_type

Specifies the AccessMethod.

certificate_authority_configuration.csr_extensions.subject_information_access[].access_method.custom_object_identifier

An object identifier (OID) specifying the AccessMethod. The OID must satisfy the regular expression shown below. For more information, see NIST's definition of Object Identifier (OID).

certificate_authority_configuration.key_algorithm

Type of the public key algorithm and size, in bits, of the key pair that your CA creates when it issues a certificate. When you create a subordinate CA, you must use a key algorithm supported by the parent CA.

certificate_authority_configuration.signing_algorithm

Name of the algorithm your private CA uses to sign certificate requests.

This parameter should not be confused with the SigningAlgorithm parameter used to sign certificates when they are issued.

certificate_authority_configuration.subject

Structure that contains X.500 distinguished name information for your private CA.

Show child fields
certificate_authority_configuration.subject.common_name

For CA and end-entity certificates in a private PKI, the common name (CN) can be any string within the length limit.

Note: In publicly trusted certificates, the common name must be a fully qualified domain name (FQDN) associated with the certificate subject.

certificate_authority_configuration.subject.country

Two-digit code that specifies the country in which the certificate subject located.

certificate_authority_configuration.subject.custom_attributes[]
Show child fields
certificate_authority_configuration.subject.custom_attributes[].object_identifier

Specifies the object identifier (OID) of the attribute type of the relative distinguished name (RDN).

certificate_authority_configuration.subject.custom_attributes[].value

Specifies the attribute value of relative distinguished name (RDN).

certificate_authority_configuration.subject.distinguished_name_qualifier

Disambiguating information for the certificate subject.

certificate_authority_configuration.subject.generation_qualifier

Typically a qualifier appended to the name of an individual. Examples include Jr. for junior, Sr. for senior, and III for third.

certificate_authority_configuration.subject.given_name

First name.

certificate_authority_configuration.subject.initials

Concatenation that typically contains the first letter of the GivenName, the first letter of the middle name if one exists, and the first letter of the Surname.

certificate_authority_configuration.subject.locality

The locality (such as a city or town) in which the certificate subject is located.

certificate_authority_configuration.subject.organization

Legal name of the organization with which the certificate subject is affiliated.

certificate_authority_configuration.subject.organizational_unit

A subdivision or unit of the organization (such as sales or finance) with which the certificate subject is affiliated.

certificate_authority_configuration.subject.pseudonym

Typically a shortened version of a longer GivenName. For example, Jonathan is often shortened to John. Elizabeth is often shortened to Beth, Liz, or Eliza.

certificate_authority_configuration.subject.serial_number

The certificate serial number.

certificate_authority_configuration.subject.state

State in which the subject of the certificate is located.

certificate_authority_configuration.subject.surname

Family name. In the US and the UK, for example, the surname of an individual is ordered last. In Asian cultures the surname is typically ordered first.

certificate_authority_configuration.subject.title

A title such as Mr. or Ms., which is pre-pended to the name to refer formally to the certificate subject.

created_at

Date and time at which your private CA was created.

TIMESTAMP_S
failure_reason

Reason the request to create your private CA failed.

VARCHAR
key_storage_security_standard

Defines a cryptographic key management compliance standard used for handling CA keys.

Default: FIPS_140_2_LEVEL_3_OR_HIGHER

Note: Amazon Web Services Region ap-northeast-3 supports only FIPS_140_2_LEVEL_2_OR_HIGHER. You must explicitly specify this parameter and value when creating a CA in that Region. Specifying a different value (or no value) results in an InvalidArgsException with the message "A certificate authority cannot be created in this region with the specified security standard."

VARCHAR
last_state_change_at

Date and time at which your private CA was last updated.

TIMESTAMP_S
not_after

Date and time after which your private CA certificate is not valid.

TIMESTAMP_S
not_before

Date and time before which your private CA certificate is not valid.

TIMESTAMP_S
owner_account

The Amazon Web Services account ID that owns the certificate authority.

VARCHAR
restorable_until

The period during which a deleted CA can be restored. For more information, see the PermanentDeletionTimeInDays parameter of the DeleteCertificateAuthorityRequest action. 

TIMESTAMP_S
revocation_configuration

Information about the Online Certificate Status Protocol (OCSP) configuration or certificate revocation list (CRL) created and maintained by your private CA. 

STRUCT(
    "crl_configuration" STRUCT(
        "enabled" BOOLEAN,
        "expiration_in_days" BIGINT,
        "custom_cname" VARCHAR,
        "s3_bucket_name" VARCHAR,
        "s3_object_acl" VARCHAR,
        "crl_distribution_point_extension_configuration" STRUCT(
            "omit_extension" BOOLEAN
        )
    ),
    "ocsp_configuration" STRUCT(
        "enabled" BOOLEAN,
        "ocsp_custom_cname" VARCHAR
    )
)
Show child fields
revocation_configuration.crl_configuration

Configuration of the certificate revocation list (CRL), if any, maintained by your private CA. A CRL is typically updated approximately 30 minutes after a certificate is revoked. If for any reason a CRL update fails, Amazon Web Services Private CA makes further attempts every 15 minutes.

Show child fields
revocation_configuration.crl_configuration.crl_distribution_point_extension_configuration

Configures the behavior of the CRL Distribution Point extension for certificates issued by your certificate authority. If this field is not provided, then the CRl Distribution Point Extension will be present and contain the default CRL URL.

Show child fields
revocation_configuration.crl_configuration.crl_distribution_point_extension_configuration.omit_extension

Configures whether the CRL Distribution Point extension should be populated with the default URL to the CRL. If set to true, then the CDP extension will not be present in any certificates issued by that CA unless otherwise specified through CSR or API passthrough.

Only set this if you have another way to distribute the CRL Distribution Points ffor certificates issued by your CA, such as the Matter Distributed Compliance Ledger

This configuration cannot be enabled with a custom CNAME set.

revocation_configuration.crl_configuration.custom_cname

Name inserted into the certificate CRL Distribution Points extension that enables the use of an alias for the CRL distribution point. Use this value if you don't want the name of your S3 bucket to be public.

The content of a Canonical Name (CNAME) record must conform to RFC2396 restrictions on the use of special characters in URIs. Additionally, the value of the CNAME must not include a protocol prefix such as "http://" or "https://".

revocation_configuration.crl_configuration.enabled

Boolean value that specifies whether certificate revocation lists (CRLs) are enabled. You can use this value to enable certificate revocation for a new CA when you call the CreateCertificateAuthority action or for an existing CA when you call the UpdateCertificateAuthority action.

revocation_configuration.crl_configuration.expiration_in_days

Validity period of the CRL in days.

revocation_configuration.crl_configuration.s3_bucket_name

Name of the S3 bucket that contains the CRL. If you do not provide a value for the CustomCname argument, the name of your S3 bucket is placed into the CRL Distribution Points extension of the issued certificate. You can change the name of your bucket by calling the UpdateCertificateAuthority operation. You must specify a bucket policy that allows Amazon Web Services Private CA to write the CRL to your bucket.

The S3BucketName parameter must conform to the S3 bucket naming rules.

revocation_configuration.crl_configuration.s3_object_acl

Determines whether the CRL will be publicly readable or privately held in the CRL Amazon S3 bucket. If you choose PUBLIC_READ, the CRL will be accessible over the public internet. If you choose BUCKET_OWNER_FULL_CONTROL, only the owner of the CRL S3 bucket can access the CRL, and your PKI clients may need an alternative method of access.

If no value is specified, the default is PUBLIC_READ.

Note: This default can cause CA creation to fail in some circumstances. If you have have enabled the Block Public Access (BPA) feature in your S3 account, then you must specify the value of this parameter as BUCKET_OWNER_FULL_CONTROL, and not doing so results in an error. If you have disabled BPA in S3, then you can specify either BUCKET_OWNER_FULL_CONTROL or PUBLIC_READ as the value.

For more information, see Blocking public access to the S3 bucket.

revocation_configuration.ocsp_configuration

Configuration of Online Certificate Status Protocol (OCSP) support, if any, maintained by your private CA. When you revoke a certificate, OCSP responses may take up to 60 minutes to reflect the new status.

Show child fields
revocation_configuration.ocsp_configuration.enabled

Flag enabling use of the Online Certificate Status Protocol (OCSP) for validating certificate revocation status.

revocation_configuration.ocsp_configuration.ocsp_custom_cname

By default, Amazon Web Services Private CA injects an Amazon Web Services domain into certificates being validated by the Online Certificate Status Protocol (OCSP). A customer can alternatively use this object to define a CNAME specifying a customized OCSP domain.

The content of a Canonical Name (CNAME) record must conform to RFC2396 restrictions on the use of special characters in URIs. Additionally, the value of the CNAME must not include a protocol prefix such as "http://" or "https://".

For more information, see Customizing Online Certificate Status Protocol (OCSP) in the Amazon Web Services Private Certificate Authority User Guide.

serial

Serial number of your private CA.

VARCHAR
status

Status of your private CA.

VARCHAR
type

Type of your private CA.

VARCHAR
usage_mode

Specifies whether the CA issues general-purpose certificates that typically require a revocation mechanism, or short-lived certificates that may optionally omit revocation because they expire quickly. Short-lived certificate validity is limited to seven days.

The default value is GENERAL_PURPOSE.

VARCHAR