Thread: Problem to connect to the Windows Port

Problem to connect to the Windows Port

From
"Cornelia Boenigk"
Date:
Hello everybody

I have installed the Windows-Port on a the machine 'mini' in the local
network.
In postgresql.conf on mini listen_adresses is set to '*'
When trying to connect to this installation from the machine 'mach1' I
get the following error:

conni@mach1 ~
$ psql -h mini -U postgres -d minitest
psql: could not connect to server: Connection refused
        Is the server running on host "mini" and accepting
        TCP/IP connections on port 5432?

I shut down the database server on mini and restarted it with the
option -i.

Then the error looks like this:

conni@mach1 ~
$ psql -h mini -U postgres -d minitest
psql: FATAL:  no pg_hba.conf entry for host "192.168.1.2", user
"postgres", data
base "minitest"

All these errormessages appear on both machines.

The IP for mach1 is 192.168.1.2
The pg_hba.conf on mini contains the entries:

host    all               all                192.168.1.2
192.168.255.255   password
host    template1    postgres      192.168.1.2    192.168.255.255
password
host    minitest       postgres       192.168.1.2    192.168.255.255
password

Do I miss something?

Any help is appreciated.

Regards
Conni


Re: Problem to connect to the Windows Port

From
"Magnus Hagander"
Date:
>Hello everybody
>
>I have installed the Windows-Port on a the machine 'mini' in the local
>network.
>In postgresql.conf on mini listen_adresses is set to '*'
>When trying to connect to this installation from the machine 'mach1' I
>get the following error:
>
>conni@mach1 ~
>$ psql -h mini -U postgres -d minitest
>psql: could not connect to server: Connection refused
>        Is the server running on host "mini" and accepting
>        TCP/IP connections on port 5432?
>
>I shut down the database server on mini and restarted it with the
>option -i.
>
>Then the error looks like this:
>
>conni@mach1 ~
>$ psql -h mini -U postgres -d minitest
>psql: FATAL:  no pg_hba.conf entry for host "192.168.1.2", user
>"postgres", data
>base "minitest"
>
>All these errormessages appear on both machines.
>
>The IP for mach1 is 192.168.1.2
>The pg_hba.conf on mini contains the entries:
>
>host    all               all                192.168.1.2
>192.168.255.255   password
>host    template1    postgres      192.168.1.2    192.168.255.255
>password
>host    minitest       postgres       192.168.1.2    192.168.255.255
>password
>
>Do I miss something?

The pg_hba lines should be:
host all all 192.168.1.2 255.255.255.255 password
host template1 postgres 192.168.1.2 255.255.255.255 password
host minitest postgres 192.168.1.2 255.255.255.255 password

192.168.255.255 is not a valid netmask. The mask above will match one
host. If you meant to match the entire subnet (192.168.*.*), instead put
host minitest postgres 192.168.0.0 255.255.0.0 password


//Magnus

Re: Problem to connect to the Windows Port

From
"Cornelia Boenigk"
Date:
Hi Magnus

Thanks.

> If you meant to match the entire subnet (192.168.*.*), instead put
> host minitest postgres 192.168.0.0 255.255.0.0 password

Ok. I changed the line in pg_hba.conf to

host all all 192.168.0.0  255.255.0.0 password

then rebooted the computer and tried again:

C:\>psql -h 192.168.1.8 -U postgres -d minitest
psql: could not connect to server: Connection refused
        Is the server running on host "192.168.1.8" and accepting
        TCP/IP connections on port 5432?

The server on 192.168.1.8 is running, (I can connect on this machine
to localhost/minitest.).
I can ping 192.168.1.8

Regards
Conni


Re: Problem to connect to the Windows Port

From
Tom Lane
Date:
"Cornelia Boenigk" <poppcorn@cornelia-boenigk.de> writes:
> C:\>psql -h 192.168.1.8 -U postgres -d minitest
> psql: could not connect to server: Connection refused
>         Is the server running on host "192.168.1.8" and accepting
>         TCP/IP connections on port 5432?

"Connection refused" suggests that you've got a firewall-type problem,
ie the operating system is rejecting the connection rather than letting
the postmaster receive it.  Check packet filtering rules...

            regards, tom lane

Re: Problem to connect to the Windows Port

From
Doug McNaught
Date:
Tom Lane <tgl@sss.pgh.pa.us> writes:

