Skip to main content

7 posts tagged with "aws"

View All Tags

Auditing three clouds without writing three scripts

· 6 min read
Nirmal Chhodvadiya
Cloud Consultant and AWS Community Builder

If you've audited buckets in more than one cloud, you already know what this is like. Three consoles. Three CLIs. Three auth patterns. And a bit of glue code to pull the outputs together every time someone asks a question that spans all three.

The question is usually simple. "Which buckets are public across all our clouds?" You end up running three separate scripts, then reconciling three different output shapes just to give one answer.

This tutorial doesn't make that faster. It replaces it. One query, one view, all three clouds.

What StackQL is, in three sentences​

StackQL is SQL for cloud APIs. You write a normal SQL query, and it makes the API calls to AWS, GCP, Azure, or dozens of other providers to get the answer back as rows and columns.

The important part for this tutorial: it queries the live provider APIs on every run. There's no local database, no cache, no sync job to schedule. What you see is the current state of your cloud, right now.

It exists because someone got tired of writing the same audit logic three times.

What you'll need​

You'll need Docker installed and read-only credentials for whichever clouds you want to audit. All three are optional. If you leave a cloud's credentials blank, it gets skipped.

For AWS, GCP, and Azure, the audit needs a service account or role that can list storage buckets and read their configuration. The audit-action repo has the exact permissions per provider here.

Run the audit​

The tutorial folder has three files: a docker-compose file, an .env.audit.example template, and a README. Grab the folder from docs/tutorials/preview-bucket-01/ in the stackql/stackql repo.

1. Copy the env template

cp ./audit/.env.audit.example ./audit/.env.audit

Then open .env.audit and fill in credentials for whichever clouds you want. The variables are named for what they are: AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY, AWS_REGION, AZURE_TENANT_ID, AZURE_CLIENT_ID, AZURE_CLIENT_SECRET, GOOGLE_CREDENTIALS (the service account JSON, on one line), and GOOGLE_ORG_ID.

If you're only auditing one or two clouds, leave the others blank.

2. Pull the StackQL Docker image

docker compose -f docker-compose.bucket.audit.yaml pull

The tutorial pins to stackql/stackql:v0.10.601. The audit uses views under stackql_preview.* which are still evolving, so pinning matters for reproducibility.

3. Run the audit

docker compose -f docker-compose.bucket.audit.yaml run --rm stackql

On a modest account this takes about 30 seconds. Output goes straight to your terminal as a table.

What the output tells you​

When the audit finishes, you get a single table printed to your terminal. Something like this (bucket names are placeholders):

Terminal screenshot showing StackQL audit output as a table with columns for provider, bucket name, encryption class, public flag, and HTTPS enforcement. Rows include AWS, GCP, and Azure buckets with a mix of provider-managed and customer-managed encryption, and one public bucket flagged per AWS and GCP.

Example output from the audit. Bucket configuration across three cloud providers in one normalised table.

The point isn't the specific findings. It's the shape.

Three different resource types (S3 buckets, GCS buckets, and Azure Storage Accounts) normalised into one table with the same columns. Encryption class, public flag, HTTPS enforcement. Side by side, for every cloud you provided credentials for.

A few patterns to notice in the table:

  • Public buckets across providers. Filter by public = true and you have the answer to your security team's question, in every cloud, in one place.
  • Encryption side by side. The encryption_class column shows which buckets use provider-managed keys versus customer-managed. Different providers have different naming conventions internally; StackQL normalises them to the same two categories.
  • HTTPS enforcement in one column. Whether it's S3 bucket policies, GCS uniform access, or Azure secure transfer, same column, same values, no translation needed.

This is what SQL for cloud APIs actually delivers. Not just SQL as a query language, but a uniform data model across providers, so you can ask one question and get one answer.

Performance and scope​

StackQL runs each provider's query in parallel using the credentials you provided. There's no local database being populated and no background sync; every run hits the live APIs and returns what's there right now.

On the test account this took about 30 seconds. The query is scoped to one AWS region and one GCP organization, which keeps the API footprint bounded and reflects the current preview default. Streaming output and broader audits (across regions, deeper across resource types) are in flight from the StackQL team.

