This CDK construct library makes it possible to create databases, schemas, and roles in an Aurora Serverless (v1 and v2 are supported), RDS Database Cluster or Database Instance created in that stack.
This construct library is intended to be used in enterprise environments, and works in isolated subnets.
npm i cdk-rds-sql
First setup your VPC and create your cluster:
import { Duration, RemovalPolicy } from "aws-cdk-lib"
import * as ec2 from "aws-cdk-lib/aws-ec2"
import * as rds from "aws-cdk-lib/aws-rds"
const vpc = ec2.Vpc.fromLookup(this, "Vpc", {
vpcId: this.node.tryGetContext("vpc-id"),
})
const cluster = new rds.ServerlessCluster(this, "Cluster", {
vpc: vpc,
vpcSubnets: {
subnetType: ec2.SubnetType.PRIVATE_ISOLATED,
},
engine: rds.DatabaseClusterEngine.AURORA_POSTGRESQL,
parameterGroup: rds.ParameterGroup.fromParameterGroupName(
this,
"ParameterGroup",
"default.aurora-postgresql11"
),
removalPolicy: RemovalPolicy.DESTROY,
scaling: {
autoPause: Duration.minutes(60),
minCapacity: rds.AuroraCapacityUnit.ACU_2,
maxCapacity: rds.AuroraCapacityUnit.ACU_2,
},
})
Then create a provider which will connect to your database. For a cluster:
import { Provider } from "cdk-rds-sql"
const provider = new Provider(this, "Provider", {
vpc: vpc,
cluster: cluster,
secret: cluster.secret!,
})
For an instance:
import { Provider } from "cdk-rds-sql"
const provider = new Provider(this, "Provider", {
vpc: vpc,
instance: instance,
secret: cluster.secret!,
})
The provider will setup a lambda, which normally lives in the same VPC as the database. You can give a different VPC, as long as that VPC has access to the VPC of the database. Only the provider lambda will talk to your database.
The provider will by default use the private isolated subnet of the VPC. This is a breaking change from the past, where the provider used the default strategy, which may not have been the private isolated subnet. But from an enterprise security point of view having third party code run in an isolated network by default is better.
Your isolated network must have a VPC endpoint to AWS Secrets Manager and possibly KMS as well. If you want to use a subnet with egress access in case you have no such VPC endpoints, specify the subnet as follows:
import { Provider } from "cdk-rds-sql"
const provider = new Provider(this, "Provider", {
vpc: vpc,
vpcSubnet: {
subnetType: ec2.SubnetType.PRIVATE_WITH_EGRESS,
},
cluster: cluster,
secret: cluster.secret!,
})
Create a postgres role (user) for a cluster as follows:
import { Role } from "cdk-rds-sql"
const role = new Role(this, "Role", {
provider: provider,
roleName: "myrole",
databaseName: "mydb",
})
This will automatically create a secret just like ServerlessCluster
does, with all the connection info needed for this user. It's secret value is a JSON like:
{
"dbClusterIdentifier": "teststack-clustereb1186t9-sh4wpqfdyfvu",
"password": "very-long-and-boring",
"dbname": "mydb",
"engine": "postgres",
"port": 5432,
"host": "teststack-clustereb1186t9-sh4wpqfdyfvu.cluster-cgudolabssna.us-east-1.rds.amazonaws.com",
"username": "myrole"
}
You can access the secret via role.secret
.
Instead of databaseName
you can also specify database
to reference
an existing database. The role will not be created until the database
has been created.
Create a database as follows:
import { Database } from "cdk-rds-sql"
const database = new Database(this, "Database", {
provider: provider,
databaseName: "mydb",
})
You can provide an owner, which makes it easy to create database owned by a new user:
const database = new Database(this, "Database", {
provider: provider,
databaseName: "mydb",
owner: role,
})
Create a schema in the default database as follows:
import { Schema } from "cdk-rds-sql"
new Schema(this, "Schema", {
provider: provider,
schemaName: "myschema",
})
Or in another database:
const database = new Database(this, "Database", {
provider: provider,
databaseName: "mydb",
})
new Schema(this, "Schema", {
provider: provider,
schemaName: "myschema",
databaseName: database.databaseName,
})
One may need a role permitted for using schema:
new Schema(this, "Schema", {
provider: provider,
schemaName: "myschema",
databaseName: database.databaseName,
role: role,
})
You can insert arbitrary SQL into your database with the Sql
construct:
import { Sql } from "cdk-rds-sql"
const sql = new Sql(this, "Sql", {
provider: provider,
database: database,
statement: "create table t (i int)",
})
Create a table if it does not exist, and grant a role privileges:
const sql = new Sql(this, "Sql", {
provider: provider,
database: database,
statement: `
create table if not exists t (i int);
grant select on t to myrole;
`,
})
Rollback sql on stack deletion:
const sql = new Sql(this, "Sql", {
provider: provider,
database: database,
statement: `
create table if not exists t (i int);
grant select on t to myrole;
`,
rollback: `
DO $$BEGIN
IF EXISTS (select from pg_database WHERE datname = 't') THEN
IF EXISTS (select from pg_catalog.pg_roles WHERE rolname = 'myrole') THEN
revoke select t from myrole;
END IF;
drop table t;
END IF;
END$$;
`
})
Note that there is no synchronisation between various Sql
constructs, in particular the order in your code does not determine
the order in which your SQL is executed. This happens in parallel,
unless you specify an explicit dependency via sql.node.addDepency()
.
There are a lot of concerns when using Sql
:
Sql
construct the rollback is executed if specifiedSql
they must be removed via rollback to succesfully remove the roleSql
constructs has less than 5 minutes to execute
its work.This library manages dependencies, there is no need to specify
dependencies except possibly for Sql
constructs.
This code is managed by projen. In addition pre-commit is used.
So after git clone and npm ci
you would do:
pre-commit install --install-hooks --hook-type commit-msg --hook-type pre-commit
to install the pre-commit hooks.
Test code via projen with:
npx projen test
You can run the sample stack with:
npx cdk deploy --context vpc-id=vpc-0123456789 TestServerlessV1Stack
Or for v2:
npx cdk deploy TestServerlessV2Stack
If you want to use an existing vpc:
npx cdk deploy --context vpc-id=vpc-0123456789 TestServerlessV2Stack