> "Cornelia Boenigk" <poppcorn@cornelia-boenigk.de> writes:
>> C:\>psql -h 192.168.1.8 -U postgres -d minitest
>> psql: could not connect to server: Connection refused
>>         Is the server running on host "192.168.1.8" and accepting
>>         TCP/IP connections on port 5432?
>
> "Connection refused" suggests that you've got a firewall-type problem,
> ie the operating system is rejecting the connection rather than letting
> the postmaster receive it.  Check packet filtering rules...

It's also possible that the postmaster is listening on a port other
than 5342...

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

Re: Problem to connect to the Windows Port

From
Gaetano Mendola
Date:
Cornelia Boenigk wrote:

> Hi Magnus
>
> Thanks.
>
>
>>If you meant to match the entire subnet (192.168.*.*), instead put
>>host minitest postgres 192.168.0.0 255.255.0.0 password
>
>
> Ok. I changed the line in pg_hba.conf to
>
> host all all 192.168.0.0  255.255.0.0 password
>
> then rebooted the computer and tried again:
>
> C:\>psql -h 192.168.1.8 -U postgres -d minitest
> psql: could not connect to server: Connection refused
>         Is the server running on host "192.168.1.8" and accepting
>         TCP/IP connections on port 5432?
>
> The server on 192.168.1.8 is running, (I can connect on this machine
> to localhost/minitest.).
> I can ping 192.168.1.8

I could you execute on your server the following command:

netstat -an and look for the row that say LISTENING on port 5432 ?


Regards
Gaetano Mendola










Question about SQL statement error

From
Mário Gamito
Date:
Hi,

I'm a newbie at postgreSQL, although i have years of experience with MySQL.

My question is (and i tried to found the answer):
why this gives an error ?

SELECT url_negado INTO OUTFILE '/tmp/urls_negados.txt' FROM urls_negados;

Any help would be appreciated.

