steampipe plugin install aws

Table: aws_rds_db_instance - Query AWS RDS DB Instances using SQL

Title: AWS RDS DB Instances

Description: AWS RDS DB Instances are a part of Amazon Relational Database Service (RDS), which makes it easier to set up, operate, and scale a relational database in the cloud. They provide cost-efficient and resizable capacity while automating time-consuming administration tasks such as hardware provisioning, database setup, patching, and backups. It frees you to focus on your applications so you can give them the fast performance, high availability, security and compatibility they need.

Table Usage Guide

The aws_rds_db_instance table in Steampipe provides you with comprehensive information about each database instance within Amazon Relational Database Service (RDS). This table allows you, as a DevOps engineer, database administrator, or other technical professional, to query detailed information about each DB instance, including its configuration, status, performance metrics, and other associated metadata. You can leverage this table to gather insights about DB instances, such as instance specifications, security configurations, backup policies, and more. The schema outlines the various attributes of the DB instance for you, including the DB instance identifier, instance class, engine version, storage type, and associated tags.

Examples

Basic info

Explore which Amazon RDS database instances are publicly accessible, along with their identifiers, classes, engines, and versions. This information can help determine areas where security might be improved by limiting public access.

select
db_instance_identifier,
class,
engine,
engine_version,
publicly_accessible
from
aws_rds_db_instance
select
db_instance_identifier,
class,
engine,
engine_version,
publicly_accessible
from
aws_rds_db_instance

List DB instances which are publicly accessible

Determine the areas in which your database instances are publicly accessible. This is useful for identifying potential security risks and ensuring that your data is protected.

select
db_instance_identifier,
publicly_accessible
from
aws_rds_db_instance
where
publicly_accessible;
select
db_instance_identifier,
publicly_accessible
from
aws_rds_db_instance
where
publicly_accessible = 1;

List DB instances which are not authenticated through IAM users and roles

Identify instances where database instances are not utilizing IAM users and roles for authentication. This query is useful in highlighting potential security risks and enforcing best practices for access management.

select
db_instance_identifier,
iam_database_authentication_enabled
from
aws_rds_db_instance
where
not iam_database_authentication_enabled;
select
db_instance_identifier,
iam_database_authentication_enabled
from
aws_rds_db_instance
where
iam_database_authentication_enabled = 0;

Get VPC and subnet info for each DB instance

Determine the areas in which your database instances are located and the security measures in place. This helps ensure your databases are in the desired regions and properly secured, enhancing your data management strategy.

select
db_instance_identifier as attached_vpc,
vsg ->> 'VpcSecurityGroupId' as vpc_security_group_id,
vsg ->> 'Status' as status,
sub -> 'SubnetAvailabilityZone' ->> 'Name' as subnet_availability_zone,
sub ->> 'SubnetIdentifier' as subnet_identifier,
sub -> 'SubnetOutpost' ->> 'Arn' as subnet_outpost,
sub ->> 'SubnetStatus' as subnet_status
from
aws_rds_db_instance
cross join jsonb_array_elements(vpc_security_groups) as vsg
cross join jsonb_array_elements(subnets) as sub;
select
db_instance_identifier as attached_vpc,
json_extract(vsg.value, '$.VpcSecurityGroupId') as vpc_security_group_id,
json_extract(vsg.value, '$.Status') as status,
json_extract(
json_extract(sub.value, '$.SubnetAvailabilityZone'),
'$.Name'
) as subnet_availability_zone,
json_extract(sub.value, '$.SubnetIdentifier') as subnet_identifier,
json_extract(
json_extract(sub.value, '$.SubnetOutpost'),
'$.Arn'
) as subnet_outpost,
json_extract(sub.value, '$.SubnetStatus') as subnet_status
from
aws_rds_db_instance,
json_each(vpc_security_groups) as vsg,
json_each(subnets) as sub;

List DB instances with deletion protection disabled

Discover the segments that consist of DB instances where deletion protection is not enabled. This is useful for identifying potential security vulnerabilities within your AWS RDS DB instances.

select
db_instance_identifier,
class,
engine,
engine_version,
deletion_protection
from
aws_rds_db_instance
where
not deletion_protection;
select
db_instance_identifier,
class,
engine,
engine_version,
deletion_protection
from
aws_rds_db_instance
where
deletion_protection = 0;

List DB instances with unecrypted storage

