aws_vpc

Components

Tables

    availability_zone

    elastic_ip

    endpoint_gateway

    endpoint_interface

    internet_gateway

    nat_gateway

    peering_connection

    route

    route_table

    route_table_association

    subnet

    vpc

Enums

    endpoint_gateway_service

    endpoint_interface_service

    connectivity_type

    nat_gateway_state

    peering_connection_state

    subnet_state

    vpc_state

Code examples

Manage VPCs

--- installs the vpc module
SELECT * FROM iasql_install('<modules>');
--- adds a new vpc
INSERT INTO vpc (cidr_block, tags, enable_dns_hostnames, enable_dns_support)
VALUES ('192.<randIPBlock>.0.0/16', '{"name":"<prefix>-1"}', true, true);
--- adds a subnet
INSERT INTO subnet (availability_zone, vpc_id, cidr_block)
SELECT (SELECT name FROM availability_zone WHERE region = '<region>' LIMIT 1), id, '192.<randIPBlock>.0.0/16'
FROM vpc
WHERE is_default = false
AND cidr_block = '192.<randIPBlock>.0.0/16';
--- tries to update vpc tags
UPDATE vpc SET tags = '{"name": "<prefix>-2"}' WHERE cidr_block='192.<randIPBlock>.0.0/16';
--- checks that tags have been modified
SELECT * FROM vpc WHERE cidr_block='192.<randIPBlock>.0.0/16' AND tags ->> 'name' = '<prefix>-2';
--- tries to update vpc cidr
UPDATE subnet SET cidr_block='191.<randIPBlock>.0.0/16' WHERE cidr_block='192.<randIPBlock>.0.0/16';
UPDATE vpc SET cidr_block='191.<randIPBlock>.0.0/16' WHERE cidr_block='192.<randIPBlock>.0.0/16';
--- checks that cidr have been modified
SELECT * FROM vpc WHERE cidr_block='191.<randIPBlock>.0.0/16';
--- creates a second vpc in another region
INSERT INTO vpc (cidr_block, tags, enable_dns_hostnames, enable_dns_support, region)
VALUES ('176.<randIPBlock>.0.0/16', '{"name":"<prefix>-peering-vpc"}', true, true, 'us-east-1');
--- adds a subnet to the vpc
INSERT INTO subnet (availability_zone, vpc_id, cidr_block, region)
SELECT 'us-east-1a', id, '176.<randIPBlock>.1.0/24', 'us-east-1'
FROM vpc
WHERE tags ->> 'name' = '<prefix>-peering-vpc';
--- creates a peering connection between the first and second vpc
INSERT INTO peering_connection (requester_id, accepter_id, tags)
VALUES ((SELECT id FROM vpc WHERE tags ->> 'name' = '<prefix>-2'),
(SELECT id FROM vpc WHERE tags ->> 'name' = '<prefix>-peering-vpc'),
'{"name": "<prefix>-peering-connection-test"}');
--- checks the state for peering connection is active
SELECT state
FROM peering_connection
WHERE tags ->> 'name' = '<prefix>-peering-connection-test';
--- checks if routes from requester to accepter is added
SELECT destination
FROM route
WHERE vpc_peering_connection_id = (SELECT peering_connection_id
FROM peering_connection
WHERE tags ->> 'name' = '<prefix>-peering-connection-test');
--- changes the tags for the peering connection
UPDATE peering_connection
SET tags = '{"name": "<prefix>-peering-connection-test-changed"}'
WHERE tags ->> 'name' = '<prefix>-peering-connection-test';
--- checks the peering connection tags are changed
SELECT *
FROM peering_connection
WHERE tags ->> 'name' = '<prefix>-peering-connection-test-changed';
--- deletes the peering connection
DELETE
FROM peering_connection
WHERE tags ->> 'name' = '<prefix>-peering-connection-test-changed';
--- checks deletion of the peering connection
SELECT *
FROM peering_connection
WHERE tags ->> 'name' = '<prefix>-peering-connection-test';
--- deletes the subnet
WITH vpc as (
SELECT id
FROM vpc
WHERE is_default = false
AND cidr_block = '191.<randIPBlock>.0.0/16'
)
DELETE FROM subnet
USING vpc
WHERE vpc_id = vpc.id;
--- deletes the vpc
DELETE FROM security_group_rule
WHERE security_group_id = (
SELECT id
FROM security_group
WHERE vpc_id = (
SELECT id
FROM vpc
WHERE cidr_block = '191.<randIPBlock>.0.0/16' AND tags ->> 'name' = '<prefix>-2'
)
);
DELETE FROM route_table_association
WHERE vpc_id = (
SELECT id
FROM vpc
WHERE cidr_block = '191.<randIPBlock>.0.0/16' AND tags ->> 'name' = '<prefix>-2'
);
DELETE FROM route_table
WHERE vpc_id = (
SELECT id
FROM vpc
WHERE cidr_block = '191.<randIPBlock>.0.0/16' AND tags ->> 'name' = '<prefix>-2'
);
WITH vpc as (
SELECT id
FROM vpc
WHERE cidr_block = '191.<randIPBlock>.0.0/16' AND tags ->> 'name' = '<prefix>-2'
)
DELETE FROM security_group
USING vpc
WHERE vpc_id = vpc.id;