Sorry for the basic answer :(

Warm Regards,
Mário Gamito

Re: Question about SQL statement error

From
Michael Glaesemann
Date:
On Aug 25, 2004, at 8:37 PM, Mário Gamito wrote:

> why this gives an error ?
>
> SELECT url_negado INTO OUTFILE '/tmp/urls_negados.txt' FROM
> urls_negados;

I'm not sure whether this is a MySQLism or part of the SQL spec not
implemented in PostgreSQL, but I suspect the former. INTO OUTFILE is
not valid in PostgreSQL. You might want to look at the COPY command

<http://www.postgresql.org/docs/current/static/sql-copy.html>

, or perhaps using \o in psql to redirect results to a file.

<http://www.postgresql.org/docs/current/static/app-psql.html>

The PostgreSQL SQL command reference lists valid SQL syntax. I think
you'll find it useful.


Michael Glaesemann
grzm myrealbox com


Re: Question about SQL statement error

From
Richard Huxton
Date:
Mário Gamito wrote:
> Hi,
>
> I'm a newbie at postgreSQL, although i have years of experience with MySQL.

Hi Mario. No problem, but try not to reply to existing messages to post
a new question.

> My question is (and i tried to found the answer):
> why this gives an error ?
>
> SELECT url_negado INTO OUTFILE '/tmp/urls_negados.txt' FROM urls_negados;

INTO OUTFILE ... is not SQL.

 From psql you can use:
  \o MYFILE.TXT
  SELECT * FROM  ...

SQL syntax is in the "SQL COMMANDS" section of the manuals. See the
"client applications" section for details on psql.

HTH
--
   Richard Huxton
   Archonet Ltd

Re: Question about SQL statement error

From
Oliver Elphick
Date:
On Wed, 2004-08-25 at 12:37, Mário Gamito wrote:
> Hi,
>
> I'm a newbie at postgreSQL, although i have years of experience with MySQL.
>
> My question is (and i tried to found the answer):
> why this gives an error ?
>
> SELECT url_negado INTO OUTFILE '/tmp/urls_negados.txt' FROM urls_negados;
>
> Any help would be appreciated.

It isn't valid PostgreSQL syntax.  PostgreSQL tries to stick to SQL
standards as far as possible.  Use SELECT INTO to create a new table.

To dump to a flat file, use COPY:

  COPY urls_negado (url_negado) TO '/tmp/urls_negados.txt';

Nulls will be represented as \N

--
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
                 ========================================
     "But the day of the Lord will come as a thief in the
      night. The heavens shall pass away with a great noise,
      and the elements shall melt with fervent heat, and the
      earth and the works that are therein shall be burned
      up."                     II Peter 3:10


Re: Question about SQL statement error

From
Gaetano Mendola
Date:
Mário Gamito wrote:

> Hi,
>
> I'm a newbie at postgreSQL, although i have years of experience with MySQL.
>
> My question is (and i tried to found the answer):
> why this gives an error ?
>
> SELECT url_negado INTO OUTFILE '/tmp/urls_negados.txt' FROM urls_negados;
>
> Any help would be appreciated.

You can not do it in postgres in that way.
Try with this:

\o '/tmp/urls_negados.txt'
select url_negado from urls_negados;
\o




Regards
Gaetano Mendola


Re: Problem to connect to the Windows Port

From
"Cornelia Boenigk"
Date:
Hi all

> "Connection refused" suggests that you've got a firewall-type
problem,

The port 5432 is open in bith directions.
I can connect to remote databases from all machines and all OS without
any problem. Inside the local network I can access the databases in a
Cygwin-installation on another machine from the Windows Port but not
vice versa.

> netstat -an and look for the row that say LISTENING on port 5432 ?

On both machines there is the line
TCP    0.0.0.0:5432           0.0.0.0:0              LISTENING

Regards
Conni


Re: Question about SQL statement error

From
Marco Colombo
Date:
On Wed, 25 Aug 2004, [ISO-8859-1] Mário Gamito wrote:

> Hi,
>
> I'm a newbie at postgreSQL, although i have years of experience with MySQL.
>
> My question is (and i tried to found the answer):
> why this gives an error ?
>
> SELECT url_negado INTO OUTFILE '/tmp/urls_negados.txt' FROM urls_negados;
>
> Any help would be appreciated.
>
> Sorry for the basic answer :(
>
> Warm Regards,
> Mário Gamito
>

Check out COPY and \copy. COPY is server-side, thus it's executed on
the host the server runs on, with the permissions of the user the server
is running as. \copy is a psql command, thus client-side. It's executed
on the client host and with the permissions of the user running psql (you).
If you are writing your own client application, you can use
COPY ... FROM STDIN and COPY ... TO STDOUT (which is what psql does).

http://www.postgresql.org/docs/7.4/interactive/sql-copy.html
http://www.postgresql.org/docs/7.4/interactive/app-psql.html

.TM.
--
       ____/  ____/   /
      /      /       /            Marco Colombo
     ___/  ___  /   /              Technical Manager
    /          /   /             ESI s.r.l.
  _____/ _____/  _/               Colombo@ESI.it

Re: Problem to connect to the Windows Port

From
Jan Wieck
Date:
On 8/25/2004 3:11 PM, Cornelia Boenigk wrote:
> Hi all
>
>> "Connection refused" suggests that you've got a firewall-type
> problem,

Hi Conni,

Gruß aus Philadelphia

connection refused means at least that the machine responded to the
connection attempt, but that (it pretended?) nobody is listening on the
port.

The firewall I would set up would not respond at all, but simply drop
the packet. So I doubt you're facing a firewall problem here. Are you
sure that you are trying to contact the right host?


Jan

>
> The port 5432 is open in bith directions.
> I can connect to remote databases from all machines and all OS without
> any problem. Inside the local network I can access the databases in a
> Cygwin-installation on another machine from the Windows Port but not
> vice versa.
>
>> netstat -an and look for the row that say LISTENING on port 5432 ?
>
> On both machines there is the line
> TCP    0.0.0.0:5432           0.0.0.0:0              LISTENING
>
> Regards
> Conni
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Re: Problem to connect to the Windows Port

From
"Anony Mous"
Date:
I've been watching this thread because I am experience exactly the same
thing.  I can also repeat all the diagnostic results of Conni.  Postmaster
resides on a Win 2K box.  I can telnet to the server no problem.  My pg_hba
file line entries will allow access from all hosts, all users, all databases
on ip 192.168.0.0 with a mask of 0.0.0.0 method=trust.

Connection to the postmaster on the local machine works just fine, but I
just cannot get it to connect remotely.


-----Original Message-----
From: Jan Wieck [mailto:JanWieck@Yahoo.com]
Sent: August 25, 2004 8:27 PM
To: Cornelia Boenigk
Cc: pgsql-general
Subject: Re: Problem to connect to the Windows Port

On 8/25/2004 3:11 PM, Cornelia Boenigk wrote:
> Hi all
>
>> "Connection refused" suggests that you've got a firewall-type
> problem,

Hi Conni,

Gruß aus Philadelphia

connection refused means at least that the machine responded to the
connection attempt, but that (it pretended?) nobody is listening on the
port.

The firewall I would set up would not respond at all, but simply drop
the packet. So I doubt you're facing a firewall problem here. Are you
sure that you are trying to contact the right host?


Jan

>
> The port 5432 is open in bith directions.
> I can connect to remote databases from all machines and all OS without
> any problem. Inside the local network I can access the databases in a
> Cygwin-installation on another machine from the Windows Port but not
> vice versa.
>
>> netstat -an and look for the row that say LISTENING on port 5432 ?
>
> On both machines there is the line
> TCP    0.0.0.0:5432           0.0.0.0:0              LISTENING
>
> Regards
> Conni
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Re: Problem to connect to the Windows Port

From
"Anony Mous"
Date:
OK, I've found what worked for me.

By default, the pg_hba file installs like so:

# 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
# IPv6-style local connections:
host all all ::1 ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff trust

Once I removed the IPv6-style line, then I could add my own IP + Mask
combination and it worked fine (in my case this is 192.168.0.0
255.255.0.0)

Perhaps upon installation the IPv6 line should be "remmed" out by default?


-----Original Message-----
From: Anony Mous [mailto:A.Mous@shaw.ca]
Sent: August 26, 2004 5:55 AM
To: 'Jan Wieck'; 'Cornelia Boenigk'
Cc: 'pgsql-general'
Subject: RE: Problem to connect to the Windows Port

I've been watching this thread because I am experience exactly the same
thing.  I can also repeat all the diagnostic results of Conni.  Postmaster
resides on a Win 2K box.  I can telnet to the server no problem.  My pg_hba
file line entries will allow access from all hosts, all users, all databases
on ip 192.168.0.0 with a mask of 0.0.0.0 method=trust.

Connection to the postmaster on the local machine works just fine, but I
just cannot get it to connect remotely.


-----Original Message-----
From: Jan Wieck [mailto:JanWieck@Yahoo.com]
Sent: August 25, 2004 8:27 PM
To: Cornelia Boenigk
Cc: pgsql-general
Subject: Re: Problem to connect to the Windows Port

On 8/25/2004 3:11 PM, Cornelia Boenigk wrote:
> Hi all
>
>> "Connection refused" suggests that you've got a firewall-type
> problem,

Hi Conni,

Gruß aus Philadelphia

connection refused means at least that the machine responded to the
connection attempt, but that (it pretended?) nobody is listening on the
port.

The firewall I would set up would not respond at all, but simply drop
the packet. So I doubt you're facing a firewall problem here. Are you
sure that you are trying to contact the right host?


Jan

>
> The port 5432 is open in bith directions.
> I can connect to remote databases from all machines and all OS without
> any problem. Inside the local network I can access the databases in a
> Cygwin-installation on another machine from the Windows Port but not
> vice versa.
>
>> netstat -an and look for the row that say LISTENING on port 5432 ?
>
> On both machines there is the line
> TCP    0.0.0.0:5432           0.0.0.0:0              LISTENING
>
> Regards
> Conni
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #


Re: Problem to connect to the Windows Port

From
"Cornelia Boenigk"
Date:
Hi Jan

> but that (it pretended?) nobody is listening on the
> port.

netstat says it is listening on port 5432.

> Are you
> sure that you are trying to contact the right host?

Of course ;-)

Trying to connect from another machine running Win98 I get a socket
error with no description available ??

Regards
Conni


Re: Problem to connect to the Windows Port

From
"Cornelia Boenigk"
Date:
Hi Anony

> Once I removed the IPv6-style line, then I could add my own IP +
Mask
> combination and it worked fine (in my case this is 192.168.0.0
> 255.255.0.0)

This doesn't help ;-(
The connection is still refused.

Regards
Conni


Re: Problem to connect to the Windows Port

From
Jan Wieck
Date:
On 8/26/2004 2:18 PM, Cornelia Boenigk wrote:

> Hi Jan
>
>> but that (it pretended?) nobody is listening on the
>> port.
>
> netstat says it is listening on port 5432.
>
>> Are you
>> sure that you are trying to contact the right host?
>
> Of course ;-)
>
> Trying to connect from another machine running Win98 I get a socket
> error with no description available ??

oh wait ... this isn't by chance Windows-XP or some 3rd party firewall
that is blocking the traffic?


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Re: Problem to connect to the Windows Port

From
"Cornelia Boenigk"
Date:
Hi Jan

> oh wait ... this isn't by chance Windows-XP or some 3rd party
firewall
> that is blocking the traffic?

No, it's Win2k on both computers and the firewall is built in in the
DSL router.

If it was a problem with the firewall I couldn't connect from the Win
Port to Cygwin PostgreSQL either, isnt'it?

Regards
Conni