Postgres RDS DB Connection issues: SSL error: Connection reset

Postgres RDS DB Connection issues: SSL error: Connection reset

Warning
Issue Description: 
The client has enabled SSL on their postgresql RDS database; however, they are encountering issues when attempting to connect through AutomationEdge Process Studio.

Warning
Error Message:
Error connecting to database: (using class org.postgresql.Driver)
SSL error:connection reset


Notes
Root Cause:

 when connecting to database using SSL, we must add sslmode=allow in the connection string or along with the database name in database plugin steps like table input, Call DB Procedure/Function, Insert/Update, and Java class, etc., as below.

Idea
Recommended Solution: 

Here are the options that we can use along with the database name:

Idea
Method 1:

A. postgres?sslmode=allow

B. postgres?ssl=true&sslmode=allow

C. postgres?ssl=true&sslmode=allow&sslrootcert=E:\\global-bundle.pem


Idea
Method 2:

You can also use “Connection String” option where the entire string can be mentioned as

Connection string sample: jdbc:postgresql://<Host>:<Port>/<DB Name>?sslmode=allow


Idea
Method 3:

Using User Defined Java Class. Refer below code.

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public boolean processRow(StepMetaInterface smi, StepDataInterface sdi) throws ProcessStudioException {

if (first) {

first = false;

}

Object[] r = getRow();

if (r == null) {

setOutputDone();

return false;

}

r = createOutputRow(r, data.outputRowMeta.size());

String jdbcUrl = "jdbc:postgresql://<host>:<port>/dbname?sslmode=allow";

String username = "abc";

String password = "abc";

try {

System.out.println("Connecting to database...");

// Attempt connection

Connection conn = DriverManager.getConnection(jdbcUrl, username, password);

logBasic("Connection successful!");

// Close the connection

conn.close();


} catch (SQLException e) {

logBasic("Connection failed:");

logBasic(e.getMessage());

//logBasic(e.printStackTrace());

}

putRow(data.outputRowMeta, r);

return true;

}

After adding this we were able to test the connection successfully.




      Links to better reach 

            Bot Store

             EPD