DEV Community: IaSQL

Why SQL is right for Infrastructure Management

Luis Fernando De Pombo — Thu, 06 Apr 2023 21:15:23 +0000

Infrastructure is the heart of your company. Without it, nothing can actually be done and there'd be no reason for customers to pay you. For software companies that infrastructure is the software that its engineers directly write, and usually the cloud infrastructure and services it runs on top of and integrates with.

In this post, we will geek out on various software abstractions and data structures, tools used by professionals of various sorts, and dig into the pros and cons of these with respect to cloud infrastructure management in particular. We'll see that while SQL has its own warts, it is the "least worst" of all of the options out there.

Following these instructions is imperative

The simplest way to define how to set something up is to write up a list of instructions to follow, in order, to build whatever infrastructure you're dealing with, whether its the instructions on how to build a restaurant or an AWS Fargate cluster. This list of steps to process (with a LISt Processor, or LISP, if you will 😉), which can include instructions to repeat or skip over steps based on conditions you have run into, is called imperative programming in the software world.

Imperative Restaurant Instructions

Design restaurant.
Flatten the ground.
Prop up pieces of wood next to each other.
Bang nails into wood.
Is restaurant finished? No, go to 3.

For your cloud infrastructure, this is similar to using the AWS SDK and directly calling the various methods, checking their results and eventually arriving at the desired infrastructure. It is also like following a step-by-step guide clicking through the various AWS console UI elements to set up your infrastructure, like in this guide. The latter may not be automated, to you, but to your company executives it is as they don't care how the infrastructure building was accomplished as long as it was done quickly, cheaply, and reliably, and you know they always want all three. 😉

Imperative infrastructure management works well for initial setup of new infrastructure. There's nothing there to interfere with it, so you can just write the "happy path" and get it working. And if that fails, you can just tear it all down and start all over again if that's cheap, which it is for cloud infrastructure (though not for building a restaurant), so the cyclomatic complexity of the code you write is low and everyone can follow along with it. For example, one can use the aws cli application inside of a bash script to create a new ssh keypair in AWS, a security group enabling ssh access, a new EC2 instance associated with both, and then access that instance (to demonstrate that it works).

#!/bin/bash

aws ec2 create-key-pair \
  --region us-west-2 \
  --key-name login \
  --key-type ed25519 | \
  jq -r .KeyMaterial > login.pem
chmod 600 login.pem
SG_ID=$(aws ec2 create-security-group \
  --group-name login-sg \
  --description "Login security group" \
  --region us-west-2 \
  --vpc-id $(aws ec2 describe-vpcs \
    --region us-west-2 | \
    jq -r '.Vpcs[] | select(.IsDefault = true) | .VpcId') | \
  jq -r .GroupId)
aws ec2 authorize-security-group-ingress \
  --group-name login-sg \
  --region us-west-2 \
  --ip-permissions 'IpProtocol=tcp,FromPort=22,ToPort=22,IpRanges=[{CidrIp=0.0.0.0/0}]' > /dev/null
INSTANCE_ID=$(aws ec2 run-instances \
  --region us-west-2 \
  --subnet-id $(aws ec2 describe-subnets \
    --region us-west-2 | \
    jq -r .Subnets[0].SubnetId) \
  --instance-type t3.small \
  --image-id resolve:ssm:/aws/service/canonical/ubuntu/server/20.04/stable/current/amd64/hvm/ebs-gp2/ami-id \
  --security-group-ids ${SG_ID} \
  --key-name login \
  --associate-public-ip-address \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=name,Value=login-inst}]' \
  --output json | \
  jq -r .Instances[0].InstanceId)
echo EC2 instance ${INSTANCE_ID} starting 
aws ec2 wait instance-status-ok \
  --region us-west-2 \
  --instance-ids ${INSTANCE_ID}
PUBLIC_IP=$(aws ec2 describe-instances \
  --region us-west-2 \
  --filters Name=tag:name,Values=[login-inst] \
  --filters Name=instance-state-name,Values=[running] | \
  jq -r .Reservations[0].Instances[0].PublicIpAddress)
echo Server accessible at ${PUBLIC_IP}
ssh -i login.pem -o "StrictHostKeyChecking no" ubuntu@${PUBLIC_IP} uname -a

Keep in mind that this is a simple script that does not handle failure of any of the commands called, creating one EC2 instance with two supporting elements (a security group to allow access to the SSH port and an SSH keypair to log into the instance). If you intend to actually use it multiple times, several of the arguments currently hardwired should be turned into shell arguments themselves, and all of the error paths should be tackled.

However, the vast majority of the time you are not creating clones of the same infrastructure over and over again, instead you need to make changes to your existing infrastructure, and that original code is more than likely useless to you. To get from the current state of your infrastructure to your desired state, you need to call different APIs than you did before, and its much riskier to make a mistake because this infrastructure is already in use.

Declare your intentions at once

When the desired state is relatively simple to define and the mechanism to reach that state is not that important, writing up a declaration of what is needed and letting something/someone else deal with it is the most logical abstraction. This would be like drafting up the architectural draft for your new restaurant and paying a contracting company to actually build it, or writing HTML and letting a web browser render it, or writing a Terraform HCL file and letting the Terraform CLI tool apply it. This is called declarative programming in the software world, and has many advantages (and a few disadvantages!) for cloud infrastructure management.

Declarative Restaurant Instructions

By decree of the king, a restaurant shall be built!

In declarative programming you have some initial state and in comparatively dense and high-level declarative code define the desired state. In many use-cases (like web browsers and sometimes for restaurant-building contractors) the initial state is "blank" and the engine that transforms the declarative code into imperative operations can often be a relatively straightforward interpreter walking the AST of the declarative code.

Infrastructure management generally is not that straightforward. Changes to infrastructure require in-place mutations of the current state or a full blue/green deployment of the entirety of your production infrastructure is very expensive, requiring all resource costs to be doubled, and deployments to require some amount of downtime for users to swap over and state to be resynchronized. Particularly for databases this approach is essentially impossible as there is too much state to swap, which is what makes database migrations tricky when dropping data.

So declarative programming for infrastructure, also known as Infrastructure as Code, must take the existing state and generate the imperative operations to perform based on the differences between the existing state and the declared desired state. This is like a contracting company being given an architecture draft for a new restaurant and being told to remodel an existing commercial space (maybe it was also a restaurant, maybe it was a clothing store, etc) and the contracting company figuring out what needs to be torn down first, what can be re-used, and what needs to be built new.

terraform {
  required_providers {
    aws = {
      source = "hashicorp/aws"
      version = "4.60.0"
    }
  }
}

data "external" "example" {
  program = [
    "bash",
    "-c",
    <<-EOT
      if [ ! -f ./login.pem ]; then
        # Terraform doesn't support creating the PEM file via AWS. It has to be
        # created locally and then imported into AWS. Inside of a conditional so
        # we don't accidentally re-create the file between 'plan' and 'apply'
        yes '' | ssh-keygen -t ed25519 -m PEM -f login.pem
      fi
      chmod 600 login.pem
      # The output of this script needs to be JSON formatted. This is a bit wonky
      # but building it over time via 'jq' operators is actually harder to follow
      echo {
      echo   '"access_key_id": "'$(aws configure get aws_access_key_id)'",'
      echo   '"secret_access_key": "'$(aws configure get aws_secret_access_key)'",'
      echo   '"public_key": "'$(cat login.pem.pub)'"'
      echo }
    EOT
  ]
}

provider "aws" {
  region = "us-west-2"
  access_key = data.external.example.result["access_key_id"]
  secret_key = data.external.example.result["secret_access_key"]
}

resource "aws_key_pair" "login" {
  key_name = "login"
  public_key = data.external.example.result["public_key"]
}

resource "aws_security_group" "login_sg" {
  name = "login-sg"
  description = "Login security group"
  ingress {
    from_port = 22
    to_port = 22
    protocol = "tcp"
    cidr_blocks = ["0.0.0.0/0"]
  }
}

resource "aws_instance" "login" {
  ami = "resolve:ssm:/aws/service/canonical/ubuntu/server/20.04/stable/current/amd64/hvm/ebs-gp2/ami-id"
  instance_type = "t3.small"
  associate_public_ip_address = true
  key_name = aws_key_pair.login.key_name
  vpc_security_group_ids = [aws_security_group.login_sg.id]
  tags = {
    name = "login-inst"
  }
}

This example still requires the following lines from the imperative example to get the public IP address of the new EC2 instance and log into it:

PUBLIC_IP=$(aws ec2 describe-instances \
  --region us-west-2 \
  --filters Name=tag:name,Values=[login-inst] \
  --filters Name=instance-state-name,Values=[running] | \
  jq -r .Reservations[0].Instances[0].PublicIpAddress)
echo Server accessible at ${PUBLIC_IP}
ssh -i login.pem -o "StrictHostKeyChecking no" ubuntu@${PUBLIC_IP} uname -a

This diffing of original and desired state can be resolved several ways. The contracting company could spend a lot of time inspecting the existing commercial space to find absolutely every piece that can be re-used, removing them and setting them aside with the materials that need to be purchased, then rework the interior walls, and build everything from scratch, or it could decide to tear everything out back into an empty space and rebuild from scratch (like what your web browser does between web pages), or perhaps you need to keep the space mostly usable for customers, minimizing disruption to the current operations while things are changed. This last case is what infrastructure management tools need to tackle your infrastructure with the least amount of downtime or no downtime at all, if you're careful with it.

