This transformer is free to use while it is in preview. It
will become a paid premium offering when it reaches general availability. For more information, reach out to
CloudQuery support.
Understanding what is connected to what is hard at AWS scale: the answer is
spread across hundreds of tables, and every service spells a reference
differently. This transformer collapses that into one table of edges.
For every AWS record it sees, it finds every reference the record contains and
emits one row per reference:
| source_table | source_cq_id | target_table | target_cq_id | relationship_type | source_column |
|---|
| aws_ec2_instances | 4f2a… | aws_ec2_vpcs | 5f42… | id_reference | vpc_id |
| aws_ec2_instances | 4f2a… | aws_iam_roles | 1c91… | arn_reference | iam_instance_profile_arn |
Both _cq_id values are the real identifiers of the two rows, so the table
always joins straight back to the resource tables:
select r.*
from aws_resource_relationships r
join aws_ec2_instances i on i._cq_id = r.source_cq_id
where r.target_table = 'aws_ec2_vpcs';
The generated table #
Every edge goes into one table, aws_resource_relationships. The name is fixed,
and it is the only table the transformer writes: every AWS table's schema maps
onto this one.
| Column | Type | Notes |
|---|
_cq_id | uuid | Primary key; a hash of the edge's key components |
_cq_sync_group_id | string | Mirrored from the source when the CLI adds it |
_cq_sync_time | timestamp | Mirrored from the source when the CLI adds it |
_cq_source_name | string | Mirrored from the source when the CLI adds it |
source_table | string | Key component |
source_cq_id | uuid | Key component; joins to the source table's _cq_id |
source_identifier | string | The ARN naming the source row; null for tables the catalog knows no identifier column for |
target_table | string | Key component |
target_cq_id | uuid | Key component; joins to the target table's _cq_id |
source_column | string | Key component; path to the value, e.g. configuration.Environment.Variables.ASSETS |
relationship_type | string | arn_reference, id_reference, account_reference or region_reference |
target_identifier | string | Key component; the ARN or id the edge was resolved from |
source_account_id | string | Account of the source record |
source_region | string | Region of the source record |
source_column is part of the key, so two different columns pointing at the same
target produce two rows. That is what makes it possible to tell why two
resources are related. List indices are normalised to [] so an edge keeps the
same identity when AWS reorders a list.
target_identifier is part of the key too. target_cq_id already identifies the
target, so it adds nothing, but it has been a key component since the table's
first version and dropping it would renumber every existing edge.
_cq_id is a hash of the six key components above (source_table,
source_cq_id, target_table, target_cq_id, source_column and
target_identifier), computed the same way the SDK identifies every other
CloudQuery row, so the same edge keeps the same identifier across syncs.
The three _cq_* sync columns are mirrored from the source schema only when the
CLI put them there, so a sync configured without a sync time produces a
relationship table without one too. _cq_sync_group_id is marked a primary key,
matching how the CLI marks it on source tables, so delete-stale behaves the same
way here.
Reading an edge without joining #
source_table varies per row, so naming a source the long way means generating
a UNION over every table that appears in the column. source_identifier
carries the source row's own ARN instead, which answers most questions outright:
select source_identifier, source_column, target_table, target_identifier
from aws_resource_relationships
where target_identifier = 'arn:aws:ec2:us-east-1:123456789012:vpc/vpc-0abc';
It is null for tables the catalog knows no identifier column for.
aws_ec2_dhcp_options is keyed by (account_id, dhcp_options_id, region) and has
no ARN to carry, and the per-account settings tables are identified by the account
and region already carried in their own columns. It is also the only way to name a
source whose own table never reached the destination, since the edge is emitted
regardless.
Where a table's key holds several ARNs, the label is the one naming the row itself:
an aws_ecs_cluster_services row is labelled by its service ARN, not by the cluster
ARN also in its key. A handful of junction tables (aws_iam_role_attached_policies
is one) are keyed by two ARNs that both name some other resource, and are left
null rather than labelled with an arbitrary half of the pair.
source_identifier is not a key component: it is a label for source_cq_id,
which already identifies the row.
How a target _cq_id is derived #
The plugin never looks the target row up. It computes the identifier that row
will carry. CloudQuery derives _cq_id deterministically from a resource's
primary key components, so a reference plus knowledge of the target table's key
is enough to reproduce it exactly.
The plugin embeds a catalog, generated from the AWS source plugin's own table
definitions, recording those key components for every AWS table that has them.
It is generated from whichever version of the AWS plugin it was last built
against, so its coverage tracks that plugin rather than this one's version. The
hashing is pinned against the CloudQuery SDK by golden vectors in
client/cqid/cqid_test.go.
This means edges are emitted even when the referencing record is synced before
the referenced one, and when the referenced table is not synced at all, in
which case the edge simply joins to nothing.
What counts as a reference #
The three kinds below are fixed. skip_tables and skip_columns are the whole
of the plugin's configuration, and both only narrow what is scanned: neither can
change what a scanned value means. Records from tables other than aws_* are
never scanned, though they are still learned from, so such a table can be a
relationship target without being a source.
ARN references (arn_reference). Any well-formed ARN found anywhere in the
record, including inside JSON columns and lists. The ARN identifies the target's
service, resource type, account and region, so nothing is assumed.
References to a sub-resource resolve to the resource that owns them: an S3
object ARN becomes its bucket, an SNS subscription ARN becomes its topic.
A row's own ARN column is the exception: it states the row's identity rather
than referencing anything, so it is left unscanned and no resource points at
itself through it. Self-edges are dropped more generally: a value that resolves
back to the row holding it produces no edge.
Native id references (id_reference). AWS resource ids such as vpc-,
subnet-, sg-, i-, ami-, vol-, eni-, rtb-, igw-, nat-, tgw-.
The body must be 8 or 17 hex characters, so ordinary prose is not mistaken for an
id. Because a bare id carries no context, the target ARN is synthesised from the
referencing record's own partition, account and region.
A few prefixes target tables keyed by the bare id rather than by an ARN.
dopt- on aws_ec2_dhcp_options and eipalloc- on aws_ec2_eips use the id
itself as the key component, with the account and region still taken from the
referencing record.
Container references (account_reference, region_reference). Every AWS
record says which account and region it lives in, so every record gets an edge to
its row in aws_account_information and one to its row in aws_regions. Both
tables are keyed by exactly that context, (account_id) and
(account_id, region), so the target _cq_id follows from the record without
anything being matched against the catalog.
These are separate types rather than arn_reference or id_reference because
nothing was scanned to find them. A reference edge exists because a record
mentioned something; a container edge exists for every record, whether or not it
mentions anything at all.
Every record produces both container edges, so a query asking "what does this
resource point at" has to exclude them:
where relationship_type not in ('account_reference', 'region_reference')
Giving them their own types is what makes that a one-line filter rather than a
guess at which target tables are containers. The adjacency view in
docs/views.sql is built on it. For the same reason they are unaffected by
skip_columns, which says what not to scan.
source_column names the field the value was read from, so it is account_id on
most tables, request_account_id or request_region on the tables keyed that
way, and the ARN column on a table with neither: the row's own ARN states where
it lives when no dedicated column does.
An edge is only emitted when every component of the target's key is present.
aws_regions is keyed per account, so a record with a region but no account
produces neither edge, and a record in a global service whose ARN carries no
region (IAM, S3) gets the account edge alone. The two container tables get no
edge to themselves: both are keyed by the very context the target key is built
from, so the edge would point at the row it came from.
Policies are not scanned #
Any column or JSON key whose name contains policy_json, in any case, is left
unscanned, and so are the ARNs and ids inside it. The AWS plugin names every
policy document that way (policy_json, inline_policy_json,
data_protection_policy_json), which covers identity policies, resource
policies and Organizations SCPs alike. This is not configurable.
A policy says what a permission is scoped to, which is a different claim from
what the row holding the policy is attached to. "Resource": "arn:aws:s3:::*"
on a role would otherwise connect that role to every bucket in the account, and
a Deny statement would produce an edge indistinguishable from an allow. Both
read as topology the account does not have.
The name is matched at every depth, so a policy nested inside another JSON
column is skipped at the key that holds it rather than at the column.
Edges about policies are unaffected: aws_iam_roles still reaches
aws_iam_role_policies and aws_iam_role_attached_policies, and an attachment
row still reaches aws_iam_policies, because those come from the attachment
columns rather than from inside a document.
Mapping an ARN back to a table #
Two mechanisms combine:
A seed map of curated and statically mined (service, resource-type)
pairs, covering EC2, IAM, S3, Lambda, RDS, ECS, EKS, KMS, SNS, SQS, DynamoDB,
ELB, CloudFront, Route 53 and more.
Runtime learning. Most AWS tables take their ARN straight from the API, so
the shape cannot be read out of the source statically. Instead, every record
that streams through teaches the plugin which table owns its ARN shape. This
covers the large majority of the tables a reference can resolve to; the seed
map is what makes the rest, and the common shapes, resolve without waiting for
the owning table to be synced.
Learning only accepts top-level tables identified solely by their own ARN, so a
child table cannot claim a shape it does not own, and it is skipped for services
whose ARNs carry no resource type.
Learning makes resolution ordering-dependent: an ARN shape that is neither
seeded nor yet observed does not resolve. A seeded shape resolves from the first
record that carries it, whatever order the sync runs in.
Known limitations #
Shared resources. The target's account and region are taken from the ARN.
When an account syncs a resource owned by another account, a shared VPC for
example, the row's source_account_id is the syncing account while the ARN
names the owner, and the two disagree. Such edges will not join.
Synthesised ARNs assume locality. A native id is resolved against the
referencing record's own account and region, so a cross-account or
cross-region id reference resolves to the wrong target.
Qualified ARNs. A versioned Lambda ARN
(…:function:my-func:1) keys off the qualified form, while the function row
keys off the unqualified one.
Ambiguous keys. A table keyed by two ARNs (aws_ecs_cluster_services, keyed
by arn and cluster_arn) is not a valid target, because there is no way to
tell which one a reference matched.
Opaque key components. Tables keyed partly by something not derivable from
a reference (aws_iam_policies, keyed partly by id and input_hash) are not
valid targets.
Traversing more than one hop #
Edge direction records which record held the reference, not which resource
depends on which. AWS writes containment child-to-parent: a subnet carries
vpc_id, a VPC carries nothing about its subnets. A walk that follows only
source -> target from a VPC therefore finds almost nothing, while the same walk
treating edges as undirected finds the whole neighbourhood.
docs/views.sql has optional destination-side views for this: one exposing every
edge in both directions with source_column preserved, and a deduplicated
adjacency view for the recursive join. They are views rather than plugin output
because the transform protocol maps one input schema to one output schema, so
this plugin cannot emit a second table.
Two things there are easy to get wrong by hand. The adjacency view must be
DISTINCT on the endpoint pair, because source_column is a key component, so
the same pair reached through two columns is two rows and a walk would traverse
it twice. The recursive term also wants an index on (a_table, a_cq_id), which
it hits once per frontier node.
Where the account and region come from #
source_account_id and source_region belong to the source record, and are the
fallback context a reference carrying none of its own is resolved against. They
say nothing about the target: a us-east-1 record can reference a resource in
eu-west-1, and the columns will still read us-east-1. source_region is null
for global services such as IAM and S3.
They are columns on every edge, not edges themselves. The container edges above
are what makes containment joinable: source_account_id is a string that has to
match aws_account_information.account_id by hand, while an account_reference
edge carries the account row's _cq_id and joins like anything else.
The source_ prefix is deliberate. Unqualified account_id and region invite
exactly the mistake of filtering targets by them, so queries written against the
unprefixed names will not work here.
Relationship to the raw tables #
The transform protocol maps one input schema to one output schema, so this
plugin replaces the records in the destination it is attached to. To keep the
raw AWS tables as well, point a second destination at the same database without
this transformer. The configuration page has an example.