problem connecting from JDBC: not over

Hello,

I read all the messages of the thread start by Ravi Kanth and sought many others in group list and over the Internet.
Yet I am unable to solve the problem despite all the efforts put into.

The problem addressed here is the connection of client application started from a host different from that of PostGre.
That is, I can run perfectly my sample program (TestServer.java) in the same machine where PostGreSQL
resides (10.10.5.41) but not in elsewhere (from 10.10.6.11 a windows client and 10.10.1.61 a linux client)

I am providing all the details that might help your diagnostic of this case.
However, I do not want to overwhelm you with so much data. They are useful indeed.
At least this proves that I tried very hard before asking for help.

The Linux distribution is RedHat 9, and PostGreSQL 7.3.4, and PostGIS 0.7.5
The pg_hba.conf has the following lines
local all all trust
host all all 127.0.0.1 255.255.255.255 trust
host all all 10.10.5.41 255.255.255.255 trust
host all all 10.10.6.11 255.255.255.255 trust
host all all 10.10.1.61 255.255.255.255 trust

I tried postgresql.conf with
tcpip_socket = 1
or
tcpip_socket = true
or
tcpip_socket = on

and port = 5432

The postmaster command line where issued as follows
/usr/local/pgsql/bin/postmaster -i -S -D /usr/local/pgsql/data -p 5432 &

I can enter in a database session with:
psql -p 5432 test

The netstat -an | grep 10.10. produces the relevant line

tcp 0 0 10.10.5.41:22 10.10.6.11:2631 ESTABLISHED
tcp 0 0 10.10.5.41:22 10.10.5.168:2382 ESTABLISHED
tcp 0 0 10.10.5.41:22 10.10.6.11:2770 ESTABLISHED

The compilation realized with the following command:
C:\work\postgis-0.7.5\jdbc>javac -classpath .;C:\work\postgis-0.7.5\jdbc\lib\pg7
2jdbc2.jar;C:\work\postgis-0.7.5\jdbc\postgres.jar examples/TestServer.java

The execution of the test:
C:\work\postgis-0.7.5\jdbc>java -cp .;C:\work\postgis-0.7.5\jdbc\postgis.jar;C:\
work\postgis-0.7.5\jdbc\postgres.jar examples/TestServer

The java version is j2sdk1.4.1_05.

===================================================================
The error message from 6.11 starts here: ==========================

C:\work\postgis-0.7.5\jdbc>java -cp .;C:\work\postgis-0.7.5\jdbc\postgis.jar;C
work\postgis-0.7.5\jdbc\postgres.jar examples/TestServer
entrando: dbhost=10.10.5.41
entrando: createSQL=create table jdbc_test (geom geometry, id int4)
Creating JDBC connection...
getConnection: url=jdbc:postgresql://10.10.5.41/test
The connection attempt failed because Exception: java.net.SocketException: Con
ction reset by peer: connect
Stack Trace:

java.net.SocketException: Connection reset by peer: connect
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:305)
at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:171)
at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:158)
at java.net.Socket.connect(Socket.java:434)
at java.net.Socket.connect(Socket.java:384)
at java.net.Socket.<init>(Socket.java:291)
at java.net.Socket.<init>(Socket.java:119)
at org.postgresql.PG_Stream.<init>(PG_Stream.java:38)
at org.postgresql.jdbc1.AbstractJdbc1Connection.openConnection(Abstrac
dbc1Connection.java:160)
at org.postgresql.Driver.connect(Driver.java:122)
at java.sql.DriverManager.getConnection(DriverManager.java:512)
at java.sql.DriverManager.getConnection(DriverManager.java:171)
at examples.TestServer.main(TestServer.java:47)
End of Stack Trace

at org.postgresql.jdbc1.AbstractJdbc1Connection.openConnection(Abstrac
dbc1Connection.java:171)
at org.postgresql.Driver.connect(Driver.java:122)
at java.sql.DriverManager.getConnection(DriverManager.java:512)
at java.sql.DriverManager.getConnection(DriverManager.java:171)
at examples.TestServer.main(TestServer.java:47)

The error message from 6.11 ends here: ============================
===================================================================

///////////////////////////////////////////////////
The source code starts here: //////////////////////

package examples;

import java.sql.*;
import java.util.*;
import java.lang.*;
import org.postgis.*;

public class TestServer
{

public static void main(String[] args)
{
Connection conn;

String dbname = "test";
String dbuser = "postgres";
String dbpass = "";
String dbhost = "10.10.5.41";
String dbport = "5432";
String dbtable = "jdbc_test";

String dropSQL = "drop table " + dbtable;
String createSQL = "create table " + dbtable + " (geom geometry, id int4)";
String insertPointSQL = "insert into " + dbtable + " values ('POINT (10 10 10)',1)";
String insertPolygonSQL = "insert into " + dbtable + " values ('POLYGON ((0 0 0,0 10 0,10 10 0,10 0 0,0 0 0))',2)";

try {
System.out.println("entrando: dbhost="+dbhost);
System.out.println("entrando: createSQL="+createSQL);

System.out.println("Creating JDBC connection...");
Class.forName("org.postgresql.Driver").newInstance();
String url = "jdbc:postgresql://" + dbhost + ":" + dbport + "/" + dbname;
System.out.println("getConnection: url="+url);
conn = DriverManager.getConnection(url, dbuser, dbpass);
System.out.println("Adding geometric type entries...");
((org.postgresql.Connection)conn).addDataType("geometry","org.postgis.PGgeometry");
((org.postgresql.Connection)conn).addDataType("box3d","org.postgis.PGbox3d");
Statement s = conn.createStatement();
System.out.println("Creating table with geometric types...");
//table might not yet exist

System.out.println("Dropping table...");

s.execute(createSQL);
System.out.println("Inserting point...");
s.execute("create table "+dbtable+" (id int4)");
s.close();
conn.close();
}
catch( Exception e ) {
e.printStackTrace();
}
}
}

The source code ends here: //////////////////////
///////////////////////////////////////////////////

Finally I ask: is it an issue of granting access to machines and/or users?
What else I should double check either in the files, postmaster flags, or with linux command line such as netstat?
Is it an issue of network configuration? I can make ftp from point to point.

When changing the port in the postmaster -p and I change the corresponding port in the client program running in the same host it works properly as expected. If the port numbers do not have correspondence obvious it does not work.

I dearly thank you for your help and patience.
Carlos

-----Mensagem original-----
De: Ravi Kanth [mailto:ravi_kanth(at)msn(dot)com]
Enviada em: segunda-feira, 10 de novembro de 2003 13:44
Para: pgsql-jdbc(at)postgresql(dot)org
Assunto: [JDBC] problem connecting from JDBC

Hi,
I am trying to access postgresql database (7.3.2 - that came by default with
RedHat Linux 9 distro) using JDBC. I have a Linux user called "oracle" that
I have created in postgresql also and gave that user privileges to create a
database.
I tried to create a databse using createdb and it works.
The url that I am using in JDBC is: "jdbc:postgresql://localhost:5432/test",
"oracle", "". It reverts bck with error that says:
SQLException: Connection refused. Check that the hostname and port are
correct and that the postmaster is accepting TCP/IP connections.
I am using JDK 1.4.2 and NetBeans IDE.
Can anyone plz help? Thanks.

Ravi.

_________________________________________________________________
Compare high-speed Internet plans, starting at $26.95.
https://broadband.msn.com (Prices may vary by service area.)

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match