Cannot connect to Postgres instance with LDAP auth
When I try to connect to one of our production postgres databases instances using 0xDBE, I get an error no pg_hba.conf entry for host "[My IP]".
I am able to connect to the instance through command line using this:
psql [database] -h [host] -p 5432 -U [username] -W
Interesting is if I don't specify -W explicitly in the command line, I am not prompted for my password and then I receive the same error on the command line:
psql: FATAL: LDAP authentication failed for user "[user]"
FATAL: no pg_hba.conf entry for host "[My IP]", user "[user]", database "[db]", SSL off
I'm not sure if this might be a dba/config issue or not. But I can't connect through 0xDBE - I just always get this no pg_hba.conf entry error even if I re-enter my credentials. Any suggestions welcome!
Please sign in to leave a comment.
Hello.
I have not yet reproduced the case in our test environment. However, from what I've read on the net (e.g.:
http://stackoverflow.com/questions/26777392/accessing-postgresql-with-jdbc-using-ldap-authentication
http://postgresql.nabble.com/JBDC-LDAP-support-td5812046.html
) PostgreSQL JDBC has no any special support for LDAP: you just should provide the LDAP user name and the LDAP password.
Did you enter the password in 0xDBE data source settings? It looks like you did not.
Regards,
Alexander.
I do not think I've missed entering the data source settings. I think this should be everything that I need to connect.
Thank you. We'll try to set up LDAP on our side and check this.
Alexander.
So our databases use SSL. We always need to require SSL when connecting. But when I click "use SSL" on the next screen, and I am getting this error:
Connection to PostgreSQL - [host name] failed
java.sql.SQLException: SSL error: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
at org.postgresql.ssl.jdbc4.AbstractJdbc4MakeSSL.convert(AbstractJdbc4MakeSSL.java:125)
at org.postgresql.core.v3.ConnectionFactoryImpl.enableSSL(ConnectionFactoryImpl.java:316)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:105)
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64)
at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:136)
at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:29)
at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:21)
at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:31)
at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24)
at org.postgresql.Driver.makeConnection(Driver.java:410)
at org.postgresql.Driver.connect(Driver.java:280)
in RemoteDriverImpl.connect(RemoteDriverImpl.java:27)
in RemoteUtil.access$100(RemoteUtil.java:36)
in RemoteUtil$RemoteInvocationHandler.invoke(RemoteUtil.java:227)
at com.sun.proxy.$Proxy91.connect(Unknown Source)
in LocalDataSource.getConnection(LocalDataSource.java:240)
Caused by: java.lang.RuntimeException: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
at com.sun.net.ssl.internal.ssl.Alerts.getSSLException(Alerts.java:174)
at com.sun.net.ssl.internal.ssl.SSLSocketImpl.fatal(SSLSocketImpl.java:1747)
at com.sun.net.ssl.internal.ssl.Handshaker.fatalSE(Handshaker.java:241)
at com.sun.net.ssl.internal.ssl.Handshaker.fatalSE(Handshaker.java:235)
at com.sun.net.ssl.internal.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1209)
at com.sun.net.ssl.internal.ssl.ClientHandshaker.processMessage(ClientHandshaker.java:135)
at com.sun.net.ssl.internal.ssl.Handshaker.processLoop(Handshaker.java:593)
at com.sun.net.ssl.internal.ssl.Handshaker.process_record(Handshaker.java:529)
at com.sun.net.ssl.internal.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:943)
at com.sun.net.ssl.internal.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1188)
at com.sun.net.ssl.internal.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1215)
at com.sun.net.ssl.internal.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1199)
at org.postgresql.ssl.jdbc4.AbstractJdbc4MakeSSL.convert(AbstractJdbc4MakeSSL.java:118)
at org.postgresql.core.v3.ConnectionFactoryImpl.enableSSL(ConnectionFactoryImpl.java:316)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:105)
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64)
at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:136)
at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:29)
at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:21)
at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:31)
at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24)
at org.postgresql.Driver.makeConnection(Driver.java:410)
at org.postgresql.Driver.connect(Driver.java:280)
in RemoteDriverImpl.connect(RemoteDriverImpl.java:27)
Caused by: java.lang.RuntimeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
at sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:323)
at sun.security.validator.PKIXValidator.engineValidate(PKIXValidator.java:217)
at sun.security.validator.Validator.validate(Validator.java:218)
at com.sun.net.ssl.internal.ssl.X509TrustManagerImpl.validate(X509TrustManagerImpl.java:126)
at com.sun.net.ssl.internal.ssl.X509TrustManagerImpl.checkServerTrusted(X509TrustManagerImpl.java:209)
at com.sun.net.ssl.internal.ssl.X509TrustManagerImpl.checkServerTrusted(X509TrustManagerImpl.java:249)
at com.sun.net.ssl.internal.ssl.ClientHandshaker.serverCertificate(ClientHandshaker.java:1188)
... 33 more
Caused by: java.lang.RuntimeException: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
at sun.security.provider.certpath.SunCertPathBuilder.engineBuild(SunCertPathBuilder.java:174)
at java.security.cert.CertPathBuilder.build(CertPathBuilder.java:238)
at sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:318)
... 39 more
I'd love an update if you have one, or just to confirm that the issue may be SSL-related. We have had the same problems with RubyMine and IntelliJ. I have a large team of DB devs and DBAs who might be very interested in this IDE, starting with me, if it can work with Postgres! pgAdmin really doesn't cut it (and neither does command line)!
Found my answer for anyone interested in how to implement this for SSL correctly:
(1) Enter Host, Port, Database, Username, Password in the properties Database window
(2) Go to Advanced tab.
(3) In the ssl field, put Value of true.
(4) In the sslfactory field, put Value of org.postgresql.ssl.NonValidatingFactory.
That made it work like a charm! Very excited about this tool! Keep up the good work!
For anyone trying to do this in the Database Navigator plugin, the process is largely the same. After entering the info in the Database tab, go to the Properties tab. Add a property and put ssl under Property and true under Value.
(I did not need to do anything for the sslfactory option that Jeremy listed, although that may be specific to my database.)