环境说明
PostgreSQL版本
PostgreSQL 9.4 docker容器
pom.xml
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.18</version>
</dependency>
JDK版本
JDK 1.8
PostgreSQL的SSL连接配置
JDBC连接PostgreSQL
JDBC以SSL方式连接PostgreSQL需要三个文件root.crt(根证书)
、postgresql.pk8(客户端私钥)
、postgresql.crt(客户端证书)
转化私钥
根据jdbc官方文档说明,需将客户端的私钥生成JDBC适配的格式
$ openssl pkcs8 -topk8 -inform PEM -in postgresql.key -outform der -out postgresql.pk8 -v1 PBE-MD5-DES
Enter Encryption Password:
Verifying - Enter Encryption Password:
# 此处需设置密码,在连接时使用
编写下载代码
由于PostgreSQL JDBC驱动读取证书文件用的是FileInputStream
,当证书地址是远程地址时会报错,因此需将远程证书下载到本地后,使用本地地址填写。
/**
* 下载证书
* @param fileName 文件名
* @param path 下载后文件放置的地址
*/
public static void downCA(String fileName, String path) {
URL url = null;
try {
url = new URL("http://localhost:9999/" + fileName);
ReadableByteChannel rbc = Channels.newChannel(url.openStream());
FileOutputStream fos = new FileOutputStream(path + "/" + fileName);
fos.getChannel().transferFrom(rbc, 0, Long.MAX_VALUE);
} catch (Exception e) {
e.printStackTrace();
}
}
编写JDBC SSL连接PostgreSQL代码
package ssl;
import java.io.File;
import java.io.FileOutputStream;
import java.net.URL;
import java.nio.channels.Channels;
import java.nio.channels.ReadableByteChannel;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
/**
* @author UV
* @version 1.0
* @date 2020/6/11
*/
public class JDBCPostgresql {
public static void main(String[] args) {
// 获取当前绝对路径
String path = new File("").getAbsolutePath();
// 下载根证书
downCA("root.crt", path);
// 下载客户端私钥
downCA("postgresql.pk8", path);
// 下载客户端证书
downCA("postgresql.crt", path);
Connection connection = null;
String urlWithCe = "jdbc:postgresql://192.168.254.82:15432/postgres" ;
Properties properties = new Properties();
properties.setProperty("user", "postgres");
properties.setProperty("password", "123456");
// 配置以ssl访问
properties.setProperty("ssl", "true");
// 配置根证书地址
properties.setProperty("sslrootcert", path + "/" + "root.crt");
// 配置客户端私钥地址
properties.setProperty("sslkey", path + "/" + "postgresql.pk8");
// 配置客户端证书地址
properties.setProperty("sslcert", path + "/" + "postgresql.crt");
// 配置私钥密码
properties.setProperty("sslpassword", "123456");
// 配置ssl模式,可选值为require、verify-ca、verify-full
properties.setProperty("sslmode", "verify-ca");
try {
Class.forName("org.postgresql.Driver");
connection = DriverManager.getConnection(urlWithCe, properties);
PreparedStatement preparedStatement = connection.prepareStatement("select * from " +
"test.student");
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
ResultSetMetaData rsmd = resultSet.getMetaData();
int columnCount = rsmd.getColumnCount();
Map map = new HashMap();
for (int i = 0; i < columnCount; i++) {
map.put(rsmd.getColumnName(i + 1).toLowerCase(), resultSet.getObject(i + 1));
}
System.out.println(map);
}
} catch (Exception exception) {
exception.printStackTrace();
}
}
/**
* 下载证书
* @param fileName 文件名
* @param path 下载后文件放置的地址
*/
public static void downCA(String fileName, String path) {
URL url = null;
try {
url = new URL("http://localhost:9999/" + fileName);
ReadableByteChannel rbc = Channels.newChannel(url.openStream());
FileOutputStream fos = new FileOutputStream(path + "/" + fileName);
fos.getChannel().transferFrom(rbc, 0, Long.MAX_VALUE);
} catch (Exception e) {
e.printStackTrace();
}
}
}