PikoPong
  • Web Dev
  • Hack
  • Database
  • Big Data
  • AWS
  • Linux
No Result
View All Result
PikoPong
  • Web Dev
  • Hack
  • Database
  • Big Data
  • AWS
  • Linux
No Result
View All Result
PikoPong
No Result
View All Result
Home Database

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

September 1, 2020
in Database
289 3
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.

sslmode Eavesdropping protection MITM protection Statement
disable No No I don’t care about security, and I don’t want to pay the overhead of encryption.
allow Maybe No I don’t care about security, but I will pay the overhead of encryption if the server insists on it.
prefer Maybe No I don’t care about encryption, but I wish to pay the overhead of encryption if the server supports it. This is default.
require Yes No I want my data to be encrypted, and I accept the overhead. I trust that the network will make sure I always connect to the server I want.
verify-ca Yes Depends on CA-policy I want my data encrypted, and I accept the overhead. I want to be sure that I connect to a server that I trust.
verify-full Yes Yes I want my data encrypted, and I accept the overhead. I want to be sure that I connect to a server I trust, and that it’s the one I specify.

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.

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

  1. For Parameters, search for ‘force_ssl’ and select it.
  2. Choose Edit Parameters.

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

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

  1. Choose Continue.
  2. On the next page, under Scheduling of modifications, select when to apply the changes (for this post, we select Apply Immediately.
  3. 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.

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

  1. 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.

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

  1. 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.
  2. Choose Select Folder.

The include directory should now be added to the list.

  1. Choose OK.
  2. In the Project Properties dialog box choose Apply.

  1. On the Properties page, under Configuration Properties, choose VC++ Directories.
  2. For Library Directories choose Edit.
  3. In the Libraries Directories dialog box, choose the New line insert
  4. Choose the lib directory (C:Program FilesPostgreSQL<version number>lib).
  5. Choose Select Folder.You can see the lib folder added to the Library Directories.
  6. Choose OK.
  7. In the Properties dialog box, choose
  8. Choose OK.
  9. Choose Local Windows Debugger to compile the program as is and then stop the program.
  10. Locate the bin directory for PostgreSQL (C:Program FilesPostgreSQL<version number>bin).
  11. Copy the following highlighted files from bin to the debug folder of your application
    (e.g. postgreslcppclientx64Debug).

  1. 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.

  1. 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.
  2. 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”.

  1. 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.



Source link

Share219Tweet137Share55Pin49

Related Posts

Complement Commercial Intelligence by Building a Knowledge Graph out of a Data Warehouse with Amazon Neptune : idk.dev
Database

Complement Commercial Intelligence by Building a Knowledge Graph out of a Data Warehouse with Amazon Neptune : idk.dev

This is a guest post from Shahria Hossain, Software Engineer, and Mikael Graindorge, Sales Operations Leader at Thermo Fisher...

September 23, 2020
Cox Automotive scales digital personalization using an identity graph powered by Amazon Neptune : idk.dev
Database

Cox Automotive scales digital personalization using an identity graph powered by Amazon Neptune : idk.dev

Cox Automotive Inc. makes buying, selling, owning and using cars easier for everyone. The global company’s 34,000-plus team members...

September 18, 2020
Load balance graph queries using the Amazon Neptune Gremlin Client : idk.dev
Database

Load balance graph queries using the Amazon Neptune Gremlin Client : idk.dev

Amazon Neptune is a fast, reliable, fully managed graph database service that makes it easy to build and run...

September 16, 2020
Migrate to an Amazon Aurora PostgreSQL instance from another PostgreSQL source : idk.dev
Database

Migrate to an Amazon Aurora PostgreSQL instance from another PostgreSQL source : idk.dev

Amazon Aurora with PostgreSQL compatibility combines the performance and availability of high-end commercial databases with the simplicity and cost-effectiveness...

September 9, 2020
Next Post

The Art Of Graphic Design — Smashing Magazine

Setting Up An API Using Flask, Google’s Cloud SQL And App Engine — Smashing Magazine

Setting Up An API Using Flask, Google’s Cloud SQL And App Engine — Smashing Magazine

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recommended

Make Jamstack Slow? Challenge Accepted.

Make Jamstack Slow? Challenge Accepted.

July 15, 2020
Interact with NFC devices on Chrome for Android

Interact with NFC devices on Chrome for Android

May 27, 2020
Top 10 performance tuning techniques for Amazon Redshift : idk.dev

Top 10 performance tuning techniques for Amazon Redshift : idk.dev

August 28, 2020
Chapter 5: Publishing | CSS-Tricks

Chapter 5: Publishing | CSS-Tricks

November 9, 2020

Categories

  • AWS
  • Big Data
  • Database
  • DevOps
  • IoT
  • Linux
  • Web Dev
No Result
View All Result
  • Web Dev
  • Hack
  • Database
  • Big Data
  • AWS
  • Linux

Welcome Back!

Login to your account below

Forgotten Password?

Create New Account!

Fill the forms bellow to register

All fields are required. Log In

Retrieve your password

Please enter your username or email address to reset your password.

Log In