Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Unable to connect to remote postgres 13.1 using scram-sha-256 or md5 but password authentication works #1970

Closed
1 of 2 tasks
HemilTheRebel opened this issue Nov 28, 2020 · 23 comments
Closed
1 of 2 tasks

Unable to connect to remote postgres 13.1 using scram-sha-256 or md5 but password authentication works #1970

HemilTheRebel opened this issue Nov 28, 2020 · 23 comments

Comments

@HemilTheRebel
Copy link

I'm submitting a ...

  • bug report
  • feature request

Describe the issue
I am unable to connect to my remote postgresql database when using md5 or scram-sha-256 on server as authentication method. The password authentication method works fine. One weird thing is that, I am able to connect to the database using psql and python's psycopg2 driver.

Driver Version?
42.2.18

Java Version?
openjdk 11.0.9 2020-10-20
OpenJDK Runtime Environment JBR-11.0.9.11-944.49-jcef (build 11.0.9+11-b944.49)
OpenJDK 64-Bit Server VM JBR-11.0.9.11-944.49-jcef (build 11.0.9+11-b944.49, mixed mode)

OS Version?
Ubuntu 20.04

PostgreSQL Version?
PostgreSQL 13.1

To Reproduce
Steps to reproduce the behaviour:

  1. Create a remote database.
  2. Try to loging in using this program:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class Test {
    public static void main(String[] args) throws SQLException {
                Properties properties = new Properties();
        properties.setProperty("loggerLevel", "TRACE");
        Connection connection = DriverManager.getConnection(
                "jdbc:postgresql://preetibhojan.co.in/preetibhojan?user=centos&password=password",
                properties
        );

        try (connection) {
            Statement statement = connection.createStatement();
            final ResultSet resultSet = statement.executeQuery("select version()");
            resultSet.next();
            System.out.println(resultSet.getString(1));
        }
    }
}

Expected behaviour
I should be able to connect to the database

Logs
When using scram-sha-256:

FATAL:  password authentication failed for user "centos"
DETAIL:  Connection matched pg_hba.conf line 88: "host    preetibhojan      centos          0.0.0.0/0               scram-sha-256"

Driver log:

Exception in thread "main" org.postgresql.util.PSQLException: FATAL: password authentication failed for user "centos"
  Location: File: auth.c, Routine: auth_failed, Line: 334
  Server SQLState: 28P01
	at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:520)
	at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:141)
	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:192)
	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
	at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:195)
	at org.postgresql.Driver.makeConnection(Driver.java:458)
	at org.postgresql.Driver.connect(Driver.java:260)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:189)
	at Test.main(Test.java:11)
