Spring Boot集成PostgreSQL-12遭遇SSL连接错误求助

问题背景 #

在macOS通过Homebrew安装PostgreSQL 12后，PgAdmin可正常连接数据库，但Spring Boot应用启动时抛出SSL连接错误，已尝试在postgresql.conf中设置ssl = off但问题依旧。

现有配置 #

pg_hba.conf内容：

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust

问题复现 #

1. 执行brew install postgresql@12安装数据库
2. Spring数据源URL使用：jdbc:postgresql://localhost:5432/test_db?reWriteBatchedInserts=true
3. 启动应用触发SSL错误，堆栈信息如下：

SQL State  : 08P01
Error Code : 0
Message    : An error occurred while setting up the SSL connection.

at org.flywaydb.core.internal.jdbc.JdbcUtils.openConnection(JdbcUtils.java:60) ~[flyway-core-9.16.3.jar:na]
at org.flywaydb.core.internal.jdbc.JdbcConnectionFactory.<init>(JdbcConnectionFactory.java:74) ~[flyway-core-9.16.3.jar:na]
at org.flywaydb.core.FlywayExecutor.execute(FlywayExecutor.java:140) ~[flyway-core-9.16.3.jar:na]
at org.flywaydb.core.Flyway.migrate(Flyway.java:140) ~[flyway-core-9.16.3.jar:na]
at org.springframework.boot.autoconfigure.flyway.FlywayMigrationInitializer.afterPropertiesSet(FlywayMigrationInitializer.java:66) ~[spring-boot-autoconfigure-3.1.0.jar:3.1.0]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1816) ~[spring-beans-6.0.9.jar:6.0.9]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1766) ~[spring-beans-6.0.9.jar:6.0.9]
... 18 common frames omitted
Caused by: org.postgresql.util.PSQLException: An error occurred while setting up the SSL connection.
at org.postgresql.core.v3.ConnectionFactoryImpl.enableSSL(ConnectionFactoryImpl.java:624) ~[postgresql-42.6.0.jar:42.6.0]
at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:191) ~[postgresql-42.6.0.jar:42.6.0]
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:258) ~[postgresql-42.6.0.jar:42.6.0]
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:54) ~[postgresql-42.6.0.jar:42.6.0]
at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:263) ~[postgresql-42.6.0.jar:42.6.0]
at org.postgresql.Driver.makeConnection(Driver.java:443) ~[postgresql-42.6.0.jar:42.6.0]
at org.postgresql.Driver.connect(Driver.java:297) ~[postgresql-42.6.0.jar:42.6.0]
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:681) ~[java.sql:na]
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:190) ~[java.sql:na]
at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriverManager(DriverManagerDataSource.java:155) ~[spring-jdbc-6.0.9.jar:6.0.9]
at org.springframework.jdbc.datasource.DriverManagerDataSource.getConnectionFromDriver(DriverManagerDataSource.java:146) ~[spring-jdbc-6.0.9.jar:6.0.9]
at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnectionFromDriver(AbstractDriverBasedDataSource.java:205) ~[spring-jdbc-6.0.9.jar:6.0.9]
at org.springframework.jdbc.datasource.AbstractDriverBasedDataSource.getConnection(AbstractDriverBasedDataSource.java:169) ~[spring-jdbc-6.0.9.jar:6.0.9]
at com.fareye.entityframework.multitenant.CustomConnectionProvider.getConnection(CustomConnectionProvider.java:20) ~[fareye-entity-framework-lib-1.0.1-SNAPSHOT.jar:na]
at org.flywaydb.core.internal.jdbc.JdbcUtils.openConnection(JdbcUtils.java:48) ~[flyway-core-9.16.3.jar:na]
... 24 common frames omitted

解决步骤 #

1. 确保PostgreSQL SSL配置生效 #

修改postgresql.conf后必须重启服务才能生效：

brew services restart postgresql@12

登录psql验证SSL状态：

SHOW ssl;

若返回off则配置生效，若未生效，确认修改的是Homebrew对应的配置文件（路径通常为/usr/local/var/postgresql@12/postgresql.conf）。

2. 在JDBC URL中强制禁用SSL #

新版本PostgreSQL JDBC驱动（如42.6.0）默认尝试SSL连接，直接在URL中添加sslmode=disable参数：

jdbc:postgresql://localhost:5432/test_db?reWriteBatchedInserts=true&sslmode=disable

3. 尝试Unix套接字连接（绕过TCP/IP SSL问题） #

Homebrew安装的PostgreSQL默认启用Unix套接字，可直接用套接字连接：

jdbc:postgresql:///test_db?reWriteBatchedInserts=true

或指定套接字路径：

jdbc:postgresql://localhost/test_db?reWriteBatchedInserts=true&socketPath=/usr/local/var/postgresql@12

4. 检查JDBC驱动兼容性 #

若上述方法无效，尝试降级JDBC驱动版本到42.5.x（与PostgreSQL 12兼容性更稳定），Maven依赖示例：

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.5.4</version>
</dependency>

内容的提问来源于stack exchange，提问作者Awadesh