7 posts tagged with "aws"

registry pull aws;

Happy New Year 🎉. The latest AWS provider for StackQL is now available.  The StackQL AWS Provider by the numbers:

• 230 services
• 3174 resources
• 3917 methods

with additional new support for the following services:

• amazonmq - Managed message broker service for Apache ActiveMQ and RabbitMQ that simplifies setup and operation of open-source message brokers on AWS.
• applicationsignals - CloudWatch Application Signals automatically provides a correlated view of application performance that includes real user monitoring data and canaries.
• apptest - AWS mainframe modernization ppplication Testing
• connectcampaignsv2 - Amazon Connect Outbound Campaigns V2
• invoicing - Deploy and query invoice units allowing you separate AWS account costs and configures your invoice for each business entity
• launchwizard - Easily size, configure, and deploy third party applications on AWS
• pcaconnectorscep - AWS Private CA Connector for SCEP
• pcs - AWS Parallel Computing Service, easily run HPC workloads at virtually any scale
• rbin - Recycle Bin is a resource recovery feature that enables you to restore accidentally deleted snapshots and EBS-backed AMIs.
• s3tables - Amazon S3 Tables enabling Tabular Data Storage At Scale
• ssmquicksetup - AWS Systems Manager Quick Setup

And 150 new resources with some notable additions including:

• aws.apigateway.domain_name_access_associations
• aws.appconfig.deployments, aws.appconfig.deployment_strategies
• aws.batch.job_definitions
• aws.bedrock.flows, aws.bedrock.prompts
• aws.chatbot.custom_actions
• aws.cloudformation.guard_hooks, aws.cloudformation.lambda_hooks
• aws.cloudfront.anycast_ip_lists
• aws.cloudtrail.dashboards, aws.cloudwatch.dashboards
• aws.codepipeline.pipelines
• aws.cognito.user_pool_identity_providers
• aws.ec2.security_group_vpc_associations, aws.ec2.vpc_block_public_access_exclusions, aws.ec2.vpc_block_public_access_options
• aws.glue.crawlers, aws.glue.databases, aws.glue.jobs, aws.glue.triggers
• aws.guardduty.malware_protection_plans
• aws.iot.commands
• aws.memorydb.multi_region_clusters
• aws.rds.db_shard_groups
• aws.redshift.integrations
• aws.sagemaker.clusters, aws.sagemaker.endpoints
• aws.secretsmanager.resource_policies, aws.secretsmanager.rotation_schedules, aws.secretsmanager.secret_target_attachments
• aws.workspaces.workspaces_pools
• aws.wisdom.ai_agents, aws.wisdom.ai_prompts, aws.wisdom.ai_guardrails, aws.wisdom.message_templates
• and much more!

⭐ us on GitHub and join our community!

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​

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 using:

