无法连接到 Postgres 数据库,因为不支持身份验证类型 10。

huangapple go评论83阅读模式
英文:

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地址或子网,以及是否使用驱动程序支持的身份验证方案。 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.<init>(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]

我还尝试使用了JDBCTemplate,但不起作用。

我已经修改了pg_hba.cfg文件,参考了这个帖子 - 但不起作用。

我还尝试了已弃用的Lib - 也不起作用。

请为我提供解决此问题的解决方案。

我的代码和配置:

@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 (?, ?)";
		
		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(customerId);
		return customer;
	}
}

依赖项:

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 的设置如下:

postgres=# show password_encryption;
 password_encryption
---------------------
 scram-sha-256
(1 row)
英文:

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.<init>(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(&quot;org.postgresql.Driver&quot;);
dataSourceBuilder.setUrl(&quot;jdbc:postgresql://localhost:5432/postgres&quot;);
dataSourceBuilder.setUsername(&quot;postgres&quot;);
dataSourceBuilder.setPassword(&quot;root&quot;);
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 = &quot;insert into CustomerOrder (customerType, customerPayment) values (?, ?)&quot;;
//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(&#39;org.springframework.boot:spring-boot-starter-web&#39;)
compile(&quot;org.springframework.boot:spring-boot-devtools&quot;)
compile(group: &#39;org.postgresql&#39;, name: &#39;postgresql&#39;, version: &#39;42.1.4&#39;)
compile(&quot;org.springdoc:springdoc-openapi-ui:1.4.1&quot;)
compile(&quot;org.springframework:spring-jdbc:5.2.5.RELEASE&quot;)

password_encryption is set like this:

postgres=# show password_encryption;
password_encryption
---------------------
scram-sha-256
(1 row)

答案1

得分: 60

我通过在 PostgreSQL 版本13 中执行以下步骤来解决类似的问题:

  1. postgresql.conf 中将 password_encryption 更改为 md5
Windows:C:\Program Files\PostgreSQL\13\data\postgresql.conf
GNU/Linux:/etc/postgresql/13/main/postgresql.conf

无法连接到 Postgres 数据库,因为不支持身份验证类型 10。

  1. pg_hba.conf 中将 scram-sha-256 更改为 md5
Windows:C:\Program Files\PostgreSQL\13\data\pg_hba.conf
GNU/Linux:/etc/postgresql/13/main/pg_hba.conf
host	all				all				0.0.0.0/0				md5

无法连接到 Postgres 数据库,因为不支持身份验证类型 10。

  1. 更改密码(将密码恢复为md5格式)。

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

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

英文:

I solved a similar issue by applying the steps below in PostgreSQL Version 13:

  1. Change password_encryption to md5 in postgresql.conf
Windows: C:\Program Files\PostgreSQL\13\data\postgresql.conf
GNU/Linux:           /etc/postgresql/13/main/postgresql.conf

无法连接到 Postgres 数据库,因为不支持身份验证类型 10。

  1. Change scram-sha-256 to md5 in pg_hba.conf
Windows: C:\Program Files\PostgreSQL\13\data\pg_hba.conf
GNU/Linux:           /etc/postgresql/13/main/pg_hba.conf
host	all				all				0.0.0.0/0				md5

无法连接到 Postgres 数据库,因为不支持身份验证类型 10。

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

    Example: ALTER ROLE postgres WITH PASSWORD &#39;root&#39;;

  2. Make sure you set listen_addresses = &#39;*&#39; in postgresql.conf if you are working non production environment.

答案2

得分: 44

根据维基,支持 SCRAM-SHA-256 加密的 JDBC 驱动版本应为 42.2.0 或更高。
在我的情况下,驱动程序版本为 41.1.1。请将其更改为 42.2.0 或更高版本。这对我起效了。

(Maven,pom.xml):

&lt;dependency&gt;
&lt;groupId&gt;org.postgresql&lt;/groupId&gt;
&lt;artifactId&gt;postgresql&lt;/artifactId&gt;
&lt;version&gt;42.2.0&lt;/version&gt;
&lt;/dependency&gt;
英文:

According to the wiki, the supported JDBC driver for SCRAM-SHA-256 encryption is 42.2.0 or above.
In my case, the driver was 41.1.1. Change it to 42.2.0 or above. That fixed it for me.

(Maven, pom.xml):

&lt;dependency&gt;
&lt;groupId&gt;org.postgresql&lt;/groupId&gt;
&lt;artifactId&gt;postgresql&lt;/artifactId&gt;
&lt;version&gt;42.2.0&lt;/version&gt;
&lt;/dependency&gt;

答案3

得分: 38

获取位于目录中的 pg_hba.conf 文件
C:\Program Files\PostgreSQL\13\data\pg_hba.conf

然后只需将 Method 列下的 scram-sha-256 更改为 trust。

无法连接到 Postgres 数据库,因为不支持身份验证类型 10。

这对我有效!

英文:

Get your pg_hba.conf File in the Directory
C:\Program Files\PostgreSQL\13\data\pg_hba.conf

And Simply Change scram-sha-256 under Column Method to trust.

无法连接到 Postgres 数据库,因为不支持身份验证类型 10。

It worked For me!

答案4

得分: 11

通过将 password_encryption 设置为 scram-sha-256(这是 v13 中的默认值),即使在 pg_hba.conf 中使用了 md5,您也将获得 scram-sha-256 身份验证。

现在客户端正在使用旧的 JDBC 驱动程序版本,不支持这种身份验证方法,尽管 PostgreSQL 在三年前的 v10 中引入了它。

您应该升级您的 JDBC 驱动程序。另一种选择是将 password_encryption 设置回 md5,但那样您将不得不重置所有密码,并且安全性会降低。

英文:

By setting password_encryption to scram-sha-256 (which is the default value in v13) you also get scram-sha-256 authentication, even if you have md5 in pg_hba.conf.

Now you are using an old JDBC driver version on the client side that does not support that authentication method, even though PostgreSQL introduced it in v10, three years ago.

You should upgrade your JDBC driver. An alternative would be to set password_encryption back to md5, but then you'll have to reset all passwords and live with lower security.

答案5

得分: 6

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>postgresJDBC</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <java.version>11</java.version>
        <maven.compiler.target>${java.version}</maven.compiler.target>
        <maven.compiler.source>${java.version}</maven.compiler.source>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.2.18</version>
        </dependency>
    </dependencies>

</project>

如果您正在使用 PostgreSQL 9.1+,请检查您的 Maven 依赖,依赖应该如上所示。

要了解有关 Maven 依赖的更多信息,请参考此链接:https://stackoverflow.com/questions/13242196/how-do-you-add-postgresql-driver-as-a-dependency-in-maven。

英文:
&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;

<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd&quot;>
<modelVersion>4.0.0</modelVersion>

&lt;groupId&gt;org.example&lt;/groupId&gt;
&lt;artifactId&gt;postgresJDBC&lt;/artifactId&gt;
&lt;version&gt;1.0-SNAPSHOT&lt;/version&gt;
&lt;properties&gt;
&lt;java.version&gt;11&lt;/java.version&gt;
&lt;maven.compiler.target&gt;${java.version}&lt;/maven.compiler.target&gt;
&lt;maven.compiler.source&gt;${java.version}&lt;/maven.compiler.source&gt;
&lt;/properties&gt;
&lt;dependencies&gt;
&lt;dependency&gt;
&lt;groupId&gt;org.postgresql&lt;/groupId&gt;
&lt;artifactId&gt;postgresql&lt;/artifactId&gt;
&lt;version&gt;42.2.18&lt;/version&gt;
&lt;/dependency&gt;
&lt;/dependencies&gt;

</project>

you have to check your maven dependency if you are using postgresql 9.1+ then your dependency should be like above

to know about maven dependency refer this link https://stackoverflow.com/questions/13242196/how-do-you-add-postgresql-driver-as-a-dependency-in-maven

答案6

得分: 4

将pg_hba.conf中的METHOD更改为"trust"。

英文:

Change METHOD to "trust" in pg_hba.conf

答案7

得分: 4

如果您在Docker中遇到困难:

首先:使用 -e POSTGRES_HOST_AUTH_METHOD=md5 运行容器(文档链接

docker run -e POSTGRES_HOST_AUTH_METHOD=md5 -e POSTGRES_PASSWORD=doesntmatter -p 5432:5432 --name CONTAINERNAME -d postgres

其次:按照其他答案中讨论的,允许使用 md5 加密:

docker exec -ti -u postgres CONTAINERNAME bash -c "echo 'password_encryption=md5' >> /var/lib/postgresql/data/postgresql.conf"

第三:重新启动容器

docker restart CONTAINER NAME

第四:您需要以 md5 格式重新创建 postgres 用户的密码

docker exec -ti -u postgres CONTAINERNAME psql
alter role postgres with password 'THE-NEW-PASSWORD';

请注意,scram-sha-256md5 更安全(文档链接)。

英文:

In case you are struggling to get this working in Docker:

  1. Firstly: run the container with -e POSTGRES_HOST_AUTH_METHOD=md5 (doc)
docker run -e POSTGRES_HOST_AUTH_METHOD=md5 -e POSTGRES_PASSWORD=doesntmatter -p 5432:5432 --name CONTAINERNAME -d postgres
  1. Secondly: allow md5 encryption as discussed in other answers:
docker exec -ti -u postgres CONTAINERNAME bash -c &quot;echo &#39;password_encryption=md5&#39; &gt;&gt; /var/lib/postgresql/data/postgresql.conf&quot;
  1. Thirdly: restart the container
docker restart CONTAINER NAME
  1. Fourthly: you need to recreate the postgres password in md5 format
docker exec -ti -u postgres CONTAINERNAME psql
alter role postgres with password &#39;THE-NEW-PASSWORD&#39;;

* please be aware scram-sha-256 is much better than md5 (doc)

答案8

得分: 3

Sure, here is the translated content:

  1. wget https://jdbc.postgresql.org/download/postgresql-42.2.24.jar

将其复制到您的Hive库中

  1. sudo mv postgresql-42.2.24.jar /opt/hive/lib/postgresql-42.2.24.jar
英文:

use these :

  1. wget https://jdbc.postgresql.org/download/postgresql-42.2.24.jar

Copy it to your hive library

  1. sudo mv postgresql-42.2.24.jar /opt/hive/lib/postgresql-42.2.24.jar

答案9

得分: 2

更新postgres库有助于解决此问题。

英文:

For me, updating the postgres library helped fixing this.

答案10

得分: 1

Suggestions:

  1. 当前的JDBC驱动程序将会有所帮助(例如postgresql-42.3.6.jar)。

  2. 将其复制到您的Spark安装目录下的/jars文件夹中(在此示例中,我假设是单机环境)。

  3. Python - 安装"findspark",以便将pyspark作为常规库进行导入。

  4. 这是一个希望能帮助某人的示例:

    import findspark
    findspark.init()
    
    from pyspark.sql import SparkSession
    
    sparkClassPath = "C:/spark/spark-3.0.3-bin-hadoop2.7/jars"
    
    spark = SparkSession \
        .builder \
        .config("spark.driver.extraClassPath", sparkClassPath) \
        .getOrCreate()
    
    df = spark.read \
        .format("jdbc") \
        .option("url", "jdbc:postgresql://{YourHostName}:5432/{YourDBName}") \
        .option("driver", "org.postgresql.Driver") \
        .option("dbtable", "{YourTableName}") \
        .option("user", "{YourUserName}") \
        .option("password", "{YourSketchyPassword}") \
        .load()
    
  5. 如果尚未安装,请安装pgadmin。
    通过Docker尝试

英文:

Suggestions:

  1. Current JDBC driver will help (e.g. postgresql-42.3.6.jar)

  2. Copy it to the /jars folder under your spark install directory (I'm assuming a single machine here in this example)

  3. Python - install "findspark" to make pyspark importable as a regular library

  4. Here is an example I hope will help someone:

    import findspark
    findspark.init()
    from pyspark.sql import SparkSession
    sparkClassPath = &quot;C:/spark/spark-3.0.3-bin-hadoop2.7/jars&quot;
    spark = SparkSession \
    .builder \
    .config(&quot;spark.driver.extraClassPath&quot;, sparkClassPath) \
    .getOrCreate()
    df = spark.read \
    .format(&quot;jdbc&quot;) \
    .option(&quot;url&quot;, &quot;jdbc:postgresql://{YourHostName}:5432/{YourDBName}&quot;) \
    .option(&quot;driver&quot;, &quot;org.postgresql.Driver&quot;) \
    .option(&quot;dbtable&quot;, &quot;{YourTableName}&quot;) \
    .option(&quot;user&quot;, &quot;{YourUserName&quot;) \
    .option(&quot;password&quot;, &quot;{YourSketchyPassword&quot;) \
    .load()
    
  5. Install pgadmin if you have not already done so.
    Try it via Docker

答案11

得分: 1

即使在所有地方都将 pg_hba.conf 更改为 MD5,仍然没有起作用。起作用的是执行以下操作:

show password_encryption;

如果显示为 scram-sha-256,请执行以下操作:

set password_encryption = 'md5';

重新启动服务器,这解决了我的问题。

英文:

Even after changing pg_hba.conf to MD5 on everything it didn't work.
What worked was doing this:

show password_encryption;

If it shows up as being scram-sha-256 do this:

set password_encryption = &#39;md5&#39;;

Restart server, this solved my issue

答案12

得分: 0

在版本12.6中运行正常...只需降级PostgreSQL。

英文:

working fine with version 12.6 ... just downgrade the PostgreSQL

答案13

得分: 0

你可能需要检查你正在使用的 Postgres 版本。如果通过 Spring 父项目指向了该版本,可能需要更新 Spring 版本。
在我的情况下:由于当前的 Postgres 版本是 v13。修改了 Spring 父项目的版本:之前是 1.4;将它修改为匹配的 2.14。最后更新 Maven 依赖并重新运行应用程序。这解决了这个问题。

英文:

You might need to check the version of Postgres you are running. Migh need to update spring version if the version is being pointed through spring parent.
In my case: since current postgres is at v13. Modified spring parent version: it was on 1.4; made it to match to 2.14. Finally update maven dependency and re-run the application.This fixed the issue.

答案14

得分: 0

你需要下载postgresql..jar文件,然后将它移动到.../jre/lib/ext/文件夹中。这对我起作用了。

英文:

You need to download postgresql.<version>.jar and then move it into .../jre/lib/ext/ folder. It worked for me

答案15

得分: 0

已更新postgis-jdbc和postgresql:

implementation 'net.postgis:postgis-jdbc:2.5.0'
runtimeOnly 'org.postgresql:postgresql:42.6.0'
英文:

Updated both postgis-jbdc and postgresql:

implementation &#39;net.postgis:postgis-jdbc:2.5.0&#39;
runtimeOnly &#39;org.postgresql:postgresql:42.6.0&#39;

答案16

得分: -1

使用最新的Maven依赖项更新pom.xml中的Postgres。

英文:

Use latest maven dependency for Postgres in pom.xml

答案17

得分: -1

更改本地连接的 IPv4 的信任方式对我起了作用。

解决方法:

获取位于目录 C:\Program Files\PostgreSQL\13\data\pg_hba.conf 中的 pg_hba.conf 文件,

然后只需将 Method 列下的 scram-sha-256 更改为 trust。

英文:

Changing trust for ipv4 local connect worked for me.

Solution:

Get your pg_hba.conf File in the Directory C:\Program Files\PostgreSQL\13\data\pg_hba.conf

And Simply Change scram-sha-256 under Column Method to trust.

答案18

得分: -2

我猜这个问题的解决方案是使用9.6版本。在更改版本后,它完美地运行。

英文:

I guess the solution to this problem is using version 9.6.
It works just fine after changing the version.

答案19

得分: -2

打开 pg_hba.conf
将 IPv4 本地连接设置为信任

英文:

Open pg_hba.conf
Set IPv4 local connections to trust

huangapple
  • 本文由 发表于 2020年10月5日 22:01:17
  • 转载请务必保留本文链接:https://go.coder-hub.com/64210167.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定