What this covers today​

Today, this audit covers storage buckets across AWS, GCP, and Azure. One region at a time for AWS, one organization at a time for GCP. Entitlements, IAM, and other resource types are on the roadmap as separate audits under stackql_preview.* and will get their own tutorials as they land.

Where to go next​

github.com/stackql/stackql is the flagship repo. If this tutorial was worth reading, give it a star. It's genuinely the thing that tells the team to build more tutorials like this one, and it helps other engineers with the same three-console problem find the project.

A few natural next steps from here:

  • Run it against your own accounts. The audit works exactly the same way against real credentials. You can start with one cloud and add the others by adding their env vars.
  • Move it into CI. The stackql-audit-action repo has ready-to-use GitHub Actions workflows for all-clouds, single-cloud, deep-audit, and OIDC-authenticated variants. Drop one into your repo, add secrets, and you have a scheduled cross-cloud audit running on every push.
  • Ask questions. The StackQL community Slack is the fastest way. GitHub issues on the flagship repo also work.

More audits are on the way, including entitlements, deeper checks, and larger estates. If there's a specific one you'd find useful, opening an issue is the fastest way to get it on the roadmap.

(Quickly) Identify Old Node Runtimes in AWS Lambda

· 3 min read
Jeffrey Aven
Technologist and Cloud Consultant

Have you been sent one of these?

[Action Required] AWS Lambda end of support for Node.js 18 [AWS Account: 824123456789] [EU-CENTRAL-1]

If you are like me and manage AWS accounts with numerous Lambda functions potentially deployed across multiple regions, you need to identify affected resources, in this case, Lambda node runtimes, which will be discontinued later this year.  

