PostgreSQL + SSL

Creazione certificato SSL lato Server

#!/bin/sh

# creazione chiave privata server e certificate signing request (CSR)
openssl req -new -text -nodes -keyout server.key -out server.csr -subj '/C=IT/ST=Italia/L=Milan/O=PPQ/OU=PPQ/CN=127.0.0.1' # CN is the server's IP address

# creazione certificato server
openssl req -x509 -days 365 -text -in server.csr -key server.key -out server.crt

cp server.crt root.crt
rm server.csr
chmod og-rwx server.key

Creazione certificato SSL lato Client

#!/bin/sh

# creazione chiave privata e certificate signing request (CSR)
openssl req -new -nodes -keyout client.key -out client.csr -subj '/C=IT/ST=Italia/L=Milano/O=PPQ/OU=PPQ/CN=ppq' # ppq is the database user name

# creazione certificato client con certificato server e chiave private server
openssl x509 -days 365 -req -CAcreateserial -in client.csr -CA root.crt -CAkey server.key -out client.crt

chmod 600 client.key
rm client.csr

Il driver jdbc di PostgreSQL (postgresql-42.2.14.jar) utilizza il certificato in formato “der”, quindi va fatta la conversione (conversione certificati):

openssl pkcs8 -topk8 -inform PEM -in client.key -outform DER -nocrypt -out client.key.pk8
chmod 600 client.key.pk8

Per visualizzare il contenuto di un certificato:

openssl x509 -in server.crt -text

Per verificare il certificato client con quello server:

openssl verify -CAfile root.crt -purpose sslclient client.crt

Test connessione con il comando psql

#/bin/sh
ROOT_CRT=/Users/pasquale/temp/root.crt
CLIENT_CRT=/Users/pasquale/temp/client.crt
CLIENT_KEY=/Users/pasquale/temp/client.key

psql "sslmode=verify-ca sslrootcert=$ROOT_CRT sslcert=$CLIENT_CRT sslkey=$CLIENT_KEY host=127.0.0.1 hostaddr=127.0.0.1 port=5432 user=ppq dbname=ppq"

Test connessione JDBC

Script per lo startup

#!/bin/sh
JAVA_HOME=/usr/local/java

DRIVER=org.postgresql.Driver
URL="jdbc:postgresql://127.0.0.1:5432/ppq?\
sslmode=verify-full&\
sslrootcert=/tmp/jdbctest/root.crt&\
sslcert=/tmp/jdbctest/client.crt&\
sslkey=/tmp/jdbctest/client.key.pk8&\
loggerLevel=TRACE&loggerFile=/tmp/jdbctest/pgjdbc.log"

# ssl=true&\
# sslfactory=org.postgresql.ssl.NonValidatingFactory&\
# sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory&\
# loggerLevel=DEBUG"

USERNAME=user_pasquale
PASSWORD=non-necessaria

# per compilare la classe Java
#$JAVA_HOME/bin/javac TestJDBCConnection.java

#$JAVA_HOME/bin/java -Djavax.net.ssl.trustStore=mystore -Djavax.net.ssl.trustStorePassword=changeit -cp .:postgresql-42.2.14.jar TestJDBCConnection $DRIVER $URL $USERNAME $PASSWORD

$JAVA_HOME/bin/java -cp .:postgresql-42.2.14.jar TestJDBCConnection $DRIVER $URL $USERNAME $PASSWORD

#$JAVA_HOME/bin/java -Djavax.net.debug=ssl -cp .:postgresql-42.2.14.jar TestJDBCConnection $DRIVER $URL $USERNAME $PASSWORD

Sorgente Java

import java.sql.*;    
public class TestJDBCConnection {

  public static void main(String[] args) {
    System.out.println("");
    System.out.println("java TestJDBCConnection drivername url user password");
    System.out.println();
    System.out.println("example:");
    System.out.println("java TestJDBCConnection org.postgresql.Driver jdbc:postgresql://127.0.0.1:5432/ppq?sslmode=verify-full&sslrootcert=/path/root.crt&sslcert=/path/client.crt&sslkey=/path/client.key.pk8&loggerLevel=TRACE&loggerFile=/path/logs/ssl-logs.log user_pasquale not-required");
    System.out.println();

    String driverName=args[0];
    String url=args[1];
    String username=args[2];
    String password=args[3];
    System.out.println("driverName: ["+driverName+"]" );
    System.out.println("url: ["+url+"]" );
    System.out.println("username: ["+username+"]" );
    System.out.println("password: ["+password+"]" );

    Connection conn = null;  
    try {    
      Class.forName(driverName);    
      System.out.println("Connecting to database...");    
      conn = DriverManager.getConnection(url,username,password);    
    } catch (Exception e) {    
      e.printStackTrace();    
    } finally {    
      if (conn != null) {    
        try {    
          conn.close();    
        } catch (SQLException e) {    
          // ignore    
        }    
      }    
    }            
  }    
}