Unable to connect to Postgres DB due to the authentication type 10 is not supported

Question

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)

Kosygin

What does show password_encryption; give you? If that is set to SCRAM-SHA-256 then you will need a newer JDBC driver – Russo

Yes. postgres=# show password_encryption; password_encryption --------------------- scram-sha-256 (1 row) – Kosygin

In my case, the only way to solve this problem in a Docker image was to downgrade Postgres version, as per my colleague's advice. So I created a new container with PostgreSQL v13.2 and it solved the issue. It is a test server so this solution works for me. – Sylvester

Answer 1

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

2. 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

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

   Example: ALTER ROLE postgres WITH PASSWORD 'root';

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

Algoid

This downgrades the security of the driver. You might want to consider upgrading your JDBC driver instead. – Willey

Password are still created with scram-sha-256 encryption after the described changes... Any idea why? Thanks – Gluteus

Don't forget the ; at the end of the ALTER ROLE statement. Took me a while to realize. ;) – Peay

For me, I had to ALTER USER instead as the last step, i.e., ALTER USER postgres WITH PASSWORD 'root'; – Venereal

I strongly recommend people to follow this answer instead, which is to upgrade the PostgreSQL JDBC driver to 42.2.0 or higher. – Willey

Although the most secure recommendation is to upgrade the driver, this answer is very useful (it works!) when you cannot upgrade the driver (for example, you don't have access to upgrade the software broken) – Franko

Answer 2

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):

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

Gluteus

There's a bug in Postgres jdbc driver 42.2.0, better use higher version. Check out: youtrack.jetbrains.com/issue/DBE-5833 – Lineation

for Java 1.7 works updating to 42.2.25.jre7 – Framing

Thanks! I'm using dropwizard. Tried this, the original error was gone but got another build error (A required class was missing while executing org.jooq:jooq-codegen-maven:3.18.5:generate); Updated version to 42.6.0 (latest at the time of writing this comment) and build was a success. – Mcshane

Answer 3

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.

It worked For me!

Colville

Answer 4

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.

Flak

Just curious.. how am I able to connect using a maven project and not a gradle project. – Kosygin

No, that is independent of what you are running above the JDBC driver. – Flak

The driver that is used in my maven project is same as above(org.postgresql.Driver). That is why I used the same in my gradle project. – Kosygin

Well, upgrade the driver for both. – Flak

Upgrading the driver did the job for me, as well. – Enquire

This should be the accepted answer, as changing values in pg_hba.conf is just a workaround and the real solution is to upgrade the JDBC driver version to one that supports scram-sha-256 . E.g. version 8.1-404.jdbc does not support it, but version 42.2.19 does. – Catamaran

Answer 5

<?xml version="1.0" encoding="UTF-8"?>

4.0.0

<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>

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 How do you add PostgreSQL Driver as a dependency in Maven?

Dimeter

Answer 6

Change METHOD to "trust" in pg_hba.conf

Begat

Not secure, and definitely not suitable for ongoing use in a real system, but for my purposes (temporary testing without needing to reconfigure encryption or user passwords) it was a convenient workaround! – Deuno

Answer 7

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

2. Secondly: allow md5 encryption as discussed in other answers:

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

3. Thirdly: restart the container

docker restart CONTAINER NAME

4. Fourthly: you need to recreate the postgres password in md5 format

docker exec -ti -u postgres CONTAINERNAME psql

alter role postgres with password 'THE-NEW-PASSWORD';

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

Temporize

Answer 8

use these :

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

Copy it to your hive library

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

Wilkinson

best solution to keep security – Braley

THIS IS THE BEST ANSWER! You should follow the server's security requirements with your client! I had to to change only the client jar, and everything went flowlessly. – Nimocks

Answer 9

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 = 'md5';

Restart server, this solved my issue

Freese

Answer 10

For me, updating the postgres library helped fixing this.

Mispleading

Answer 11

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 = "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. Install pgadmin if you have not already done so.
   Try it via Docker

Crucifer

Answer 12

Updated both postgis-jbdc and postgresql:

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

Mut

Answer 13

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

Backsaw

Answer 14

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.

Reticular

Answer 15

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

Considered

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Sevilla

Answer 16

For AWS QuickSights, you can create a user using the old md5 encryption by providing a pre-encrypted string as password.

To generate a md5 hash for PostgreSQL concatenate the password with the username, get the hash, then prefix it with md5. Here is a oneliner:

echo -n 'mypasswordUSER' | md5sum | awk '{print "md5"$1}'

If the presented password string is already in MD5-encrypted or SCRAM-encrypted format, then it is stored as-is regardless of password_encryption

For an MD5 encrypted password, rolpassword column will begin with the string md5 followed by a 32-character hexadecimal MD5 hash. The MD5 hash will be of the user's password concatenated to their user name. For example, if user joe has password xyzzy, PostgreSQL will store the md5 hash of xyzzyjoe.

• https://www.postgresql.org/docs/16/sql-createrole.html
• https://www.postgresql.org/docs/current/catalog-pg-authid.html

Achelous

Answer 17

In my case, after changing configuration and restarting the server, I also needed to reload the configuration. Easiest done in pgAdmin:

1. Right click on the server
2. Click on Reload Configuration

Ophthalmitis

Answer 18

Use latest maven dependency for Postgres in pom.xml

Sandbank

Please edit your answer and add some explanation. – Toady

Answer 19

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.

Territus

Answer 20

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

Kosygin

Answer 21

Open pg_hba.conf Set IPv4 local connections to trust

Pretrice

I would not take this as actual supported advice, you should NOT trust all connections by default, as that allows for attackers to authenticate without credentials and is not safe in any way. This should be considered a wholly unsafe solution – Shill