DELETE FROM vpc
WHERE cidr_block = '191.<randIPBlock>.0.0/16';

Manage Elastic IPs and NAT Gateways

--- installs the vpc module
SELECT * FROM iasql_install('<modules>');
--- adds a new vpc
INSERT INTO vpc (cidr_block, tags, enable_dns_hostnames, enable_dns_support)
VALUES ('192.<randIPBlock>.0.0/16', '{"name":"<prefix>-1"}', true, true);
--- check vpc is available
SELECT * FROM vpc WHERE cidr_block='192.<randIPBlock>.0.0/16' AND state='available';
--- check vpc has the right attributes
SELECT * FROM vpc WHERE tags ->> 'name' = '<prefix>-1' AND enable_dns_hostnames and enable_dns_support;
--- adds a subnet
INSERT INTO subnet (availability_zone, vpc_id, cidr_block)
SELECT (SELECT name FROM availability_zone WHERE region = '<region>' LIMIT 1), id, '192.<randIPBlock>.0.0/16'
FROM vpc
WHERE is_default = false
AND cidr_block = '192.<randIPBlock>.0.0/16';
--- adds a new elastic ip
INSERT INTO elastic_ip (tags)
VALUES ('{"name": "<eip>"}');
--- check elastic ip count
SELECT * FROM elastic_ip WHERE tags ->> 'name' = '<eip>';
--- adds a private nat gateway
INSERT INTO nat_gateway (connectivity_type, subnet_id, tags)
SELECT 'private', id, '{"Name":"<ng>"}'
FROM subnet
WHERE cidr_block = '192.<randIPBlock>.0.0/16';
--- checks private nat gateway count
SELECT * FROM nat_gateway WHERE tags ->> 'Name' = '<ng>';
--- adds a public nat gateway with existing elastic ip
INSERT INTO nat_gateway (connectivity_type, subnet_id, tags, elastic_ip_id)
SELECT 'public', subnet.id, '{"Name":"<pubNg1>"}', elastic_ip.id
FROM subnet, elastic_ip
WHERE cidr_block = '192.<randIPBlock>.0.0/16' AND elastic_ip.tags ->> 'name' = '<eip>';
--- checks public nat gateway with existing elastic ip count
SELECT * FROM nat_gateway WHERE tags ->> 'Name' = '<pubNg1>';
--- adds a public nat gateway with no existing elastic ip
INSERT INTO nat_gateway (connectivity_type, subnet_id, tags)
SELECT 'public', subnet.id, '{"Name":"<pubNg2>"}'
FROM subnet
WHERE cidr_block = '192.<randIPBlock>.0.0/16';
--- checks public nat gateway with no existing elastic ip count
SELECT * FROM nat_gateway WHERE tags ->> 'Name' = '<pubNg2>';
--- checks elastic IP count
SELECT * FROM elastic_ip WHERE tags ->> 'Name' = '<pubNg2>';
--- updates a elastic ip
UPDATE elastic_ip
SET tags = '{"name": "<eip>", "updated": "true"}'
WHERE tags ->> 'name' = '<eip>';
--- check elastic ip count
SELECT * FROM elastic_ip WHERE tags ->> 'name' = '<eip>';
--- updates a public nat gateway with existing elastic ip to be private
UPDATE nat_gateway
SET elastic_ip_id = NULL, connectivity_type = 'private'
WHERE nat_gateway.tags ->> 'Name' = '<pubNg1>';
--- checks public nat gateway with existing elastic ip to be private count
SELECT * FROM nat_gateway WHERE tags ->> 'Name' = '<pubNg1>';
--- updates a public nat gateway with no existing elastic ip
UPDATE nat_gateway
SET elastic_ip_id = elastic_ip.id, tags = '{"Name": "<pubNg2>", "updated": "true"}'
FROM elastic_ip
WHERE nat_gateway.tags ->> 'Name' = '<pubNg2>' AND elastic_ip.tags ->> 'name' = '<eip>';
--- checks public nat gateway with no existing elastic ip count
SELECT * FROM nat_gateway WHERE tags ->> 'Name' = '<pubNg2>';
--- deletes a public nat gateways
DELETE FROM nat_gateway
WHERE tags ->> 'Name' = '<pubNg1>' OR tags ->> 'Name' = '<pubNg2>';
--- checks public nat gateways count
SELECT * FROM nat_gateway WHERE tags ->> 'Name' = '<pubNg1>' OR tags ->> 'Name' = '<pubNg2>';
--- deletes a elastic ip created by the nat gateway
DELETE FROM elastic_ip
WHERE tags ->> 'Name' = '<pubNg2>';
--- deletes a elastic ip
DELETE FROM elastic_ip
WHERE tags ->> 'name' = '<eip>';
--- deletes a private nat gateway
DELETE FROM nat_gateway
WHERE tags ->> 'Name' = '<ng>';
--- deletes the subnet
WITH vpc as (
SELECT id
FROM vpc
WHERE is_default = false
AND cidr_block = '192.<randIPBlock>.0.0/16'
)
DELETE FROM subnet
USING vpc
WHERE vpc_id = vpc.id;
--- deletes the vpc
DELETE FROM security_group_rule
WHERE security_group_id = (
SELECT id
FROM security_group
WHERE vpc_id = (
SELECT id
FROM vpc
WHERE cidr_block = '192.<randIPBlock>.0.0/16'
)
);
DELETE FROM route_table_association
WHERE vpc_id = (
SELECT id
FROM vpc
WHERE cidr_block = '192.<randIPBlock>.0.0/16'
);
DELETE FROM route
WHERE route_table_id = (
SELECT id
FROM route_table
WHERE vpc_id = (
SELECT id
FROM vpc
WHERE cidr_block = '192.<randIPBlock>.0.0/16'
)
);
DELETE FROM route_table
WHERE vpc_id = (
SELECT id
FROM vpc
WHERE cidr_block = '192.<randIPBlock>.0.0/16'
);
WITH vpc as (
SELECT id
FROM vpc
WHERE cidr_block = '192.<randIPBlock>.0.0/16'
)
DELETE FROM security_group
USING vpc
WHERE vpc_id = vpc.id;