With stackql this task is easy...

  1. Open AWS cloud shell in your AWS account (any region - it doesn't matter)
  2. Download stackql
curl -L https://bit.ly/stackql-zip -O && unzip stackql-zip
  1. Open an authenticated stackql command shell
sh stackql-aws-cloud-shell.sh
  1. Run some analytic queries using stackql; here are some examples...

🔍 List all functions and runtimes across regions​

Run a stackql query to get the details about functions, runtimes, etc, deployed at any given time across one or more AWS regions.  You can include all 25 AWS regions; each query will be performed asynchronously - speeding up the results.

select 
  function_name, 
  region,
  runtime 
FROM aws.lambda.functions 
WHERE region IN ('us-east-1', 'eu-west-1');

📊 Group by runtime and region​

Perform an analytic query like a group by aggregate query such as...

select 
  runtime, 
  region, 
  count(*) as num_functions 
FROM aws.lambda.functions 
WHERE region IN ('us-east-1', 'eu-west-1', 'ap-southeast-2')
GROUP BY runtime, region;
tip

You can easily visualise this data using a notebook; see stackql-codespaces-notebook or stackql-jupyter-demo.

Using StackQL you can:

  • Quickly spot functions running on runtimes like nodejs18.x that are approaching end of support.
  • Plan your upgrades region-by-region with confidence.

⭐ us on GitHub and join our community!

Delete Default AWS VPCs with StackQL

· 6 min read
Kieran Rimmer
Technologist and Cloud Consultant

AWS creates default VPCs in each region for convenience. However, these default VPCs often contain noncompliant network ACLs and security group rules that do not align with best practices for AWS Config and Security Hub. Deleting these default VPCs is beneficial, especially for regions not used by your organization or architectures that do not utilize a VPC.  

This guide demonstrates how to use StackQL to enumerate and delete all default VPCs and their associated resources in all AWS regions.  

What you need​

All you need to do is to install the pystackql package using:

pip install pystackql

Deleting resources will require a privileged AWS IAM user. You can do this from a terminal with the AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY environment variables set (and optionally AWS_SESSION_TOKEN set if you are using sts assume-role).  

You can also use the StackQL Cloud Shell Scripts, from within AWS Cloud Shell, to run authenticated StackQL queries using:

sh stackql-aws-cloud-shell.sh

How it works​

This example uses the awscc (AWS Cloud Control) provider. Cloud Control resources are read in two steps: a _list_only view returns the resource identifiers in a region, and the resource view returns the full properties for a single resource selected by its Identifier.

Default VPCs in AWS regions have a CIDR block of 172.31.0.0/16, before deleting anything, the program ensures it is not in use by listing the network interfaces in the region and checking whether any belong to the VPC:

SELECT id
FROM awscc.ec2.network_interfaces_list_only
WHERE region = '{region}'

If any resources are using the VPC (such as EC2, RDS, ELB/ALB, VPC attached Lambda functions, etc), it will skip these VPCs.  Once determined that the VPC is not in use, all of the resources associated with the VPC must be deleted before the VPC itself can be deleted; this includes:

  • External Routes (awscc.ec2.routes)
  • Internet Gateways (awscc.ec2.internet_gateways)
  • NACLs (awscc.ec2.network_acls)
  • Subnets (awscc.ec2.subnets)
  • and then finally, the VPC (awscc.ec2.vpcs)

The default route table and default security group are deleted automatically when deleting the VPC

This is done by discovering the resources using StackQL SELECT queries and deleting them using StackQL DELETE queries, like:

DELETE FROM awscc.ec2.network_acls
WHERE data__Identifier = '{nacl_id}'
AND region = '{region}'

Complete code​

The following Python program uses StackQL to list and delete default VPCs and their associated resources (subnets, route tables, internet gateways, security groups, and network ACLs) across all AWS regions.

Get the complete code here
from pystackql import StackQL
stackql = StackQL()
stackql.executeStmt("REGISTRY PULL awscc")

def ensure_one_or_zero(resource_list, resource_name, region):
    if len(resource_list) > 1:
        raise RuntimeError(f"ah snap! multiple default {resource_name} resources found in {region}")
    elif len(resource_list) == 0:
        print(f"/* no default {resource_name} found in {region} */\n")
        return False
    return True

def get_resource(region, resource, identifier):
    # fetch the full properties of a single Cloud Control resource by its identifier
    rows = stackql.execute(f"""
        SELECT *
        FROM awscc.ec2.{resource}
        WHERE region = '{region}'
        AND Identifier = '{identifier}'
    """)
    return rows[0] if len(rows) else None

def find_in_vpc(region, resource, id_col, vpc_id):
    # list all identifiers for a resource in a region, then keep those attached to the target VPC
    matches = []
    for row in stackql.execute(f"SELECT {id_col} FROM awscc.ec2.{resource}_list_only WHERE region = '{region}'"):
        detail = get_resource(region, resource, row[id_col])
        if detail and detail.get('vpc_id') == vpc_id:
            matches.append(detail)
    return matches

regions = [
    'us-east-1', 'us-east-2', 'us-west-1', 'us-west-2',
    'eu-central-1', 'eu-central-2', 'eu-west-1', 'eu-west-2', 'eu-west-3', 'eu-north-1', 'eu-south-1',
    'ap-east-1', 'ap-south-1', 'ap-south-2', 'ap-northeast-1', 'ap-northeast-2', 'ap-northeast-3',
    'ap-southeast-1', 'ap-southeast-2', 'ap-southeast-3', 'ap-southeast-4', 'af-south-1',
    'ca-central-1',  'me-south-1', 'me-central-1', 'sa-east-1'
]

for region in regions:
    # find the default VPC (CIDR 172.31.0.0/16, untagged) by listing VPCs then inspecting each
    default_vpcs = []
    for row in stackql.execute(f"SELECT vpc_id FROM awscc.ec2.vpcs_list_only WHERE region = '{region}'"):
        vpc = get_resource(region, 'vpcs', row['vpc_id'])
        if vpc and vpc.get('cidr_block') == '172.31.0.0/16' and not vpc.get('tags'):
            default_vpcs.append(vpc)
    if not ensure_one_or_zero(default_vpcs, 'VPC', region): continue
    vpc_id = default_vpcs[0]['vpc_id']

    # check if the VPC is in use
    network_interfaces = find_in_vpc(region, 'network_interfaces', 'id', vpc_id)
    if len(network_interfaces) > 0:
        print(f"/* skipping deletion of default VPC ({vpc_id}) in {region} because it is in use */\n")
        continue

    print(f"/* deleting resources for default VPC ({vpc_id}) in {region} */\n")

    # get the default route table for the VPC
    route_tables = find_in_vpc(region, 'route_tables', 'route_table_id', vpc_id)
    ensure_one_or_zero(route_tables, 'route table', region)
    route_table_id = route_tables[0]['route_table_id']

    # get the internet gateway from the default (0.0.0.0/0) route
    default_route = get_resource(region, 'routes', f'{route_table_id}|0.0.0.0/0')
    inet_gateway_id = default_route['gateway_id'] if default_route else None

    # delete the default route
    print(f"/* deleting default VPC routes in route table ({route_table_id}) in {region} */")
    print(f"""
DELETE FROM awscc.ec2.routes
WHERE data__Identifier = '{route_table_id}|0.0.0.0/0'
AND region = '{region}';
    """)

    # detach the internet gateway
    print(f"/* detaching default VPC internet gateway ({inet_gateway_id}) in {region} */")
    print(f"""
DELETE FROM awscc.ec2.vpc_gateway_attachments
WHERE data__Identifier = 'IGW|{vpc_id}'
AND region = '{region}';
    """)

    # delete the internet gateway
    print(f"/* deleting default VPC internet gateway ({inet_gateway_id}) in {region} */")
    print(f"""
DELETE FROM awscc.ec2.internet_gateways
WHERE data__Identifier = '{inet_gateway_id}'
AND region = '{region}';
    """)

    # delete the network ACL
    nacls = find_in_vpc(region, 'network_acls', 'id', vpc_id)
    ensure_one_or_zero(nacls, 'network acl', region)
    nacl_id = nacls[0]['id']
    print(f"/* deleting default VPC NACL ({nacl_id}) in {region} */")
    print(f"""
DELETE FROM awscc.ec2.network_acls
WHERE data__Identifier = '{nacl_id}'
AND region = '{region}';
    """)

    # delete the subnets
    subnets = find_in_vpc(region, 'subnets', 'subnet_id', vpc_id)
    for subnet in subnets:
        print(f"/* deleting default VPC subnet ({subnet['subnet_id']}) in {region} */")
        print(f"""
DELETE FROM awscc.ec2.subnets
WHERE data__Identifier = '{subnet['subnet_id']}'
AND region = '{region}';
        """)

    # delete the VPC
    print(f"/* deleting default VPC ({vpc_id}) in {region} */")
    print(f"""
DELETE FROM awscc.ec2.vpcs
WHERE data__Identifier = '{vpc_id}'
AND region = '{region}';
    """)

run the program using:

python3 delete-all-default-vpcs.py > delete_default_vpcs.iql

to generate a StackQL script you can run as a batch using stackql exec or as individual statements in the stackql shell.

Conclusion​

Deleting default VPCs can help improve your AWS security posture by removing potentially noncompliant network configurations. This program leverages StackQL to automate the process, ensuring consistency across all regions.

Let us know what you think! ⭐ us on GitHub.

The 5 Minute Global AWS Inventory

· One min read
Jeffrey Aven
Technologist and Cloud Consultant

StackQL allows you to query and interact with your cloud and SaaS assets using a simple SQL framework. Use cases include CSPM, asset inventory and analysis, finops and more, as well as IaC and sysops (lifecycle management).

Using stackql and the aws provider (AWS Cloud Control provider for stackql), here's how you can query your entire AWS estate in real time (globally) and generate a simple report like this...

aws-inventory-example

Check out the code at AWS Global Inventory!

Visit us and give us a ⭐ on GitHub

Using StackQL in Native Cloud Shells in AWS, Azure and GCP

· 4 min read
Jeffrey Aven
Technologist and Cloud Consultant

StackQL allows you to query and interact with your cloud and SaaS assets using a simple SQL framework. Use cases include CSPM, asset inventory and analysis, finops and more, as well as our IaC and ops (lifecycle management).

The three major cloud providers all offer a built-in Linux shell for executing commands using their respective CLIs; in some cases, they come with tools like terraform pre-installed. They are pre-authorized with your credentials in the cloud console for the user you authenticated with.

Now you can easily use stackql - a unified analytics and IaC dev tool - in all major cloud providers' built-in shells, using cloud shell scripts packaged with the stackql Linux binary (available from v0.5.587 onwards).

StackQL is particularly useful for asynchronously querying across regions in AWS, projects in Google, or resource groups in Azure, which is challenging to do via the CLIs. For example:

SELECT region, COUNT(*) as num_functions
FROM aws.lambda.functions
WHERE region IN (
	'us-east-1','us-east-2','us-west-1','us-west-2',
	'ap-south-1','ap-northeast-3','ap-northeast-2',
	'ap-southeast-1','ap-southeast-2','ap-northeast-1',
	'ca-central-1','eu-central-1','eu-west-1',
	'eu-west-2','eu-west-3','eu-north-1','sa-east-1')
GROUP BY region;

Additionally, you could authenticate to another provider from one cloud shell simultaneously and run multi-cloud inventory commands. For example:

SELECT 
  name, 
  SPLIT_PART(machineType, '/', -1) as instance_type,
  'google' as provider
  FROM google.compute.instances 
  WHERE project IN ('myproject1','myproject2')
UNION
SELECT
  instanceId as name,
  instanceType as instance_type,
  'aws' as provider
  FROM aws.ec2.instances 
  WHERE region IN (
	'us-east-1','us-east-2','us-west-1','us-west-2',
	'ap-south-1','ap-northeast-3','ap-northeast-2',
	'ap-southeast-1','ap-southeast-2','ap-northeast-1',
	'ca-central-1','eu-central-1','eu-west-1',
	'eu-west-2','eu-west-3','eu-north-1','sa-east-1');

Getting Started​

To get started with StackQL in your preferred cloud shell environment, download the StackQL package using the following command:

curl -L https://bit.ly/stackql-zip -O \
&& unzip stackql-zip

This command downloads the StackQL package, unzips it, and sets the appropriate permissions. From there, you can use our tailored scripts for AWS, Google Cloud, or Azure to integrate StackQL seamlessly into your cloud shell environment.

Using StackQL in the AWS Cloud Shell​

Run the stackql-aws-cloud-shell.sh as follows to use the StackQL command shell within the AWS cloud shell:

sh stackql-aws-cloud-shell.sh

An example is shown here:

aws-cloud-shell-example

You can also run stackql exec commands using the stackql-aws-cloud-shell.sh script; for instance, this command will write a CSV file for the results of a query that could be downloaded from the Cloud Shell.

sh stackql-aws-cloud-shell.sh exec \
--output csv --outfile instances.csv \
"SELECT region, instanceType FROM aws.ec2.instances WHERE region IN ('us-east-1')"

Additionally, you can supply an IAM role using the --role-arn argument to assume another role for your query or mutation operation, an example is shown here:

sh stackql-aws-cloud-shell.sh \
--role-arn arn:aws:iam::824532806693:role/SecurityReviewerRole exec \
--infile query.iql \
--output csv --outfile output.csv

Using StackQL in the Azure Cloud Shell​

Run the stackql-azure-cloud-shell.sh as follows to open a StackQL command shell from the Azure Cloud Shell:

sh stackql-azure-cloud-shell.sh

An example is shown here:

azure-cloud-shell-example

Similar to the AWS script, you can also invoke stackql exec as well, an example is shown here:

sh stackql-azure-cloud-shell.sh exec \
--output csv --outfile instances_by_location.csv \
"SELECT location, COUNT(*) as num_instances FROM azure.compute.virtual_machines WHERE resource_group_name =  'stackql-ops-cicd-dev-01' AND subscription_id = '631d1c6d-2a65-43e7-93c2-688bfe4e1468' GROUP BY location"

Using StackQL in the Google Cloud Shell​

Run the stackql-google-cloud-shell.sh as shown below to launch a StackQL command shell from within the google cloud shell:

sh stackql-google-cloud-shell.sh

An example is shown here:

google-cloud-shell-example

As with the other two providers, you can run exec commands following the example below:

sh stackql-google-cloud-shell.sh exec \
--output csv --outfile instances.csv \
"SELECT name, status FROM google.compute.instances WHERE project = 'stackql-demo'"

Please give us your feedback! Star us at github.com/stackql.