Discover the segments that are using unencrypted storage within your database instances to assess potential vulnerabilities and improve your system's security. This query is particularly useful in identifying areas where sensitive data may be at risk due to lack of encryption.

select
db_instance_identifier,
class,
allocated_storage,
deletion_protection
from
aws_rds_db_instance
where
not storage_encrypted;
select
db_instance_identifier,
class,
allocated_storage,
deletion_protection
from
aws_rds_db_instance
where
storage_encrypted = 0;

Get endpoint info for each DB instance

Explore which database instances are linked to specific endpoints. This information can help assess the network connections and their respective ports, aiding in efficient database management and troubleshooting.

select
db_instance_identifier,
endpoint_address,
endpoint_hosted_zone_id,
endpoint_port
from
aws_rds_db_instance;
select
db_instance_identifier,
endpoint_address,
endpoint_hosted_zone_id,
endpoint_port
from
aws_rds_db_instance;

List SQL Server DB instances with SSL disabled in assigned parameter group

Identify instances where SQL Server database instances have SSL disabled in their assigned parameter groups. This is useful for uncovering potential security vulnerabilities related to unencrypted data transmission.

with db_parameter_group as (
select
name as db_parameter_group_name,
pg ->> 'ParameterName' as parameter_name,
pg ->> 'ParameterValue' as parameter_value
from
aws_rds_db_parameter_group,
jsonb_array_elements(parameters) as pg
where
-- The example is limited to SQL Server, this may change based on DB engine
pg ->> 'ParameterName' like 'rds.force_ssl'
and name not like 'default.%'
),
rds_associated_parameter_group as (
select
db_instance_identifier as db_instance_identifier,
arn,
pg ->> 'DBParameterGroupName' as DBParameterGroupName
from
aws_rds_db_instance,
jsonb_array_elements(db_parameter_groups) as pg
where
engine like 'sqlserve%'
)
select
rds.db_instance_identifier as name,
rds.DBParameterGroupName,
parameter_name,
parameter_value
from
rds_associated_parameter_group as rds
left join db_parameter_group d on rds.DBParameterGroupName = d.db_parameter_group_name
where
parameter_value = '0'
with db_parameter_group as (
select
name as db_parameter_group_name,
json_extract(pg.value, '$.ParameterName') as parameter_name,
json_extract(pg.value, '$.ParameterValue') as parameter_value
from
aws_rds_db_parameter_group,
json_each(parameters) as pg
where
json_extract(pg.value, '$.ParameterName') like 'rds.force_ssl'
and name not like 'default.%'
),
rds_associated_parameter_group as (
select
db_instance_identifier as db_instance_identifier,
arn,
json_extract(pg.value, '$.DBParameterGroupName') as DBParameterGroupName
from
aws_rds_db_instance,
json_each(db_parameter_groups) as pg
where
engine like 'sqlserve%'
)
select
rds.db_instance_identifier as name,
rds.DBParameterGroupName,
parameter_name,
parameter_value
from
rds_associated_parameter_group as rds
left join db_parameter_group d on rds.DBParameterGroupName = d.db_parameter_group_name
where
parameter_value = '0';

List DB instance pending maintenance actions

Determine the areas in which your database instances have pending maintenance actions. This can help ensure your databases are up-to-date and avoid potential downtime or performance issues due to missed maintenance.

select
actions ->> 'ResourceIdentifier' as db_instance_identifier,
details ->> 'Action' as action,
details ->> 'OptInStatus' as opt_in_status,
details ->> 'ForcedApplyDate' as forced_apply_date,
details ->> 'CurrentApplyDate' as current_apply_date,
details ->> 'AutoAppliedAfterDate' as auto_applied_after_date
from
aws_rds_db_instance,
jsonb_array_elements(pending_maintenance_actions) as actions,
jsonb_array_elements(actions -> 'PendingMaintenanceActionDetails') as details;
select
json_extract(actions.value, '$.ResourceIdentifier') as db_instance_identifier,
json_extract(details.value, '$.Action') as action,
json_extract(details.value, '$.OptInStatus') as opt_in_status,
json_extract(details.value, '$.ForcedApplyDate') as forced_apply_date,
json_extract(details.value, '$.CurrentApplyDate') as current_apply_date,
json_extract(details.value, '$.AutoAppliedAfterDate') as auto_applied_after_date
from
aws_rds_db_instance,
json_each(pending_maintenance_actions) as actions,
json_each(
json_extract(
actions.value,
'$.PendingMaintenanceActionDetails'
)
) as details;