DELETE FROM vpc
WHERE cidr_block = '192.<randIPBlock>.0.0/16';

Manage Endpoint Gateways

--- installs the vpc module
SELECT * FROM iasql_install('<modules>');
--- adds a new vpc
INSERT INTO vpc (cidr_block, tags, enable_dns_hostnames, enable_dns_support)
VALUES ('192.<randIPBlock>.0.0/16', '{"name":"<prefix>-1"}', true, true);
--- adds a subnet
INSERT INTO subnet (availability_zone, vpc_id, cidr_block)
SELECT (SELECT name FROM availability_zone WHERE region = '<region>' LIMIT 1), id, '192.<randIPBlock>.0.0/16'
FROM vpc
WHERE is_default = false
AND cidr_block = '192.<randIPBlock>.0.0/16';
--- adds a new s3 endpoint gateway
INSERT INTO endpoint_gateway (service, vpc_id, tags)
SELECT 's3', id, '{"Name": "<s3VpcEndpoint>"}'
FROM vpc
WHERE is_default = false
AND cidr_block = '192.<randIPBlock>.0.0/16';
--- checks endpoint gateway count
SELECT * FROM endpoint_gateway WHERE tags ->> 'Name' = '<s3VpcEndpoint>';
--- checks endpoint gateway count
SELECT * FROM endpoint_gateway WHERE tags ->> 'Name' = '<s3VpcEndpoint>';
--- updates a endpoint gateway policy
UPDATE endpoint_gateway
SET policy = '<s3VpcEndpoint>'
WHERE tags ->> 'Name' = '';
--- checks endpoint_gateway policy update
SELECT * FROM endpoint_gateway WHERE tags ->> 'Name' = '<s3VpcEndpoint>';
--- updates a endpoint gateway tags
UPDATE endpoint_gateway
SET tags = '{"Name": "<s3VpcEndpoint>", "updated": "true"}'
WHERE tags ->> 'Name' = '<s3VpcEndpoint>';
--- checks endpoint_gateway policy update
SELECT * FROM endpoint_gateway WHERE tags ->> 'Name' = '<s3VpcEndpoint>';
--- deletes a endpoint_gateway
DELETE FROM endpoint_gateway
WHERE tags ->> 'Name' = '<dynamodbVpcEndpoint>';
--- deletes the subnet
WITH vpc as (
SELECT id
FROM vpc
WHERE is_default = false
AND cidr_block = '192.<randIPBlock>.0.0/16'
)
DELETE FROM subnet
USING vpc
WHERE vpc_id = vpc.id;
--- deletes the vpc
DELETE FROM security_group_rule
WHERE security_group_id = (
SELECT id
FROM security_group
WHERE vpc_id = (
SELECT id
FROM vpc
WHERE cidr_block = '192.<randIPBlock>.0.0/16'
)
);
DELETE FROM route_table_association
WHERE vpc_id = (
SELECT id
FROM vpc
WHERE cidr_block = '192.<randIPBlock>.0.0/16'
);
DELETE FROM route
WHERE route_table_id = (
SELECT id
FROM route_table
WHERE vpc_id = (
SELECT id
FROM vpc
WHERE cidr_block = '192.<randIPBlock>.0.0/16'
)
);
DELETE FROM route_table
WHERE vpc_id = (
SELECT id
FROM vpc
WHERE cidr_block = '192.<randIPBlock>.0.0/16'
);
WITH vpc as (
SELECT id
FROM vpc
WHERE cidr_block = '192.<randIPBlock>.0.0/16'
)
DELETE FROM security_group
USING vpc
WHERE vpc_id = vpc.id;