How can you "careful" with declarative programming tools, if you don't have direct control over what they do? Terraform does this with a plan command, which performs the diffing of current and desired state and reports back to you the operations it expects to execute to do so. Terraform will tell you when a change it intends to make is going to provision new resources, alter existing resources, drop resources, and replace resources. That last one is particularly annoying when it shows up because it means the change you want to perform can only be done by dropping it and recreating it with the new configuration.

$ terraform plan

data.external.example: Reading...
data.external.example: Read complete after 1s [id=-]

Terraform used the selected providers to generate the following execution
plan. Resource actions are indicated with the following symbols:
  + create

Terraform will perform the following actions:

  # aws_instance.login will be created
  + resource "aws_instance" "login" {
      + ami                                  = "resolve:ssm:/aws/service/canonical/ubuntu/server/20.04/stable/current/amd64/hvm/ebs-gp2/ami-id"
      + arn                                  = (known after apply)
      + associate_public_ip_address          = true
      + availability_zone                    = (known after apply)
      + cpu_core_count                       = (known after apply)
      + cpu_threads_per_core                 = (known after apply)
      + disable_api_stop                     = (known after apply)
      + disable_api_termination              = (known after apply)
      + ebs_optimized                        = (known after apply)
      + get_password_data                    = false
      + host_id                              = (known after apply)
      + host_resource_group_arn              = (known after apply)
      + iam_instance_profile                 = (known after apply)
      + id                                   = (known after apply)
      + instance_initiated_shutdown_behavior = (known after apply)
      + instance_state                       = (known after apply)
      + instance_type                        = "t3.small"
      + ipv6_address_count                   = (known after apply)
      + ipv6_addresses                       = (known after apply)
      + key_name                             = "login"
      + monitoring                           = (known after apply)
      + outpost_arn                          = (known after apply)
      + password_data                        = (known after apply)
      + placement_group                      = (known after apply)
      + placement_partition_number           = (known after apply)
      + primary_network_interface_id         = (known after apply)
      + private_dns                          = (known after apply)
      + private_ip                           = (known after apply)
      + public_dns                           = (known after apply)
      + public_ip                            = (known after apply)
      + secondary_private_ips                = (known after apply)
      + security_groups                      = (known after apply)
      + source_dest_check                    = true
      + subnet_id                            = (known after apply)
      + tags                                 = {
          + "name" = "login-inst"
        }
      + tags_all                             = {
          + "name" = "login-inst"
        }
      + tenancy                              = (known after apply)
      + user_data                            = (known after apply)
      + user_data_base64                     = (known after apply)
      + user_data_replace_on_change          = false
      + vpc_security_group_ids               = (known after apply)
    }

  # aws_key_pair.login will be created
  + resource "aws_key_pair" "login" {
      + arn             = (known after apply)
      + fingerprint     = (known after apply)
      + id              = (known after apply)
      + key_name        = "login"
      + key_name_prefix = (known after apply)
      + key_pair_id     = (known after apply)
      + key_type        = (known after apply)
      + public_key      = "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIF4yiy4lef2WFQ7zdtuOZUHz9onyADTJ16l0OX8a211y damocles@zelbinion"
      + tags_all        = (known after apply)
    }

  # aws_security_group.login_sg will be created
  + resource "aws_security_group" "login_sg" {
      + arn                    = (known after apply)
      + description            = "Login security group"
      + egress                 = (known after apply)
      + id                     = (known after apply)
      + ingress                = [
          + {
              + cidr_blocks      = [
                  + "0.0.0.0/0",
                ]
              + description      = ""
              + from_port        = 22
              + ipv6_cidr_blocks = []
              + prefix_list_ids  = []
              + protocol         = "tcp"
              + security_groups  = []
              + self             = false
              + to_port          = 22
            },
        ]
      + name                   = "login-sg"
      + name_prefix            = (known after apply)
      + owner_id               = (known after apply)
      + revoke_rules_on_delete = false
      + tags_all               = (known after apply)
      + vpc_id                 = (known after apply)
    }

Plan: 3 to add, 0 to change, 0 to destroy.

─────────────────────────────────────────────────────────────────────────────

Note: You didn't use the -out option to save this plan, so Terraform can't
guarantee to take exactly these actions if you run "terraform apply" now.

When you see steps where Terraform plans to drop or replace resources, you can decide if that's actually alright, or if it will negatively impact your business during the deployment, and then you can abort and define an intermediate state to first transition to, usually creating a new resource without dropping the old one, then do whatever operation is necessary to move internal state to it, then continue by deleting the old resource. This is like checking up on your contracting company before they start doing their work to make sure they don't do anything strange you don't want, and sometimes needing to handhold them through details of your business to come up with the new plan of action.

Until cloud resource management is as quick to execute as a webpage render where state changes can be done faster than a human can respond, this sort of hand-holding is necessary for live infrastructure with uptime guarantees. Declarative programming, at least for cloud infrastructure management, is a leaky abstraction over the imperative operations that need to be executed.

The Terraform HCL files don't have the prior state encoded into them, nor does it depend on being stored within a git repository to provide that prior state, so how does Terraform get that initial state to operate on? Terraform maintains an internal statefile to perform the diffing against. It has a mechanism to refresh that state, but being a JSON blob with poor typing, can become corrupted, requiring manual intervention.

Why does Terraform need this JSON-based statefile when it has the much cleaner HCL format that you use as a developer? This is because HCL, as a language that provides affordances to the developer's own intuition on how to structure their representation of their infrastructure, does not have a canonical form to make these comparisons with. Terraform must first pre-process the HCL into an internal data structure which it can then generate a diff that can then be used to determine the operations to perform. The JSON-based statefile is likely a very close representation of this internal data structure, and manual editing of it is highly discouraged because the details of it likely vary from release to release with an internal migration mechanism performed on upgrade of the Terraform CLI.

Needing to learn Terraform's HCL language to make infrastructure changes has been cited as a weakness that can be mitigated by wrapping it in the syntaxes more familiar to the user like Terraform CDK, Pulumi, or AWS CDK. This is a weakness, but we believe that the HCL-to-Statefile transformation being surjective instead of bijective is actually the bigger issue.

Surjective Function f Diagram

From Wikipedia: A function f is surjective if all of its inputs X map to all outputs Y, but overlaps can occur.

This is like multiple Terraform HCL representations all mapping to the exact same cloud infrastructure.

The Objective is Bi- hmmmm....

Bijective Function f Diagram

From Wikipedia: A function f is bijective if all of its inputs X map to all outputs Y exactly once.

A bijective function has a one-to-one mapping of all state in one set with all state in another set. Both representations are equivalent (given the appropriate transform functions), both sets (of possible cloud states) are the same size (conceptually), and for any one particular state in one set there is exactly one state in the other set. Programming Languages don't fit the bill here, as was demonstrated earlier, but what does? What, exactly, defines your cloud resources in the most minimal way?

The best answer in most cases is ~~water vapor~~ Entities. Each cloud resource has some unique identifier and a set of properties that configure it. Some of those properties can be relations or dependencies on other cloud resources. The ordering of the entities does not matter, so long as the dependencies are explicitly defined to make determination of the order of operations possible. You may have noticed that this sounds a lot like Objects in Object-Oriented Programming. It is very much the same, with the only difference being that the unique identifier must be unique, so if we considered an array of pointers to these objects as the entity IDs, multiple pointers to the same object wouldn't be allowed.