List certificate details associated to the instance

Discover the segments that highlight the validity and type of certificates associated with a specific instance. This can be especially useful in managing and tracking certificate expiration dates, ensuring the security and reliability of your database instances.

select
arn,
certificate ->> 'CertificateArn' as certificate_arn,
certificate ->> 'CertificateType' as certificate_type,
certificate ->> 'ValidFrom' as valid_from,
certificate ->> 'ValidTill' as valid_till
from
aws_rds_db_instance;
select
arn,
json_extract(certificate, '$.CertificateArn') as certificate_arn,
json_extract(certificate, '$.CertificateType') as certificate_type,
json_extract(certificate, '$.ValidFrom') as valid_from,
json_extract(certificate, '$.ValidTill') as valid_till
from
aws_rds_db_instance;

List certificates valid for less than 90 days

Discover the segments that have certificates valid for less than 90 days in your AWS RDS database instances. This can help in proactive renewal of certificates, thereby avoiding any service disruption due to certificate expiry.

select
arn,
certificate ->> 'CertificateArn' as certificate_arn,
certificate ->> 'CertificateType' as certificate_type,
certificate ->> 'ValidFrom' as valid_from,
certificate ->> 'ValidTill' as valid_till
from
aws_rds_db_instance
where
(certificate ->> 'ValidTill') :: timestamp <= (current_date - interval '90' day);
select
arn,
json_extract(certificate, '$.CertificateArn') as certificate_arn,
json_extract(certificate, '$.CertificateType') as certificate_type,
json_extract(certificate, '$.ValidFrom') as valid_from,
json_extract(certificate, '$.ValidTill') as valid_till
from
aws_rds_db_instance
where
julianday('now') - julianday(json_extract(certificate, '$.ValidTill')) >= 90;

Listing RDS DB Instances with Existing Processor Features

Supports Infrastructure as Code (IaC) and Automation For organizations using IaC practices or automation in their cloud environments, such queries can help in generating reports, monitoring configurations, or triggering workflows based on the state of RDS instances.

select
db_instance_identifier,
class,
engine,
engine_version,
kms_key_id,
processor_features
from
aws_rds_db_instance
where
processor_features not null;
select
db_instance_identifier,
class,
engine,
engine_version,
kms_key_id,
processor_features
from
aws_rds_db_instance
where
processor_features not null;

Control examples

