Does your deployed infrastructure match what you outlined?

In “How SQL can unify entry to APIs” I made the case for SQL as a typical setting wherein to motive about knowledge flowing from many various APIs. The important thing enabler of that state of affairs is Steampipe, a Postgres-based device with a rising suite of API plugins that map APIs to international tables in Postgres.

These APIs have been, initially, those supplied by AWS, Azure, and GCP. Such APIs are usually made extra accessible to builders by the use of wrappers like boto3. A standard SQL interface is arguably a greater unifier of the sprawling API ecosystems inside these clouds, and that’s inarguably true in multicloud eventualities. With Postgres beneath the hood, by the best way, you’re not restricted to SQL: You may hook Python or JavaScript or one other language to Postgres and leverage the widespread SQL interface from these languages too.

The Steampipe ecosystem then expanded with plugins for a lot of different companies together with GitHub, Google Workspace, IMAP, Jira, LDAP, Shodan, Slack, Stripe, and Zendesk. Becoming a member of throughout these APIs is a superpower greatest confirmed by this instance that joins Amazon EC2 endpoints with Shodan vulnerabilities in simply 10 traces of very primary SQL.

choose
  a.instance_id,
  s.ports
  s.vulns
from
  aws_ec2_instance a
left be part of
  shodan_host s on a.public_ip_address = s.ip
the place
  a.public_ip_address shouldn't be null;

+---------------------+----------+--------------------+
| instance_id         | ports    | vulns              |
+---------------------+----------+--------------------+
| i-0dc60dd191cb84239 | null     | null               |
| i-042a51a815773780d | [80,22]  | null               |
| i-00cf426db9b8a58b6 | [22]     | null               |
| i-0e97f373db42dfa3f | [22,111] | ["CVE-2018-15919"] |
+---------------------+----------+--------------------+

Information are APIs too

However what’s an API, actually? Should it at all times entail HTTP requests to service endpoints? Extra broadly APIs are knowledge sources that are available different flavors too. Net pages are sometimes, nonetheless, de facto APIs. I’ve finished extra internet scraping than I care to consider through the years and the ability stays helpful.

Information are additionally knowledge sources: configuration recordsdata (INI, YAML, JSON), infrastructure-as-code recordsdata (Terraform, CloudFormation), knowledge recordsdata (CSV). When plugins for these sources started to affix the combo, Steampipe turned much more highly effective.

First got here the CSV plugin, which unlocked all types of helpful queries. Contemplate, for instance, how we regularly fake spreadsheets are databases. In doing so we will assume there’s referential integrity when actually there isn’t. Should you export spreadsheet knowledge to CSV, you need to use SQL to discover these flawed assumptions. And that’s simply one of many limitless methods I can think about utilizing SQL to question the world’s main file format for knowledge trade.

Then got here the Terraform plugin, which queries Terraform recordsdata to ask and reply questions like: “Which trails aren’t encrypted?”

choose
  identify,
  path
from
  terraform_resource
the place
  kind="aws_cloudtrail"
  and arguments -> 'kms_key_id' is null;

Utilizing the AWS plugin’s aws_cloudtrail_trail desk, we will ask and reply the identical query for deployed infrastructure, and return a end result set that you could possibly UNION with the primary one.

choose
  identify,
  arn as path
from
  aws_cloudtrail_trail
the place
  kms_key_id is null;

Ideally the solutions will at all times be the identical. What you mentioned must be deployed, utilizing Terraform, ought to match what’s truly deployed when you question AWS APIs. In the true world, after all, upkeep and/or incident response may end up in configuration drift. Given a typical solution to motive over outlined and deployed infrastructure, we will handle such drift programmatically.

Belt and suspenders

For deployed infrastucture, Steampipe has lengthy supplied a collection of mods that layer safety and compliance checks onto API-derived international tables. The AWS Compliance mod, for instance, offers benchmarks and controls to verify deployed infrastructure in opposition to eleven requirements and frameworks together with CIS, GDPR, HIPAA, NIST 800-53, and SOC 2.

IDG

With the arrival of the Terraform plugin it turned potential to create complementary mods, like Terraform AWS Compliance, that present the identical sorts of checks for outlined infrastructure.

IDG

Does what you outlined final month match what you deployed yesterday? A passable reply requires the power to motive over outlined and deployed infrastructure in a typical and frictionless manner. SQL can’t take away all of the friction however it’s a strong solvent.