This means the representation of your cloud resources should be data, not code. Particularly, it should be relational data to allow the references/relations with other entities that actually exist within your cloud. Modeling each entity as a wholly-siloed object without those relations explicitly tracked might work for read-only cases, but puts the burden of properly joining the entities on the user and could lead to unexpected errors during mutation if the disjointed state is not carefully kept in sync. This mutation of that data would be the code, and fits very well into a classic REST architecture (in the original sense, though it could also work in the Web-oriented RESTful meaning.

Since your infrastructure is actually data, just about any data format could be used to store it, as demonstrated by Terraform's usage of JSON under-the-hood. Some will require more application-level guarantees on top of the data format to achieve all of the necessary components, however. For instance JSON does not have any native way to represent references to other JSON objects that are not wholly contained within the object in question, so you would need to define a way to represent them and enforce it within your application. YAML does support them via tags but being a markup language has multiple equivalent representations and is therefore not suitable for this purpose (without a strict linter to reduce it back down to a canonical form). There are also binary formats that can encode references with SQLite's file format arguably being the most prolific.

SQL immediately stands out here because it was designed for making relational algebra, the other side of the Entity-Relationship model, accessible. There are likely more people who know SQL than any programming language (for IaC) or data format you could choose to represent your cloud infrastructure. Many non-programmers know it, as well, such as data scientists, business analysts, accountants, etc, and there is an entire ecosystem of useful functionality you gain "for free" by using a SQL representation than just about any format out there. Furthermore, a powerful SQL engine like the open source PostgreSQL provides tools capable of elevating the infrastructure management experience beyond what IaC can do today.

The Advantages of SQL Databases for Infrastructure Management

First, by defining foreign key relations between tables, the database can immediately inform you if you have dependencies that must be created before you can create the resource you desire. It will simply return an error message to you if you have inserted an incomplete record missing any required fields.

"But," you say, "programming languages with solid type systems, like Rust, can also do that for you." That is true, and this was not an attempt to disparage them, merely to show that you aren't losing anything by using SQL as the representation. There are, however, ways SQL goes above and beyond. By defining unique, not-null, and check constraints on top of a rich type system the database can prevent you from inserting faulty data to a greater degree than even Rust, as the constraints can be against the live state of your infrastructure itself. Instead of code needing to deal with unexpected values further down the line, the faulty data can be prevented from ever being inserted. Rust's compiler making sure that you handle every possible branch is an awesome thing, but SQL has the power to make more of these branches impossible in the first place.

By being in a database, you can answer questions about your infrastructure via queries, and in the case of security vulnerabilities or expensive misconfigurations you can update that state across the board at the same time. The shared, live nature of the SQL database more closely matches the reality of the cloud infrastructure, while the more malleable nature of the querying in the Structured Query Language lets you cross-check in many dimensions the AWS console does not.

There's a further advantage by having a bijective relationship with your cloud: you can re-synchronize the database with your cloud at any time, avoiding statefile issues by never getting too out-of-date, and being almost impossible to corrupt. Software bugs are unavoidable, but since populating the database is trivial (compared to the cloud account itself), it could simply be dropped and recreated at any time without an impact on your own usage.

Most SQL databases have the concept of trigger functions that execute based on changes to the database itself. With a collection of automatically-created trigger functions, an audit log can be maintained, which can keep track of who made what cloud changes and when they did, going a step further than even a git repository of IaC changes, as changes done manually through the AWS console will also show up (though these would not have a database user to tie them to).

Trigger functions have other uses that don't even have an analogous concept in IaC tooling. For instance, trigger functions can be added to automatically re-insert deleted records of sensitive cloud resources for automated Chaos Engineering recovery. Either a mutation that was attempted by a developer that shouldn't be allowed (without also going through the much larger change of removing said trigger function first) so the resource is never deleted in the first place, as well as automatic recreation of the desired resource in the cloud if deleted via the AWS console or dropped during an outage.

SQL Database Snapshotting could be used to quickly revert all infrastructure changes for infrastructure without hidden state (eg, the snapshot of your load balancers' prior state would successfully restore the load balancer configuration, but a dropped database restored via a snapshot would not restore the data within, only that you had such a database in the first place and you would still need to manually re-load a backup snapshot of those, as well).

Database engines also represent a standardized protocol to interface with your data. This standard opens up a world of possibilities that can encompass the imperative and declarative infrastructure management styles and go beyond them. You can:

Perform operations on your database via scripts and the psql CLI tool, using whatever mix of declarative and imperative styles you prefer, mutating various tables declaratively and calling AWS SDK functions directly where and when you see fit.
Integrate the database into your application itself with a postgres client library allowing your applications to make infrastructure changes (like provisioning sharded resources for a client that wants isolation, or using a more accurate forecasting model to pre-allocate more resources before the storm hits).
Connect with a SQL IDE for a variety of reasons, such as outage mitigation or inspection of your infrastructure in a tabular (Excel-like) view.
Connect the database to a graphical dashboard for live visualization of your infrastructure's state and any other metadata transferred through.
Connect it to a low code tool for rapid response dashboards for your infrastructure with buttons you can tackle known outage vectors near-instantly, or just to make self-service provisioning of new microservices in the infrastructure team's preferred way.

There are negatives to representing your infrastructure as SQL, of course, as there is no perfect solution for all situations, but we believe IaSQL is a superset versus IaC in all but one category.

SQL is an old, irregular language to work with, but it is better known than HCL and SQL already has it's own Pulumi/CDK in the form of every ORM with introspection (like Javascript's Prisma, Python's Django, Go's XO etc) and QueryBuilder (LINQ, Knex, etc) in whatever programming language you prefer. You probably already know it.

Peer review of changes is different versus IaC. Instead of writing HCL or other IaC code, having it reviewed, and then applying it, you can enter an IaSQL transaction, make the desired changes with Postgres acting as your IDE, automatically blocking many impossible changes via types and relations, and use iasql_create_review to create a review artifact for your team after IaSQL has already vetted it for technical correctness. This vetting makes the result superior to the traditional peer review system in IaC.

Database schemas can be overwhelming, and a schema accurately representing all of AWS would be dizzying. HCL and other IaC tools let you elide parts of AWS via a module system, so we have implemented our own module system on top of Postgres inspired equally by programming language module systems and Linux distro package managers. This module system takes the place the usual database schema migration system for the management of the tables in the database, which may feel unusual at first, but we believe it is superior (for this use-case).

Even with IaC tools letting you ignore details you don't specifically need, AWS and other clouds expose many details in multiple layers for you to control when often you just need the same subset of functionality over-and-over, so these IaC tools have higher-level modules for these cookie-cutter use-cases. For IaSQL we did the same, though in a way that coexists with the lower-level modules so you can still query those details later, if you like. Here is an example of a high-level module that greatly simplifies ECS fargate.

IaSQL is declarative like IaC, so the leaky abstraction problem of declarative programming can still impact things. Like IaC's apply concept IaSQL has its own transaction system (separate from the actual database transaction system) that will let you enqueue changes and preview what actions the engine will take before commiting to it (or rolling it back). Going beyond that and acknowledging that sometimes you need to directly work in the lower abstraction layer, IaSQL exposes optional raw SDK access within the database to handle those tougher situations, and can then re-synchronize the declarative state afterwards.

As IaSQL is able to both push changes from the database to the cloud and pull changes from the cloud to the database versus the one-way push from HCL that Terraform provides, you can make changes directly in the AWS console, then inspect the audit log directly or call a special formatting function to see what SQL statements were the equivalent of those console changes. This reduces the learning curve of IaSQL and also lowers the barrier to entry in comparison to IaC. You can continue using other cloud management tooling in conjunction with IaSQL (including to a degree IaC) and IaSQL will show you "it's way" of doing the same when it syncs.

Finally, IaSQL makes your infrastructure accessible to a much large portion of your company than IaC tools can. Data Scientists who know SQL could help with the provisioning of their own batch processing code, EngSec can query all company infrastructure for security vulnerabilities and propose changes to improve things, Business Analysts in Growth teams can query actual traffic information from the live infrastructure (with read-only AWS credentials, of course), Finance auditors can query your infrastructure joined on pricing data and figure out accurate expense reporting per division, and DevOps can run queries joined on CloudWatch utilization stats to automatically identify overprovisioned services and create recommended auto-load scaling configuration changes. Tons of "not-developer" and "developer-adjacent" roles that can take over a lot of the ancillary burden of maintaining a production system.

But IaSQL is much newer than the IaC tools. We have coverage at the time of writing for 25 different AWS services, but that is admittedly far from complete. IaC tools also have cloud resources they cannot represent, but it is a much smaller percentage. This disadvantage will diminish over time, but creating a bijective entity representation of every cloud service requires more developer firepower than the approach IaC tools have taken, so we expect to always lag temporally on this front. At least, as long as AWS and friends don't follow a resource-oriented standard like Swagger.

Cloud APIs on Swagger?

Me too, Craig, me too...

With that said, what does our EC2 instance example look like in IaSQL, uh, SQL?

-- Assuming that the AWS credentials have already been inserted as done during the setup wizard
SELECT default_aws_region('us-west-2');

-- Install the necessary modules
SELECT iasql_install('aws_ec2', 'aws_ec2_metadata', 'ssh_accounts');

-- Create the key-pair and put it in our ssh_credentials table
-- We will update this later with the public IP address
INSERT INTO ssh_credentials ("name", hostname, username, private_key)
VALUES ('login', 'tbd', 'ubuntu', (SELECT privateKey FROM key_pair_request('login', 'us-west-2')));

-- Start an IaSQL transaction so these changes don't mutate our infrastructure immediately
SELECT iasql_begin();

-- Define the security group
INSERT INTO security_group (description, group_name)
VALUES ('Login security group', 'login-sg');

-- And it's ingress rule
INSERT INTO security_group_rule (is_egress, ip_protocol, from_port, to_port, cidr_ipv4, security_group_id)
SELECT false, 'tcp', 22, 22, '0.0.0.0/0', id
FROM security_group
WHERE group_name = 'login-sg';

-- Define the instance and the key-pair to use
INSERT INTO instance (ami, instance_type, key_pair_name, tags, subnet_id)
  SELECT
    'resolve:ssm:/aws/service/canonical/ubuntu/server/20.04/stable/current/amd64/hvm/ebs-gp2/ami-id',
    't3.small',
    'login',
    '{"name":"login"}',
    id
  FROM subnet
  WHERE availability_zone = 'us-west-2a'
  LIMIT 1;

-- Associate the instance with the security group
INSERT INTO instance_security_groups (instance_id, security_group_id) SELECT
  (SELECT id FROM instance WHERE tags ->> 'name' = 'login'),
  (SELECT id FROM security_group WHERE group_name='login-sg' AND region = 'us-west-2');

We can then SELECT * FROM iasql_preview(); to see what this does, like a terraform plan:

`iasql_preview` output

action	table_name	id	description
create	instance	1	1
create	security_group	18	18
create	security_group_rule	1	1

This is a "high level" overview with minimal details. We can get back a listing of all changes, represented in auto-generated SQL with SELECT * FROM iasql_get_sql_for_transaction();

INSERT INTO security_group (description, group_name, region)
VALUES ('Login security group', 'login-sg', (SELECT region FROM aws_regions WHERE region = 'us-west-2'));


INSERT INTO security_group_rule (is_egress, ip_protocol, from_port, to_port, cidr_ipv4, region, security_group_id)
VALUES ('f', 'tcp', '22', '22', '0.0.0.0/0', (SELECT region FROM aws_regions WHERE region = 'us-west-2'), (SELECT id FROM security_group WHERE group_id = NULL AND region = (SELECT region FROM aws_regions WHERE region = 'us-west-2')));


INSERT INTO instance (ami, instance_type, key_pair_name, state, tags, hibernation_enabled, region, subnet_id)
VALUES ('resolve:ssm:/aws/service/canonical/ubuntu/server/20.04/stable/current/amd64/hvm/ebs-gp2/ami-id', 't3.small', 'login', 'running', '{"name":"login"}'::jsonb, 'f', (SELECT region FROM aws_regions WHERE region = 'us-west-2'), (SELECT id FROM subnet WHERE subnet_id = 'subnet-06140fd708a495450' AND region = (SELECT region FROM aws_regions WHERE region = 'us-west-2')));


INSERT INTO instance_security_groups (instance_id, security_group_id)
VALUES ((SELECT id FROM instance WHERE instance_id = NULL AND region = (SELECT region FROM aws_regions WHERE region = 'us-west-2')), (SELECT id FROM security_group WHERE group_id = NULL AND region = (SELECT region FROM aws_regions WHERE region = 'us-west-2')));

which in this case is very similar to (though a bit more verbose than) our original SQL statements, but if you were going back and forth on what exact changes you're going to make, it can be a good summary.

If we're good with this, we can SELECT iasql_commit() to push this to our cloud account and then update our ssh_credentials record with the public IP address and check the connection:

WITH h AS (
  SELECT host(im.public_ip_address) AS hostname
  FROM instance_metadata im
  INNER JOIN instance i ON im.instance_id = i.instance_id
  WHERE i.tags ->> 'name' = 'login'
  LIMIT 1
)
UPDATE ssh_credentials SET hostname = h.hostname FROM ssh_credentials, h WHERE hostname = 'tbd';

SELECT * FROM ssh_exec('login', 'uname -a');

SQL is the least bad option for Infrastructure Management

Infrastructure Management is a complex beast. The cloud APIs are massive with a rich collection of knobs to tweak, and mistakes can be dangerous because the changes often take a long time and recovery even more so. We have learned about the two main types of infrastructure management: imperative and declarative, where ad-hoc usage of the AWS console fits under the imperative umbrella along with scripts calling an SDK, while IaC has until recently been the only way to declaratively manage your infrastructure.

Both imperative and declarative styles have their downsides. Imperative being more explicit makes it more tedious and more prone to error, and the usual execution frequency of only once for most production changes makes scripting usually no better than clicking around in the AWS console. Declarative is much less tedious, but because it is a leaky abstraction inspecting its execution plan is critical to make sure it doesn't automatically put you into an outage, and failures during application can still require falling back to imperative mode to get things back into shape from time-to-time.

Also until recently, all declarative approaches have suffered from only being a one-way transition, from your IaC codebase into the cloud, but no good or easy way to synchronize that code with the current state of the cloud if they drift from each other. Because these declarative programming approaches do not have a singular canonical form, they can't be bijective, making the transform back from the other side impossible to square up.

Data, on the other hand, is much easier to normalize in such a way, and relational data models, as found in SQL databases, can naturally model the relationships between cloud services, making it possible to produce a bijective representation. IaSQL takes advantage of this to make a declarative model based on tables and foreign keys that can automatically handle changes done outside of this declarative model, either by dropping down into its own imperative mode or by using any other tooling to manage your infrastructure, giving it a flexibility and resiliency that IaC tools don't have.

On top of this foundation, tooling to replicate or substitute for existing IaC tooling has been added, along with all of the past ~50 years of database tooling and expertise that you can take advantage of, providing a superset of functionality that makes existing use-cases easier and new use-cases possible.

IaSQL is currently in beta and can be used locally with docker or via our SaaS. We're proud of what we have built, and can't wait to hear from you on feature requests and bug reports.

Securely connect to an Amazon RDS via PrivateLink using SQL

Luis Fernando De Pombo — Wed, 29 Mar 2023 20:55:59 +0000

Do you have some database instances on RDS and wonder what's the most secure way to reach them? In this post, we will walk you through how to securely connect to an AWS RDS instance using PrivateLink and IaSQL. AWS PrivateLink provides private connectivity between virtual private clouds (VPCs), supported AWS services, and your on-premises networks without exposing your traffic to the public internet. IaSQL is an open-source software tool that creates a two-way connection between an unmodified PostgreSQL database and an AWS account so you can manage your infrastructure from a database.

When creating a database, AWS provides some specific information about the hostnames and ports used. You can access those to reach your databases by using specific clients for MySQL, Postgres, etc...:

On a public VPC, those endpoints will be public by default. However, consider creating them under a private VPC and accessing them internally, to grant additional security to critical services. AWS PrivateLink offers the possibility to securely access those services without exposing them to the internet, just using Amazon's private network.

Are my RDS instances properly configured?

Please use this query to check it:

 ---- Installing the needed modules
SELECT
  iasql_install ('aws_rds', 'aws_vpc');

-- Perform the query for endpoints
SELECT
  rds.region,
  vpc.is_default,
  vpc.cidr_block,
  (
    SELECT
      COUNT(*) > 0
    FROM
      endpoint_interface
    WHERE
      endpoint_interface.region = rds.region
      AND service = 'rds'
      AND endpoint_interface.vpc_id = vpc.id
  ) AS has_endpoint_interface
FROM
  rds
  LEFT OUTER JOIN vpc ON vpc.region = rds.region;

Have you found missing endpoints? No problem, IaSQL can generate missing Endpoint Interfaces for you:

SELECT
  *
FROM
  iasql_begin ();

-- Inserts the missing endpoints
INSERT INTO
  endpoint_interface (region, vpc_id, service, private_dns_enabled)
SELECT
  RDS.region,
  vpc.id,
  'rds',
  TRUE
FROM
  rds
  INNER JOIN vpc ON rds.region = vpc.region
WHERE
  NOT EXISTS (
    SELECT
      id
    FROM
      endpoint_interface
    WHERE
      endpoint_interface.region = rds.region
      AND endpoint_interface.vpc_id = vpc.id
  );

-- Preview the changes
SELECT
  *
FROM
  iasql_preview ();

-- Apply the changes
--select * from iasql_commit();
-- Rollback the changes
select * from iasql_rollback();

Running this query on an IaSQL database will auto-generate the missing endpoint interfaces and will allow you to preview the changes to be applied on your cloud. Once you are OK with the results, you can uncomment the iasql_commit query, comment the iasql_rollback query, and it will create the endpoints for you. If the final results in the cloud are not as expected, you can always roll back your changes by calling the iasql_rollback command.

Testing the result

After running the query, you should have Endpoint Interfaces created for your RDS resources. Those should be on the region and VPC where you had your databases:

To start testing the result, you can start a new EC2 instance on a private VPC in the same region where your RDS and Endpoint interface is configured. You can double-check that there is no internet connectivity. But the RDS endpoint could still be reached, by using the interface that has been created:

Please note that you could only use those endpoints from the same region. You could reach the services in multiple regions with the use of VPC peering

Save $ on public S3 buckets using VPC endpoints via SQL

Luis Fernando De Pombo — Thu, 23 Mar 2023 20:19:30 +0000

Are you using S3 buckets as part of your cloud deployments? How are you accessing them?

When running applications behind VPCs without public access, there may be the need to access S3 buckets from the private subnet over the public internet.
One simple but costly way to do so is to rely on NAT gateways.

However, creating gateway or interface VPC endpoints for each region where your buckets are exposed is a more optimal solution.

When the VPC endpoints are enabled you can access your S3 buckets using this endpoint. In this post, we will walk you through how to control your buckets from an internal network with the desired security, and without the extra costs of a NAT gateway using a VPC endpoint and IaSQL. IaSQL is an open-source software tool that creates a two-way connection between an unmodified PostgreSQL database and an AWS account so you can manage your infrastructure from a database.

Why use VPC Interface Endpoints

AWS VPC Interface Endpoints are a component of AWS's PrivateLink infrastructure. AWS PrivateLink provides private connectivity between virtual private clouds (VPCs), supported AWS services, and your on-premises networks without exposing your traffic to the public internet.

Relying on PrivateLink offers a set of advantages in terms of cost, security and performance.

Cost

When using NAT gateways, you are paying for the NAT gateway itself, and the bandwidth used to access the internet. A NAT gateway is a generic way for private instances to reach the internet, and can be useful for generic usage, such as pulling dependencies, updating repositories, etc... But when used for reaching AWS resources directly, there are more optimal and cheaper solutions such as VPC endpoints - they will not have generic access to internet but only to specified public AWS endpoints. When using VPC endpoints, you are paying for the bandwidth used to access the public endpoints, but not for the VPC endpoint itself.

Costs for NAT gateway depend on each region, but it should be over 0.045 USD/hour per GB processed + 0.045 USD/hour for the instance itself. Costs for VPC endpoints are charged at 0.01 USD/hour per GB processed, being cheaper after the first PB.

Component	Hourly pricing	Per GB pricing
NAT gateway	0.045 USD/hour	0.45 USD/hour per GB
VPC endpoint	--	0.01 USD/hour per GB

Security: no internet traversal

When deploying services in production, the concept of defense-in-depth is essential: have an information assurance strategy that provides multiple, redundant defensive measures in case a security control fails or a vulnerability is exploited.
By using interface endpoints the services will be accessed via Amazon's internal networks. It is an essential security measure, that will prevent attackers from directly reaching the services by keeping them in a private network, reducing the surface area of attack.

Security: policies

AWS services can benefit from IAM policies for fine-grained access control. When using interface endpoints, those policies can be applied to determine the traffic that can pass through the interface.

Performance: latency

Because the traffic stays within Amazon’s networks, the physical distance traveled, the number of hops and the risk of traversing congested networks are all significantly lower.

Performance: bandwidth

PrivateLink supports a sustained bandwidth of 10 Gbps per availability zone with bursts up to 40 Gbps.

Performance: stability

PrivateLink consistently lowers the number of errors and timeouts on high loads. The distance traveled, the number of hops, and the risks associated with traversing congested networks are reduced when the traffic over Private Link stays within Amazon’s networks.

In terms of speed, Private Link supports a sustained bandwidth of 10 Gbps per availability zone with bursts up to 40 Gbps.

Do you want to know if you have it properly configured? The following query will check for active S3 VPC interface endpoints:

This query will find your active S3 buckets for all regions, associated with the existing endpoint gateways or interfaces - if they exist.

 -- Installing the needed modules
SELECT
  iasql_install ('aws_s3', 'aws_vpc');

-- Perform the query for endpoints
SELECT
  bucket.region,
  vpc.is_default,
  vpc.cidr_block,
  (
    SELECT
      COUNT(*) > 0
    FROM
      endpoint_gateway
    WHERE
      endpoint_gateway.region = bucket.region
      AND service = 's3'
      AND endpoint_gateway.vpc_id = vpc.id
  ) AS has_endpoint_gateway,
  (
    SELECT
      COUNT(*) > 0
    FROM
      endpoint_interface
    WHERE
      endpoint_interface.region = bucket.region
      AND service = 's3'
      AND endpoint_interface.vpc_id = vpc.id
  ) AS has_endpoint_interface
FROM
  bucket
  LEFT OUTER JOIN vpc ON vpc.region = bucket.region;

Have you found missing endpoints? No problem, IaSQL can generate them for you. You can create two different types of endpoints: gateway and interface. We're going to describe their main features and you can create the desired type in an automated way.

Interface endpoints

An interface endpoint is an elastic network interface with a private IP address that serves as an entry point for traffic destined for a supported AWS service. As they use ENIs, they can benefit from security groups to control traffic.

IAM policies can also be
added to control access to those endpoints.

They are regional, meaning they can only be accessed by the same region they are created. However, Multi-region is possible by also using VPC peering, so resources in one region can be accessed from others, though this only supports IPv4 TCP traffic.

An interface endpoint (except S3 interface endpoint) has a corresponding private DNS hostname, that can be used to access the resource.

Interface endpoints cover a wide range of services such as S3, Lambda, API Gateway, etc... Get more detail about interface endpoints

This query will auto-generate the missing interface endpoints and will preview the changes to be applied on your cloud. Simply uncomment the final statement and run it to make the changes to your cloud account.

 -- Inserts the missing endpoints
SELECT
  *
FROM
  iasql_begin ();

INSERT INTO
  endpoint_interface (region, vpc_id, service)
SELECT
  bucket.region,
  vpc.id,
  's3'
FROM
  bucket
  INNER JOIN vpc ON bucket.region = vpc.region
WHERE
  NOT EXISTS (
    SELECT
      id
    FROM
      endpoint_interface
    WHERE
      endpoint_interface.region = bucket.region
      AND endpoint_interface.vpc_id = vpc.id
  );

-- Preview the changes
SELECT
  *
FROM
  iasql_preview ();

-- Commit the changes
-- SELECT * FROM iasql_commit();
-- Rollback changes
-- SELECT * FROM iasql_rollback();

Endpoint gateways

An endpoint gateway is a gateway that can be configured as a target for a route in your route table, used to access traffic in DynamoDB or S3.

Multiple gateway endpoints can be created in a single VPC, and those can be used on different route tables to enforce different access policies from different subnets to the same service.

Gateway endpoints are supported within the same region only, resources from multiple regions cannot be accessed, even using VPC peering. They also support IPv4 traffic only.

To use them, DNS resolution must be enabled in the VPC.

When a route is added, all instances in the subnets associated with the route table will automatically use the endpoint to access the service.

Gateway endpoints only support S3 and DynamoDB services. Get more detail about gateway endpoints.

This query will auto-generate the missing endpoint gateways and will allow you to preview the changes to be applied on your cloud.

 -- Inserts the missing endpoints
SELECT
  *
FROM
  iasql_begin ();

INSERT INTO
  endpoint_gateway (region, vpc_id, service)
SELECT
  bucket.region,
  vpc.id,
  's3'
FROM
  bucket
  INNER JOIN vpc ON bucket.region = vpc.region
WHERE
  NOT EXISTS (
    SELECT
      id
    FROM
      endpoint_gateway
    WHERE
      endpoint_gateway.region = bucket.region
      AND endpoint_gateway.vpc_id = vpc.id
  )
  -- Preview the changes
SELECT
  *
FROM
  iasql_preview ();

-- Commit the changes
-- SELECT * FROM iasql_commit();
-- Rollback changes
-- SELECT * FROM iasql_rollback();

Testing the result

Once all the relevant endpoints have been created, you can confirm your access to the gateway endpoints from an internal EC2 instance on the same region as the endpoint you want to test:

For interface endpoints, access also can be tested using the named endpoint.

Deploy a Static Website on AWS using SQL

Luis Fernando De Pombo — Mon, 06 Mar 2023 20:37:34 +0000

Did you know you can deploy a static website using a SQL REPL? In this post, we'll show you how to use IaSQL to deploy a static website from your GitHub repository to AWS S3 + Cloudfront services using only SQL queries. IaSQL is an open-source software tool that creates a two-way connection between an unmodified PostgreSQL database and an AWS account so you can manage your infrastructure from a database.

We will create and configure an S3 bucket to serve our static website. To enable support for HTTPS, we'll also add a CloudFront distribution. We will also leverage CodeBuild to automatically build the files for our project and copy them to the S3 bucket created already.

Create a S3 Bucket

To be able to work with S3, we should first install the corresponding IaSQL module.

SELECT
  iasql_install ('aws_s3');

Installing a module gives us the ability to use tables and RPCs provided by it. aws_s3 module gives us the ability to manage an S3 bucket, S3 static website hosting, and other related stuff. So let's create an S3 bucket first.

SELECT
  iasql_begin ();

INSERT INTO
  bucket (NAME, policy_document, region)
VALUES
  (
    '<bucket-name>',
    '{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "PublicReadGetObject",
      "Effect": "Allow",
      "Principal": "*",
      "Action": [
        "s3:GetObject"
      ],
      "Resource": [
        "arn:aws:s3:::<bucket-name>/*"
      ]
    }
  ]
}',
    'us-east-1'
  );

