Thread: Java proxies connection to postgres

Could someone help me or suggest a solution? I want to connect from a computer that has firewall to other where the postgres server run. The problem is that computer (client) has a firewall and I have not access to configure it, or open ports, ping does not respond. The computer (server) where PostgreSQL has open ports but I can not connect to it from another because of firewall. I can only access the computer through proxy.
How I could with Java programming access remotely through proxy to postgres forgetting firewall?
 Java has a connection with proxies. But I dont now how put together with postgres connection.
Thank you all.
the same text in Spanish, sorry for bad English

Me podría ayudar alquien o sugerir la solución? Yo quiero conectarse desde ordenador que tiene firewall a servidor donde esta postgres. El problema esta que ordenador (cliente) tiene firewall y yo no tengo acceso para configurar a el, o abrir puertos, ping no contesta. El ordenador (servidor) donde esta postgre tiene abiertos puertos pero no puedo conectarme a el desde otro por causa de firewall. Solo puedo acceder a ordenador a través de proxy.
Como podría programando con Java acceder remotamente a través de proxy a postgres pasando el firewall?
 Java tiene conexión con  proxys. Pero no se como juntar con conexión a postgres.
Gracias a todos.

System.getProperties().put( "proxySet", "true" );
            System.getProperties().put( "proxyHost", "67.210.82.198" );
            System.getProperties().put( "proxyPort", "80" );
           
            URL validateURL = new URL("http://domain.com");
            URLConnection urlConnection = validateURL.openConnection();
           
            //how put together ???
           
            Class.forName("org.postgresql.Driver");
            Connection connection = DriverManager.getConnection("jdbc:postgresql://ipPublica:5432/DataBase","user", "pass");

On 08/08/10 17:24, - wrote:
> Could someone help me or suggest a solution? I want to connect from a
> computer that has firewall to other where the postgres server run. The
> problem is that computer (client) has a firewall and I have not access to
> configure it, or open ports, ping does not respond. The computer (server)
> where PostgreSQL has open ports but I can not connect to it from another
> because of firewall. I can only access the computer through proxy.
> How I could with Java programming access remotely through proxy to postgres
> forgetting firewall?
>  Java has a connection with proxies. But I dont now how put together with
> postgres connection.

What kind of proxy is it that you have access to?

Do you mean a regular HTTP proxy server? A SOCKS4 or SOCKS5 proxy? Or
something else?

If you can connect via a SOCKS proxy you should be able to set up a
socket to the PostgreSQL server via the socks proxy and use that with
the JDBC driver. You can specify the SOCKS proxy to use using the system
properties "socksProxyHost" and "socksProxyPort".

http://download.oracle.com/javase/7/docs/technotes/guides/net/properties.html

http://download-llnw.oracle.com/javase/6/docs/technotes/guides/net/proxies.html

There are also ways to configure this at runtime via a custom
SocketFactory if you can't change system properties.

If you don't have a SOCKS proxy to work with, and your proxy is
HTTP-only, you're in trouble. If the proxy supports SSL/TLS you might be
able to use its CONNECT command to proxy traffic, but it's probably not
going to be something you can do without writing a custom SocketFactory.

I can't help but wonder if it'd be a good idea to be able to pass a
custom SocketFactory to the JDBC driver, in much the same way you can
pass an SSLSocketFactory with the "sslfactory" JDBC URL argument. That
way, someone needing to do funky things like tunnel a JDBC connection
via a HTTPs proxy could do so without having to patch the driver, just
by handing it a suitable Socket instance when asked.

--
Craig Ringer

You can use ssh to create a tunnel between your local host and the
remote host over the ssh connection.

ssh -L 5432:localhost:5432 user@db.host

That will open port 5432 locally and any connections received by that
port will be forwarded to port 5432 on localhost as resolved from the
far side of the connection - so 'localhost' in that context is the
remote db server.  I create temporary proxies through firewalls via
ssh all the time and it works very nicely.  You can create a similar
proxy by ssh'ing out of the db server back to your workstation if you
cannot open a port for ssh on the db server and it isn't already
running sshd.    In that case, you use the -R parameter to specify the
remote port that will be forwarded back to the local port.

