SSL connection to an Amazon Aurora PostgreSQL database from a C++ application using Visual Studio : idk.dev

Your organization may require you to connect to databases using secure SSL connections so all traffic communicating with the database is encrypted. In this post, we provide guidance on how to connect to an Amazon Aurora PostgreSQL database from a C++ application using the libpq library. We show you how to enforce SSL connections to your Aurora PostgreSQL database and connect to this from a C++ application using a secured SSL connection.

You can also apply the same principles to an Amazon RDS for PostgreSQL database.

Prerequisites

You need the following prerequisites:

1. An AWS account.
2. An Aurora PostgreSQL database with SSL enforced.
3. Visual Studio

Enforcing an SSL Connection to an Aurora PostgreSQL database

For this post, we use Aurora PostgreSQL 11.7. We must first enforce SSL connections to our Aurora PostgreSQL database. We need to enable the rds.force_ssl parameter through the parameter groups so we can enforce SSL connections from the server side. If we don’t specify this, the clients may be able to connect to the database server without SSL. The following table summarizes the different SSL modes. For more information, see libpq SSL Support.

To enforce an SSL connection on your database, complete the following steps:

1. On the Amazon RDS console, choose Parameter groups.
2. Choose Create parameter group.

3. On the Create parameter group page, for Parameter group family, choose the version of Aurora PostgreSQL what you’re using.
4. For Type select DB Cluster Parameter Group.
5. For Group name, enter a name for your group.
6. For Description, enter a description
7. Choose Create.
8. On the Parameter groups page, search for your new group and select it.

9. For Parameters, search for ‘force_ssl’ and select it.
10. Choose Edit Parameters.

11. Select the force_ssl parameter and change the Value to 1.
12. Choose Save changes.

13. On the Databases page, select the database for which you want to enforce the SSL connections.
14. Choose Modify.
15. In the Database options section, choose the DB cluster parameter group you just created.

16. Choose Continue.
17. On the next page, under Scheduling of modifications, select when to apply the changes (for this post, we select Apply Immediately.
18. Choose Modify cluster.

If you’re using Aurora PostgreSQL version 11 or above and already using non-default parameter group, you don’t need to stop and start the database cluster in the following steps.

19. To stop the database, select it and choose Stop from the Actions drop-down menu.
20. Choose Stop database when prompted.

21. When the database has stopped, start the database again by choosing Start from the Actions drop-down menu.

Download the Root Certificate

Download the root certificate that works for all AWS Regions, excluding opt-in Regions. Save this root certificate in the directory of your choice. You reference this directory from your code in the next section.

Connecting to the Aurora PostgreSQL database

To connect the database from a C++ application, complete the following steps:

1. Open the Visual Studio IDE and create a new C++ console application.
2. After you create the application, change the Debug type for the project to x64.

We need to setup the project so it build’s successfully.

3. From the Project drop-down menu, choose Properties.
4. When the Properties dialog box opens for the project, under C/C++, choose General.
5. For Additional Include Directories, choose Edit.
6. In the Additional Include Directories dialog box, choose the New line insert

7. Navigate to the include directory in the PostgreSQL install folder (similar to C:Program FilesPostgreSQL<version number>include).Your version number might be different, but the library is backwards compatible.
8. Choose Select Folder.

The include directory should now be added to the list.

9. Choose OK.
10. In the Project Properties dialog box choose Apply.

11. On the Properties page, under Configuration Properties, choose VC++ Directories.
12. For Library Directories choose Edit.
13. In the Libraries Directories dialog box, choose the New line insert
14. Choose the lib directory (C:Program FilesPostgreSQL<version number>lib).
15. Choose Select Folder.You can see the lib folder added to the Library Directories.
16. Choose OK.
17. In the Properties dialog box, choose
18. Choose OK.
19. Choose Local Windows Debugger to compile the program as is and then stop the program.
20. Locate the bin directory for PostgreSQL (C:Program FilesPostgreSQL<version number>bin).
21. Copy the following highlighted files from bin to the debug folder of your application
    (e.g. postgreslcppclientx64Debug).

22. Replace the C++ code in your console application with the following code (replace the placeholder text with your database connection information).

#include <stdio.h>
#include <stdlib.h>
#include "libpq-fe.h"
#include <iostream>
#include <cstring>
#include <string>

#pragma comment(lib, "libpq.lib")

static void exit_nicely(PGconn* conn)
{
    PQfinish(conn);
    exit(1);
}

int main()
{
    PGconn* conn = NULL;
    PGresult* pgres = NULL;

    std::string host = "<Database Cluster Endpoint from RDS Console>";
    std::string user = "<your database username>";
    std::string password = "<your database password>";
    std::string database = "<your database name>";
    std::string port = "5432";

    std::string connstr = "user=" + user + " password=" + password + " dbname=" + 
                        database + " host=" + host + " port=" + port + 
                       " sslmode=verify-full sslrootcert=<path to rds-ca-2019-root.pem>";


    conn = PQconnectdb(connstr.c_str());

    if (PQstatus(conn) != CONNECTION_OK)
    {
        std::cout << "ERROR: Connection to database failed: " << PQerrorMessage(conn);

        exit_nicely(conn);

    }
    else if (PQstatus(conn) == CONNECTION_OK)
    {

        std::cout << "Connection to database successful with SSL" << "n";

        exit_nicely(conn);

    }

    return 0;
}

The connection string requires you to provide the location of the root certificate.

23. Download the rds-ca-2019-root.pem certificate from Using SSL/TLS to Encrypt a Connection to a DB Cluster and store it in a folder.
24. Provide a path to that folder for the sslrootcert parameter in the connection string below.

    std::string connstr = "user=" + user + " password=" + password + " dbname=" +     
                    database + " host=" + host + " port=" + port + 
                    " sslmode=verify-full sslrootcert=<path to rds-ca-2019-root.pem>";

    The certificate rds-ca-2019-root.pem expires in August 2024. You must update the certificate when it expires.

In the preceding code, we assign the parameter sslmode a value of verify-full. As per libpq SSL Support, “If sslmode is set to verify-full, libpq will also verify that the server host name matches its certificate. The SSL connection will fail if the server certificate cannot be verified. verify-full is recommended in most security-sensitive environments”.

25. Run the program by choosing Local Windows Debugger as shown below.

If you configured everything correctly you should be able to connect to the database with SSL. See the following screenshot.

Conclusion

In this post we provided information on how to enforce SSL connection to an Aurora PostgreSQL database (the same concept applies to Amazon RDS for PostgreSQL). We then downloaded the root certificate from Amazon. With the preceding C++ code, you can connect to Aurora PostgreSQL or Amazon RDS for PostgreSQL and enforce SSL connections to your PostgreSQL database to meet your organization’s security requirements of encrypted connections.

About the Author

Zafar Kapadia is a Sr. Cloud Application Architect at Amazon Web Services. He specializes in Application Development and Optimization. He is also an avid cricketer and plays in various local leagues.

Li Liu is a Database Cloud Architect with Amazon Web Services.