SELECT
  iasql_commit ();

The above query will create a new bucket in the us-east-1 region with the defined name <bucket-name> and the given policy using IaSQL. The iasql_begin and iasql_commit functions are RPCs that will start and finish an IaSQL transaction. Learn more about IaSQL transactions in this part of our documentation.

Now that we have a bucket, we can upload a file to it and see if we're able to view it using our web browser. Let's use IaSQL to upload a file to our newly created bucket:

SELECT
  *
FROM
  s3_upload_object ('<bucket-name>', 'hello.txt', 'Hello IaSQL!', 'text/plain');

This is going to upload a file named hello.txt in our bucket whose content is Hello IaSQL!.

You can read the code for s3_upload_object RPC as well as all other IaSQL modules and RPCs in our GitHub repository to see how they work.

Let's see if we can access our file using the S3 bucket URL. It should be as follows:

https://<bucket-name>.s3.amazonaws.com/hello.txt

But we're unable to access the file directly because S3 blocks public access by default.

Make The Bucket Public

We need to enable public access to our bucket files to be able to directly access the files. We can use the public_access_block table provided by aws_s3 module to allow for public requests to reach our objects.

If you want to know which resources (via tables) an IaSQL module handles, you can visit our documentation page. It also provides a list and explanation of all the RPCs that are provided by a module. In our case, we can visit this link to get a list of tables and RPCs available for aws_s3 module.