Nov 28, 2020 10:33:40 PM org.postgresql.Driver connect
FINE: Connecting with URL: jdbc:postgresql://preetibhojan.co.in/preetibhojan?user=centos&password=password
Nov 28, 2020 10:33:41 PM org.postgresql.jdbc.PgConnection <init>
FINE: PostgreSQL JDBC Driver 42.2.8
Nov 28, 2020 10:33:41 PM org.postgresql.jdbc.PgConnection setDefaultFetchSize
FINE:   setDefaultFetchSize = 0
Nov 28, 2020 10:33:41 PM org.postgresql.jdbc.PgConnection setPrepareThreshold
FINE:   setPrepareThreshold = 5
Nov 28, 2020 10:33:41 PM org.postgresql.core.v3.ConnectionFactoryImpl openConnectionImpl
FINE: Trying to establish a protocol version 3 connection to preetibhojan.co.in:5432
Nov 28, 2020 10:33:41 PM org.postgresql.core.Encoding <init>
FINEST: Creating new Encoding UTF-8 with fastASCIINumbers true
Nov 28, 2020 10:33:41 PM org.postgresql.core.Encoding <init>
FINEST: Creating new Encoding UTF-8 with fastASCIINumbers true
Nov 28, 2020 10:33:41 PM org.postgresql.core.Encoding <init>
FINEST: Creating new Encoding UTF-8 with fastASCIINumbers true
Nov 28, 2020 10:33:41 PM org.postgresql.core.v3.ConnectionFactoryImpl enableSSL
FINEST:  FE=> SSLRequest
Nov 28, 2020 10:33:41 PM org.postgresql.core.v3.ConnectionFactoryImpl enableSSL
FINEST:  <=BE SSLRefused
Nov 28, 2020 10:33:41 PM org.postgresql.core.v3.ConnectionFactoryImpl tryConnect
FINE: Receive Buffer Size is 65,536
Nov 28, 2020 10:33:41 PM org.postgresql.core.v3.ConnectionFactoryImpl tryConnect
FINE: Send Buffer Size is 43,520
Nov 28, 2020 10:33:41 PM org.postgresql.core.v3.ConnectionFactoryImpl sendStartupPacket
FINEST:  FE=> StartupPacket(user=centos, database=preetibhojan, client_encoding=UTF8, DateStyle=ISO, TimeZone=Asia/Kolkata, extra_float_digits=2)
Nov 28, 2020 10:33:41 PM org.postgresql.core.v3.ConnectionFactoryImpl doAuthentication
FINEST:  <=BE AuthenticationSASL
Nov 28, 2020 10:33:41 PM org.postgresql.jre7.sasl.ScramAuthenticator processServerMechanismsAndInit
FINEST:  Using SCRAM mechanism SCRAM-SHA-256
Nov 28, 2020 10:33:41 PM org.postgresql.jre7.sasl.ScramAuthenticator sendScramClientFirstMessage
FINEST:  FE=> SASLInitialResponse( n,,n=*,r='@$BP=Cu+JrPd//g=@F>d/A/ )
Nov 28, 2020 10:33:41 PM org.postgresql.jre7.sasl.ScramAuthenticator processServerFirstMessage
FINEST:  <=BE AuthenticationSASLContinue( r='@$BP=Cu+JrPd//g=@F>d/A/l3zxUk93BmsOHBkMy8Xq2bU5,s=tdUM50mSm5eA9F6t4cgdKw==,i=4096 )
Nov 28, 2020 10:33:41 PM org.postgresql.jre7.sasl.ScramAuthenticator processServerFirstMessage
FINEST:  <=BE AuthenticationSASLContinue(salt=tdUM50mSm5eA9F6t4cgdKw==, iterations=4,096)
Nov 28, 2020 10:33:41 PM org.postgresql.jre7.sasl.ScramAuthenticator processServerFirstMessage
FINEST:  FE=> SASLResponse( c=biws,r='@$BP=Cu+JrPd//g=@F>d/A/l3zxUk93BmsOHBkMy8Xq2bU5,p=PFW8CZ4mT37RUM/3we0eiN+kWMWOcRgLlDCWxaUT4LM= )
Nov 28, 2020 10:33:41 PM org.postgresql.core.v3.ConnectionFactoryImpl doAuthentication
FINEST:  <=BE ErrorMessage(FATAL: password authentication failed for user "centos"
  Location: File: auth.c, Routine: auth_failed, Line: 334
  Server SQLState: 28P01)
Nov 28, 2020 10:33:41 PM org.postgresql.Driver connect
FINE: Connection error: 
org.postgresql.util.PSQLException: FATAL: password authentication failed for user "centos"
  Location: File: auth.c, Routine: auth_failed, Line: 334
  Server SQLState: 28P01
	at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:520)
	at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:141)
	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:192)
	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
	at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:195)
	at org.postgresql.Driver.makeConnection(Driver.java:458)
	at org.postgresql.Driver.connect(Driver.java:260)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:189)
	at Test.main(Test.java:11)

When using md5:

FATAL:  password authentication failed for user "centos"
DETAIL:  Connection matched pg_hba.conf line 89: "host      preetibhojan    centos          0.0.0.0/0               md5"

Driver log:

Exception in thread "main" org.postgresql.util.PSQLException: FATAL: password authentication failed for user "centos"
  Location: File: auth.c, Routine: auth_failed, Line: 334
  Server SQLState: 28P01
	at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:520)
	at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:141)
	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:192)
	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
	at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:195)
	at org.postgresql.Driver.makeConnection(Driver.java:458)
	at org.postgresql.Driver.connect(Driver.java:260)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:189)
	at Test.main(Test.java:11)
