Thread: no pg_hba.conf entry for host

This is in part a diary entry, since I'm no longer so enthused or optimistic
about getting this to work, or whether it's practical for me.

I reread some instructions I got on this list, and decided to give them a
try. I configured Windows for an ODBC connection, possibly correctly. From
the very start, it wasn't clear to me which user's account information
needed to be used and altered, the Owner's or postgres's. The file pg_hba
seemed to be configured to allow everything, according to the docs, so I
didn't change it, but I got the fatal "no pg_hba.config entry" error. Then I
tried creating a pg_hba.config file for the Owner's account, which I intend
to access the database from. After I rebooted, permission was denied for the
whole /var/postgresql/ folder for the user Owner, and I still got the fatal
error. Then I went to the postgres user's account and saw that my edits to
pg_hba.config weren't there, but I was able to edit the file, so I did. I
rebooted, and still got the fatal error from both accounts.

The only edit I made to /var/postgresql/ was to replace the "127.0.0.1" from
"host    all         all         127.0.0.1         255.255.255.255   trust"
with the IP address I saw in the error message. Maybe I needed to replace
the mask too, but the only mask I saw at the command line was a subnet mask
(255.255.255.0), not an IP-mask. In fact, I think there were two different
ones, along with two different IP Addresses. I used the one under "Ethernet
adapter local area connection." The other title and entries aren't there at
the moment for some reason.

Still seems like this should be easier. Since this is for a product I might
sell, I think I should be looking at something more commercial that includes
an installer to make it easier for others, even if I myself am masochistic.
More likely, I'll use flat files. Wouldn't be so bad for my purposes anyway.

pg_hba.conf is configured to allow connections only from the pc the
database is installed on initially.  Only one login account exists by
default after a new installation and that is the postgres account.
Until you connect to the db the first time using the postgres account
and create new users accounts will you have the option of using your
Owner account.

For connecting from another windows pc other than the one postgres is
running on the default postgresql.conf file must be modified before
modifying pg_hba.conf.  Within postgresql.conf there is an entry
commented out, tcpip_socket = false, uncomment that line and change it
to equal true.
Now you can modify the pg_hba.conf.  Add a new line to that reads the
following:

host      all       all         XXX   255.255.255.255  trust

XXX = the ip address of the computer that your application is running
on.  If your ip address is say 192.168.123.250 it is ok to enter it in
pg_hba.conf as 192.168.123.0.  The .0 acts a wildcard in case your
computer is assigned a new ip address at a later time (hopefully it will
be in the same range as the original...)

Once the above is done you can reboot the computer to stop and restart
postgres.

Before using your application to connect to postgres might I suggest
that you use pgadminIII to connect to it first.  PgadminIII is a
graphical program specifically designed to access postgres, create users
/ tables / databases, issue commands etc.  A graphical program can save
you the trouble of having to learn some of the sql.  PgAdminIII can be
downloaded freely from http://www.pgadmin.org  If you choose this the
new connection to create within pgadmin would use a login of postgres,
select the trusted connection box, and you should connect.

Once the above is done with or without getting pgadminIII then modify
your windows ODBC connection to use the postgres account.
The userid would be postgres.  No password would be needed.  Your ODBC
connection will require a name of a database to connect to initially
however.  The only one that exists by default is template1.  Template1
is a system database and not to be used to store your applications data
in.  You really should create another database within postgres that your
application will write to.  You can use pgadminIII to do that with a few
clicks.

Hope that helps.

There are some books on postgresql out there that you can get which
might help as well.

In addition there is a mailing list on www.postgresql.org specifically
for novice users which might help as well.

Mike

On Sat, 2004-06-19 at 05:47, - Barry - wrote:
> This is in part a diary entry, since I'm no longer so enthused or optimistic
> about getting this to work, or whether it's practical for me.
>
> I reread some instructions I got on this list, and decided to give them a
> try. I configured Windows for an ODBC connection, possibly correctly. From
> the very start, it wasn't clear to me which user's account information
> needed to be used and altered, the Owner's or postgres's. The file pg_hba
> seemed to be configured to allow everything, according to the docs, so I
> didn't change it, but I got the fatal "no pg_hba.config entry" error. Then I
> tried creating a pg_hba.config file for the Owner's account, which I intend
> to access the database from. After I rebooted, permission was denied for the
> whole /var/postgresql/ folder for the user Owner, and I still got the fatal
> error. Then I went to the postgres user's account and saw that my edits to
> pg_hba.config weren't there, but I was able to edit the file, so I did. I
> rebooted, and still got the fatal error from both accounts.
>
> The only edit I made to /var/postgresql/ was to replace the "127.0.0.1" from
> "host    all         all         127.0.0.1         255.255.255.255   trust"
> with the IP address I saw in the error message. Maybe I needed to replace
> the mask too, but the only mask I saw at the command line was a subnet mask
> (255.255.255.0), not an IP-mask. In fact, I think there were two different
> ones, along with two different IP Addresses. I used the one under "Ethernet
> adapter local area connection." The other title and entries aren't there at
> the moment for some reason.
>
> Still seems like this should be easier. Since this is for a product I might
> sell, I think I should be looking at something more commercial that includes
> an installer to make it easier for others, even if I myself am masochistic.
> More likely, I'll use flat files. Wouldn't be so bad for my purposes anyway.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Hi Mike

>Within postgresql.conf there is an entry
> commented out, tcpip_socket = false, uncomment that line and change it
> to equal true.

I forgot to mention that I did that, except I kept the "false" line as a
comment and added an uncommented "true" version. I also added the following
line to pg_hba.conf:
host      all       all         XXX   255.255.255.255  trust
in which XXX is the ip address of my computer (the one listed under ethernet
adapter local area connection when I entered "ipconfig" at the command
prompt--not the one listed under PPP adapter). I might have changed
pg_hba.conf first, but as user postgres I changed both before I tried my
database-accessing Perl code. Then I got the error "no pg_hba.conf entry for
host" and checked those config files and they were still set correctly, and
that's when I made my previous post to this list, then uninstalled the ODBC
server and undid Windows' ODBC settings and gave up after a week of
problems.

The description of the "no pg_hba.conf entry for host" might be a little
misleading. It says:
----------------------
19.3. Authentication problems
Genuine authentication failures and related problems generally manifest
themselves through error messages like the following.
FATAL:  no pg_hba.conf entry for host "123.123.123.123", user "andym",
database "testdb"
This is what you are most likely to get if you succeed in contacting the
server, but it does not want to talk to you. As the message suggests, the
server refused the connection request because it found no authorizing entry
in its pg_hba.conf configuration file.
----------------------
As I said, pg_hba.conf contained the authorizing entry, assuming I used the
correct IP address of the two that the ipconfig showed me, and there's no
way to choose a user name or database in that file, though I got that error
from both users' accounts. Maybe the error was due to the database getting
corrupted because I later tried accessing the psql command prompt interface
with "psql -U postgres template1" and it didn't work as it had in the past.
Or maybe I had to delete one of the files (data maybe?), which appeared to
solve one of the numerous problems of the past, but I no longer remember
where I found that tip (the information I needed just to reach this level of
failure was scattered all over). Or maybe I needed to "start the database
server using /usr/bin/postmaster -D /usr/local/pgsql/data" as the command
prompt told me after one of the configuration steps, before trying my
script, though you said I could "Once the above is done you can reboot the
computer to stop and restart postgres." and I did reboot before getting that
error.

I can't tell you how good it feels now that I'm past all this and am doing
things the way I was strongly advised not to--by using the Perl script:
----------------------
$Cygwin_Addr = 'c:\cygwin\cygwin.bat';
open(Chan, "|$Cygwin_Addr");
print Chan "psql -U postgres template1";
----------------------
That appears to work. Soon I'll create a database other than template1, as
you mentioned I should, and see if I could add some print statements to the
above script for simple writing to and reading from the database. I'd use a
GUI, but this needs to be done programmatically. It seems like one benefit
of this method is, if for some reason me or any users of my product want to
access the database manually with psql at the command prompt, the exact
same, prewritten commands that I write for the script could be used.

Thanks again for the help, even if I'm going in a slightly different
direction for now.

Barry

The latest is I experienced numerous problems trying to do things from the
psql prompt, which means my idea of simulating that with a script wouldn't
work. I mistakenly entered "createdb mydb" at that prompt and got no
response. I forgot the order of things I did after that, but I saw that same
error about pg_hba.config not being set up correctly when it was, I tried
entering "createdb mydb" at the cygwin command prompt and got no response,
and I get no response with "psql -U postgres template1" now too.

I'm now afraid that I'd end up destroying my data somehow once I get to the
hard part, and I'm not even going to try any more. PostgreSQL will be
uninstalled in the morning.

I'm sure glad I was able to teach myself Perl and complete a complex 3500
line script or I'd think there was something wrong with me.