PostgreSQL, encryption and AWS RDS instance

Sometime ago, I wrote about installing postgresql on an encrypted volume and running postgresql on an ebs volume encrypted with keys managed by AWS KMS. Today let's see how we can quickly deploy postgresql on an RDS instance with both encryption at-rest and in-transit.

Creating the encrypted RDS instance

First we create an RDS instance. The AWS RDS documentation hints that we must pass an --storage-encrypted flag to enable encryption of the underlying EBS volume.

Despite the awscli documentation stating otherwise, we must specify the size of the underlying EBS volume. The documentation also states that RDS only supports standard | gp2 | io1 out of the various options for block storages. It is unclear it is a shortcoming of the documentation or a real technical issue. Either way, we end-up with a command like:

$ aws rds create-db-instance \
    --availability-zone us-west-2 \
    --db-instance-class db.t2.medium \
    --allocated-storage 20 \
    --storage-type gp2 \
    --storage-encrypted \
    --db-instance-identifier mytestdb \
    --engine postgres \
    --master-username postgres \
    --master-user-password **********

We specify the availability zone so that the database does not randomly end up in a different region than our web servers are running. If the database and web servers were in a different region, we would need to have the RDS instance resolve to a public address in order for the web servers to communicate with it. Keeping both in the same region enable to communicate on a private subnet.

Specifying the security group for the RDS instance was also some trial and error. Passing --db-security-groups GroupName lead to an error DB Security Groups can only be associated with VPC DB Instances using API versions 2012-01-15 through 2012-09-17, which in insight means the command line flag is deprecated. I didn't find out before trying both --vpc-security-group-ids and --db-security-groups together (leading to DB Security Groups and Vpc Security Groups cannot both be provided) and --vpc-security-group-ids GroupName (leading to Invalid security group) before figuring out you need to use

--vpc-security-group-ids GroupId

An another bit of interesting user interface design is that despite all the command line options that definitely make you believe RDS provisions EBS volumes and EC2 instances, the RDS instance does not show up in the EC2 console panel.

Encryption in transit

Encryption at-rest was pretty easy. Let's now move to secure the connection to the database with TLS.

The AWS documentation gives us multiple options.

We want to enforce all connections to the database to enable TLS so the only viable option seems to set rds.force_ssl = 1.

After going through some more unclear documentation, the rds.force_ssl flag must be set on the database parameter group. So let's review our configuration.

$ aws rds describe-db-instances --db-instance-identifier mytestdb
{
    "DBInstance": {
        {
...
            "DBParameterGroups": [
                {
                    "DBParameterGroupName": "default.postgres9.6",
                    "ParameterApplyStatus": "in-sync"
                }
            ],
...
        }
    }
}
$ aws rds describe-db-parameters --db-parameter-group-name default.postgres9.6
{
    "Parameters": [
...
        {
            "ParameterName": "rds.force_ssl",
            "ParameterValue": "0",
            "Description": "Force SSL connections.",
            "Source": "system",
            "ApplyType": "static",
            "DataType": "boolean",
            "AllowedValues": "0,1",
            "IsModifiable": true,
            "ApplyMethod": "pending-reboot"
        },
...
    ]
}

As it turns out, RDS did not create a default db parameters group for us as it creates a default VPC, default security group, etc. The default db parameters group seems to be shared across all AWS customers and thus cannot be modified directly. We need to create our own db parameter group.

$ aws rds create-db-parameter-group --db-parameter-group-name mytestdb \
    --db-parameter-group-family postgres9.6 \
    --description "mytestdb parameter group for postgres9.6"
{
    "DBParameterGroup": {
        "DBParameterGroupName": "mytestdb",
        "DBParameterGroupFamily": "postgres9.6",
        "Description": "mytestdb parameter group for postgres9.6",
        "DBParameterGroupArn": "arn:aws:rds:********:pg:mytestdb"
    }
}

We can then update the force_ssl flag. For some reason we need to specify the ApplyMethod value as well (otherwise we get an error).

$ aws rds modify-db-parameter-group --db-parameter-group-name mytestdb \
    --parameters ParameterName=rds.force_ssl,ParameterValue=1,ApplyMethod=pending-reboot
{
    "DBParameterGroupName": "mytestdb"
}

We now change the parameter group for our RDS instance and reboot it.

$ aws rds modify-db-instance --db-instance-identifier mytestdb \
    --db-parameter-group-name mytestdb
...
        "DBParameterGroups": [
            {
                "DBParameterGroupName": "mytestdb",
                "ParameterApplyStatus": "applying"
            }
        ],
...

$ aws rds reboot-db-instance --db-instance-identifier mytestdb
{
    "DBInstance": {
...
        "Endpoint": {
            "Address": "mytestdb.***************.rds.amazonaws.com",
...

The latest versions of the postgresql client enables us to connect to an database through a URI. It is straightforward to check the setup is completed correctly.

$ psql -U postgres "postgresql://mytestdb.***************.rds.amazonaws.com"
Password for user postgres:
psql (9.6.3, server 9.6.2)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

Now that the RDS instance is up running a postgresql server, all SQL and postgres specific commands apply to create databases, restore content, etc.