In “How SQL can unify entry to APIs” I made the case for SQL as a standard atmosphere through which to motive about information flowing from many alternative APIs. The important thing enabler of that situation is Steampipe, a Postgres-based device with a rising suite of API plugins that map APIs to overseas tables in Postgres.
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 fundamental 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 will not be null; +---------------------+----------+--------------------+ | instance_id | ports | vulns | +---------------------+----------+--------------------+ | i-0dc60dd191cb84239 | null | null | | i-042a51a815773780d | [80,22] | null | | i-00cf426db9b8a58b6 |  | null | | i-0e97f373db42dfa3f | [22,111] | ["CVE-2018-15919"] | +---------------------+----------+--------------------+
Recordsdata are APIs too
However what’s an API, actually? Should it at all times entail HTTP requests to service endpoints? Extra broadly APIs are information sources that are available different flavors too. Net pages are sometimes, nonetheless, de facto APIs. I’ve carried out extra net scraping than I care to consider over time and the ability stays helpful.
Recordsdata are additionally information sources: configuration recordsdata (INI, YAML, JSON), infrastructure-as-code recordsdata (Terraform, CloudFormation), information recordsdata (CSV). When plugins for these sources started to hitch the combination, Steampipe grew to become much more highly effective.
First got here the CSV plugin, which unlocked all types of helpful queries. Think about, for instance, how we regularly faux spreadsheets are databases. In doing so we are able to assume there’s referential integrity when actually there isn’t. In case you export spreadsheet information to CSV, you need to use SQL to search out these flawed assumptions. And that’s simply one of many countless methods I can think about utilizing SQL to question the world’s main file format for information trade.
Then got here the Terraform plugin, which queries Terraform recordsdata to ask and reply questions like: “Which trails are usually not encrypted?”
choose title, 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 are able to ask and reply the identical query for deployed infrastructure, and return a end result set that you can UNION with the primary one.
choose title, 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 really deployed if you happen to question AWS APIs. In the true world, in fact, upkeep and/or incident response may end up in configuration drift. Given a standard technique to motive over outlined and deployed infrastructure, we are able to handle such drift programmatically.
Belt and suspenders
For deployed infrastucture, Steampipe has lengthy offered a set of mods that layer safety and compliance checks onto API-derived overseas tables. The AWS Compliance mod, for instance, supplies benchmarks and controls to examine deployed infrastructure towards eleven requirements and frameworks together with CIS, GDPR, HIPAA, NIST 800-53, and SOC 2.
With the appearance of the Terraform plugin it grew to become potential to create complementary mods, like Terraform AWS Compliance, that present the identical sorts of checks for outlined infrastructure.
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 standard and frictionless manner. SQL can’t take away all of the friction however it’s a strong solvent.
Copyright © 2022 IDG Communications, .