由于不支持身份验证类型 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(Unknown Source)[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.没用

修改 pg_hba.cfg 文件参考

在主机设置中将 scram-sha-256 更改为 md5.

文件:C:Program FilesPostgreSQL13datapg_hba.conf.

托管所有 0.0.0.0/0 md5

更改密码(此密码为 md5 格式).

示例:ALTER ROLE postgres WITH PASSWORD 'root';

如果您在非生产环境中工作，请确保设置 listen_addresses = '*'环境.

文件:C:Program FilesPostgreSQL13datapostgresql.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 :

1. Change password_encryption to md5.

   File: C:Program FilesPostgreSQL13datapostgresql.conf

2. Change scram-sha-256 to md5 in host settings.

   File: C:Program FilesPostgreSQL13datapg_hba.conf.

   host all all 0.0.0.0/0 md5

3. Change Password ( this restore password in md5 format).

   Example: ALTER ROLE postgres WITH PASSWORD 'root';

4. Make sure you set listen_addresses = '*' if you are working non production environment.

   File : C:Program FilesPostgreSQL13datapostgresql.conf

这篇关于由于不支持身份验证类型 10，无法连接到 Postgres DB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！