SELECT
id
FROM aws.ec2.network_interfaces
WHERE region = '{region}'
AND vpc_id = '{vpc_id}'

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 (aws.ec2.routes)
• Internet Gateways (aws.ec2.internet_gateways)
• NACLs (aws.ec2.network_acls)
• Subnets (aws.ec2.subnets)
• and then finally, the VPC (aws.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 aws.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.

from pystackql import StackQL
stackql = StackQL()
stackql.executeStmt("REGISTRY PULL aws")

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

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:
    vpc_ids = stackql.execute(
        f"""
        SELECT
        vpc_id
        FROM aws.ec2.vpcs
        WHERE region = '{region}'
        AND cidr_block = '172.31.0.0/16'
        AND JSON_ARRAY_LENGTH(tags) = 0
        """
    )
    if not ensure_one_or_zero(vpc_ids, 'VPC', region): continue 
    vpc_id = vpc_ids[0]['vpc_id']
    
    # check if vpc is in use
    network_interfaces = stackql.execute(
        f"""
        SELECT
        id
        FROM aws.ec2.network_interfaces
        WHERE region = '{region}'
        AND vpc_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 route table
    route_table_ids = stackql.execute(
        f"""
        SELECT
        route_table_id
        FROM aws.ec2.route_tables
        WHERE region = '{region}'
        AND vpc_id = '{vpc_id}'
        """
    )
    ensure_one_or_zero(route_table_ids, 'route table', region)
    route_table_id = route_table_ids[0]['route_table_id']

    # get inet gateway id
    inet_gateway_ids = stackql.execute(
        f"""
        SELECT gateway_id 
        FROM aws.ec2.routes 
        WHERE data__Identifier = '{route_table_id}|0.0.0.0/0' 
        AND region = '{region}'
        """
    )
    ensure_one_or_zero(inet_gateway_ids, 'internet gateway', region)
    inet_gateway_id = inet_gateway_ids[0]['gateway_id']

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

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

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

    # delete nacl
    nacl_ids = stackql.execute(
        f"""
        SELECT
        id
        FROM aws.ec2.network_acls
        WHERE vpc_id = '{vpc_id}'
        AND region = '{region}'
        """
    )
    ensure_one_or_zero(nacl_ids, 'network acl', region)
    nacl_id = nacl_ids[0]['id']
    print(f"/* deleting default VPC NACL ({nacl_id}) in {region} */")
    print(f"""
DELETE FROM aws.ec2.network_acls
WHERE data__Identifier = '{nacl_id}'
AND region = '{region}';
    """)

    # delete subnets
    subnet_ids = stackql.execute(
        f"""
        SELECT
        subnet_id
        FROM aws.ec2.subnets
        WHERE vpc_id = '{vpc_id}'
        AND region = '{region}'
        """
    )
    for subnet_id in subnet_ids:
        print(f"/* deleting default VPC subnet ({subnet_id['subnet_id']}) in {region} */")
        print(f"""
DELETE FROM aws.ec2.subnets
WHERE data__Identifier = '{subnet_id['subnet_id']}'
AND region = '{region}';
        """)

    # delete vpc
    print(f"/* deleting default VPC ({vpc_id}) in {region} */")
    print(f"""
DELETE FROM aws.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.

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...

Check out the code at AWS Global Inventory!

Visit us and give us a ⭐ on GitHub

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:

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:

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 resourceGroupName =  'stackql-ops-cicd-dev-01' AND subscriptionId = '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:

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.

Most AWS services and resources are regionally scoped, meaning the UI, CLI, SDKs, and all other methods of querying the aws provider give you a regional view (us-east-1 or ap-southeast-2, for instance). Many customer AWS estates span multiple regions - for multinational organizations, for example, or organizations with numerous dispersed locations within the US.

Sure, you could write custom scripts wrapping the CLI or SDKs - which would require development effort (not reusable for other providers); or get an abstract view with tools like AWS Config or Systems Manager, which requires these services to be enabled and configured (not flexible and not extendible to other providers). In either case:

1. You can't write and run customized queries and generate custom reports - as you can do in SQL
2. Any solutions you build will have to be rebuilt entirely for other providers

Using the latest (AWS provider for StackQL - which leverages the AWS Cloud Control API) and the executeQueriesAsync method in the pystackql Python package, I've put together an example here which runs a query to bring back attributes from all AWS Lambda functions deployed across 17 different AWS regions asynchronously. Results can be returned as a list of Python dictionaries or a Pandas dataframe. I am doing the former here, which took less than 10s.

from pystackql import StackQL
from pprint import pprint
from asyncio import run
stackql = StackQL()
stackql.executeStmt("REGISTRY PULL aws") # not required if the aws provider is already installed

async def stackql_async_queries(queries):
    return await stackql.executeQueriesAsync(queries)

regions= ["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"]

# list functions from all regions asynchronously
get_fns = [
    f"""
    SELECT *
    FROM aws.lambda.functions
    WHERE region = '{region}'
    """
    for region in regions
]

functions = run(stackql_async_queries(get_fns))

# get function details for all functions across all regions asynchronously
get_fn_details = [
    f"""
    SELECT 
    function_name,
    region,
    arn,
    description,
    architectures,
    memory_size,
    runtime
    FROM aws.lambda.function
    WHERE region = '{function['region']}'
    AND data__Identifier = '{function['function_name']}'
    """
    for function in functions
]

function_details = run(stackql_async_queries(get_fn_details))
pprint(function_details)

which returns...

[{'architectures': '["x86_64"]',
  'arn': 'arn:aws:lambda:us-east-1:824532806693:function:stackql-helloworld-fn',
  'description': '',
  'function_name': 'stackql-helloworld-fn',
  'memory_size': '128',
  'region': 'us-east-1',
  'runtime': 'nodejs18.x'},
 {'architectures': '["x86_64"]',
  'arn': 'arn:aws:lambda:us-east-2:824532806693:function:stackql-helloworld-fn',
  'description': '',
  'function_name': 'stackql-helloworld-fn',
  'memory_size': '128',
  'region': 'us-east-2',
  'runtime': 'nodejs18.x'},
 {'architectures': '["x86_64"]',
  'arn': 'arn:aws:lambda:us-west-1:824532806693:function:stackql-helloworld-fn',
  'description': '',
  'function_name': 'stackql-helloworld-fn',
  'memory_size': '128',
  'region': 'us-west-1',
  'runtime': 'nodejs18.x'},
...

You could customize the StackQL query to run specific reports and visualize the results in a Jupyter notebook, for example:

• Functions by runtimes
• Function by memory size
• Functions by tags
• etc...

You could do something similar for other hyperscalars, for example, GCP, which scopes resources by projects, or Azure, which scopes resources by resource groups.

Let us know your thoughts! Visit us and give us a ⭐ on GitHub