turbot/kubernetes
steampipe plugin install kubernetes

Table: kubernetes_{custom_resource_singular_name} - Query Kubernetes Custom Resources using SQL

Kubernetes Custom Resources are extensions of the Kubernetes API. A Custom Resource Definition (CRD) is a built-in API that allows you to define your own custom types of resources. These custom resources act like any other native Kubernetes object, providing the same functionalities.

Table Usage Guide

The kubernetes_{custom_resource_singular_name} table provides insights into the custom resources within Kubernetes. As a Kubernetes administrator, you can explore custom resource-specific details through this table, including their definitions, specifications, and associated metadata. Utilize it to uncover information about custom resources, such as their status, the version of the custom resource definition they are associated with, and their scope.

Query data from the custom resource called kubernetes_{custom_resource_singular_name}, e.g., kubernetes_certificate, kubernetes_capacityrequest. A table is automatically created to represent each custom resource.

If multiple custom resources share the same singular name, you can query the data using their table fully qualified name kubernetes_{custom_resource_singular_name}_{custom_resource_group_name}, e.g., kubernetes_certificate_cert_manager_io.

For instance, given the CRD certManager.yaml:

apiVersion: apiextensions.k8s.io/v1
kind: CustomResourceDefinition
metadata:
name: certificates.cert-manager.io
labels:
app: cert-manager
app.kubernetes.io/name: cert-manager-crd
spec:
group: cert-manager.io
names:
kind: Certificate
listKind: CertificateList
plural: certificates
shortNames:
- cert
- certs
# table name - kubernetes_certificate
singular: certificate
categories:
- cert-manager
scope: Namespaced
versions:
- name: v1
subresources:
status: {}
schema:
openAPIV3Schema:
description: "A Certificate resource should be created to ensure an up to date and signed x509 certificate is stored in the Kubernetes Secret resource named in `spec.secretName`."
type: object
required:
- spec
properties:
apiVersion:
description: The versioned schema of this representation of an object.
type: string
kind:
description: A string value representing the REST resource this object represents.
type: string
metadata:
type: object
spec:
description: Desired state of the Certificate resource.
type: object
required:
- issuerRef
- secretName
properties:
commonName:
description: A common name to be used on the Certificate.
type: string
dnsNames:
description: A list of DNS subjectAltNames to be set on the Certificate.
type: array
items:
type: string
duration:
description: "The requested 'duration' (i.e. lifetime) of the Certificate. Defaults to 90 days."
type: string
ipAddresses:
description: A list of IP address subjectAltNames to be set on the Certificate.
type: array
items:
type: string
isCA:
description: IsCA will mark this Certificate as valid for certificate signing.
type: boolean
issuerRef:
description: A reference to the issuer for this certificate.
type: object
required:
- name
properties:
name:
description: Name of the resource being referred to.
type: string
renewBefore:
description: "How long before the currently issued certificate's expiry cert-manager should renew the certificate. Default to 2/3 of the duration."
type: string
secretName:
description: The name of the secret resource that will be automatically created and managed by this Certificate resource.
type: string
served: true
storage: true

And the custom resource spCloudCertificate.yaml:

apiVersion: cert-manager.io/v1
kind: Certificate
metadata:
name: temporal-w-spcloud123456
labels:
workspace-id: w_spcloud123456
identity-id: o_spcloud123456
region: apse1
shard: "0001"
workspace-pluginset: "202203170111114"
app.kubernetes.io/component: steampipe-workspace-db
app.kubernetes.io/managed-by: steampipe-api
app.kubernetes.io/part-of: steampipe-cloud
app.kubernetes.io/instance: w-spcloud123456
app.kubernetes.io/version: 0.13.3-workspace-spcloud.20220317010004
spec:
secretName: temporal-w-spcloud123456-tls
duration: 87600h # 10 years
dnsNames:
- w-spcloud123456

If my connection configuration is:

connection "kubernetes" {
plugin = "kubernetes"
custom_resource_tables = ["certificates.*"]
}

Steampipe will automatically create the kubernetes_certificate table, which can then be inspected and queried like other tables:

.inspect kubernetes_certificate;
+--------------------+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| column | type | description |
+--------------------+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| _ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. |
| api_version | text | The API version of the resource. |
| common_name | text | A common name to be used on the Certificate. |
| context_name | text | Kubectl config context name. |
| creation_timestamp | timestamp with time zone | CreationTimestamp is a timestamp representing the server time when this object was created. |
| dns_names | jsonb | A list of DNS subjectAltNames to be set on the Certificate. |
| duration | text | The requested 'duration' (i.e. lifetime) of the Certificate. Defaults to 90 days. |
| end_line | bigint | The path to the manifest file. |
| ip_addresses | jsonb | A list of IP address subjectAltNames to be set on the Certificate. |
| is_ca | boolean | IsCA will mark this Certificate as valid for certificate signing. |
| issuer_ref | jsonb | A reference to the issuer for this certificate. |
| kind | text | Type of resource. |
| labels | jsonb | Map of string keys and values that can be used to organize and categorize (scope and select) objects. May match selectors of replication controllers and services. |
| name | text | Name of resource. |
| namespace | text | Namespace defines the space within which each name must be unique. |
| path | text | The path to the manifest file. |
| renew_before | text | How long before the currently issued certificate's expiry cert-manager should renew the certificate. Default to 2/3 of the duration. |
| secret_name | text | The name of the secret resource that will be automatically created and managed by this Certificate resource. |
| source_type | text | The source of the resource. Possible values are: deployed and manifest. If the resource is fetched from the spec file the value will be manifest. |
| start_line | bigint | The path to the manifest file. |
| uid | text | UID is the unique in time and space value for this object. |
+--------------------+--------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
> select name, uid, kind, api_version, namespace from kubernetes_certificate;
+------------------------------------+--------------------------------------+-------------+--------------------+-----------+
| name | uid | kind | api_version | namespace |
+------------------------------------+--------------------------------------+-------------+--------------------+-----------+
| temporal-w-spcloudt6t6sk7toegg-tls | 5ccd69be-6e73-4edc-8c1d-bccd6a1e6e38 | Certificate | cert-manager.io/v1 | default |
+------------------------------------+--------------------------------------+-------------+--------------------+-----------+

Similarly, the plugin also allows reading the CRDs and custom resource configurations from the manifest files:

  • If the paths for both CRD and custom resource manifests are configured in the config, the plugin will create table using that manifest, which can be queried like other tables. For example:
connection "kubernetes" {
plugin = "kubernetes"
custom_resource_tables = ["*"]
# both CRD and custom resource configurations are from manifests
manifest_file_paths = ["/path/to/certManager.yaml", "/path/to/spCloudCertificate.yaml"]
}
  • If the CRD is already deployed to the current cluster context, the plugin can read the CRD configuration from there, and will create the table using that configuration. In that case the plugin will only read the resource configuration from the manifest. For example:
connection "kubernetes" {
plugin = "kubernetes"
custom_resource_tables = ["*"]
# CRD is already deployed, and the resource configuration will be taken from manifest
manifest_file_paths = ["/path/to/spCloudCertificate.yaml"]
}

Examples

List all certificates

Explore which certificates are present, their unique identifiers, and the time of their creation to gain insights into the system's security infrastructure and its evolution over time. This information is crucial for maintaining the security integrity of the system and planning future updates or modifications.

select
name,
uid,
namespace,
creation_timestamp,
api_version
from
kubernetes_certificate;
select
name,
uid,
namespace,
creation_timestamp,
api_version
from
kubernetes_certificate;

List certificates added in the last 24 hours

Discover the certificates that have been added recently to maintain an up-to-date understanding of your Kubernetes environment. This can be useful for tracking changes and ensuring new additions are properly configured and secured.

select
name,
uid,
namespace,
creation_timestamp,
api_version
from
kubernetes_certificate
where
creation_timestamp = now() - interval '24 hrs';

List certificates by annotation key

Explore which Kubernetes certificates are designated as certificate authorities by annotation key.

select
name,
uid,
namespace,
annotations,
labels
from
kubernetes_certificate
where
annotations -> 'foo' is not null;
select
name,
uid,
namespace,
json_extract(annotations, '$.foo') as foo_annotation,
labels
from
kubernetes_certificate
where
json_extract(annotations, '$.foo') is not null;

List ISCA certificates

Explore which Kubernetes certificates are designated as certificate authorities. This is useful to understand the hierarchy and trust relationships within your Kubernetes environment.

select
name,
uid,
namespace,
creation_timestamp,
api_version
from
kubernetes_certificate
where
is_ca;
select
name,
uid,
namespace,
creation_timestamp,
api_version
from
kubernetes_certificate
where
is_ca = 1;

List expired certificates

Identify instances where your Kubernetes certificates have expired. This is beneficial in maintaining the security and integrity of your system by ensuring all certificates are up to date.

select
name,
uid,
namespace,
creation_timestamp,
api_version
from
kubernetes_certificate
where
now() > to_timestamp(not_after, 'YYYY-MM-DD');
select
name,
uid,
namespace,
creation_timestamp,
api_version
from
kubernetes_certificate
where
datetime('now') > datetime(not_after);

Export

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)" -- kubernetes

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

steampipe_export_kubernetes --config '<your_config>' kubernetes_{custom_resource_singular_name}