jdbc pg_hba.conf error

Bhavana.Rakesh wrote:

Hi,

I'm a newbee to postgreSQL. Does anyone know what this error means.

Couldn't connect: print out a stack trace and exit.
org.postgresql.util.PSQLException: A connection error has occurred:
org.postgres ql.util.PSQLException:
FATAL: no pg_hba.conf entry for host "127.0.0.1", user
"b rakesh", database "testing123", SSL
off

It means exactly what it says - you have no entry in your pg_hba.conf
for host "127.0.0.1"

Here is my pg_.conf file

[snip lots of commented-out lines]

# Using sockets credentials for improved security. Not available
everywhere,
# but works on Linux, *BSD (and probably some others)

local all all ident sameuser

This is the only uncommented line. Try uncommenting the line for
127.0.0.1 or adding your own if you don't want "trust" security.
--
Richard Huxton
Archonet Ltd

Hello,

On Wed, 2007-05-30 at 07:36 -0400, Bhavana.Rakesh wrote:

Here is my pg_.conf file

<snip>

Uncomment this line:

#host all all 127.0.0.1 255.255.255.255
trust

and reload PostgreSQL. And make sure that you read this part of the
manual:

http://www.postgresql.org/docs/current/static/client-authentication.html

Regards,
--
Devrim GÜNDÜZ
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/

1. Please don't forget to cc: the list
2. Please don't top-quote

Bhavana.Rakesh wrote:

Does this mean the pg_hba.conf file has to be edited everytime a new
database/schema is created?

No, only when you want to change network access permissions for the
installation as a whole. See the manuals for full details.

Of course, if you have different rules for each database you'll need to
make more changes than if you have two or three rules that apply to a
whole installation.

With 8.2 you can control access to databases via ROLEs as well as access
to schemas,tables etc.

--
Richard Huxton
Archonet Ltd

Hello,
I'm still getting the error

[brakesh@lnx383 ~/db_connect]$ java db_connect_pgsql.class
Checking if Driver is registered with DriverManager

Registered the driver ok, making DB connection now

Couldn't connect: print out a stack trace and exit.
org.postgresql.util.PSQLException: A connection error has occurred:
org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host
"127.0.0.1", user "brakesh", database "testing123", SSL off

at
org.postgresql.jdbc1.AbstractJdbc1Connection.openConnectionV3(AbstractJdbc1Connection.java:337)
at
org.postgresql.jdbc1.AbstractJdbc1Connection.openConnection(AbstractJdbc1Connection.java:214)
at org.postgresql.Driver.connect(Driver.java:139)
at java.sql.DriverManager.getConnection(DriverManager.java:559)
at java.sql.DriverManager.getConnection(DriverManager.java:189)
at db_connect_pgsql.main(db_connect_pgsql.java:25)

Here is my updated version of pg_hba.conf file
__________________________________________________________________________
#
# If you want to allow non-local connections, you need to add more
# "host" records. Also, remember TCP/IP connections are only enabled
# if you enable "tcpip_socket" in postgresql.conf.

# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
host all all trust

# IPv4-style local connections:
host all all 127.0.0.1 255.255.255.255 trust
host testing123 brakesh 127.0.0.1 255.255.255.255 trust
# IPv6-style local connections:
#host all all ::1/128
ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff trust

# Using sockets credentials for improved security. Not available everywhere,
# but works on Linux, *BSD (and probably some others)

#local all all ident sameuser

#Allow any user from any host with IP address 192.168.93.x to
# connect to database "template1" as the same username that ident on that
# host identifies him as (typically his Unix username):
#
#TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
host testing123 all 140.90.193.238 255.255.255.0 ident sameuser

On Wed, 2007-05-30 at 11:47 -0400, Bhavana.Rakesh wrote:

Hello,
I'm still getting the error

...

Here is my updated version of pg_hba.conf file
__________________________________________________________________________
#
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
host all all trust

