Обсуждение: Unable to connect to 9.5 via JDBC driver 1207

Hello,

I downloaded the 9.5 release as Debian packages and everything is
working as intended except for obtaining a connection via the JDBC
driver.

I use ExecuteQuery a lot. Both it and a silly little Java class that I
wrote to test connecting both return the same error.

I downloaded the Java source and tried to figure out what is happening
without any success.




org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host
"127.0.0.1", user "timeman", database "timemanager", SSL off
    at
org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(Connectio
nFactoryImpl.java:427)
    at
org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(Connect
ionFactoryImpl.java:203)
    at
org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.
java:65)
    at
org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:159)
    at org.postgresql.Driver.makeConnection(Driver.java:416)
    at org.postgresql.Driver.connect(Driver.java:283)
    at
org.executequery.datasource.SimpleDataSource.getConnection(SimpleDataSo
urce.java:102)
    at
org.executequery.datasource.SimpleDataSource.getConnection(SimpleDataSo
urce.java:83)
    at
org.executequery.datasource.ConnectionPoolImpl.createConnection(Connect
ionPoolImpl.java:254)
    at
org.executequery.datasource.ConnectionPoolImpl.ensureCapacity(Connectio
nPoolImpl.java:202)
    at
org.executequery.datasource.ConnectionPoolImpl.setMinimumConnections(Co
nnectionPoolImpl.java:421)
    at
org.executequery.datasource.ConnectionManager.createDataSource(Connecti
onManager.java:87)
    at
org.executequery.databasemediators.spi.DefaultConnectionBuilder.createD
ataSource(DefaultConnectionBuilder.java:122)
    at
org.executequery.databasemediators.spi.DefaultConnectionBuilder.access$
000(DefaultConnectionBuilder.java:35)
    at
org.executequery.databasemediators.spi.DefaultConnectionBuilder$1.const
ruct(DefaultConnectionBuilder.java:100)
    at
org.underworldlabs.swing.util.SwingWorker$2.run(SwingWorker.java:130)
    at java.lang.Thread.run(Thread.java:745)


--------------------------------------

my pg_hba.conf file

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
# host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
# host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                trust
#host    replication     postgres        127.0.0.1/32            trust
#host    replication     postgres        ::1/128                 trust



-----------------------------------

I can run psql without any problems.

postgres@roblaptop:/home/postgres/data95$ psql -d timemanager -U
timeman -p 5434
psql (9.5.0)
Type "help" for help.

timemanager=> select count(*) from error_messages;
 count 
-------
   357
(1 row)

timemanager=> 



Please let me know if any further information is required.


Thanks,
Rob

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

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

Hello,

I downloaded the 9.5 release as Debian packages and everything is
working as intended except for obtaining a connection via the JDBC
driver.

I use ExecuteQuery a lot. Both it and a silly little Java class that I
wrote to test connecting both return the same error.

I downloaded the Java source and tried to figure out what is happening
without any success.




org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host
"127.0.0.1", user "timeman", database "timemanager", SSL offat
org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(Connectio
nFactoryImpl.java:427)at
org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(Connect
ionFactoryImpl.java:203)at
org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.
java:65)at
org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:159)at org.postgresql.Driver.makeConnection(Driver.java:416)at org.postgresql.Driver.connect(Driver.java:283)at
org.executequery.datasource.SimpleDataSource.getConnection(SimpleDataSo
urce.java:102)at
org.executequery.datasource.SimpleDataSource.getConnection(SimpleDataSo
urce.java:83)at
org.executequery.datasource.ConnectionPoolImpl.createConnection(Connect
ionPoolImpl.java:254)at
org.executequery.datasource.ConnectionPoolImpl.ensureCapacity(Connectio
nPoolImpl.java:202)at
org.executequery.datasource.ConnectionPoolImpl.setMinimumConnections(Co
nnectionPoolImpl.java:421)at
org.executequery.datasource.ConnectionManager.createDataSource(Connecti
onManager.java:87)at
org.executequery.databasemediators.spi.DefaultConnectionBuilder.createD
ataSource(DefaultConnectionBuilder.java:122)at
org.executequery.databasemediators.spi.DefaultConnectionBuilder.access$
000(DefaultConnectionBuilder.java:35)at
org.executequery.databasemediators.spi.DefaultConnectionBuilder$1.const
ruct(DefaultConnectionBuilder.java:100)at
org.underworldlabs.swing.util.SwingWorker$2.run(SwingWorker.java:130)at java.lang.Thread.run(Thread.java:745)


--------------------------------------

my pg_hba.conf file

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
# host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
# host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                trust
#host    replication     postgres        127.0.0.1/32            trust
#host    replication     postgres        ::1/128                 trust



-----------------------------------

I can run psql without any problems.

postgres@roblaptop:/home/postgres/data95$ psql -d timemanager -U
timeman -p 5434
psql (9.5.0)
Type "help" for help.

timemanager=> select count(*) from error_messages;
 count 
