Enable encrypted connections for PostgreSQL DB instances in Amazon RDS - AWS Prescriptive Guidance
SummaryPrerequisites and limitationsArchitectureToolsBest practicesEpicsTroubleshootingRelated resources

Enable encrypted connections for PostgreSQL DB instances in Amazon RDS

Created by Rohit Kapoor (AWS)

Environment: PoC or pilot

Technologies: Databases; Networking; Security, identity, compliance

Workload: Open-source

AWS services: Amazon RDS; Amazon Aurora

Summary

Amazon Relational Database Service (Amazon RDS) supports SSL encryption for PostgreSQL DB instances. Using SSL, you can encrypt a PostgreSQL connection between your applications and your Amazon RDS for PostgreSQL DB instances. By default, Amazon RDS for PostgreSQL uses SSL/TLS and expects all clients to connect by using SSL/TLS encryption. Amazon RDS for PostgreSQL supports TLS versions 1.1 and 1.2.

This pattern describes how you can enable encrypted connections for an Amazon RDS for PostgreSQL DB instance. You can use the same process to enable encrypted connections for Amazon Aurora PostgreSQL-Compatible Edition.

Prerequisites and limitations

Architecture

Enabling encrypted connections for PostgreSQL DB instances in Amazon RDS

Tools

  • pgAdmin is an open-source administration and development platform for PostgreSQL. You can use pgAdmin on Linux, Unix, macOS, and Windows to manage your database objects in PostgreSQL 10 and later.

  • PostgreSQL editors provide a more user-friendly interface to help you create, develop, and run queries, and to edit code according to your requirements.

Best practices

  • Monitor unsecure database connections.

  • Audit database access rights.

  • Make sure that backups and snapshots are encrypted at rest.

  • Monitor database access.

  • Avoid unrestricted access groups.

  • Enhance your notifications with Amazon GuardDuty.

  • Monitor policy adherence regularly.

Epics

TaskDescriptionSkills required

Load a trusted certificate to your computer.

To add certificates to the Trusted Root Certification Authorities store for your computer, follow these steps. (These instructions use Window Server as a example.)

  1. In Windows Server, choose Start, Run, and then type mmc.

  2. In the console, choose File, Add/Remove Snap-in.

  3. Under Available snap-ins, choose Certificates, and then choose Add.

  4. Under This snap-in will always manage certificates for, choose Computer account, Next.

  5. Choose Local computer, Finish.

  6. If you have no more snap-ins to add to the console, choose OK.

  7. In the console tree, double-click Certificates.

  8. Right-click Trusted Root Certification Authorities.

  9. Choose All Tasks, Import to import the downloaded certificates.

  10. Follow the steps in the Certificate Import Wizard.

DevOps engineer, Migration engineer, DBA
TaskDescriptionSkills required

Create a parameter group and set the rds.force_ssl parameter.

If the PostgreSQL DB instance has a custom parameter group, edit the parameter group and change rds.force_ssl to 1.

If the DB instance uses the default parameter group that doesn’t have rds.force_ssl enabled, create a new parameter group. You can modify the new parameter group by using the Amazon RDS API or manually as in the following instructions.

To create a new parameter group:

  1. Sign in to the AWS Management console and open the Amazon RDS console for the AWS Region that hosts the DB instance.

  2. In the navigation pane, choose Parameter groups.

  3. Choose Create parameter group, and set the following values: 

    • For Parameter group family, choose postgres14.

    • For Group name, type pgsql-<database_instance>-ssl.

    • For Description, enter a free-form description for the parameter group you’re adding.

    • Choose Create.

  4. Choose the parameter group that you created.

  5. From Parameter group actions, choose Edit.

  6. Find rds.force_ssl and change its setting to 1.

    Note: Conduct client-side testing before changing this parameter.

  7. Choose Save changes.

To associate the parameter group with your PostgreSQL DB instance:

  1. On the Amazon RDS console, in the navigation pane, choose Databases, and then choose the PostgreSQL DB instance.

  2. Choose Modify.

  3. Under Additional configuration, choose the new parameter group, and then choose Continue.

  4. Under Schedule modifications, choose Apply immediately.

  5. Choose Modify DB instance.

For more information, see the Amazon RDS documentation.

DevOps engineer, Migration engineer, DBA

Force SSL connections.

Connect to the Amazon RDS for PostgreSQL DB instance. Connection attempts that don’t use SSL are rejected with an error message. For more information, see the Amazon RDS documentation.

DevOps engineer, Migration engineer, DBA
TaskDescriptionSkills required

Install the SSL extension.

  1. Launch a psql or pgAdmin connection as a DBA.

  2. Call the ssl_is_used() function to determine if SSL is being used.

    select ssl_is_used();

    The function returns t if the connection is using SSL; otherwise, it returns f.

  3. Install the SSL extension.

    create extension sslinfo;
show ssl;
select ssl_cipher();

For more information, see the Amazon RDS documentation.

DevOps engineer, Migration engineer, DBA
TaskDescriptionSkills required

Configure a client for SSL.

By using SSL, you can start the PostgreSQL server with support for encrypted connections that use TLS protocols. The server listens for both standard and SSL connections on the same TCP port, and negotiates with any connecting client on whether to use SSL. By default, this is a client option.

If you’re using the psql client:

  1. Make sure that the Amazon RDS certificate has been loaded to your local computer.

  2. Launch an SSL client connection by adding the following:

    psql postgres -h SOMEHOST.amazonaws.com -p 8192 -U someuser sslmode=verify-full sslrootcert=rds-ssl-ca-cert.pem
select ssl_cipher();

For other PostgreSQL clients:

  • Modify the respective application public key parameter. This might be available as an option, as part of your connection string, or as a property on the connection page in GUI tools. 

Review the following pages for these clients:

DevOps engineer, Migration engineer, DBA

Troubleshooting

IssueSolution

Cannot download the SSL certificate.

Check your connection to the website, and retry downloading the certificate to your local computer.

Related resources