Deploy IaSQL on EC2 using IaSQL

This is the first in a series of blog posts on deploying different services to AWS using IaSQL. IaSQL covers a wide range of AWS services, so there are lots of different services with different architectures to explore. Deploying containers to ECS, launching servers using EC2, and use AWS Codepipeline to automate the building and deploying your applications are just a few possibilities.

In this post we'll explore how to deploy IaSQL on EC2, using IaSQL!

Start IaSQL

To manage the cloud resources for your deployment of IaSQL, we'll need to use a separate instance of IaSQL. You may use our SaaS offering at app.iasql.com, or locally via Docker:

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

This will download the latest IaSQL image from dockerhub and run it. After it's ready, you can access the dashboard at the following URL:

[http://localhost:9876/](http://localhost:9876)

Now you can connect your AWS account in the dashboard. This will create a new database that can be connected using any Postgres client you prefer, or you may run queries directly through the dashboard.

Deploy IaSQL on EC2 with IaSQL

Overview

To deploy IaSQL on EC2, we need to:

• Launch an EC2 instance that will host IaSQL
• Set the instance user data so that Docker will be installed upon instance launch, and the IaSQL container will be started
• Import an SSH key pair into AWS to be able to access the EC2 instance afterwards
• Set a security group for the instance to allow SSH, while disallowing unauthorized access to the dashboard

Install IaSQL Modules

Let's start by installing the needed IaSQL modules. IaSQL has a modular design, and therefore to manage EC2 instances, we need to use aws_ec2 module. Also, aws_ec2_metadata allows us to get more info on our EC2 instance, like its public IP.

SELECT iasql_install('aws_ec2', 'aws_ec2_metadata');

note

The above query can also be written like SELECT * FROM iasql_install(). The difference is that when doing SELECT *, the result will be in a table while the raw SELECT would put the result into Python-tuple-like rows. If you don't want to inspect the output of a query, it might be easier to follow the shorter query.

Import SSH Key Pair Into AWS

Having SSH access to the instance is important, since if you don't have access to the instance you won't be able to upgrade the running IaSQL container. To enable SSH access, you can import your public key into AWS by executing the following IaSQL RPC:

SELECT * FROM key_pair_import('iasql', '<your-public-key-content>', default_aws_region());

This will direct IaSQL to create an AWS key pair with the name iasql in the us-east-1 region, and with your public key contents.

note

An IaSQL RPC is a function available in Postgres that upon calling, sends a request to the IaSQL engine to run a pre-defined logic and returns results to the Postgres console. In the above example key_pair_import is an IaSQL RPC that triggers the IaSQL engine to send a request to AWS for importing the public key.

Create And Configure Security Group

Now it's time to launch our EC2 instance. Before we go for the instance itself, let's first define the security group that's going to be attached to it. For this purpose, we'll need aws_security_group module. But since aws_ec2 module depends on it, it's already installed because IaSQL automatically installs the dependencies for modules upon an iasql_install call.

WITH sg as (INSERT INTO security_group (description, group_name)
            VALUES ('IaSQL security group', 'iasql_sg')
            RETURNING id)
INSERT
INTO security_group_rule (is_egress, ip_protocol, from_port, to_port, cidr_ipv4, description, security_group_id)
VALUES (FALSE, 'tcp', 22, 22, '0.0.0.0/0', 'ssh port is accessible from all ips', (SELECT id FROM sg)),
       (FALSE, 'tcp', 9879, 9879, '<your-ip>/0', 'open dashboard to just me', (SELECT id FROM sg)),
       (FALSE, 'tcp', 5432, 5432, '0.0.0.0/0', 'postgres port is accessible from all ips', (SELECT id FROM sg)),
       (TRUE, 'tcp', 1, 65535, '0.0.0.0/0', 'allow outgoing traffic to anywhere', (SELECT id FROM sg));

This query uses a WITH Postgres query to insert a security group, and then select the id of the resulting security group to be re-used in the second INSERT query. The WITH query can be thought of as a temporary table that exists just for one query. By use of WITH we can simplify our query and do the insert, and preloading of the resulting id in a single query. We could've written the query using subqueries as follows, but that way we should've repeated the SELECT id FROM security_group WHERE ... part for each set of values (and of course, doing the insert in a separate query):

INSERT INTO security_group_rule (is_egress, ip_protocol, from_port, to_port, cidr_ipv4, description, security_group_id)
VALUES (FALSE, 'tcp', 22, 22, '0.0.0.0/0', 'ssh port is accessible from all ips',
        (SELECT id FROM security_group WHERE group_name = 'iasql_sg')),
       (FALSE, 'tcp', 9879, 9879, '<your-ip>/0', 'open dashboard to just me',
        (SELECT id FROM security_group WHERE group_name = 'iasql_sg')),
       (FALSE, 'tcp', 5432, 5432, '0.0.0.0/0', 'postgres port is accessible from all ips',
        (SELECT id FROM security_group WHERE group_name = 'iasql_sg')),
       (TRUE, 'tcp', 1, 65535, '0.0.0.0/0', 'allow outgoing traffic to anywhere',
        (SELECT id FROM security_group WHERE group_name = 'iasql_sg'));

The security_group_id column has an FK set to the security_group table's id column. Therefore, we need to insert the correct id for the security group we just created.

danger

Since the IaSQL dashboard does not have a default authorization mechanism set, remember to replace <your-ip> value in the above queries with your own IP address.

You can find your current IP address by visiting api.ipify.org. Just remember that most internet users don't have a static IP address and your IP address will be changed each time your router reconnects.

Launch EC2 Instance

It's now time to actually create the EC2 instance. We'll issue a query as follows to do so:

INSERT INTO instance (ami, instance_type, subnet_id, tags, user_data, key_pair_name)
VALUES (...);

Before we write the query, let's investigate each column in the instance table. As you've already noticed, we create AWS resources by INSERTing rows into corresponding tables. For example, we created a security group by executing the INESRT INTO security_group query. With that in mind, let's look into the columns of the instance table (which is in charge of managing EC2 servers) that will be used in our query:

• ami: this column is the Amazon Machine Image ID that is used to launch the EC2 server. We can either directly insert the AMI ID (like ami-0f1a5f5ada0e7da53), or use a parameter reference to the AWS Systems Manager Parameter Store (like resolve:ssm:/aws/service/canonical/ubuntu/server/22.04/stable/current/amd64/hvm/ebs-gp2/ami-id). We'll go with the parameter reference choice.

info

The parameter reference resolve:ssm:/aws/service/canonical/ubuntu/server/22.04/stable/current/amd64/hvm/ebs-gp2/ami-id uses the resolve syntax to retrieve the value of an SSM parameter.

The ssm service name specifies that the parameter is stored in the AWS Systems Manager Parameter Store. The rest of the path /aws/service/canonical/ubuntu/server/22.04/stable/current/amd64/hvm/ebs-gp2/ami-id is the path to the SSM parameter containing the Amazon Machine Image (AMI) ID for a specific Ubuntu Server 22.04 AMI that is optimized for use with Amazon Elastic Block Store (EBS) General Purpose SSD volumes.

info

Dynamic References provide a compact, powerful way for you to specify external values that are stored and managed in other AWS services, such as the Systems Manager Parameter Store and AWS Secrets Manager. We use AWS Resolve syntax to dynamically reference a value.

The AWS Resolve syntax uses the following format:

resolve:<service-name>:<reference-key>

• service-name: Specifies the service in which the value is stored and managed. Currently, valid values are ssm for AWS Systems Manager Parameter Store, ssm-secure Systems Manager too, but for secure strings, and secretsmanager for AWS Secrets Manager.
• reference-key: The reference key. Depending on the type of dynamic reference, the reference key may be comprised of multiple segments. For example when using Systems Manager Parameter Store, this key can be like parameter-name:version.

Using the AWS Resolve syntax in AWS SDK, CloudFormation templates or EC2 user data script allows you to dynamically reference and use the values of resources in other AWS services, which can be useful for automating the deployment of AWS resources.

For example, you could use resolve:ssm:S3AccessControl:2 to reference version 2 of a parameter stored in AWS Systems Manager Parameter Store, and resolve:secretsmanager:MyRDSSecret:SecretString:username to reference a secret stored in AWS Secrets Manager.

• instance_type: this column contains the instance type of the EC2 server. We'll use a t2.small instance for our deployment.
• subnet_id: this is a foreign key to subnet table based on its id column. Good thing about IaSQL is that it'll automatically import every subnet in your cloud environment when you install the iasql_vpc module. This is not just for the subnets, but all the resources will be imported when installing the module that manages them. You can also manage the infrastructure outside IaSQL afterwards, having IaSQL also import your subsequent changes to the database. We'll use a SELECT query from the subnet table to fill this column.
• tags: this column is a JSON column. It'll contain all the tags for this instance. We'll use {"name":"iasql"} as the value for this column since AWS treats the instance name as a tag.
• user_data: when you launch an instance in Amazon EC2, you have the option of passing user data to the instance that can be used to perform common automated configuration tasks and even run scripts after the instance starts. You can pass two types of user data to Amazon EC2: shell scripts and cloud-init directives [1]. We'll write the deployment scripts in bash, but it's also possible to pass cloud-init directives.
• key_pair_name: AWS accepts a key pair for the instance. It can be used to log into the instance through SSH. We'll use the iasql key pair we initially created using key_pair_import RPC.
• region: this column is omitted and therefore will be replaced with the result of default_aws_region() Postgres function. we can choose the AWS region in which the instance will be launched. IaSQL is already multi-region, which means it'll manage your resources in all the regions. So this additional region column can be used to determine which region the instance is in, and while INSERTing used to direct IaSQL to create the instance in that region.

Bash Startup Script

Alright, we can now write our query to deploy IaSQL on EC2. Let's write the query with EC2 startup script being a shell script:

INSERT INTO instance (ami, instance_type, subnet_id, tags, user_data, key_pair_name)
SELECT 'resolve:ssm:/aws/service/canonical/ubuntu/server/22.04/stable/current/amd64/hvm/ebs-gp2/ami-id',
       't2.small',
       subnet.id,
       '{"name":"iasql"}',
       '#!/bin/bash
     sudo apt-get upgrade -y
     sudo apt-get install -y docker.io
     docker run -p 9876:9876 -p 5432:5432 --name iasql iasql/iasql',
       'iasql'
FROM subnet
         INNER JOIN vpc ON vpc.id = subnet.vpc_id
    AND vpc.is_default
WHERE vpc.region = default_aws_region()
LIMIT 1;

There's just one last step needed. Remember the security group we created earlier? We never assigned our instance to it (we couldn't because it didn't exist, yet). So let's do it and let IaSQL do the rest:

INSERT INTO instance_security_groups (instance_id, security_group_id)
SELECT instance.id, security_group.id
FROM instance, security_group
WHERE instance.tags ->> 'name' = 'iasql'
      AND security_group.group_name = 'iasql_sg'
LIMIT 1;

The instance_security_groups table is a Many-to-Many join table that connects instances to their security_groups. Now we need to wait for IaSQL to start syncing the state of the cloud with our local changes. This will normally happen every 2 minutes. There is a way to make the cloud apply/sync happen immediately using IaSQL transactions, but for the purpose of this post we'll wait for the next cronjob tick to apply our changes.

After waiting long enough for the server to start and for the startup script to finish, we can use instance's public IP to access our self-hosted IaSQL dashboard:

SELECT public_ip_address
FROM instance_metadata
WHERE id = (SELECT id
            FROM instance
            WHERE tags ->> 'name' = 'iasql'
            LIMIT 1);

Now we can go to http://<instance-ip>:9876/ URL, and we'll see the beautiful IaSQL dashboard deployed on our EC2 server.