PHP Connect to PostgreSQL using ssh2_tunnel

Question

I'm busy trying to connect to a PostgreSQL database (running on a different server) after creating an ssh2_tunnel to that server.

The ssh2_tunnel seems to be working fine but my pg_connect is not working and I'm hoping I'm missing something small

<?php


ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);


echo "<span>test script<br /></span>";


$connection = ssh2_connect('xxx.xx.xx.xx', 22);


if (ssh2_auth_pubkey_file($connection, 'usname', './ssh_key.pub', './ssh_key', 'psword')) {
    echo "<span>Public Key Authentication Successful<br /></span>";
} else {
    die('Public Key Authentication Failed');
}


if ($tunnel = ssh2_tunnel($connection, '127.0.0.1', 5432)) {
    echo "<span>Tunnel Successful<br /></span>";
} else {
    die('Tunnel Failed');
};


// this is where it is failing and I'm not sure why
$string = "host=127.0.0.1 port=5432 dbname=dbname user=dbuser password=dbpass";
$pgsqlcon = pg_connect($string) or die('Could not connect: ' . pg_last_error());


?>

It is giving me the following error

Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: Connection refused Is the server running on host "127.0.0.1" and accepting TCP/IP connections on port 5432?

Another error from pg_last_error

Warning: pg_last_error(): No PostgreSQL link opened yet in /home/shoepack/public_html/admin_php_ssh/notused.php on line 26

Alex Blex · Accepted Answer · 2015-11-03 10:15:53Z

7

Sorry, it is not going to work this way. ssh2_tunnel creates a remote file pointer, aka resource, to be used in php functions like fgets(), fwrite() etc. It is not the same with ssh port forwarding.

You can try to open ssh tunnel on your php server form the shell: ssh [email protected] -i ./ssh_key -L 5555:localhost:5432. While the session is alive, you should be able to connect to the database from your php script as pg_connect("host=127.0.0.1 port=5555 dbname=dbname user=dbuser password=dbpass");

It is not for production use of course. What you need for production is to allow access to the database from your php application server. You may need to edit postgresql.conf to ensure the server is bound to correct interface, and pg_hba.conf to allow connections from your php host.

answered Nov 3, 2015 at 10:15

Alex Blex

37.3k7 gold badges53 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TheLovelySausage Over a year ago

Not the answer I was hoping for but removing the ssh accessibility and forwarding the postgresql port directly seems to work ok, do you have any advice on making the postgresql connection more secure though?

Alex Blex Over a year ago

It depends on what you have now, your vectors, and how far you are ready to proceed. Some general advices: allow as little hosts to connect as possible, enable ssl, use pgcrypto. Worth reading: postgresql.org/docs/9.4/static/auth-pg-hba-conf.html

TheLovelySausage Over a year ago

This looks really useful I really appreciate the help

Collectives™ on Stack Overflow

PHP Connect to PostgreSQL using ssh2_tunnel

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related