Nov 28, 2020 10:35:16 PM org.postgresql.Driver connect
FINE: Connecting with URL: jdbc:postgresql://preetibhojan.co.in/preetibhojan?user=centos&password=password
Nov 28, 2020 10:35:16 PM org.postgresql.jdbc.PgConnection <init>
FINE: PostgreSQL JDBC Driver 42.2.8
Nov 28, 2020 10:35:16 PM org.postgresql.jdbc.PgConnection setDefaultFetchSize
FINE:   setDefaultFetchSize = 0
Nov 28, 2020 10:35:16 PM org.postgresql.jdbc.PgConnection setPrepareThreshold
FINE:   setPrepareThreshold = 5
Nov 28, 2020 10:35:16 PM org.postgresql.core.v3.ConnectionFactoryImpl openConnectionImpl
FINE: Trying to establish a protocol version 3 connection to preetibhojan.co.in:5432
Nov 28, 2020 10:35:16 PM org.postgresql.core.Encoding <init>
FINEST: Creating new Encoding UTF-8 with fastASCIINumbers true
Nov 28, 2020 10:35:16 PM org.postgresql.core.Encoding <init>
FINEST: Creating new Encoding UTF-8 with fastASCIINumbers true
Nov 28, 2020 10:35:16 PM org.postgresql.core.Encoding <init>
FINEST: Creating new Encoding UTF-8 with fastASCIINumbers true
Nov 28, 2020 10:35:16 PM org.postgresql.core.v3.ConnectionFactoryImpl enableSSL
FINEST:  FE=> SSLRequest
Nov 28, 2020 10:35:16 PM org.postgresql.core.v3.ConnectionFactoryImpl enableSSL
FINEST:  <=BE SSLRefused
Nov 28, 2020 10:35:16 PM org.postgresql.core.v3.ConnectionFactoryImpl tryConnect
FINE: Receive Buffer Size is 65,536
Nov 28, 2020 10:35:16 PM org.postgresql.core.v3.ConnectionFactoryImpl tryConnect
FINE: Send Buffer Size is 43,520
Nov 28, 2020 10:35:16 PM org.postgresql.core.v3.ConnectionFactoryImpl sendStartupPacket
FINEST:  FE=> StartupPacket(user=centos, database=preetibhojan, client_encoding=UTF8, DateStyle=ISO, TimeZone=Asia/Kolkata, extra_float_digits=2)
Nov 28, 2020 10:35:16 PM org.postgresql.core.v3.ConnectionFactoryImpl doAuthentication
FINEST:  <=BE AuthenticationSASL
Nov 28, 2020 10:35:16 PM org.postgresql.jre7.sasl.ScramAuthenticator processServerMechanismsAndInit
FINEST:  Using SCRAM mechanism SCRAM-SHA-256
Nov 28, 2020 10:35:16 PM org.postgresql.jre7.sasl.ScramAuthenticator sendScramClientFirstMessage
FINEST:  FE=> SASLInitialResponse( n,,n=*,r=rYxB2!VHN?iEVj}XAiM\=2R0 )
Nov 28, 2020 10:35:16 PM org.postgresql.jre7.sasl.ScramAuthenticator processServerFirstMessage
FINEST:  <=BE AuthenticationSASLContinue( r=rYxB2!VHN?iEVj}XAiM\=2R0FQvlbwYrfPefmyC5LRIswn8n,s=tdUM50mSm5eA9F6t4cgdKw==,i=4096 )
Nov 28, 2020 10:35:16 PM org.postgresql.jre7.sasl.ScramAuthenticator processServerFirstMessage
FINEST:  <=BE AuthenticationSASLContinue(salt=tdUM50mSm5eA9F6t4cgdKw==, iterations=4,096)
Nov 28, 2020 10:35:16 PM org.postgresql.jre7.sasl.ScramAuthenticator processServerFirstMessage
FINEST:  FE=> SASLResponse( c=biws,r=rYxB2!VHN?iEVj}XAiM\=2R0FQvlbwYrfPefmyC5LRIswn8n,p=GXQGiAKH6OF9GkWnKnPcD85omYnj86KAZiic4YSy0pw= )
Nov 28, 2020 10:35:16 PM org.postgresql.core.v3.ConnectionFactoryImpl doAuthentication
FINEST:  <=BE ErrorMessage(FATAL: password authentication failed for user "centos"
  Location: File: auth.c, Routine: auth_failed, Line: 334
  Server SQLState: 28P01)
Nov 28, 2020 10:35:16 PM org.postgresql.Driver connect
FINE: Connection error: 
org.postgresql.util.PSQLException: FATAL: password authentication failed for user "centos"
  Location: File: auth.c, Routine: auth_failed, Line: 334
  Server SQLState: 28P01
	at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:520)
	at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:141)
	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:192)
	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49)
	at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:195)
	at org.postgresql.Driver.makeConnection(Driver.java:458)
	at org.postgresql.Driver.connect(Driver.java:260)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:189)
	at Test.main(Test.java:11)