Schema for aws_rds_db_instance

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
account_idtextThe AWS Account ID in which the resource is located.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
allocated_storagebigintSpecifies the allocated storage size specified in gibibytes(GiB).
arntextThe Amazon Resource Name (ARN) for the DB Instance.
associated_rolesjsonbA list of AWS IAM roles that are associated with the DB instance.
auto_minor_version_upgradebooleanSpecifies whether minor version patches are applied automatically, or not.
availability_zonetextSpecifies the name of the Availability Zone the DB instance is located in.
backup_retention_periodbigintSpecifies the number of days for which automatic DB snapshots are retained.
ca_certificate_identifiertextThe identifier of the CA certificate for this DB instance.
certificatejsonbThe CA certificate associated with the DB instance.
character_set_nametextSpecifies the name of the character set that this instance is associated with.
classtextContains the name of the compute and memory capacity class of the DB instance.
copy_tags_to_snapshotbooleanSpecifies whether tags are copied from the DB instance to snapshots of the DB instance, or not.
create_timetimestamp with time zoneProvides the date and time the DB instance was created.
customer_owned_ip_enabledbooleanSpecifies whether a customer-owned IP address (CoIP) is enabled for an RDS on Outposts DB instance, or not.
db_cluster_identifiertext=The friendly name to identify the DB cluster, that the DB instance is a member of.
db_instance_identifiertext=The friendly name to identify the DB Instance.
db_nametextContains the name of the initial database of this instance that was provided at create time.
db_parameter_groupsjsonbA list of DB parameter groups applied to this DB instance.
db_security_groupsjsonbA list of DB security group associated with the DB instance.
db_subnet_group_arntextThe Amazon Resource Name (ARN) for the DB subnet group.
db_subnet_group_descriptiontextProvides the description of the DB subnet group.
db_subnet_group_nametextThe name of the DB subnet group.
db_subnet_group_statustextProvides the status of the DB subnet group.
deletion_protectionbooleanSpecifies whether the DB instance has deletion protection enabled, or not.
domain_membershipsjsonbA list of Active Directory Domain membership records associated with the DB instance.
enabled_cloudwatch_logs_exportsjsonbA list of log types that this DB instance is configured to export to CloudWatch Logs.
endpoint_addresstextSpecifies the DNS address of the DB instance.
endpoint_hosted_zone_idtextSpecifies the ID that Amazon Route 53 assigns when you create a hosted zone.
endpoint_portbigintSpecifies the port that the database engine is listening on.
enginetext=The name of the database engine to be used for this DB instance.
engine_versiontextIndicates the database engine version.
enhanced_monitoring_resource_arntextThe ARN of the Amazon CloudWatch Logs log stream that receives the Enhanced Monitoring metrics data for the DB instance.
iam_database_authentication_enabledbooleanSpecifies whether the the mapping of AWS IAM accounts to database accounts is enabled, or not.
iopsbigintSpecifies the Provisioned IOPS (I/O operations per second) value.
kms_key_idtextThe AWS KMS key identifier for the encrypted DB instance.
latest_restorable_timetimestamp with time zoneSpecifies the latest time to which a database can be restored with point-in-time restore.
license_modeltextLicense model information for this DB instance.
master_user_nametextContains the master username for the DB instance.
max_allocated_storagebigintThe upper limit to which Amazon RDS can automatically scale the storage of the DB instance.
monitoring_intervalbigintThe interval, in seconds, between points when Enhanced Monitoring metrics are collected for the DB instance.
monitoring_role_arntextThe ARN for the IAM role that permits RDS to send Enhanced Monitoring metrics to Amazon CloudWatch Logs.
multi_azbooleanSpecifies if the DB instance is a Multi-AZ deployment.
nchar_character_set_nametextThe name of the NCHAR character set for the Oracle DB instance.
option_group_membershipsjsonbA list of option group memberships for this DB instance
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
pending_maintenance_actionsjsonbA list that provides details about the pending maintenance actions for the resource.
performance_insights_enabledbooleanSpecifies whether Performance Insights is enabled for the DB instance, or not.
performance_insights_kms_key_idtextThe AWS KMS key identifier for encryption of Performance Insights data.
performance_insights_retention_periodbigintThe amount of time, in days, to retain Performance Insights data.
portbigintSpecifies the port that the DB instance listens on.
preferred_backup_windowtextSpecifies the daily time range during which automated backups are created.
preferred_maintenance_windowtextSpecifies the weekly time range during which system maintenance can occur.
processor_featuresjsonbThe number of CPU cores and the number of threads per core for the DB instance class of the DB instance.
promotion_tierbigintSpecifies the order in which an Aurora Replica is promoted to the primary instance after a failure of the existing primary instance.
publicly_accessiblebooleanSpecifies the accessibility options for the DB instance.
read_replica_db_cluster_identifiersjsonbA list of identifiers of Aurora DB clusters to which the RDS DB instance is replicated as a read replica.
read_replica_db_instance_identifiersjsonbA list of identifiers of the read replicas associated with this DB instance.
read_replica_source_db_instance_identifiertextContains the identifier of the source DB instance if this DB instance is a read replica.
regiontextThe AWS Region in which the resource is located.
replica_modetextThe mode of an Oracle read replica.
resource_idtext=The AWS Region-unique, immutable identifier for the DB instance.
secondary_availability_zonetextSpecifies the name of the secondary Availability Zone for a DB instance with multi-AZ support.
statustextSpecifies the current state of this database.
status_infosjsonbThe status of a read replica.
storage_encryptedbooleanSpecifies whether the DB instance is encrypted, or not.
storage_throughputbigintSpecifies the storage throughput for the DB instance. This setting applies only to the gp3 storage type.
storage_typetextSpecifies the storage type associated with DB instance.
subnetsjsonbA list of subnet elements.
tagsjsonbA map of tags for the resource.
tags_srcjsonbA list of tags attached to the DB Instance.
tde_credential_arntext The ARN from the key store with which the instance is associated for TDE encryption.
timezonetextThe time zone of the DB instance.
titletextTitle of the resource.
vpc_idtextProvides the VpcId of the DB subnet group.
vpc_security_groupsjsonbA list of VPC security group elements that the DB instance belongs to.

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

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

steampipe_export_aws --config '<your_config>' aws_rds_db_instance