-------
   357
(1 row)

timemanager=> 



Please let me know if any further information is required.


Thanks,
Rob

-- 
Поздрави,       | Best regards,
Радослав Петров | Radoslav Petrov

rob stone schrieb am 02.02.2016 um 18:08:
> Hello,
>
> I downloaded the 9.5 release as Debian packages and everything is
> working as intended except for obtaining a connection via the JDBC
> driver.
>
> org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host
> "127.0.0.1", user "timeman", database "timemanager", SSL off
>     at

I had the same problem, I could only resolve it by switching back to 1206

As I said: by simply switching to the previous driver, everything was fine.

It works fine with JDBC connections to localhost, but not to a remote server (psql was fine)

I had also one user of SQL Workbench/J who reported the same problem and
after switching to 1206 the problem went away for him as well.


Thomas


Dave Cramer schrieb am 02.02.2016 um 19:57:
> In this case there are no tcp connections allowed in pg_hba.conf.
>
> java by default only connects to tcp connections. psql will connect to local socket in the example above
>
> try psql -h localhost ... to see the same results
>
> Dave Cramer
>
> davec@postgresintl.com <mailto:davec@postgresintl.com>
> www.postgresintl.com <http://www.postgresintl.com>
>
> On 2 February 2016 at 13:54, Thomas Kellerer <spam_eater@gmx.net <mailto:spam_eater@gmx.net>> wrote:
>
>     rob stone schrieb am 02.02.2016 um 18:08:
>
>         Hello,
>
>         I downloaded the 9.5 release as Debian packages and everything is
>         working as intended except for obtaining a connection via the JDBC
>         driver.
>
>         org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host
>         "127.0.0.1", user "timeman", database "timemanager", SSL off
>                  at
>
>
>     I had the same problem, I could only resolve it by switching back to 1206
>
>
>
>
>
>
>     --
>     Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org <mailto:pgsql-jdbc@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-jdbc
>
>

Dave Cramer <pg@fastcrypt.com> writes:
> On 2 February 2016 at 14:13, Thomas Kellerer <spam_eater@gmx.net> wrote:
>> As I said: by simply switching to the previous driver, everything was fine.
>> It works fine with JDBC connections to localhost, but not to a remote
>> server (psql was fine)
>> I had also one user of SQL Workbench/J who reported the same problem and
>> after switching to 1206 the problem went away for him as well.

> Well clearly we would like to get to the root of this problem. Is it
> possible for you to provide us with more information?

Since the quoted error message mentions a connection from "127.0.0.1",
it's impossible that it is a reply from a remote server (unless your
networking configuration is completely broken).  Presumably what is
happening is that what you think is a connection to a remote PG server
is actually being made to localhost, and the local PG server's pg_hba.conf
doesn't allow the username and/or dbname.  You could check that by turning
on log_connections on both servers.

As to why that would happen as a consequence of a driver version change,
I'm pretty clueless, but I would wonder about differences in driver config
files or the driver's interpretation of a connection URL.  In any case,
certainly not enough information has been given to diagnose it.

            regards, tom lane

Tom Lane schrieb am 02.02.2016 um 20:38:
>>> As I said: by simply switching to the previous driver, everything was fine.
>>> It works fine with JDBC connections to localhost, but not to a remote
>>> server (psql was fine)
>>> I had also one user of SQL Workbench/J who reported the same problem and
>>> after switching to 1206 the problem went away for him as well.
>
>> Well clearly we would like to get to the root of this problem. Is it
>> possible for you to provide us with more information?
>
> Since the quoted error message mentions a connection from "127.0.0.1",
> it's impossible that it is a reply from a remote server (unless your
> networking configuration is completely broken).  Presumably what is
> happening is that what you think is a connection to a remote PG server
> is actually being made to localhost, and the local PG server's pg_hba.conf
> doesn't allow the username and/or dbname.  You could check that by turning
> on log_connections on both servers.
>
> As to why that would happen as a consequence of a driver version change,
> I'm pretty clueless, but I would wonder about differences in driver config
> files or the driver's interpretation of a connection URL.  In any case,
> certainly not enough information has been given to diagnose it.


The initial error message in this thread was not from me.

I am currently not in the office, but I will see if I can reproduce that tomorrow.

I have also contacted the SQL Workbench/J user who reported this to me last week
and asked him to describe his setup as well.

In his case, the error message was:

> FATAL: no pg_hba.conf entry for host "213.xxx.xx.xxx", user "foobar", database "postgres", SSL on

If I understood him correctly, he has no access to the server, so the only thing he did,
was to change the driver and he could connect again.

Thomas

On Tue, 2016-02-02 at 21:10 +0100, Thomas Kellerer wrote:
> Tom Lane schrieb am 02.02.2016 um 20:38:
> > > >

