由于不支持身份验证类型10,因此无法连接到Postgres DB [英] Unable to connect to Postgres DB due to the authentication type 10 is not supported
问题描述
我最近尝试过Postgres.在本地(PostgreSQL 13.0)上安装了它.创建一个maven项目并使用Spring Data JPA,效果很好.而当我尝试使用Gradle项目时,我无法连接到数据库并不断出现以下错误.
org.postgresql.util.PSQLException:认证类型10不是支持的.检查是否已将pg_hba.conf文件配置为包括客户端的IP地址或子网,并且该客户端正在使用驱动程序支持的身份验证方案.在org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:614)〜[postgresql-42.1.4.jar:42.1.4]在org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:222)〜[postgresql-42.1.4.jar:42.1.4]在org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)〜[postgresql-42.1.4.jar:42.1.4]在org.postgresql.jdbc.PgConnection.(PgConnection.java:194)〜[postgresql-42.1.4.jar:42.1.4]在org.postgresql.Driver.makeConnection(Driver.java:450)〜[postgresql-42.1.4.jar:42.1.4]在org.postgresql.Driver.connect(Driver.java:252)〜[postgresql-42.1.4.jar:42.1.4]在java.sql.DriverManager.getConnection(未知来源)[na:1.8.0_261]在java.sql.DriverManager.getConnection(未知来源)[na:1.8.0_261]在org.postgresql.ds.common.BaseDataSource.getConnection(BaseDataSource.java:94)[postgresql-42.1.4.jar:42.1.4]位于org.postgresql.ds.common.BaseDataSource.getConnection(BaseDataSource.java:79)[postgresql-42.1.4.jar:42.1.4]
我也尝试使用JDBCTemplate.不起作用
参考
在主机设置中将 scram-sha-256
更改为 md5
.
文件: C:\ Program Files \ PostgreSQL \ 13 \ data \ pg_hba.conf .
全部托管0.0.0.0/0 md5
更改密码(此恢复密码为md5格式).
示例:带有密码"root"的ALTER ROLE postgres
;
如果您非生产性工作,请确保设置 listen_addresses ='*'
环境.
文件: C:\ Program Files \ PostgreSQL \ 13 \ data \ postgresql.conf
I have recently tried my hands on Postgres. Installed it on local (PostgreSQL 13.0). Created a maven project and used Spring Data JPA, works just fine. Whereas when I tried using Gradle project, I am not able to connect to the DB and keep getting the following error.
org.postgresql.util.PSQLException: The authentication type 10 is not supported. Check that you have configured the pg_hba.conf file to include the client's IP address or subnet, and that it is using an authentication scheme supported by the driver. at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:614) ~[postgresql-42.1.4.jar:42.1.4] at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:222) ~[postgresql-42.1.4.jar:42.1.4] at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49) ~[postgresql-42.1.4.jar:42.1.4] at org.postgresql.jdbc.PgConnection.(PgConnection.java:194) ~[postgresql-42.1.4.jar:42.1.4] at org.postgresql.Driver.makeConnection(Driver.java:450) ~[postgresql-42.1.4.jar:42.1.4] at org.postgresql.Driver.connect(Driver.java:252) ~[postgresql-42.1.4.jar:42.1.4] at java.sql.DriverManager.getConnection(Unknown Source) [na:1.8.0_261] at java.sql.DriverManager.getConnection(Unknown Source) [na:1.8.0_261] at org.postgresql.ds.common.BaseDataSource.getConnection(BaseDataSource.java:94) [postgresql-42.1.4.jar:42.1.4] at org.postgresql.ds.common.BaseDataSource.getConnection(BaseDataSource.java:79) [postgresql-42.1.4.jar:42.1.4]
I tried using JDBCTemplate as well. Doesn't work
Modified the pg_hba.cfg file referring to this post - Doesn't work
Used the deprecated Lib of - Doesn't Work either.
Please Suggest me a solution for this problem.
My code and Config:
@Configuration
public class DataSourceConfig {
@Bean
public DriverManagerDataSource getDataSource() {
DriverManagerDataSource dataSourceBuilder = new DriverManagerDataSource();
dataSourceBuilder.setDriverClassName("org.postgresql.Driver");
dataSourceBuilder.setUrl("jdbc:postgresql://localhost:5432/postgres");
dataSourceBuilder.setUsername("postgres");
dataSourceBuilder.setPassword("root");
return dataSourceBuilder;
}
}
@Component
public class CustomerOrderJDBCTemplate implements CustomerOrderDao{
private DataSource dataSource;
private JdbcTemplate jdbcTemplateObject;
@Autowired
ApplicationContext context;
public void setDataSource() {
//Getting Bean by Class
DriverManagerDataSource dataSource = context.getBean(DriverManagerDataSource.class);
this.dataSource = dataSource;
this.jdbcTemplateObject = new JdbcTemplate(this.dataSource);
}
@Override
public Customer create(Customer customer) {
setDataSource();
String sql = "insert into CustomerOrder (customerType, customerPayment) values (?, ?)";
//jdbcTemplateObject.update(sql, customerOrder.getCustomerOrderType(), customerOrder.getCustomerOrderPayment());
KeyHolder holder = new GeneratedKeyHolder();
jdbcTemplateObject.update(new PreparedStatementCreator() {
@Override
public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
ps.setString(1, customer.getType());
ps.setString(2, customer.getPayment());
return ps;
}
}, holder);
long customerId = holder.getKey().longValue();
customer.setCustomerID(customerOrderId);
return customer;
}
}
dependencies
implementation('org.springframework.boot:spring-boot-starter-web')
compile("org.springframework.boot:spring-boot-devtools")
compile(group: 'org.postgresql', name: 'postgresql', version: '42.1.4')
compile("org.springdoc:springdoc-openapi-ui:1.4.1")
compile("org.springframework:spring-jdbc:5.2.5.RELEASE")
password_encryption
is set like this:
postgres=# show password_encryption;
password_encryption
---------------------
scram-sha-256
(1 row)
I solved similar issue by applying below steps in PostgreSQL Version 13 :
Change password_encryption to md5.
File: C:\Program Files\PostgreSQL\13\data\postgresql.conf
Change
scram-sha-256
tomd5
in host settings.File: C:\Program Files\PostgreSQL\13\data\pg_hba.conf.
host all all 0.0.0.0/0 md5
Change Password ( this restore password in md5 format).
Example:
ALTER ROLE postgres WITH PASSWORD 'root'
;Make sure you set
listen_addresses = '*'
if you are working non production environment.File :
C:\Program Files\PostgreSQL\13\data\postgresql.conf
这篇关于由于不支持身份验证类型10,因此无法连接到Postgres DB的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!