SELECT
  iasql_begin ();

INSERT INTO
  public_access_block (bucket_name, block_public_acls, ignore_public_acls, block_public_policy, restrict_public_buckets)
VALUES
  ('<bucket-name>', FALSE, FALSE, FALSE, FALSE) ON CONFLICT (bucket_name)
DO
UPDATE
SET
  block_public_acls = excluded.block_public_acls,
  ignore_public_acls = excluded.ignore_public_acls,
  block_public_policy = excluded.block_public_policy,
  restrict_public_buckets = excluded.restrict_public_buckets;

SELECT
  iasql_commit ();

There's a 1-1 relationship between the bucket and bucket public access block, therefore we're using Postgres ON CONFLICT syntax so that when there's a record already, we can replace it without hassle.

Now we should be able to directly access our file through the web browser.

https://<bucket-name>.s3.amazonaws.com/hello.txt

Use S3 Static Website Hosting

But simply serving the files doesn't mean we can host a static website. We need to enable static website hosting for our bucket to be able to deploy a React codebase. So let's enable it.

SELECT
  iasql_begin ();

INSERT INTO
  bucket_website (bucket_name, index_document)
VALUES
  ('<bucket-name>', 'index.html');

SELECT
  iasql_commit ();

We'll use this functionality to route all the requests to index.html file. This way we can deploy a sample React application and serve it through S3. To get the link for our S3 bucket's static website, we can use get_bucket_website_endpoint function.

