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

Answer 1

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.