On Sun, Aug 8, 2010 at 7:04 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> On 08/08/10 17:24, - wrote:
>> Could someone help me or suggest a solution? I want to connect from a
>> computer that has firewall to other where the postgres server run. The
>> problem is that computer (client) has a firewall and I have not access to
>> configure it, or open ports, ping does not respond. The computer (server)
>> where PostgreSQL has open ports but I can not connect to it from another
>> because of firewall. I can only access the computer through proxy.
>> How I could with Java programming access remotely through proxy to postgres
>> forgetting firewall?
>>  Java has a connection with proxies. But I dont now how put together with
>> postgres connection.
>
> What kind of proxy is it that you have access to?
>
> Do you mean a regular HTTP proxy server? A SOCKS4 or SOCKS5 proxy? Or
> something else?
>
> If you can connect via a SOCKS proxy you should be able to set up a
> socket to the PostgreSQL server via the socks proxy and use that with
> the JDBC driver. You can specify the SOCKS proxy to use using the system
> properties "socksProxyHost" and "socksProxyPort".
>
> http://download.oracle.com/javase/7/docs/technotes/guides/net/properties.html
>
> http://download-llnw.oracle.com/javase/6/docs/technotes/guides/net/proxies.html
>
> There are also ways to configure this at runtime via a custom
> SocketFactory if you can't change system properties.
>
> If you don't have a SOCKS proxy to work with, and your proxy is
> HTTP-only, you're in trouble. If the proxy supports SSL/TLS you might be
> able to use its CONNECT command to proxy traffic, but it's probably not
> going to be something you can do without writing a custom SocketFactory.
>
> I can't help but wonder if it'd be a good idea to be able to pass a
> custom SocketFactory to the JDBC driver, in much the same way you can
> pass an SSLSocketFactory with the "sslfactory" JDBC URL argument. That
> way, someone needing to do funky things like tunnel a JDBC connection
> via a HTTPs proxy could do so without having to patch the driver, just
> by handing it a suitable Socket instance when asked.
>
> --
> Craig Ringer
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>

I planned to use free public proxy, which supports HTTPS

            //free public proxy finded in google.com
            System.setProperty("http.proxyHost", "189.19.44.164");
            System.setProperty("http.proxyPort", "3128");

            String url = "jdbc:postgresql://ipPublica:5432/DataBaseName";
            Properties props = new Properties();
            props.setProperty("user","User");
            props.setProperty("password","Pass");

            Class.forName("org.postgresql.Driver");
         
            Connection  connection = DriverManager.getConnection(url, props);

