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 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>