SELECT
  *
FROM
  get_bucket_website_endpoint ('<bucket-name>');

Build The Project And Sync To S3

Now that everything is set, we just need to build our React app and deploy it to S3. We have already pushed a sample app to this repository:

https://github.com/iasql/sample-react-app

But you can use whatever codebase you'd like by changing the URLs so that they point to the Github repository hosting your React app.

Now it's the time to create a CodeBuild project. CodeBuild is an AWS CI/CD system that is free of cost. The CodeBuild project will do the following:

Pull the codebase from the GitHub repository
Build the app
Copy the resulting files (build/*) to the S3 bucket

We can do this with the following SQL query:

SELECT
  iasql_begin ();

-- create the needed role for codebuild
INSERT INTO
  iam_role (role_name, assume_role_policy_document, attached_policies_arns)
VALUES
  (
    'deploy-static-website-role',
    '{
    "Statement": [
      {
        "Effect": "Allow",
        "Principal": {
          "Service": "codebuild.amazonaws.com"
        },
        "Action": "sts:AssumeRole"
      }
    ],
    "Version": "2012-10-17"
  }',
    ARRAY[
      'arn:aws:iam::aws:policy/AWSCodeBuildAdminAccess',
      'arn:aws:iam::aws:policy/CloudWatchLogsFullAccess',
      'arn:aws:iam::aws:policy/AmazonS3FullAccess'
    ]
  );

-- create the codebuild project
INSERT INTO
  codebuild_project (project_name, build_spec, source_type, privileged_mode, service_role_name, region)
VALUES
  (
    'deploy-static-website',
    'version: 0.2

phases:
  pre_build:
    commands:
      - git clone https://github.com/iasql/sample-react-app && cd sample-react-app
  build:
    commands:
      - echo Installing dependencies
      - npm install
      - echo Building the app
      - npm run build
  post_build:
    commands:
      - echo Copying the files to the S3 bucket
      - aws s3 sync build/ s3://<bucket-name>',
    'NO_SOURCE',
    FALSE,
    'deploy-static-website-role',
    'us-east-1'
  );

SELECT
  iasql_commit ();

The above SQL command first creates a role that is needed for CodeBuild to operate. Then it'll create the actual CodeBuild project that clones the repo, builds it and finally syncs the resulting files to our S3 bucket. We need to trigger the CodeBuild project to run and then our files will be uploaded to our bucket.

SELECT
  *
FROM
  start_build ('deploy-static-website', 'us-east-1');

This will trigger the CodeBuild project to start. After a while we should be able to see the files appearing in our S3 bucket. We can access our React app using the endpoint for the S3 static website hosting. As we already mentioned, to get the endpoint we can use get_bucket_website_endpoint helper function.

SELECT
  get_bucket_website_endpoint ('<bucket-name>');

By visiting the link returned by the above function, you can see our sample app has been deployed. The problem is that S3 static website hosting does not support HTTPS, and therefore we need to use a CloudFront distribution in order to have HTTPS connection.

Create a CloudFront Distribution

Serving files in a bucket to the public using pure S3 isn't a good idea. In our case because the S3 static website hosting does not provide an HTTPS endpoint, but in most cases because the data transfer rates for S3 aren't cheap and can grow out of control. Also, the speed in which the users can access to the bucket objects will increase if you use a CDN because they'll be delivered to the users from the nearest edge server.

With the above in mind, let's create a CloudFront distribution for our S3 bucket. But first, we need to install the aws_cloudfront module to be able to leverage its abilities.

SELECT
  iasql_install ('aws_cloudfront');

Then create the distribution:

SELECT
  iasql_begin ();

INSERT INTO
  distribution (caller_reference, origins, default_cache_behavior)
VALUES
  (
    'my-website',
    (
      '[
  {
    "DomainName": "' || (
        SELECT
          get_bucket_website_endpoint ('<bucket-name>')
      ) || '",
    "Id": "my-website-origin",
    "CustomOriginConfig": {
      "HTTPPort": 80,
      "HTTPSPort": 443,
      "OriginProtocolPolicy": "http-only"
    }
  }
]'
    )::json,
    '{
  "TargetOriginId": "my-website-origin",
  "ViewerProtocolPolicy": "allow-all",
  "CachePolicyId": "658327ea-f89d-4fab-a63d-7e88639e58f6"
}'
  );

SELECT
  iasql_commit ();

We can access our website through the CloudFront distribution domain name. To get it, we can simply run the following query:

SELECT
  domain_name
FROM
  distribution
WHERE
  caller_reference = 'my-website';

It supports HTTPS, it's fast, it's cheaper than directly serving on S3, and it can be easily connected to a Route53 domain.

Deploy a Node.js app to AWS ECS+ECR+ELB quickly using PostgreSQL

Luis Fernando De Pombo — Thu, 02 Mar 2023 18:39:24 +0000

IaSQL is an open-source software tool that creates a two-way connection between an unmodified PostgreSQL database and an AWS account so you can manage your infrastructure from a database. In this post, we're going to discover an IaSQL module that's built to make deploying to ECS, simplified. Most of the details for deploying a container to AWS ECS are the same (load balancers, security groups, IAM roles, etc), and we have created the aws_ecs_simplified module for you so that you can give it any Github repo with a Dockerfile and get your app deployed to ECS in the fastest time possible, with scalability available! All the needed resources are going to be created automatically in your AWS account, and you'll have full access to the details while you're gaining the benefit of a higher-level simple deployment.

If you have ever tried to deploy your containerized application to ECS, you know that it's not going to be an easy click-to-deploy journey. To get your application up and running on ECS, you have to go through a bunch of resource creation. You'll need to:

Deploy a load balancer as the point-of-contact for your app
Create a target group for the load balancer and register the ECS tasks in it
Add a new listener to your load balancer and connect it to the target group
Create a security group, and you need to allow the port your app is listening on in that security group
Attach the security group above to your load balancer
Create a CloudWatch log group for your ECS task
Create an ECS cluster, and definitely the task definition as well
Oh, and create an ECR repository to push your images to be run on the container

I'm not going to continue this long list, since I've already got a headache. Doing those steps manually is going to give you a headache as well, so why bother doing all those steps yourself and risking different errors you might face when deploying your containerized app? You don't really have the time for the random IAM-related errors AWS is demanding you to resolve. Besides, you already have your codebase ready and the Dockerfile is there, so why not just run a simple command doing something that should be simply done?

An Example Usage of the `aws_ecs_simplified` Module

Let's say we are going to deploy this simple Express.js app to the ECS. It has a Dockerfile and package.json that installs express on npm install. npm start then starts the Express server which listens on port 8088.

aws_ecs_simplified is a high-level module we have created to make scalable ECS deployments easier. For more info on high-level vs low-level modules, you can check this guide.

Let's go and deploy the above app to your AWS account. Don't worry if you don't have an IaSQL database already, you can run IaSQL locally and run one locally for free here.

SELECT
  iasql_install ('aws_ecs_simplified', 'aws_codebuild');

SELECT
  iasql_begin ();

INSERT INTO
  ecs_simplified (app_name, app_port, image_tag, public_ip)
VALUES
  ('simple-express', 8088, 'latest', TRUE);

SELECT
  iasql_commit ();

SELECT
  ecr_build (
    'https://github.com/iasql/iasql/', -- the Github repo URL
    (
      SELECT
        id
      FROM
        repository
      WHERE
        repository_name = 'simple-express-repository'
    )::VARCHAR(255), -- ECR repo for the image to be pushed
    './examples/ecs-fargate/prisma/app', -- the subdirectory in Github repo
    'main', -- the Github branch or ref
    NULL -- Github personal access token - can be omitted if public repository
  );

That's it! Now wait for some time and your app is deployed! While your app is being deployed, let's go through the commands we executed in more depth:

SELECT
  iasql_install ('aws_ecs_simplified', 'aws_codebuild');

This command installs the aws_ecs_simplified high-level module. We– at IaSQL– have created that module to make it easy to deploy containerized apps to ECS. The code for it is here. But IaSQL is so flexible that anyone can create their own high-level (and of course, low-level) modules and add it to IaSQL.

INSERT INTO
  ecs_simplified (app_name, app_port, image_tag, public_ip)
VALUES
  ('simple-express', 8088, 'latest', TRUE);

This command creates a new ecs_simplified app by inserting a new row into the ecs_simplified table. Seems pretty easy, right? But under the hood, it's creating all the necessary resources like load balancers, security groups, IAM roles, etc.
You can manually check the tables to see what resources are being created. For example, looking at the load_balancer table you'll see a load balancer named simple-express-load-balancer is inserted automatically by running the above insert command.
The iasql_begin() and iasql_commit() functions are IaSQL RPCs that are used to start and then end a transaction. We use those two functions to bundle changes to be pushed to the cloud immediately. If you don't wrap the changes in a transaction, they'll be applied to the cloud in an eventually-consistent way.
For more info on the iasql_begin() and iasql_commit() commands, check this guide on how it works.
After the transaction is finished all the necessary resources are now created on the cloud, and their cloud-defined values are synced back to the database, so you can see the ARNs in the database. You can verify this by looking at different tables, eg. iam_role.
To get your load balancer address, you can easily run SELECT load_balancer_dns FROM ecs_simplified WHERE app_name = 'simple-express' query and get the URL to access your app.
Now ECS is waiting for an image to be pushed to your ECR repository to run it. You can get the URI for the ECR repository by running the SELECT repository_uri FROM ecs_simplified WHERE app_name = 'simple-express' query. You could build your docker image locally and then follow Steps 2 and 4 from this guide to connect your local docker CLI to your ECR repository and push that docker image into your ECR repository, but we have a simpler solution next.
In the next step, we'll automatically build an image for the code in Github repo and then push it to this URI (all through SQL and using another high-level function named ecr_build).

SELECT
  ecr_build (
    'https://github.com/iasql/iasql/', -- the Github repo URL
    (
      SELECT
        id
      FROM
        repository
      WHERE
        repository_name = 'simple-express-repository'
    )::VARCHAR(255), -- ECR repo for the image to be pushed
    './examples/ecs-fargate/prisma/app', -- the subdirectory in Github repo
    'main', -- the Github branch or ref
    NULL -- Github personal access token - can be omitted if public repository
  );

This command tells IaSQL to clone the iasql repository, build an image on the subdirectory specified, and then push it to the ECR repository created earlier by the aws_ecs_simplified module. Running the above command will automatically create a CodeBuild project and the related roles, etc. Then it'll start a build, and after it's successful all the created resources are deleted to ensure there won't be any additional charges to your AWS account.
To access your app on the cloud, get the load balancer address and use your browser to access the live version of it:

SELECT
  load_balancer_dns
FROM
  ecs_simplified
WHERE
  app_name = 'simple-express';

Then you can check if the server is running on the <load_balancer_dns value>:8088/health address.

Low-level Access to Resources

So the aws_ecs_simplified module simplifies things, right? But what if you still need the level of control you had when you were doing all the steps manually? The traditional PaaS trade-off is that you can't grow your app beyond the built-in limitations as you don't have access to all the small details. The IaSQL approach is not limited in that way.

Let's say you want your ECS container to be able to use the AWS CLI to provision an EC2 instance, and for that purpose its IAM role needs AmazonEC2FullAccess policy to work properly. aws_ecs_simplified does not have a column to configure such a thing, but that doesn't mean we're stuck.

The good news is that you still have the full control over all resources in the deepest details. Let's fix your app's IAM role access by attaching the needed policy to its IAM role:

UPDATE
  iam_role
SET
  attached_policies_arns = attached_policies_arns || 'arn:aws:iam::aws:policy/AmazonEC2FullAccess' -- attached_policies_arns is of text[] type
WHERE
  role_name = 'simple-express-ecs-task-exec-role';

You want additional rules for the container's security group? No problem! Just write the SQL and execute it, and it will be applied to the cloud within seconds. You want 3 copies of your container to be kept running with a round-robin load balancing on them? It's already there, just do an UPDATE ecs_simplified SET desired_count = 3 WHERE app_name = 'simple_express'; and it's there for you.

With IaSQL and its flexibility, you can benefit from both the high-level and low-level operations. We have created the aws_ecs_simplified module to show the flexibility and power of IaSQL, but the possibilities are endless. IaSQL is also an open-source project, meaning that you can use it to build your very own modules on top of it. If you're into the idea of empowering other developers to do complex infrastructure tasks simply, why don't you take a look at our contributing guide and join our Discord channel? We'll thoroughly answer any of your questions regarding the usage or development of IaSQL. Looking forward to seeing you in our small, but great community.

Save on AWS by deleting untagged ECR images

Luis Fernando De Pombo — Wed, 22 Feb 2023 19:50:41 +0000

In this post, we are going to learn how untagged ECR images can rack up your AWS bill unnecessarily and how to get rid of unused repository images with a single query in IaSQL: DELETE FROM repository_images WHERE tag = '<untagged>';

Every time you want to deploy a new version of your code to an ECS container or EKS pod, you build and push a new docker image from your code into an ECR container repository. The latest image in an ECR repository is the only one in use. Stale images remain in the repository and accumulate over long periods by normal usage of ECS, or EKS, as there is typically no workflow to delete untagged images. However, there is a gotcha. AWS charges $0.10 per GB per month for images stored in private or public ECR repositories per the pricing page. This doesn't sound like a lot, but for context, the size of the IaSQL docker image is around 2 GB. We have a CI job that deploys our staging environment every time we land a pull request to the main branch of our repository. This added up to 845 deploys over only a few months. Those 845 images, which have since been deleted, summed up to roughly 1700 GB which was $170 per month in our case. However, companies with lots of microservices on ECS or large docker containers can have many gigabytes of unused storage that can come out to hundreds or thousands of dollars per month of unnecessary AWS spend.

How do you get rid of untagged ECR images though? Deleting hundreds, or thousands, of container images across ECR repositories is quite tedious through the AWS console, which involves multiple clicks per deleted image. There are a few other options. IaC tools require state file manipulation, or it is not possible at all as ECR images are not typically modeled in the infrastructure declaration as they are often generated via CI and are quite numerous. Cloud Query and Steampipe let you query your cloud and inspect your ECR images, but do not let you delete the images or any part of your cloud account for that matter as they are read-only. The most common solution is often a script that calls the AWS SDK or CLI directly. IaSQL lets you delete untagged images to eliminate unnecessary AWS costs using the following query:

SELECT * FROM iasql_install('aws_ecr');
DELETE FROM repository_images WHERE tag = '<untagged>';

It is also possible to delete old images, or images past a certain size This query deletes unused images pushed to the repository before 2023 that are bigger than 10 GB:

DELETE FROM repository_images WHERE tag = '<untagged>' AND pushed_at < '2023-01-01' AND size_in_mb = 10000;

There is a way to delete all unused images if you are using ECR with ECS exclusively:

SELECT * FROM iasql_install('aws_ecs_fargate');
DELETE FROM repository_images;

After installing the aws_ecs_fargate module, which installs the dependant aws_ecr module if it's not already installed, the schema relations between the AWS ECS service will not let you delete ECR images currently in use and will reinstate the ones that are currently active.

Questions or issues with AWS? Join our Discord channel to ask away, or just to let us know if you would like to see more AWS cost optimizations like this one.

Infra as SQL is in beta

Luis Fernando De Pombo — Wed, 22 Feb 2023 01:16:13 +0000

IaSQL lets developers manage their cloud infrastructure as data in PostgreSQL as an alternative to the AWS console and infrastructure as code (IaC) tools like Pulumi and Terraform. We open-sourced IaSQL's Alpha version (v0.0.x) in April 2022. We still have a long way to go, but we feel ready for what is next. Today, we’re moving IaSQL to Beta (v0.x)!

We’ve been fortunate to work with lots of early adopters who helped us shape the product and prioritize the features to build. We have been quietly fixing bugs and adding goodies to IaSQL non-stop for the past few months. Some of the bugs have led to some difficult outages. We love open source and that includes airing our dirty laundry. We keep our postmortems here in case you are curious.

Additionally, we completely redid our architecture to scale and our UX to make it more intuitive and just plain simpler. More on that later. Several hundred SQL queries and cloud resources have been created on top of IaSQL. Blood, sweat, and tears went into the Alpha phase. Okay, that may be an overdramatization, but it did take a lot of thoughtful hours from us to help you manage cloud infrastructure more seamlessly.

Alpha phase in numbers

26 Alpha versions
170 databases created
199 GitHub stars
11 GitHub contributors
481 GitHub issues closed
140,755 lines of code and markdown
24 AWS services covered
90 Discord members

New features

Here are the new features that ship in the Beta versions of IaSQL:

🏡 Home is where your local env is

We made IaSQL easier to run locally by bundling up our dashboard into the IaSQL docker container and publishing it to Dockerhub. This makes IaSQL easier to try out without having your cloud credentials ever leave your local environment. It is as simple as running the command below and going to http://localhost:9876 on your preferred browser.

docker run -p 9876:9876 -p 5432:5432 --name iasql iasql/iasql

🎛️ AWS Multiregion

Support for multiple AWS regions with default region behavior in part because we also hate changing regions in the AWS console. The default region is defined when connecting your database to your AWS account. Thereon, IaSQL's data model will assume the default data model unless you explicitly override it in the column that represents your cloud resource.

Learn more about it in the RFC for this feature →

🪄 Infrastructure changes as transactions, please

We redid the UX to allow handling infrastructure changes automatically and for delicate, or complex, changes to your cloud account use an IaSQL transaction akin to transactions in a regular database. Do you want to programmatically modify your infra or control plane? We got you!

Learn more about it in the RFC for this feature →

🎚️ Moar coverage of AWS services

Increased AWS service coverage for EC2, CodeDeploy, CodeBuild, CodePipeline, SNS, ACM, Route53 amongst a few others. Additionally, we have a new aws_sdk module that lets you invoke the AWS SDK directly using PostgreSQL functions with added type safety.

See an up-to-date list of covered AWS services →

💨 Breeze through a simplified AWS

AWS is well... complicated. Our modules let you create, update, and delete your cloud resources as relational tables with the configurability AWS provides, but sometimes those details are not relevant to what you are trying to accomplish. So we have developed simplified modules that focus on specific use cases. For instance, deploying a docker container to ECS and exposing it to the internet which is not just ECS but involves ECR, ECS, ACM, and Route53. These simplified modules are written in pure SQL on top of the existing IaSQL modules and are meant to abstract the complexity of coordinating multiple AWS services while still letting you peek under the hood when needed. Think of a simplified module as a PaaS hosted in your AWS account that is built on top of known AWS services but also lets you eject back into these AWS services if necessary.

Learn more about simplified modules here →

📈 Scale for what?

Re-architected the product to scale the SaaS beyond a handful of users and allow automatic database version upgrades for modules.

Learn more about it in the RFC for this feature →

What’s next?

The next features are going to be about making IaSQL easier to use:

more examples
SQL templates for common security vulnerabilities and cost optimizations in AWS
continuously improving our documentation

Longer term, we’ll add support for 3rd party high-level modules, extensive support for AWS, and support for more cloud providers. If there is something in particular you would like to see please drop us a line on Discord or email via hello at our domain.

Want to stay in the loop? → Join our newsletter!

UPDATE iasql SET source = 'open';

Luis Fernando De Pombo — Thu, 14 Apr 2022 20:59:07 +0000

We are excited to announce that IaSQL is now open source! The main repository is under https://github.com/iasql/iasql-engine. As perfectionists, we feel like IaSQL will never be truly ready. However, we believe IaSQL is at the point where it can start to be useful for developers managing infrastructure in the cloud. IaSQL is a SaaS that lets you model your infrastructure as data by maintaining a 2-way connection between your AWS account and a Postgres SQL database to represent the definitive state (and status) of your cloud which cannot be achieved with a static infrastructure declaration. This means that when you connect an AWS account to an IaSQL instance it automatically backfills the database with your existing cloud resources. No need to painstakingly redefine or reconcile your existing infrastructure, and IaSQL's module system means you can specify which parts of your cloud infrastructure you wish to control.

IaSQL also makes it possible to express infrastructure changes as code that can be version controlled. This can be done with any migration system for schema and data changes, or in a script using idempotent SQL inserts more akin to IaC.

PostgreSQL IaSQL databases can be provisioned and configured via our dashboard. The dashboard calls the engine which is a Node.js server written in Typescript that provisions unmodified PostgreSQL instances loaded with tables representing AWS services controlled via the AWS SDK. AWS is our main focus at the moment, but we plan to support GCP, Azure and other cloud providers soon. This is an updated list of the AWS services that we currently support. Let us know if you need a specific AWS service and we should be able prioritize it!

We want to make it easier to write IaSQL modules, reduce waiting times when provisioning infrastructure, add more functionality to the existing AWS services, and so on. The list of things we want to build into IaSQL is long, but we want to do it in the open with your feedback and help. Drop us a line on Discord!

Infrastructure as SQL

Luis Fernando De Pombo — Wed, 15 Sep 2021 01:39:45 +0000

UPDATE

Since this post was originally written the schema of IaSQL has changed and the queries below no longer work. Please refer to the documentation for how to use IaSQL. The IaSQL modules include example queries that are tested and kept up-to-date, including the aws_ec2 module that this post covers.

What software you have deployed on what services and the interactions between them and the outside world is not a program, it is information about your infrastructure. Changing your infrastructure is a set of operations to perform, a program. A SQL database is a set of information and SQL queries read or change that data.

Infrastructure State is Data, Infrastructure Change is Code. It's as simple as that.

And manipulating your infrastructure in this way is natural.

INSERT INTO aws_ec2 (ami_id, ec2_instance_type_id)
SELECT ami.id, ait.id
FROM ec2_instance_type as ait, (
    SELECT id
    FROM   amis
    WHERE  image_name LIKE 'amzn-ami-hvm-%'ORDER BY creation_date DESC
    LIMIT 1
) as ami
WHERE  ait.instance_name = 't2.micro';

Relations and Types Matter for Infrastructure

Infrastructure as Code solutions do not have a good way of encoding dependencies across infrastructure pieces in a micro services architecture which makes it really hard to make and revert changes to infrastructure.

Representing your infrastructure as SQL resolves the primary issue of YAML-based infrastructure tools by making the relations between pieces of your infrastructure first-class citizens, and enforcing type safety on the data and changes to it.

You can't set the EC2 instance type as t2.mucro and have your deploy system try and fail to create such an instance. The insert statement will fail and tell you zero rows were inserted and you can quickly see why.

Similarly, if you have a record in the security_group table, you can't delete it if there are any references to it in the ec2_security_groups join table. The relational structure of IaSQL prevents you from putting your infrastructure into an invalid state.

New Powers: Explore, Query, and Automate Your Infrastructure

Because your infrastructure is presented as a SQL database, you can connect to it with a SQL client of your choice and explore what you have and what the possibilities are.

SHOW tables;

You can query for unusual usage patterns.

SELECT aws_ec2.*
FROM aws_ec2
INNER JOIN ec2_instance_type AS ait ON ait.id = aws_ec2.ec2_instance_type_id
WHERE ait.vcpus > 8
ORDER BY ait.vcpus DESC

And since it is a database, you can create your own tables with their own meaning and associate them with your infrastructure.

SELECT aws_ec2.*
FROM aws_ec2
INNER JOIN company_team_ec2s AS cte ON cte.aws_ec2_id = aws_ec2.id
INNER JOIN company_teams AS ct ON ct.id = cte.company_team_id
WHERE ct.name = 'Data Engineering'

Finally, your applications can know much more about what infrastructure they need than any auto-scaler solution out there. If you had a very infrequent but CPU/GPU-intensive job you need to handle at an unknown interval, you could give your application access to your IaSQL database and let it temporarily create and then destroy those resources.

const ec2_instance_id = await iasql(`
  INSERT INTO aws_ec2 (ami_id, ec2_instance_type_id)
  SELECT ami.id, ait.id
  FROM ec2_instance_type as ait, (
      SELECT id
      FROM amis
      WHERE image_name = 'application-job-runner'
  ) as ami
  WHERE ait.instance_name = 'g3.4xlarge'
  RETURNING id;
`);
await iasql(`
  INSERT INTO ec2_security_groups (ec2_id, security_group_id)
  SELECT ${ec2_instance_id}, sg.id
  FROM security_groups AS sg
  WHERE sg.name = 'application-job-group';
`);
// Only large-enough job runners will take it based on job metadata
const result = await job.run(myMassiveJob); 
await iasql(`
  DELETE FROM aws_ec2
  WHERE id = ${ec2_instance_id};
`);

You Don't Need to Learn a New API (Probably)

Nearly all cloud backend systems depend on a database, and most likely a SQL database, so you do not need to learn a new language to manipulate the infrastructure in this way.

And likely you're using a migration system in your backend to review changes to your database, which you can continue to use here, making it code to be reviewed, just like Infrastructure-as-Code.

You Can Test, Too

Since the safety guarantees are provided by the types and relations between tables, you can simply copy your production infrastructure database into a local database and run your changes/migration against that and verify it works before you run it on your actual Infrastructure-as-SQL database.

Recover With Ease

It's 3AM and your service has gone down. You reverted the most recent IaSQL migration, but that didn't resolve the issue, and you aren't sure which change across which service today caused the outage. So, you simply replace the state of the IaSQL database with a snapshot from yesterday to bring everything back online to a known-good-state, and then take your time after you're well-rested to figure out what actually went wrong.

Sign up for our waitlist here

DEV Community: IaSQL

Why SQL is right for Infrastructure Management

Following these instructions is imperative

Imperative Restaurant Instructions

Declare your intentions at once

Declarative Restaurant Instructions

Surjective Function f Diagram

The Objective is Bi- hmmmm....

Bijective Function f Diagram

The Advantages of SQL Databases for Infrastructure Management

Cloud APIs on Swagger?

iasql_preview output

SQL is the least bad option for Infrastructure Management

Securely connect to an Amazon RDS via PrivateLink using SQL

Are my RDS instances properly configured?

Testing the result

Save $ on public S3 buckets using VPC endpoints via SQL

Why use VPC Interface Endpoints

Interface endpoints

Endpoint gateways

Testing the result

Deploy a Static Website on AWS using SQL

Create a S3 Bucket

Make The Bucket Public

Use S3 Static Website Hosting

Build The Project And Sync To S3

Create a CloudFront Distribution

Deploy a Node.js app to AWS ECS+ECR+ELB quickly using PostgreSQL

An Example Usage of the aws_ecs_simplified Module

Low-level Access to Resources

Save on AWS by deleting untagged ECR images

Infra as SQL is in beta

Alpha phase in numbers​

New features

🏡 Home is where your local env is

🎛️ AWS Multiregion

🪄 Infrastructure changes as transactions, please

🎚️ Moar coverage of AWS services

💨 Breeze through a simplified AWS

📈 Scale for what?

What’s next?​

UPDATE iasql SET source = 'open';

Infrastructure as SQL

Relations and Types Matter for Infrastructure

New Powers: Explore, Query, and Automate Your Infrastructure

You Don't Need to Learn a New API (Probably)

You Can Test, Too

Recover With Ease

`iasql_preview` output

An Example Usage of the `aws_ecs_simplified` Module

Alpha phase in numbers

What’s next?