DELETE FROM vpc
WHERE cidr_block = '192.<randIPBlock>.0.0/16';

Manage Endpoint Interfaces

--- installs the vpc module
SELECT * FROM iasql_install('<modules>');
--- adds a new vpc
INSERT INTO vpc (cidr_block, tags, enable_dns_hostnames, enable_dns_support)
VALUES ('192.<randIPBlock>.0.0/16', '{"name":"<prefix>-1"}', true, true);
--- adds a subnet
INSERT INTO subnet (availability_zone, vpc_id, cidr_block)
SELECT (SELECT name FROM availability_zone WHERE region = '<region>' LIMIT 1), id, '192.<randIPBlock>.0.0/16'
FROM vpc
WHERE is_default = false
AND cidr_block = '192.<randIPBlock>.0.0/16';
--- adds a new lambda endpoint interface
INSERT INTO endpoint_interface (service, vpc_id, tags)
SELECT 'lambda', id, '{"Name": "<lambdaVpcEndpoint>"}'
FROM vpc
WHERE is_default = false
AND cidr_block = '192.<randIPBlock>.0.0/16';
--- checks endpoint interface count
SELECT * FROM endpoint_interface WHERE tags ->> 'Name' = '<lambdaVpcEndpoint>';
--- checks endpoint interface default subnet count
SELECT * FROM endpoint_interface_subnets WHERE endpoint_interface_id=(SELECT id FROM endpoint_interface WHERE tags ->> 'Name' = '<lambdaVpcEndpoint>');
--- queries the vpcs to confirm the record is present
SELECT * FROM vpc WHERE cidr_block = '192.<randIPBlock>.0.0/16';
--- updates a endpoint interface policy
UPDATE endpoint_interface
SET policy = '<lambdaVpcEndpoint>'
WHERE tags ->> 'Name' = '';
--- checks endpoint_interface count
SELECT * FROM endpoint_interface WHERE tags ->> 'Name' = '<lambdaVpcEndpoint>';
--- updates a endpoint interface tags
UPDATE endpoint_interface
SET tags = '{"Name": "<lambdaVpcEndpoint>", "updated": "true"}'
WHERE tags ->> 'Name' = '<lambdaVpcEndpoint>';
--- checks endpoint_interface count
SELECT * FROM endpoint_interface WHERE tags ->> 'Name' = '<lambdaVpcEndpoint>';
--- removes the current endpoint subnets
DELETE FROM endpoint_interface_subnets where endpoint_interface_id=(SELECT id FROM endpoint_interface WHERE tags ->> 'Name' = '<lambdaVpcEndpoint>');
--- adds new endpoint subnet
INSERT INTO endpoint_interface_subnets (endpoint_interface_id, subnet_id) VALUES ((SELECT id FROM endpoint_interface WHERE tags ->> 'Name' = '<lambdaVpcEndpoint>' LIMIT 1),
(SELECT subnet.id FROM subnet INNER JOIN vpc ON vpc.id=subnet.vpc_id WHERE subnet.cidr_block='192.<randIPBlock>.0.0/16' LIMIT 1));
--- checks endpoint_interface subnet count
SELECT * FROM endpoint_interface_subnets WHERE endpoint_interface_id=(SELECT id FROM endpoint_interface WHERE tags ->> 'Name' = '<lambdaVpcEndpoint>');
--- deletes a endpoint_interface
DELETE FROM endpoint_interface
WHERE tags ->> 'Name' = '<lambdaVpcEndpoint>';
--- deletes the subnet
WITH vpc as (
SELECT id
FROM vpc
WHERE is_default = false
AND cidr_block = '192.<randIPBlock>.0.0/16'
)
DELETE FROM subnet
USING vpc
WHERE vpc_id = vpc.id;
--- deletes the vpc
DELETE FROM security_group_rule
WHERE security_group_id = (
SELECT id
FROM security_group
WHERE vpc_id = (
SELECT id
FROM vpc
WHERE cidr_block = '192.<randIPBlock>.0.0/16'
)
);
DELETE FROM route_table_association
WHERE vpc_id = (
SELECT id
FROM vpc
WHERE cidr_block = '192.<randIPBlock>.0.0/16'
);
DELETE FROM route
WHERE route_table_id = (
SELECT id
FROM route_table
WHERE vpc_id = (
SELECT id
FROM vpc
WHERE cidr_block = '192.<randIPBlock>.0.0/16'
)
);
DELETE FROM route_table
WHERE vpc_id = (
SELECT id
FROM vpc
WHERE cidr_block = '192.<randIPBlock>.0.0/16'
);
WITH vpc as (
SELECT id
FROM vpc
WHERE cidr_block = '192.<randIPBlock>.0.0/16'
)
DELETE FROM security_group
USING vpc
WHERE vpc_id = vpc.id;

