aws_security_group

Components

Tables

    security_group

    security_group_rule

Code examples

Manage Security groups

--- installs the security group module
SELECT * FROM iasql_install('<modules>');
--- adds a new security group
INSERT INTO security_group (description, group_name)
VALUES ('Security Group Test', '<prefix>sgtest');
--- check security_group insertion
SELECT *
FROM security_group
WHERE group_name = '<sgName>';
--- adds security group rules
INSERT INTO security_group_rule (is_egress, ip_protocol, from_port, to_port, cidr_ipv4, description, security_group_id)
SELECT true, 'tcp', 443, 443, '0.0.0.0/8', '<prefix>testrule', id
FROM security_group
WHERE group_name = '<prefix>sgtest';
INSERT INTO security_group_rule (is_egress, ip_protocol, from_port, to_port, cidr_ipv6, description, security_group_id)
SELECT false, 'tcp', 22, 22, '::/8', '<prefix>testrule2', id
FROM security_group
WHERE group_name = '<prefix>sgtest';
--- updates the security group rule
UPDATE security_group_rule SET to_port = 8443 WHERE description = '<prefix>testrule';
UPDATE security_group_rule SET to_port = 8022 WHERE description = '<prefix>testrule2';
--- check security_group update
SELECT *
FROM security_group_rule
INNER JOIN security_group ON security_group.id = security_group_rule.security_group_id
WHERE group_name = '<sgName>';
--- updates the security group
UPDATE security_group SET group_name = '<prefix>sgtest2' WHERE group_name = '<prefix>sgtest';
--- adds a new security group rule pointing to itself
INSERT INTO security_group_rule(description, security_group_id, source_security_group, is_egress)
VALUES ('<prefix>sgsourcetestrule', (SELECT id FROM security_group WHERE group_name='<prefix>sgforsource'),
(SELECT id FROM security_group WHERE group_name='<prefix>sgforsource'), false);
--- check security group rule expanded for TCP pointing to itself
SELECT *
FROM security_group_rule
WHERE source_security_group = (SELECT id FROM security_group WHERE group_name='<prefix>sgforsource') AND ip_protocol='tcp';
--- check security group rule expanded for UDP pointing to itself
SELECT *
FROM security_group_rule
WHERE source_security_group = (SELECT id FROM security_group WHERE group_name='<prefix>sgforsource') AND ip_protocol='udp';
--- deletes the source security group rule pointing to itself
DELETE FROM security_group_rule WHERE source_security_group = (SELECT id FROM security_group WHERE group_name='<prefix>sgforsource');
--- check no security group rules remain
SELECT *
FROM security_group_rule
WHERE source_security_group = (SELECT id FROM security_group WHERE group_name='<prefix>sgforsource');
--- deletes the original security group to test source
DELETE FROM security_group WHERE group_name = '<prefix>sgforsource';
--- adds self-referential security groups A and B
INSERT INTO security_group (description, group_name)
VALUES ('Security Group Test A', '<prefix>sgtestA'), ('Security Group Test B', '<prefix>sgtestB');

INSERT INTO security_group_rule(description, security_group_id, source_security_group, is_egress)
VALUES ('<prefix>sgtestA', (SELECT id FROM security_group WHERE group_name='<prefix>sgtestA'),
(SELECT id FROM security_group WHERE group_name='<prefix>sgtestB'), false);

INSERT INTO security_group_rule(description, security_group_id, source_security_group, is_egress)
VALUES ('<prefix>sgtestB', (SELECT id FROM security_group WHERE group_name='<prefix>sgtestB'),
(SELECT id FROM security_group WHERE group_name='<prefix>sgtestA'), false);
--- check that security group and rules are correctly created - tcp, udp, icmp
SELECT sg.id, sgr.source_security_group FROM security_group sg INNER JOIN security_group_rule sgr ON sg.id = sgr.security_group_id WHERE sg.group_name='<prefix>sgtestA' AND sgr.source_security_group=(SELECT id FROM security_group WHERE group_name='<prefix>sgtestB');
--- deletes the source security group rule for A
DELETE FROM security_group_rule WHERE source_security_group = (SELECT id FROM security_group WHERE group_name='<prefix>sgtestA');
--- deletes the source security group rule for B
DELETE FROM security_group_rule WHERE source_security_group = (SELECT id FROM security_group WHERE group_name='<prefix>sgtestB');
--- deletes the source security group for A
DELETE FROM security_group WHERE group_name = '<prefix>sgtestA';
--- deletes the source security group for B
DELETE FROM security_group WHERE group_name = '<prefix>sgtestB';
--- check no security group rules remain
SELECT *
FROM security_group_rule
WHERE source_security_group IN (SELECT id FROM security_group WHERE group_name='<prefix>sgtestA' OR group_name='<prefix>sgtestB');
--- check no security group for source remain
SELECT *
FROM security_group
WHERE group_name = '<prefix>sgtestA' OR group_name='<prefix>sgtestB';