That line is missing the IP-address and IP-mask parameters. It looks as
if the first word should be "local" rather than "host", in which case it
would be valid.

Using this pg_hba.conf ought to give you the message:
FATAL: missing or erroneous pg_hba.conf file
if you try to connect from psql. I don't know if the Java stuff somehow
manages to bypass it or if you haven't done a kill -SIGHUP of the
postmaster to reload the configuration.

--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
Do you want to know God? http://www.lfix.co.uk/knowing_god.html

I used the example in the following URL

http://www.postgresql.org/docs/7.3/static/client-authentication.html

Thanks for the catch on "host" instead of "local". I made that change,
and reloaded pgsql.. But I still get the same error. I can connect to
the database using psql client, but my java connection seems to be failing.

Oliver Elphick wrote:

Oliver,

When I do a :
psql -p 5000 testing123
I can make a connection. However, when I do a

psql -U brakesh -h 127.0.0.1 -d testing123

I get the followign error:

psql: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "brakesh", database "testing123", SSL off

Oliver Elphick wrote:

On Wed, May 30, 2007 at 12:30:38PM -0400, Bhavana.Rakesh wrote:

Oliver,

When I do a :
psql -p 5000 testing123
I can make a connection. However, when I do a

psql -U brakesh -h 127.0.0.1 -d testing123

I get the followign error:

psql: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "brakesh",
database "testing123", SSL off

Ofcourse, the first connection is a local connection, which you
obviously have configured. The latter connects to localhost, which you
havn't configured.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On Wed, 2007-05-30 at 12:30 -0400, Bhavana.Rakesh wrote:

Oliver,

When I do a :
psql -p 5000 testing123
I can make a connection.

Since you aren't specifying a host (with -h), that command uses a Unix
socket connection and only looks at lines in pg_hba.conf that begin with
"local".

(If you want to do the same in JDBC, make the hostname value blank.)

However, when I do a
psql -U brakesh -h 127.0.0.1 -d testing123

I get the followign error:

psql: FATAL: no pg_hba.conf entry for host "127.0.0.1", user
"brakesh", database "testing123", SSL off

What happens if you change "host" to "hostnossl" in pg_hba.conf?
(Seeing that that error message specifies that SSL is off.)
Please remember to SIGHUP or restart the postmaster after changing it.

Again, which version of PostgreSQL is this?

Oliver Elphick wrote:

On Wed, 2007-05-30 at 12:11 -0400, Bhavana.Rakesh wrote:

I used the example in the following URL

http://www.postgresql.org/docs/7.3/static/client-authentication.html

That does have local rather than host, of course.

Are you using PostgreSQL 7,3 as well?

Thanks for the catch on "host" instead of "local". I made that
change, and reloaded pgsql.. But I still get the same error. I can
connect to the database using psql client, but my java connection
seems to be failing.

Were you using the exact same parameters for psql?

psql -U brakesh -h 127.0.0.1 -d testing123

--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
Do you want to know God? http://www.lfix.co.uk/knowing_god.html

On Wed, 2007-05-30 at 18:35 +0200, Martijn van Oosterhout wrote:

On Wed, May 30, 2007 at 12:30:38PM -0400, Bhavana.Rakesh wrote:

Oliver,

When I do a :
psql -p 5000 testing123
I can make a connection. However, when I do a

psql -U brakesh -h 127.0.0.1 -d testing123

I get the followign error:

psql: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "brakesh",
database "testing123", SSL off

Ofcourse, the first connection is a local connection, which you
obviously have configured. The latter connects to localhost, which you
havn't configured.

His original message (which I snipped) said he had:

# IPv4-style local connections:
host all all 127.0.0.1 255.255.255.255 trust
host testing123 brakesh 127.0.0.1 255.255.255.255 trust

So it seems to me he did have it configured.

In fact the first host line should be used and the second one for user
brakesh is redundant, since it comes later in the file. The only thing
I can see is that it might be related to SSL.

--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
Do you want to know God? http://www.lfix.co.uk/knowing_god.html

Did you grant access to your user?

On Wed, 30 May 2007, Oliver Elphick wrote:

On Wed, 2007-05-30 at 18:35 +0200, Martijn van Oosterhout wrote:

On Wed, May 30, 2007 at 12:30:38PM -0400, Bhavana.Rakesh wrote:

Oliver,

When I do a :
psql -p 5000 testing123
I can make a connection. However, when I do a

psql -U brakesh -h 127.0.0.1 -d testing123

I get the followign error:

psql: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "brakesh",
database "testing123", SSL off

Ofcourse, the first connection is a local connection, which you
obviously have configured. The latter connects to localhost, which you
havn't configured.

His original message (which I snipped) said he had:

# IPv4-style local connections:
host all all 127.0.0.1 255.255.255.255 trust
host testing123 brakesh 127.0.0.1 255.255.255.255 trust

So it seems to me he did have it configured.

In fact the first host line should be used and the second one for user
brakesh is redundant, since it comes later in the file. The only thing
I can see is that it might be related to SSL.

--
Louis Gonzales
louis.gonzales@linuxlouis.net
http://www.linuxlouis.net

On Wed, 2007-05-30 at 13:00 -0400, gonzales@linuxlouis.net wrote:

Did you grant access to your user?

If you mean grant access by an SQL GRANT, he hasn't got far enough to
check that. The error specifically says "no pg_hba.conf entry". As far
as I can see, his pg_hba.conf is OK.

On Wed, 30 May 2007, Oliver Elphick wrote:

On Wed, 2007-05-30 at 18:35 +0200, Martijn van Oosterhout wrote:

On Wed, May 30, 2007 at 12:30:38PM -0400, Bhavana.Rakesh wrote:

Oliver,

When I do a :
psql -p 5000 testing123
I can make a connection. However, when I do a

psql -U brakesh -h 127.0.0.1 -d testing123

I get the followign error:

psql: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "brakesh",
database "testing123", SSL off

Ofcourse, the first connection is a local connection, which you
obviously have configured. The latter connects to localhost, which you
havn't configured.

His original message (which I snipped) said he had:

# IPv4-style local connections:
host all all 127.0.0.1 255.255.255.255 trust
host testing123 brakesh 127.0.0.1 255.255.255.255 trust

So it seems to me he did have it configured.

In fact the first host line should be used and the second one for user
brakesh is redundant, since it comes later in the file. The only thing
I can see is that it might be related to SSL.

--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
Do you want to know God? http://www.lfix.co.uk/knowing_god.html

I'm using version 7.4. The "hostnossl" is not helping either. My error is

[brakesh@lnx383 ~]$ psql -U brakesh -h 127.0.0.1 -d testing123
psql: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "brakesh",
database "testing123", SSL off

The current pg_hba.conf file is as follows:

# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
local all all trust

# IPv4-style local connections:
#host all all 127.0.0.1 255.255.255.255 trust
hostnossl all all 127.0.0.1 255.255.255.255 trust
hostnossl testing123 brakesh 127.0.0.1
255.255.255.255 trust
# IPv6-style local connections:
#host all all ::1/128
ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff trust

# Using sockets credentials for improved security. Not available everywhere,
# but works on Linux, *BSD (and probably some others)

#local all all ident sameuser

#Allow any user from any host with IP address 192.168.93.x to
# connect to database "template1" as the same username that ident on that
# host identifies him as (typically his Unix username):
#
#TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
hostnossl testing123 all 140.90.193.238 255.255.255.0 ident
sameuser

Oliver Elphick wrote:

As super-user (postgres) you have to create the user in Postgres, then
Grant access. In other words, if the pg_hba.conf file specifies a user
who does not exist, "user brakesh does not exist" will cause a failure to
connect as well.

Every connection to a database, has to have a user associated with the
session.

So the fact that he has connected to at least one database, means he's
gotten far enough to connect and create the proper ID/Authentication.

Actually, I wonder if 'after' he's changed the pg_hba.conf file if he's
been restarting the postgres process? Which is 'session' associated.

Everytime you change the pg_hba.conf file, you have to restart postgres,
don't you?

On Wed, 30 May 2007, Oliver Elphick wrote:

On Wed, 2007-05-30 at 13:00 -0400, gonzales@linuxlouis.net wrote:

Did you grant access to your user?

If you mean grant access by an SQL GRANT, he hasn't got far enough to
check that. The error specifically says "no pg_hba.conf entry". As far
as I can see, his pg_hba.conf is OK.

On Wed, 30 May 2007, Oliver Elphick wrote:

On Wed, 2007-05-30 at 18:35 +0200, Martijn van Oosterhout wrote:

On Wed, May 30, 2007 at 12:30:38PM -0400, Bhavana.Rakesh wrote:

Oliver,

When I do a :
psql -p 5000 testing123
I can make a connection. However, when I do a

psql -U brakesh -h 127.0.0.1 -d testing123

I get the followign error:

psql: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "brakesh",
database "testing123", SSL off

Ofcourse, the first connection is a local connection, which you
obviously have configured. The latter connects to localhost, which you
havn't configured.

His original message (which I snipped) said he had:

# IPv4-style local connections:
host all all 127.0.0.1 255.255.255.255 trust
host testing123 brakesh 127.0.0.1 255.255.255.255 trust

So it seems to me he did have it configured.

In fact the first host line should be used and the second one for user
brakesh is redundant, since it comes later in the file. The only thing
I can see is that it might be related to SSL.

--
Louis Gonzales
louis.gonzales@linuxlouis.net
http://www.linuxlouis.net

Yes, I have been restarting the postgres every time I make changes to
the pg_hba.conf file.

-Bhavana
On a lighter note, it's a 'she' not 'he'. :) No offense taken. :))

gonzales@linuxlouis.net wrote:

Oliver Elphick <olly@lfix.co.uk> writes:

His original message (which I snipped) said he had:

# IPv4-style local connections:
host all all 127.0.0.1 255.255.255.255 trust
host testing123 brakesh 127.0.0.1 255.255.255.255 trust

So it seems to me he did have it configured.

I've seen similar problems resolved by discovering that (1) the DBA
was editing the wrong copy of the pg_hba.conf file, or (2) there was
actually more than one postmaster running on the machine.

Check "ps" for multiple postmasters. Put a deliberate error in the
pg_hba.conf file and verify that the postmaster fails to restart.

regards, tom lane

Did you ever "createuser brakesh" ???

Apologies about the he,she ;)... he + s = she, see you're greater of a
person than a 'he.' Heehee.

On Wed, 30 May 2007, Bhavana.Rakesh wrote:

Yes, I have been restarting the postgres every time I make changes to the
pg_hba.conf file.

-Bhavana
On a lighter note, it's a 'she' not 'he'. :) No offense taken. :))

gonzales@linuxlouis.net wrote:

As super-user (postgres) you have to create the user in Postgres, then
Grant access. In other words, if the pg_hba.conf file specifies a user who
does not exist, "user brakesh does not exist" will cause a failure to
connect as well.

Every connection to a database, has to have a user associated with the
session.

So the fact that he has connected to at least one database, means he's
gotten far enough to connect and create the proper ID/Authentication.

Actually, I wonder if 'after' he's changed the pg_hba.conf file if he's
been restarting the postgres process? Which is 'session' associated.

Everytime you change the pg_hba.conf file, you have to restart postgres,
don't you?

On Wed, 30 May 2007, Oliver Elphick wrote:

On Wed, 2007-05-30 at 13:00 -0400, gonzales@linuxlouis.net wrote:

Did you grant access to your user?

If you mean grant access by an SQL GRANT, he hasn't got far enough to
check that. The error specifically says "no pg_hba.conf entry". As far
as I can see, his pg_hba.conf is OK.

On Wed, 30 May 2007, Oliver Elphick wrote:

On Wed, 2007-05-30 at 18:35 +0200, Martijn van Oosterhout wrote:

On Wed, May 30, 2007 at 12:30:38PM -0400, Bhavana.Rakesh wrote:

Oliver,

When I do a :
psql -p 5000 testing123
I can make a connection. However, when I do a

psql -U brakesh -h 127.0.0.1 -d testing123

I get the followign error:

psql: FATAL: no pg_hba.conf entry for host "127.0.0.1", user
"brakesh",
database "testing123", SSL off

Ofcourse, the first connection is a local connection, which you
obviously have configured. The latter connects to localhost, which you
havn't configured.

His original message (which I snipped) said he had:

# IPv4-style local connections:
host all all 127.0.0.1 255.255.255.255
trust
host testing123 brakesh 127.0.0.1 255.255.255.255
trust

So it seems to me he did have it configured.

In fact the first host line should be used and the second one for user
brakesh is redundant, since it comes later in the file. The only thing
I can see is that it might be related to SSL.

--
Louis Gonzales
louis.gonzales@linuxlouis.net
http://www.linuxlouis.net

Ok,
I confirmed that I'm editing the right pg_hba.conf file. I made sure
that there are no other postmasters running. I made sure that there is
a user called 'brakesh'. I restart the postmaster everytime I make any
changes to pg_hba.conf file. But still same results!

[brakesh@lnx383 ~/db_connect]$ psql -U brakesh -h 127.0.0.1 -d testing123
psql: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "brakesh",
database "testing123", SSL off

[brakesh@lnx383 ~/db_connect]$ psql -p 5000 testing123
Welcome to psql 7.4.17, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
______________________________________
Here is my pg_hba.conf file again. I've commented the different records
that I've experimented with. But none of them worked. Of course, when
i commented out the first record
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
local all all trust

I couldn't connect using the 'psql -p 5000 testing123'..which confirmed
that I'm editing the right pg_hba.conf file. My current working copy of
pg_hba.conf file follows:

____________________________________________

# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
local all all trust

# IPv4-style local connections:
#host all all 127.0.0.1 255.255.255.255 trust
#host testing123 brakesh 127.0.0.1 255.255.255.255 trust
hostnossl testing123 brakesh 127.0.0.1 255.255.255.255 trust
#hostnossl all all 127.0.0.1 255.255.255.255 trust
#hostnossl testing123 brakesh 127.0.0.1
255.255.255.255 trust
# IPv6-style local connections:
#host all all ::1/128
ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff trust

#Allow any user from any host with IP address 140.90.193.238 to
# connect to database "testing123" as the same username that ident on that
# host identifies him as (typically his Unix username):
#
#TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
#hostnossl testing123 all 140.90.193.238 255.255.255.0 ident
sameuser

Tom Lane wrote:

On 5/31/07, Bhavana.Rakesh <Bhavana.Rakesh@noaa.gov> wrote:

Ok,
I confirmed that I'm editing the right pg_hba.conf file. I made sure that
there are no other postmasters running. I made sure that there is a user
called 'brakesh'. I restart the postmaster everytime I make any changes to
pg_hba.conf file. But still same results!

[brakesh@lnx383 ~/db_connect]$ psql -U brakesh -h 127.0.0.1 -d testing123
psql: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "brakesh",
database "testing123", SSL off

[brakesh@lnx383 ~/db_connect]$ psql -p 5000 testing123
Welcome to psql 7.4.17, the PostgreSQL interactive terminal.

Why are you specifying the port number when you don't include the host?

What happens if you do include the port:

psql -U brakesh -p 5000 -h 127.0.0.1 -d testing123

--
Postgresql & php tutorials
http://www.designmagick.com/