Connecting to an RDS for PostgreSQL Instance Through JDBC

Prerequisites

You are familiar with:

• Computer basics.
• Java.
• JDBC.

Obtaining and Using JDBC

• JDBC driver download address: https://jdbc.postgresql.org/download/
• JDBC API: https://jdbc.postgresql.org/documentation/

Connection with the SSL Certificate

Download the SSL certificate and verify the certificate before connecting to databases.

On the Instances page, click the instance name to go to the Overview page. Under SSL, click Download to download the root certificate or certificate bundle.

1. Connect to the RDS for PostgreSQL DB instance through JDBC.

   jdbc:postgresql://<instance_ip>:<instance_port>/<database_name>?sslmode=verify-ca&sslrootcert=<ca.pem>

   Table 1 Parameter description

   Parameter	Description
   <instance_ip>	If you attempt to access the RDS DB instance through an ECS, set instance_ip to the floating IP address displayed on the Overview page of the DB instance to which you intend to connect.
   	If you attempt to access the RDS DB instance through an EIP, set instance_ip to the EIP that has been bound to the DB instance.
   <instance_port>	Enter the database port displayed on the Overview page. Default value: 5432
   <database_name>	Enter the name of the database to which you intend to connect. Default value: postgres
   sslmode	Enter the SSL connection mode. verify-ca: I want my data encrypted, and I accept the overhead. I want to be sure that I connect to a server that I trust. For details about other options, see https://jdbc.postgresql.org/documentation/use/#connection-parameters/.
   sslrootcert	Path of the CA certificate for the SSL connection. For details, see https://jdbc.postgresql.org/documentation/use/#connection-parameters/.

   Example script in Java:

   // There will be security risks if the username and password used for authentication are directly written into code. Store the username and password in ciphertext in the configuration file or environment variables.
   // In this example, the username and password are stored in the environment variables. Before running this example, set environment variables EXAMPLE_USERNAME_ENV and EXAMPLE_PASSWORD_ENV as needed.
   
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.ResultSet;
   import java.sql.Statement;
   
   public class MyConnTest {
   	final public static void main(String[] args) {
   		Connection conn = null;
   		// set sslmode here.
   		// with ssl certificate and path.
   		String url = "jdbc:postgresql://<instance_ip>:<instance_port>/<database_name>?sslmode=verify-ca&sslrootcert=/home/Ruby/ca.pem";
   		String userName = System.getenv("EXAMPLE_USERNAME_ENV");
   		String password = System.getenv("EXAMPLE_PASSWORD_ENV");
   
   		try {
   			Class.forName("org.postgresql.Driver");
   			conn = DriverManager.getConnection(url, userName, password);
   			System.out.println("Database connected");
   
   			Statement stmt = conn.createStatement();
   			ResultSet rs = stmt.executeQuery("SELECT * FROM mytable WHERE columnfoo = 500");
   			while (rs.next()) {
   				System.out.println(rs.getString(1));
   			}
   
   			rs.close();
   			stmt.close();
   			conn.close();
   		} catch (Exception e) {
   			e.printStackTrace();
   			System.out.println("Test failed");
   		} finally {
   			// release resource ....
   		}
   	}
   }

Connection Without the SSL Certificate

You do not need to download the SSL certificate because the certificate verification on the server is not required.

1. Connect to the RDS for PostgreSQL DB instance through JDBC.

   jdbc:postgresql://<instance_ip>:<instance_port>/<database_name>?sslmode=disable

   Table 2 Parameter description

   Parameter	Description
   <instance_ip>	If you attempt to access the RDS DB instance through an ECS, set instance_ip to the floating IP address displayed on the Overview page of the DB instance to which you intend to connect.
   	If you attempt to access the RDS DB instance through an EIP, set instance_ip to the EIP that has been bound to the DB instance.
   <instance_port>	Enter the database port displayed on the Overview page. Default value: 5432
   <database_name>	Enter the name of the database to which you intend to connect. Default value: postgres
   sslmode	Enter the SSL connection mode. disable: I don't care about security and don't want to pay the overhead for encryption. For details about other options, see https://jdbc.postgresql.org/documentation/use/#connection-parameters/.

   Example script in Java:

   // There will be security risks if the username and password used for authentication are directly written into code. Store the username and password in ciphertext in the configuration file or environment variables.
   // In this example, the username and password are stored in the environment variables. Before running this example, set environment variables EXAMPLE_USERNAME_ENV and EXAMPLE_PASSWORD_ENV as needed.
   
   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.ResultSet;
   import java.sql.Statement;
   
   public class MyConnTest {
   	final public static void main(String[] args) {
   		Connection conn = null;
   		// set sslmode here.
   		// no ssl certificate, so do not specify path.
   		String url = "jdbc:postgresql://<instance_ip>:<instance_port>/<database_name>?sslmode=disable";
                   String userName = System.getenv("EXAMPLE_USERNAME_ENV");
   		String password = System.getenv("EXAMPLE_PASSWORD_ENV");
   		try {
   			Class.forName("org.postgresql.Driver");
   			conn = DriverManager.getConnection(url, userName, password);
   			System.out.println("Database connected");
   
   			Statement stmt = conn.createStatement();
   			ResultSet rs = stmt.executeQuery("SELECT * FROM mytable WHERE columnfoo = 500");
   			while (rs.next()) {
   				System.out.println(rs.getString(1));
   			}
   			rs.close();
   			stmt.close();
   			conn.close();
   		} catch (Exception e) {
   			e.printStackTrace();
   			System.out.println("Test failed");
   		} finally {
   			// release resource ....
   		}
   	}
   }