problem connecting from JDBC: not over - Mailing list pgsql-jdbc

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
samehost 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@msn.com]
Enviada em: segunda-feira, 10 de novembro de 2003 13:44
Para: pgsql-jdbc@postgresql.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