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');
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.
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.
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 INSERT
ing 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 (likeami-0f1a5f5ada0e7da53
), or use a parameter reference to the AWS Systems Manager Parameter Store (likeresolve:ssm:/aws/service/canonical/ubuntu/server/22.04/stable/current/amd64/hvm/ebs-gp2/ami-id
). We'll go with the parameter reference choice.
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.
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 aressm
for AWS Systems Manager Parameter Store,ssm-secure
Systems Manager too, but for secure strings, andsecretsmanager
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 likeparameter-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 at2.small
instance for our deployment.subnet_id
: this is a foreign key tosubnet
table based on itsid
column. Good thing about IaSQL is that it'll automatically import every subnet in your cloud environment when you install theiasql_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 aSELECT
query from thesubnet
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 passcloud-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 theiasql
key pair we initially created usingkey_pair_import
RPC.region
: this column is omitted and therefore will be replaced with the result ofdefault_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 additionalregion
column can be used to determine which region the instance is in, and whileINSERT
ing 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 instance
s to their security_group
s. 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.