PostgreSQL SSL Support

Postgres servers operating on Stable-v4 2.0.123 or Stable-v5 3.0.30 stacks can support SSL based connections to the database. This document describes how to verify the server is SSL ready, as well as the steps to prepare, use, and enforce this feature. Topics include:

• Overview
• Verify SSL is Enabled
• Verify SSL Connections
• Enforce SSL Connections
• Keys for Named Users
• Key Maintenance

Overview

SSL connections are designed to encrypt client/server traffic to and from your database for increased security. Your database components exist with your application within a virtual network, and the database communication ports by default are not externally accessible. The SSL feature is useful for customers that have specific compliance concerns for network traffic, or for cases where a security exception has been used to expose the database port in the AWS firewall.

When you upgrade to a stack that includes this feature the cookbooks run performed during the upgrade will generate self-signed SSL keys on the database master and distribute these keys automatically to the other databases within the environment. The database master will then generate a key for our administrative users as well as for your main application user; the admin keys will be distributed to all database instances in the environment, and the application keys will be distributed as needed to the home directory of your application user.

Note: The database service must be restarted before Postgres will be able to validate your SSL keys.

Verify SSL is Enabled

1. Connect via SSH to the db_master instance
2. Assume the role of the administrative user sudo su -
3. Check that ssl is enabled with psql -c 'show ssl'
   • If the value of ssl is set to on you are now running with SSL enabled, you can type exit and move on to Verifying SSL Connectivity.
4. Verify the configuration file for Postgres has the ca file configured cat /db/postgresql/*/data/postgresql.conf | grep 'ssl'.
5. If the configuration file shows SSL is on and the server indicated it was off you'll need to Restart PostgreSQL. This will result in a brief downtime for your application so you'll probably want to do this during a maintenance window.
6. Since SSL is not enabled in the configuration file, you'll need to upgrade your environment stack to at least Stable-v4 2.0.123 or Stable-v5 3.0.30.

Verify SSL Connections

Use the user, host, and database name from your database.yml to connect to the database:

psql -U <user> -h <hostname> <dbname>

If SSL is enabled and your key has been authenticated you will see a line like the following in the output:

SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: on)

If you do not see this line make sure you are supplying the DNS hostname or IP in the psql command

Enforce SSL Connections

To enforce that PostgreSQL uses SSL authentication along with Password Authentication you'll need to customize the /db/postgresql/#{major_version}/custom_pg_hba.conf on your database instances. Once this file has been set manually it will persist with the volume snapshot to any descendants of that database; so you don't necessarily need to set this file with custom chef.

Postgres provides several options for configuring this file; but a common configuration using keys for authentication is:

hostssl    all             deploy  172.31.0.0/16              md5 clientcert=1
hostssl    all             all             ::1/128                 md5 clientcert=1

hostssl    replication     postgres        172.31.0.0/16              md5 clientcert=1
hostssl    all             postgres        172.31.0.0/16              md5 clientcert=1

hostnossl    all             deploy  172.31.0.0/16              reject
hostnossl    all             all             ::1/128                 reject

hostnossl    replication     postgres        172.31.0.0/16              reject
hostnossl    all             postgres        172.31.0.0/16              reject

You'll need to change the CIDR Address blocks to match your actual deployment; you can determine the CIDR to use by running: cat /db/postgresql/*/data/pg_hba.conf | grep host.*replication|awk '{print $4}'

Once you've set the desired configuration file you need to run chef by clicking the "Apply" button on the cloud dashboard. When chef runs it will take the contents of this file and insert it into the top of the pg_hba.conf file and instruct the server to reload so your new settings take effect.

Keys for Named Users

If you have created additional OS level (Linux) named users on your instances you can generate and distribute database SSL keys for these users. In order to use this approach you'll also need to first add a Postgres user with a matching username and set its privileges as appropriate. Once the users are in place you can:

1. Generate a new key with the command: sudo -i /engineyard/bin/db_user_ssl_keys.sh [expire_in_days]
   • [expire_in_days] will default to 5 years (1825) unless otherwise specified
   • the password is actually only a temporary value and is removed from the key for ease of use
2. Create a custom chef cookbook that runs the following command for any users you have added /engineyard/bin/remote_key_copy.sh <username>

Key Maintenance

It is possible to generate new server and user keys for your environment. This will require a maintenance downtime, since once you distribute the new keys a database restart is required. Use the following steps:

1. Connect via SSH to your database master and assume the role of the super user.
2. Run the following shell commands

   mkdir -p /db/old_keys/server
   mkdir -p /db/old_keys/user 
   mv /db/postgresql/data/root.crt /db/old_keys/server/ 
   mv /db/postgresql/data/server.crt /db/old_keys/server/ 
   mv /db/postgresql/*/data/server.key /db/old_keys/server/ 
   mv /db/postgresql/keygen/postgres /db/old_keys/user/ 
   mv /db/postgresql/keygen/deploy /db/old_keys/user/

3. Run /engineyard/bin/db_server_ssl_keys.sh
4. Restart PostgreSQL
5. Click "Apply" on the cloud dashboard