Here is the pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv4 connections from internet
host    preetibhojan  centos          0.0.0.0/0               scram-sha-256
host    preetibhojan    centos          0.0.0.0/0               md5
host    preetibhojan    centos          0.0.0.0/0               password
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# IPv6 connections from internet:
host   preetibhojan    centos          ::0/0                   scram-sha-256
host    preetibhojan    centos          ::0/0                   md5
host    preetibhojan    centos          ::0/0                   password
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

Please note, the same code works when lines with scram-sha256 and md5 are commented out
@davecramer
Copy link
Member

in order to use scram you need to change the password_encryption to scram-sha-256 in postgresql.conf. Then update the users password. See https://www.postgresql.org/docs/current/auth-password.html

@HemilTheRebel
Copy link
Author

Password encryption is set to scram-sha-256 in postgresql.conf

@davecramer
Copy link
Member

Have you updated your password since then ? I have tried this on my machine and it works fine on v13.

@HemilTheRebel
Copy link
Author

Nope. I havent. I think I am hitting some edge case or doing something very silly.

@davecramer
Copy link
Member

davecramer commented Nov 29, 2020 via email

@HemilTheRebel
Copy link
Author

HemilTheRebel commented Nov 29, 2020 via email

@HemilTheRebel
Copy link
Author

HemilTheRebel commented Nov 30, 2020 •
edited

I haven't contributed to open source so far. I think I want to fix this bug myself. Also, I have no choice as I really need to be able to connect to my database using jdbc and you cant reproduce it. Can you point me to resources where I can start with?

@davecramer
Copy link
Member

I tested with jdk8. I will try to test with jdk7. In the meantime you can look https://github.com/ongres/scram here as this is where the code comes from

@davecramer
Copy link
Member

OK, so this is the server logs when I have an md5 password

[unknown] 2020-11-30 08:50:33.704 EST [48536] FATAL: password authentication failed for user "testscram"
[unknown] 2020-11-30 08:50:33.704 EST [48536] DETAIL: User "testscram" does not have a valid SCRAM secret.
Connection matched pg_hba.conf line 91: "host all all 127.0.0.1/32 scram-sha-256"

So it appears you have scram passwords.

@HemilTheRebel
Copy link
Author

HemilTheRebel commented Nov 30, 2020 via email

@davecramer
Copy link
Member

Ok, I tested this with jdk7 and it works fine. The only thing left is the encoding.
What encoding did you create the database with ? Can you try it with simple passwords
Here is what I did.

create user testscram password 'password';
create database testscram owner testscram;

then my connection string is

"jdbc:postgresql://localhost/testscram?user=testscram&password=password",

@HemilTheRebel
Copy link
Author

my password has spaces in it (which are legal i guess) if that matters.

@davecramer
Copy link
Member

davecramer commented Nov 30, 2020 via email

@HemilTheRebel
Copy link
Author

I can connect with passwords password. Seems like jdbc is unable to encode passwords with spaces

@HemilTheRebel
Copy link
Author

HemilTheRebel commented Nov 30, 2020 via email

@davecramer
Copy link
Member

seems it is more complicated than that. Let me dig into this. But for now this is your problem

@HemilTheRebel
Copy link
Author

HemilTheRebel commented Nov 30, 2020 via email

@davecramer
Copy link
Member

see https://github.com/MagicStack/asyncpg/blob/master/asyncpg/protocol/scram.pyx#L288 and https://github.com/mongodb/mongo-python-driver/blob/master/pymongo/saslprep.py for how to deal with spaces. I imagine the java implementation is not doing it correctly

@HemilTheRebel
Copy link
Author

HemilTheRebel commented Nov 30, 2020 via email

@davecramer
Copy link
Member

davecramer commented Nov 30, 2020 via email

@HemilTheRebel
Copy link
Author

HemilTheRebel commented Dec 1, 2020 •
edited

I found the bug. It was in Scram implementation. Here is the pull request. If you know the maintainer, please ask them to accept the merge request. Because I will also have to fork pgjdbc and upgrade its scram implementation to use the fixed version

@davecramer
Copy link
Member

will do. Thanks!

@bokken bokken mentioned this issue Dec 25, 2020
Closed
2 tasks
@jorsol jorsol mentioned this issue Feb 8, 2021
Merged
@jorsol
Copy link
Member

jorsol commented Feb 13, 2021

A fix should come in PgJDBC 42.2.19

@jorsol jorsol closed this as completed Feb 13, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@davecramer @jorsol @HemilTheRebel