DELETE FROM vpc
WHERE cidr_block = '192.<randIPBlock>.0.0/16';

Manage Routing tables

--- installs the vpc module
SELECT * FROM iasql_install('aws_vpc');
--- checks each vpc has at least a route table
SELECT vpc.vpc_id, COUNT(rt) as rt_count
FROM vpc
LEFT JOIN route_table rt on vpc.id = rt.vpc_id
GROUP BY vpc.vpc_id;
--- creates a new vpc
INSERT INTO vpc (cidr_block, tags, region)
VALUES ('10.<randIPBlock>.0.0/16', '{"name":"<prefix>"}', '<region>');
--- adds a subnet to the vpc
INSERT INTO subnet (availability_zone, vpc_id, cidr_block, region)
SELECT (SELECT name FROM availability_zone WHERE region = '<region>' ORDER BY 1 DESC LIMIT 1), id, '10.<randIPBlock>.1.0/24', '<region>'
FROM vpc
WHERE tags ->> 'name' = '<prefix>';
--- adds a new route table to the vpc in the region
INSERT INTO route_table (vpc_id, tags, region)
VALUES ((SELECT id FROM vpc WHERE tags ->> 'name' = '<prefix>'), '{"name":"<prefix>"}',
'<region>');
--- confirms that the default route is created
SELECT *
FROM route
WHERE route_table_id = (SELECT id FROM route_table WHERE tags ->> 'name' = '<prefix>');
--- checks there is no association for the route table
SELECT *
FROM route_table_association
WHERE route_table_id = (SELECT id FROM route_table WHERE tags ->> 'name' = '<prefix>');
--- associates the route table to the subnet
INSERT INTO route_table_association (route_table_id, vpc_id, subnet_id)
VALUES ((SELECT id FROM route_table WHERE tags ->> 'name' = '<prefix>'),
(SELECT id FROM vpc WHERE tags ->> 'name' = '<prefix>'),
(SELECT id
FROM subnet
WHERE cidr_block = '10.<randIPBlock>.1.0/24'
AND availability_zone = (SELECT name FROM availability_zone WHERE region = '<region>' ORDER BY 1 DESC LIMIT 1)));
--- checks whether the route table is associated to the subnet
SELECT *
FROM route_table_association
WHERE route_table_id = (SELECT id FROM route_table WHERE tags ->> 'name' = '<prefix>');
--- deletes the association
DELETE
FROM route_table_association
WHERE route_table_id = (SELECT id FROM route_table WHERE tags ->> 'name' = '<prefix>');
--- deletes the route table
DELETE
FROM route_table
WHERE tags ->> 'name' = '<prefix>';
--- deletes the subnet and vpc
DELETE
FROM subnet
WHERE vpc_id = (SELECT id FROM vpc WHERE tags ->> 'name' = '<prefix>');

DELETE
FROM vpc
WHERE tags ->> 'name' = '<prefix>';