With this code I got to connect to postgres en server machine, but it fails in client machine whith firewall  :(
With that error

org.postgresql.util.PSQLException: Connection refused. Verify that the hostname and port are correct and that the postmaster is accepting TCP / IP.
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl (ConnectionFactoryImpl.java: 136)
at org.postgresql.core.ConnectionFactory.openConnection (ConnectionFactory.java: 66)
at org.postgresql.jdbc2.AbstractJdbc2Connection. <init> (AbstractJdbc2Connection.java: 125)
at org.postgresql.jdbc3.AbstractJdbc3Connection. <init> (AbstractJdbc3Connection.java: 30)
at org.postgresql.jdbc3g.AbstractJdbc3gConnection. <init> (AbstractJdbc3gConnection.java: 22)
at org.postgresql.jdbc4.AbstractJdbc4Connection. <init> (AbstractJdbc4Connection.java: 30)
at org.postgresql.jdbc4.Jdbc4Connection. <init> (Jdbc4Connection.java: 24)
at org.postgresql.Driver.makeConnection (Driver.java: 393)
at org.postgresql.Driver.connect (Driver.java: 267)

On Mon, Aug 9, 2010 at 6:34 AM, - <grandebuzon@gmail.com> wrote:
> Thanks for answering.
> I planned to use free public proxy, which supports HTTPS
>
>             //free public proxy finded in google.com
>             System.setProperty("http.proxyHost", "189.19.44.164");
>             System.setProperty("http.proxyPort", "3128");
>
>             String url = "jdbc:postgresql://ipPublica:5432/DataBaseName";
>             Properties props = new Properties();
>             props.setProperty("user","User");
>             props.setProperty("password","Pass");
>
>             Class.forName("org.postgresql.Driver");
>
>             Connection  connection = DriverManager.getConnection(url,
> props);
>
> With this code I got to connect to postgres en server machine, but it fails
> in client machine whith firewall  :(
> With that error
>
> org.postgresql.util.PSQLException: Connection refused. Verify that the
> hostname and port are correct and that the postmaster is accepting TCP / IP.
> at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl
> (ConnectionFactoryImpl.java: 136)
> at org.postgresql.core.ConnectionFactory.openConnection
> (ConnectionFactory.java: 66)
> at org.postgresql.jdbc2.AbstractJdbc2Connection. <init>
> (AbstractJdbc2Connection.java: 125)
> at org.postgresql.jdbc3.AbstractJdbc3Connection. <init>
> (AbstractJdbc3Connection.java: 30)
> at org.postgresql.jdbc3g.AbstractJdbc3gConnection. <init>
> (AbstractJdbc3gConnection.java: 22)
> at org.postgresql.jdbc4.AbstractJdbc4Connection. <init>
> (AbstractJdbc4Connection.java: 30)
> at org.postgresql.jdbc4.Jdbc4Connection. <init> (Jdbc4Connection.java: 24)
> at org.postgresql.Driver.makeConnection (Driver.java: 393)
> at org.postgresql.Driver.connect (Driver.java: 267)
>
>
>
>

These proxies are setup to proxy port 80 and port 443, They have no
knowledge of port 5432.

Dave

Please look at the attached source taken from a patched version of the 7.4
driver, which is a little out of date with respect to the latest drivers.
However it gives you the idea of what you need to do in order to modify one
of the latest drivers.
The main problem with all HTTP style proxies is that you can only specify
ports 80, 443 and sometimes 8080 and or 8000.
I've spent a lot of time with proxies and to get a reliable connection you
need to use SSL on port 443.
This means you must redirect incomming requests, on the firewall that serves
requests to your PostgreSQL server, from port 443 to port 5432. Which also
means that you cannot have a secure web-server on the same IP address as
your PostgreSQL server!

Regards
Donald Fraser

On 09/08/10 18:34, - wrote:
> Thanks for answering.
>
> I planned to use free public proxy, which supports HTTPS
>
>             //free public proxy finded in google.com
>             System.setProperty("http.proxyHost", "189.19.44.164");
>             System.setProperty("http.proxyPort", "3128");

Those properties only affect the HTTP client classes. They have nothing
to do with JDBC connections over custom protocols. Those lines have
absolutely no effect on the JDBC connection.

If you want to tunnel PgJDBC over a HTTP proxy, you will have to know a
***LOT*** more about how HTTP works and how HTTP proxies work, how the
PostgreSQL protocol works, how TCP/IP connections are created and
managed in Java, the Java HTTP client classes, etc.

Even then, it might not work. You would have to be able to use a HTTPs
proxy with CONNECT support to have any chance at all. Not all proxies
allow this method. A regular HTTP proxy can not possibly do what you
want. Even with a CONNECT-supporting proxy, you will have to be capable
of understanding and modifying the JDBC driver code to use a custom
SocketFactory that you have written. This SocketFactory would have to
set up a HTTP CONNECT tunneled connection via the proxy.

http://en.wikipedia.org/wiki/HTTP_tunnel#HTTP_CONNECT_Tunneling

I'm not sure that's a good thing to tackle, based on reading your mail.
If you really cannot get the proxy changed, and it's too smart to let
you just connect directly by having PostgreSQL listen on port 443
instead of the default port, you're going to be in trouble.

Instead, you might want to see if you can get an application server set
up on the same network as the database, and use that to do all your
database access over HTTP REST calls. It's ugly, but probably less ugly
and more reliable than trying to tunnel PgJDBC over a HTTPs
CONNECT-enabled proxy.

--
Craig Ringer

The problem with using an HTTP proxy to proxy a connection to your
database server is that an http proxy is surely counting on the
protocol to be http.  I imagine that just about any proxy made since
1995 supports arbitrary port numbers, but that really sin't
sufficient.  A proxy that is expecting to read and parse http headers
such as content-length and connection will totally fail to behave
correctly if those headers are not available. You need, at minimum,
what is called a SOCKS proxy.  A SOCKS proxy basically does nothing
but look at both sides of the proxied connection and blindly forward
any bytes from one side to the other. That will be able to handle most
protocols. An ssh tunnel is effectively a socks proxy across an ssh
connection.  There are also other SOCKS proxies available.

There's a decent description of the difference between socks and http
proxies here: http://en.wikipedia.org/wiki/SOCKS

The reason you are sometimes able to get a http proxy to function with
postgres via https on port 443 is because I imagine that most proxies
will drop into a socks-like mode when proxying https since they will
be unable intercept and interpret the content of the https requests
traversing the connection, since they'll be encrypted.  As such, it
just forwards packets back and forth.

On Mon, Aug 9, 2010 at 4:47 AM, Donald Fraser <postgres@kiwi-fraser.net> wrote:
> Please look at the attached source taken from a patched version of the 7.4
> driver, which is a little out of date with respect to the latest drivers.
> However it gives you the idea of what you need to do in order to modify one
> of the latest drivers.
> The main problem with all HTTP style proxies is that you can only specify
> ports 80, 443 and sometimes 8080 and or 8000.
> I've spent a lot of time with proxies and to get a reliable connection you
> need to use SSL on port 443.
> This means you must redirect incomming requests, on the firewall that serves
> requests to your PostgreSQL server, from port 443 to port 5432. Which also
> means that you cannot have a secure web-server on the same IP address as
> your PostgreSQL server!
>
> Regards
> Donald Fraser
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>

On Mon, Aug 9, 2010 at 8:04 AM, Samuel Gendler
<sgendler@ideasculptor.com> wrote:
> The problem with using an HTTP proxy to proxy a connection to your
> database server is that an http proxy is surely counting on the
> protocol to be http.  I imagine that just about any proxy made since
> 1995 supports arbitrary port numbers, but that really sin't
> sufficient.  A proxy that is expecting to read and parse http headers
> such as content-length and connection will totally fail to behave
> correctly if those headers are not available. You need, at minimum,
> what is called a SOCKS proxy.  A SOCKS proxy basically does nothing
> but look at both sides of the proxied connection and blindly forward
> any bytes from one side to the other. That will be able to handle most
> protocols. An ssh tunnel is effectively a socks proxy across an ssh
> connection.  There are also other SOCKS proxies available.
>
> There's a decent description of the difference between socks and http
> proxies here: http://en.wikipedia.org/wiki/SOCKS
>
> The reason you are sometimes able to get a http proxy to function with
> postgres via https on port 443 is because I imagine that most proxies
> will drop into a socks-like mode when proxying https since they will
> be unable intercept and interpret the content of the https requests
> traversing the connection, since they'll be encrypted.  As such, it
> just forwards packets back and forth.
>
> On Mon, Aug 9, 2010 at 4:47 AM, Donald Fraser <postgres@kiwi-fraser.net> wrote:
>> Please look at the attached source taken from a patched version of the 7.4
>> driver, which is a little out of date with respect to the latest drivers.
>> However it gives you the idea of what you need to do in order to modify one
>> of the latest drivers.
>> The main problem with all HTTP style proxies is that you can only specify
>> ports 80, 443 and sometimes 8080 and or 8000.
>> I've spent a lot of time with proxies and to get a reliable connection you
>> need to use SSL on port 443.
>> This means you must redirect incomming requests, on the firewall that serves
>> requests to your PostgreSQL server, from port 443 to port 5432. Which also
>> means that you cannot have a secure web-server on the same IP address as
>> your PostgreSQL server!

Not to mention the fact that the connection will not persist. Even if
you got this to work by modifying the listening port of the driver you
would have to open and close each request.

Perhaps you should tell us why and what your end goal is, there may be
a better solution.

Dave

On Mon, 9 Aug 2010, Samuel Gendler wrote:
> I imagine that just about any proxy made since 1995 supports arbitrary
> port numbers, but that really sin't sufficient.

Agreed, however the port numbers that the administrator allows to be
connected to will usually be severely restricted for security reasons.

Specifically, if you can use someone else's computer to connect to the
SMTP port on any computer in the world, you have got yourself an excellent
way of hiding where your spam is coming from. For that reason, a lot of
mail systems will refuse to receive mail from any system which is known as
an open proxy.

Matthew

--
 An ant doesn't have a lot of processing power available to it. I'm not trying
 to be speciesist - I wouldn't want to detract you from such a wonderful
 creature, but, well, there isn't a lot there, is there?
                                        -- Computer Science Lecturer

This all for develop in two machines, but having database and svn common en one machine.
Postgres is installed in both machines, but one of them(prefer

> The problem with using an HTTP proxy to proxy a connection to your
> database server is that an http proxy is surely counting on the
> protocol to be http.  I imagine that just about any proxy made since
> 1995 supports arbitrary port numbers, but that really sin't
> sufficient.  A proxy that is expecting to read and parse http headers
> such as content-length and connection will totally fail to behave
> correctly if those headers are not available. You need, at minimum,
> what is called a SOCKS proxy.

We have 30+ clients all using HTTP proxies to connect to our database. It
works be cause as previously stated you must use SSL. That is, as soon as
you specify the target port to be 443, the proxy has to let the packets pass
through un touched otherwise SSL handshake will not work. We haven't found a
HTTP proxy that doesn't work with this technique.
What gives us the most headaches are proxies with authentication and in
particular single sign-on authentication. In a lot of cases our clients have
to set up special rules for our software, which they hate doing !

>Not to mention the fact that the connection will not persist. Even if
>you got this to work by modifying the listening port of the driver you
>would have to open and close each request.
95% of our clients have no connection problems since secure connections are
not closed down immediately because how does the proxy know when a request
is complete or not? The proxy cannot inspect secure data. However there are
some nasty proxies that simply shut down a connection after x amount of
time, even when data is constantly flowing over the connection! Because of
the 5% that we do have connection problems with, we had to implement a
reconnect algorithm and to make it truly work you need session management
added to both client and the PostgreSQL server so that you can identify and
close down dead connections easily...

So in summary, while it is possible do achieve connections via proxies, if
you can avoid going down this path then avoid it. Take it from someone who
has done it, its a lot of hassle!

Another solution: package everything up and use a message queue system. Most
of the JMS implementations have support for messaging over HTTP (with single
sign-on support) and you get asynchronous messaging as opposed to
synchronous.

Regards
Donald Fraser

On Mon, 9 Aug 2010, Donald Fraser wrote:
> We have 30+ clients all using HTTP proxies to connect to our database. It
> works be cause as previously stated you must use SSL. That is, as soon as you
> specify the target port to be 443, the proxy has to let the packets pass
> through un touched otherwise SSL handshake will not work. We haven't found a
> HTTP proxy that doesn't work with this technique.

The proxies pass the data through to where? How does the proxy know where
to forward the data on to unless it can inspect the headers of the
request?

No, if you are using a general HTTP proxy for an outgoing connection, the
client software needs to be proxy aware, enough to tell the proxy which
server it wants to connect to. This is done using the CONNECT command. SSL
handshake occurs after that with the target server once that connection is
set up.

Matthew

--
*****  Support feudalism - Your Count Votes!  *****

>> We have 30+ clients all using HTTP proxies to connect to our database. It
>> works be cause as previously stated you must use SSL. That is, as soon as
>> you specify the target port to be 443, the proxy has to let the packets
>> pass through un touched otherwise SSL handshake will not work. We haven't
>> found a HTTP proxy that doesn't work with this technique.
>
> The proxies pass the data through to where? How does the proxy know where
> to forward the data on to unless it can inspect the headers of the
> request?
>
> No, if you are using a general HTTP proxy for an outgoing connection, the
> client software needs to be proxy aware, enough to tell the proxy which
> server it wants to connect to. This is done using the CONNECT command. SSL
> handshake occurs after that with the target server once that connection is
> set up.

Sorry if it wasn't clear but I never said that the client does not have to
be proxy aware...
Please read my first email with attached source code. In the source code is
how to attach to a proxy server, with or without basic authentication. I
probably didn't make it clear what was in that code...

Once connected to a proxy and you have specified HTTPS as the target
protocol (port 443), the proxy does not do any protocol inspection because
its expecting the byte stream to be encrypted and would therefore be a
pointless exercise. In this mode the HTTP proxy server, as stated by Samuel
Gendler, behaves much like a SOCKS proxy, passing un-inspected packets
between client and host.

You either need to modify the JDBC driver code to make it proxy aware (see
my original post for the source code on how) or you need to provide your own
implementation for the standard SocketFactory, via
Socket.setSocketImplFactory(SocketImplFactory fac), and provide your proxy
connection code here.

I'm not saying its easy and am with the majority here in saying its a lot of
effort to make it work and behave nicely and should be avoided where ever
possible!

Does either host run an ssh daemon (on any port) and allow connection
via ssh from the other machine?  If you can ssh between the two hosts,
in either direction, then you can connect to the database using the
technique in my first email on this thread.  Ssh will allow you to
send any other protocol across an established ssh connection.

On Mon, Aug 9, 2010 at 5:45 AM, - <grandebuzon@gmail.com> wrote:
> This all for develop in two machines, but having database and svn common en
> one machine.
> Postgres is installed in both machines, but one of them(prefer
> client) has firewall and short all connections to other(server).
>
>
>
>
>
> 2010/8/9 Matthew Wakeling <matthew@flymine.org>
>>
>> On Mon, 9 Aug 2010, Samuel Gendler wrote:
>>>
>>> I imagine that just about any proxy made since 1995 supports arbitrary
>>> port numbers, but that really sin't sufficient.
>>
>> Agreed, however the port numbers that the administrator allows to be
>> connected to will usually be severely restricted for security reasons.
>>
>> Specifically, if you can use someone else's computer to connect to the
>> SMTP port on any computer in the world, you have got yourself an excellent
>> way of hiding where your spam is coming from. For that reason, a lot of mail
>> systems will refuse to receive mail from any system which is known as an
>> open proxy.
>>
>> Matthew
>>
>> --
>> An ant doesn't have a lot of processing power available to it. I'm not
>> trying
>> to be speciesist - I wouldn't want to detract you from such a wonderful
>> creature, but, well, there isn't a lot there, is there?
>>                                       -- Computer Science Lecturer
>>
>> --
>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>

On 09/08/10 23:20, Donald Fraser wrote:

> You either need to modify the JDBC driver code to make it proxy aware
> (see my original post for the source code on how) or you need to provide
> your own implementation for the standard SocketFactory, via
> Socket.setSocketImplFactory(SocketImplFactory fac), and provide your
> proxy connection code here.

I'm wondering if the JDBC driver could  use a factory arg for the
regular socket factory, like it has for SSLSocketFactory with
"sslfactory".  That'd make this, and other situations using proxies with
weird auth schemes, various tunneling systems, etc much easier to use
without JDBC driver mods or changing system-wide behaviour for
potentially unrelated socket-using operations.

--
Craig Ringer

>> You either need to modify the JDBC driver code to make it proxy aware
>> (see my original post for the source code on how) or you need to provide
>> your own implementation for the standard SocketFactory, via
>> Socket.setSocketImplFactory(SocketImplFactory fac), and provide your
>> proxy connection code here.
>
> I'm wondering if the JDBC driver could  use a factory arg for the
> regular socket factory, like it has for SSLSocketFactory with
> "sslfactory".  That'd make this, and other situations using proxies with
> weird auth schemes, various tunneling systems, etc much easier to use
> without JDBC driver mods or changing system-wide behaviour for
> potentially unrelated socket-using operations.

As you stated is it would be very similar code to what is in place for the
SSL SocketFactory scenario.
So IMO I think it would be useful.

Donald