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.

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

Postgres connection error #1219

Closed
cjunge-work opened this Issue Feb 1, 2017 · 12 comments

Comments

Projects
None yet
5 participants
@cjunge-work

cjunge-work commented Feb 1, 2017

While trying to connect to a Heroku Connect Postgres database I am getting the following error:

org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host "[my ip]", user "username", database "database", SSL off

I am running DBeaver 3.8.5 on Xubuntu 16.04.

I have tried the PostgreSQL and PostgreSQL (Generic) drivers, and same result. I can use the psql CLI client fine, and I can connect using HeidiSQL from my computer. I can connect successfully to a MySQL server.

Any help would be appreciated.

@serge-rider serge-rider added the question label Feb 2, 2017

@serge-rider

This comment has been minimized.

Member

serge-rider commented Feb 2, 2017

This is not a DBeaver issue (http://stackoverflow.com/questions/20392482/postgres-pg-hba-conf).
If you can connect with psql (from the same machine) then you need to set exactly the same connection settings (host, database, user name) in DBeaver.
If you connecting from a different machine then you need to add its IP in pg_hba.conf.

@cjunge-work

This comment has been minimized.

cjunge-work commented Feb 2, 2017

I have set the exact same settings, as far as I can tell.

$ psql -h [hostname] -p 5432 [dbname] [username]
Password for user [username]:
psql (9.5.5, server 9.6.1)
[dbname]=> `

In DBeaver the parameters are set to the same values (I copied them directly from the command above).

@cjunge-work

This comment has been minimized.

cjunge-work commented Feb 2, 2017

The password is a 64 character hexdec string... would it be silently truncated in DBeaver?

@serge-rider

This comment has been minimized.

Member

serge-rider commented Feb 2, 2017

No, DBeaver doesn't truncate passwords (or any other connection parameters).

From http://postgresql.nabble.com/Unable-to-connect-to-9-5-via-JDBC-driver-1207-td5885427.html:
try to uncomment the following in pg_hba.conf:

# IPv4 local connections:
# host    all             all             127.0.0.1/32            trust

This will enable incoming tcp/ip connections (psql may use something else).

@cjunge-work

This comment has been minimized.

cjunge-work commented Feb 2, 2017

Found the problem: Heroku Connect uses SSL. Enabling SSL for the connection is working fine! I guess psql/HeidiSQL were detecting that automatically?

@serge-rider

This comment has been minimized.

Member

serge-rider commented Feb 2, 2017

Interesting. Maybe they do.
Although is is more likely that they used non tcp/ip connection (which was permitted in pg_hba).

@serge-rider serge-rider closed this Feb 2, 2017

@lcustodio

This comment has been minimized.

lcustodio commented Jun 1, 2017

@cjunge-work How did you manage to enable SSL?
Simply setting ssl to true in the Driver properties throws:

*Same scenario with heroku postgres db.

SSL error: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
  sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
    PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
      sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

@serge-rider

This comment has been minimized.

Member

serge-rider commented Jun 1, 2017

@lcustodio You need to configure it on connection properties->Network->SSL page.

@cjunge-work

This comment has been minimized.

cjunge-work commented Jun 1, 2017

@lcustodio on the SSL page, set SSL mode: require and either leave the SSL Factory blank or use the org.postgresql.ssl.NonValidatingFactory

@lcustodio

This comment has been minimized.

lcustodio commented Jun 3, 2017

Awesome! I managed to connect using Ubuntu 16.04 and macOS 10.11.6. Thanks

@parthpuneet

This comment has been minimized.

parthpuneet commented Dec 21, 2017

@cjunge-work this worked for me

@oyepez003

This comment has been minimized.

oyepez003 commented Mar 12, 2018

And ever choose your database name... never set "postgres" like database name in the connection, because that can also be the error ...

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment