Problem
Script Example
<?php
// define constants
define('DB_HOST', '1.1.1.1');
define('DB_USER', 'usr');
define('DB_PASS', 'pw');
define('DB_NAME', 'db');
define('DB_PORT', '5432');
// connection string with SSL certificate files
$conn_str = 'host=' . DB_HOST . ' ';
$conn_str .= 'port=' . DB_PORT . ' ';
$conn_str .= 'dbname=' . DB_NAME . ' ';
$conn_str .= 'user=' . DB_USER . ' ';
$conn_str .= 'password=' . DB_PASS . ' ';
$conn_str .= 'sslmode=verify-full ';
$conn_str .= 'sslcert=etc/apache/ssl/postgresql.crt ';
$conn_str .= 'sslkey=etc/apache/ssl/postgresql.key ';
$conn_str .= 'sslrootcert=etc/apache/ssl/root.key ';
// attempt connection
$conn = pg_connect($conn_str) or die('Cannot connect to database.');
// set sql string
$sql = 'SELECT * FROM locations;';
// query and iterate results
if($result = pg_query($conn, $sql))
while($row=pg_fetch_row($result)) {
var_dump($row);
}
// close it up
pg_close();
?>
I am able to connect to an external SSL Postgres Database using xampp and PHP, but when I try it on my debian linux server, I am unable to establish a connection.
I suspect it is a pathing issue or permissions. I placed my certs in /var/www/html and /etc/ssl but neither paths on my production server work. In xampp, I just specify c:/xampp/htdocs/certs.key/.crt and they work fine.
What could be causing the issue on my production server?
Solution (Partial)
Since pg_last_error() and pg_errormessage() do not return errors for connection attempts, I had to create a custom exception handler. I attached the custom exception handler and finally got the error:
"pg_connect(): Unable to connect to PostgreSQL server: private key file "/etc/apache2/ssl/postgresql.key" has group or world access; permissions should be u=rw (0600) or less"
Next I attempted to CHMOD the key/crt
sudo chmod 0600 /etc/apache2/ssl/postgresql.crt
sudo chmod 0600 /etc/apache2/ssl/postgresql.key
This gave me the error
pg_connect(): Unable to connect to PostgreSQL server: could not read certificate file "/etc/apache2/ssl/postgresql.crt": Permission denied
I used the linux command 'ls -la', I noticed I was the owner, so I chown'd.
chown root:root /etc/apache2/ssl/postgresql.crt
chown root:root /etc/apache2/ssl/postgresql.key
Ok that didn't work, I added in my PHP script
echo exec('whoami');
That outputted 'www-data', OK so I entered
sudo chown -R www-data:www-data /etc/ssl/
chmod 700 /etc/ssl/
Finally it worked! Not sure about the safety of this, but this is my temporary solution.
Final Script
<?php
// Create a custom exception error handler for pg_connect
function exception_error_handler($errno, $errstr, $errfile, $errline ) {
throw new ErrorException($errstr, $errno, 0, $errfile, $errline);
}
// Set the error handler
set_error_handler("exception_error_handler");
// Force output of all errors
error_reporting(E_ALL);
// Define constants
define('DB_HOST', '1.1.1.1');
define('DB_USER', 'USR');
define('DB_PASS', 'PW');
define('DB_NAME', 'DB');
define('DB_PORT', '5432');
// Connection string with SSL certificate files
$conn_str = 'host=' . DB_HOST . ' ';
$conn_str .= 'port=' . DB_PORT . ' ';
$conn_str .= 'dbname=' . DB_NAME . ' ';
$conn_str .= 'user=' . DB_USER . ' ';
$conn_str .= 'password=' . DB_PASS . ' ';
$conn_str .= 'sslmode=require ';
$conn_str .= 'sslcert=/etc/apache2/ssl/postgresql.crt ';
$conn_str .= 'sslkey=/etc/apache2/ssl/postgresql.key ';
// Try catch block grabbing stored exception
try {
echo "Attempting pg_connect: " . $conn_str . "<br>";
$conn=@pg_connect($conn_str);
} catch (Exception $e) {
echo $e->getMessage();
}
// Attempt Connection
$conn = pg_connect($conn_str) or die('Cannot connect to database.');
// Set SQL String
$sql = 'SELECT * FROM locations';
// Attempt query and iterate results
if(result = pg_query($conn, $sql))
while($row=pg_fetch_row($result)) {
var_dump($row);
}
// Close it up
pg_close();
?>
