Re: problem connecting from JDBC: not over

Do you have a firewall between you and the postgres machine?

Dave
On Tue, 2003-11-18 at 16:01, Carlos Ho Shih Ning wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
>