Table: awscfn_resource

Each resource block describes one or more AWS resources that you want to include in the stack, such as Amazon EC2 instances, DynamoDB tables, or Amazon S3 buckets.

The properties_src column contains the raw resource properties, while the properties column uses AWS' goformation library to resolve CloudFormation instrinsic functions and references. In some cases, goformation is unable to parse the CloudFormation template or is unable to resolve property values.

For example, the sample AutoScalingScheduledAction CloudFormation template includes a SecurityGroup resource:

"InstanceSecurityGroup": {
  "Type": "AWS::EC2::SecurityGroup",
  "Properties": {
    "GroupDescription": "Enable SSH access and HTTP access on the configured port",
    "SecurityGroupIngress": [ {
      "IpProtocol": "tcp",
      "FromPort": "22",
      "ToPort": "22",
      "CidrIp": { "Ref": "SSHLocation" }
    }, {
      "IpProtocol": "tcp",
      "FromPort": "80",
      "ToPort": "80",
      "CidrIp": "0.0.0.0/0"
    } ],
    "VpcId": { "Ref" : "VpcId" }
  }
}

Because the FromPort and ToPort property values are of type String (which is valid per CloudFormation), but the AWS::EC2::SecurityGroup Ingress schema defines their type as Integer, goformation is unable to parse the CloudFormation template and properties will be returned as null:

select
  name,
  jsonb_pretty(properties_src) as properties_src,
  properties
from
  awscfn_resource
where
  name = 'InstanceSecurityGroup';

+-----------------------+-------------------------------------------------+------------+
| name                  | properties_src                                  | properties |
+-----------------------+-------------------------------------------------+------------+
| InstanceSecurityGroup | {                                               | <null>     |
|                       |     "VpcId": {                                  |            |
|                       |         "Ref": "VpcId"                          |            |
|                       |     },                                          |            |
|                       |     "GroupDescription": "Enable SSH access...", |            |
|                       |     "SecurityGroupIngress": [                   |            |
|                       |         {                                       |            |
|                       |             "CidrIp": {                         |            |
|                       |                 "Ref": "SSHLocation"            |            |
|                       |             },                                  |            |
|                       |             "ToPort": "22",                     |            |
|                       |             "FromPort": "22",                   |            |
|                       |             "IpProtocol": "tcp"                 |            |
|                       |         },                                      |            |
|                       |         {                                       |            |
|                       |             "CidrIp": "0.0.0.0/0",              |            |
|                       |             "ToPort": "80",                     |            |
|                       |             "FromPort": "80",                   |            |
|                       |             "IpProtocol": "tcp"                 |            |
|                       |         }                                       |            |
|                       |     ]                                           |            |
|                       | }                                               |            |
+-----------------------+-------------------------------------------------+------------+

Examples

Basic info

select
  name,
  type,
  case
    when properties is not null then properties
    else properties_src
  end as resource_properties,
  path
from
  awscfn_resource;

List AWS IAM users

select
  name,
  type,
  case
    when properties is not null then properties
    else properties_src
  end as resource_properties,
  path
from
  awscfn_resource
where
  type = 'AWS::IAM::User';

List AWS CloudTrail trails that are not encrypted

select
  name,
  path
from
  awscfn_resource
where
  type = 'AWS::CloudTrail::Trail'
  and (
    (
      properties is not null
      and properties -> 'KMSKeyId' is null
    )
    or properties_src -> 'KMSKeyId' is null
  );

Get S3 bucket BucketName property value

For instance, if a CloudFormation template is defined as:

Parameters:
  WebBucketName:
    Type: String
    Default: 'TestWebBucket'
Resources:
  DevBucket:
    Type: "AWS::S3::Bucket"
    Condition: CreateDevBucket
    Properties:
      AccessControl: PublicRead
      BucketName: !Ref WebBucketName
      WebsiteConfiguration:
        IndexDocument: index.html

select
  name as resource_map_name,
  type as resource_type,
  properties_src ->> 'BucketName' as bucket_name_src,
  default_value as bucket_name
from
  awscfn_resource
where
  type = 'AWS::S3::Bucket';

+---------------+-----------------+--------------------------+----------------+
| resource_name | resource_type   | bucket_name_src          | bucket_name    |
+---------------+-----------------+--------------------------+----------------+
| DevBucket     | AWS::S3::Bucket | {"Ref": "WebBucketName"} | TestWebBucket  |
+---------------+-----------------+--------------------------+----------------+

.inspect awscfn_resource

CloudFormation resource information.

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
condition	text	Specifies the resource conditions.
creation_policy	jsonb	Specifies the associated creation_policy with a resource to prevent its status from reaching create complete until AWS CloudFormation receives a specified number of success signals or the timeout period is exceeded.
deletion_policy	text	With the deletion_policy attribute you can preserve, and in some cases, backup a resource when its stack is deleted. You specify a deletion_policy attribute for each resource that you want to control.
depends_on	text	With the depends_on attribute you can specify that the creation of a specific resource follows another. When you add a depends_on attribute to a resource, that resource is created only after the creation of the resource specified in the depends_on attribute.
metadata	jsonb	The metadata attribute enables you to associate structured data with a resource. By adding a metadata attribute to a resource, you can add data in JSON or YAML to the resource declaration.
name	text	An identifier for the resource.
path	text	Path to the file.
properties	jsonb	Specifies the resource properties with calculated values as per given condition or parameter reference.
properties_src	jsonb	Specifies the resource properties defined in the template.
start_line	bigint	Starting line number.
type	text	The resource type identifies the type of resource that you are declaring.
update_policy	jsonb	Use the update_policy attribute to specify how AWS CloudFormation handles updates to specific resources.
update_replace_policy	text	Use the update_replace_policy attribute to retain or, in some cases, backup the existing physical instance of a resource when it's replaced during a stack update operation.

turbot/awscfn