> > Since the quoted error message mentions a connection from
> > "127.0.0.1",
> > it's impossible that it is a reply from a remote server (unless
> > your
> > networking configuration is completely broken).  Presumably what is
> > happening is that what you think is a connection to a remote PG
> > server
> > is actually being made to localhost, and the local PG server's
> > pg_hba.conf
> > doesn't allow the username and/or dbname.  You could check that by
> > turning
> > on log_connections on both servers.
> >
> > As to why that would happen as a consequence of a driver version
> > change,
> > I'm pretty clueless, but I would wonder about differences in driver
> > config
> > files or the driver's interpretation of a connection URL.  In any
> > case,
> > certainly not enough information has been given to diagnose it.
>
>
> The initial error message in this thread was not from me.
>
> I am currently not in the office, but I will see if I can reproduce
> that tomorrow.
>
> I have also contacted the SQL Workbench/J user who reported this to
> me last week
> and asked him to describe his setup as well.
>
> In his case, the error message was:
>
> > FATAL: no pg_hba.conf entry for host "213.xxx.xx.xxx", user
> > "foobar", database "postgres", SSL on
>
> If I understood him correctly, he has no access to the server, so the
> only thing he did,
> was to change the driver and he could connect again.
>
> Thomas
>
>
>


I'm using a domain socket connection on my laptop. There is NO network.

I've been using JDBC drivers since Postgres 8.x. The pg_hba.conf file
has always been identical.

postgresql-9.4-1200.jdbc41.jar works fine on 9.4.
The issue is with 9.5.

So "local all all trust" according to the pg_hba.conf doco "translates"
to "localhost domain socket connection, any user, any database and
trust the person connecting" -- i.e. don't validate password.

The package org.postgresql.core.v3.ConnectionFactoryImpl doesn't
display enough information in its error message.
It should display the URL used, which has to be built using pg_hba.conf
plus user input.

This:-

con = DriverManager.getConnection(url, user, password);

is the usual way to connect.

I don't know if using port 5434 for 9.5 is an issue. I'm still using
9.4 and its port number is 5433. (Clutching at straws. Probable red
herring.)



Please let me know if there are any other tests that I can run.


Cheers,
Rob

On 2/2/2016 1:21 PM, rob stone wrote:
> I'm using a domain socket connection on my laptop. There is NO network.
>
> I've been using JDBC drivers since Postgres 8.x. The pg_hba.conf file
> has always been identical.

Java and JDBC do not support 'domain sockets' at all.    They default to
localhost (either 127.0.0.1 or ::1).



--
john r pierce, recycling bits in santa cruz

On Tue, 2016-02-02 at 19:20 +0200, Radoslav Petrov wrote:
> Hi Rob,
> try to uncomment the following in pg_hba.conf:
> # IPv4 local connections:
> #
> host    all             all             127.0.0.1/32            trust
> To become:
> # IPv4 local connections:
> host    all             all             127.0.0.1/32            trust
> You can check the documentation here: http://www.postgresql.org/docs/
> devel/static/auth-pg-hba-conf.html
>


Mea culpa!

I haven't a clue as to why I commented out that line. Losing my
marbles, perhaps.

I put that line back into the pg_hba.conf file and everything is now
working as intended.

My apologies for raising a non-issue.

Cheers,
Rob

Thomas Kellerer schrieb am 02.02.2016 um 21:10:
> I am currently not in the office, but I will see if I can reproduce that tomorrow.

I tried to reproduce this, but unfortunately I couldn't. And the server logs are also
no longer available (being just a development/test database)

One thing that might be interesting: the database was migrated from 9.4 to 9.5 using pg_upgrade

And the error occurred after the first start of 9.5. When I got the error, I remembered
the experience that was reported to me, that switching the drivers helped. So I switched the
driver (1206) and the connection worked. Now using 1207 again, the error unfortunately
does not occur any more.

The _server_ changed it's IP address at some point, I am not sure when that was or if
that is important at all.

As I can't reproduce this any longer, I'm fine with just disposing this as "probably a user error"

Thomas

On Thu, 2016-02-04 at 09:11 +0100, Thomas Kellerer wrote:
> Thomas Kellerer schrieb am 02.02.2016 um 21:10:
> > I am currently not in the office, but I will see if I can reproduce
> > that tomorrow.
>
> I tried to reproduce this, but unfortunately I couldn't. And the
> server logs are also 
> no longer available (being just a development/test database)
>
> One thing that might be interesting: the database was migrated from
> 9.4 to 9.5 using pg_upgrade
>
> As I can't reproduce this any longer, I'm fine with just disposing
> this as "probably a user error"
>
> Thomas
>
Hello Thomas,

Yes, it was just a "user error".
I'd compiled 9.5rc1 and was testing it out.
Weeks later I downloaded the deb binaries so as to have an "official"
version but overlooked pg_hba.conf changes.
Thus the JDBC connection failed.

So my understanding of this is that it looks up the IP address of
"localhost" and then trawls thru pg_hba.conf looking for an entry
containing that IP address, and if it exists, try the connection,
otherwise throw an error.

Cheers,
Robert