Обсуждение: Can't connect Win XP client to Linux server

Sorry for a complete newbie question.

I've just started experimenting with PostgreSQL and managed to get the
server running under CentOS 5.3. However, things didn't go smoothly
and it took a while to figure out that I needed to su to root, then su
to postgres before I could create roles for root and for my normal
login. Having done that for both as superusers, I can now define and
manipulate data via psql (version 8.1.11). For info, I installed
PostgreSQL on the server via the CentOS packet manager.

However, I cannot connect to this server from a Windows XP client
using either the 8.03.04.00 ANSI or Unicode drivers. At first, I got
the response:
    Could not connect to the server;
    No connection could be made because the target machine actively
    refused it.
    [192.168.42.106:5432]
when I click the Test button.

I can telnet onto the server and run the psql client in a telnet
session as the user I'm trying to connect as. I suspected I needed to
tweak the server to permit connections from remote clients but had no
idea how to do that other than set listen_addresses = '*' in
postgresql.conf. Having done that, I got another error message telling
me there wasn't an entry in pg_hba.conf. So to that file, I added the
line:
    host  all  all  192.168.42.0/24  md5
and after rebooting the server got the error:
  FATAL: password authentication failed for user "geoff"
I changed "md5" to "password" and rebooted the server but to no avail.
However, connection was successful when I changed "password" to
"trust" - but I'm uneasy about that level of security and would prefer
full authentication.

I've tried searching through the docs, FAQ's and the archives of this
list but failed to find a solution (although that could be due to not
knowing the most appropriate search terms!)

Can someone give me a few pointers?

TIA,

--
Geoff Lane

On 19/05/2009 20:48, Geoff Lane wrote:
> I can telnet onto the server and run the psql client in a telnet
> session as the user I'm trying to connect as. I suspected I needed to
> tweak the server to permit connections from remote clients but had no
> idea how to do that other than set listen_addresses = '*' in

Yes, that's exactly it - it listens only on localhost out of the box.

> postgresql.conf. Having done that, I got another error message telling
> me there wasn't an entry in pg_hba.conf. So to that file, I added the
> line:

That trips most people new to Postgres. :-)

>     host  all  all  192.168.42.0/24  md5
> and after rebooting the server got the error:
>   FATAL: password authentication failed for user "geoff"
> I changed "md5" to "password" and rebooted the server but to no avail.
> However, connection was successful when I changed "password" to
> "trust" - but I'm uneasy about that level of security and would prefer
> full authentication.

Are you supplying the correct username/password combination in the ODBC
connection? - It sounds like this sort of a problem.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

"Raymond O'Donnell" <rod@iol.ie> writes:
> On 19/05/2009 20:48, Geoff Lane wrote:
>> and after rebooting the server got the error:
>> FATAL: password authentication failed for user "geoff"

> Are you supplying the correct username/password combination in the ODBC
> connection? - It sounds like this sort of a problem.

Did you set a password for the "geoff" database user at all?
It isn't going to automatically use your system-level password...
see ALTER USER ... PASSWORD ...

            regards, tom lane

On Wednesday, May 20, 2009, Tom Lane wrote:

>> Are you supplying the correct username/password combination in the ODBC
>> connection? - It sounds like this sort of a problem.

> Did you set a password for the "geoff" database user at all?
> It isn't going to automatically use your system-level password...
> see ALTER USER ... PASSWORD ...


D'oh! I assumed that roles created via the "createuser" utility had
the password for the system user account. SELECT * FROM pg_roles;
showed passwords for all roles where I expected nulls or empty strings
where no password was set. Doing as you suggested did the trick.

My thanks both to Tom and to Raymond.

--
Geoff Lane