PHP - Connecting to heroku PostGres database from localhost (on Windows)

Question

I'm trying to work with a PostGres database hosted on heroku, I need to connect to it from my local machine. Heres an example of the credentials I'm using:

params:
  driver: pdo_pgsql
  host: e5-31-281-414-28.compute-1.amazonaws.com
  dbname: b6m44bwjmaa41n
  user: sgjwiakskki
  password: e4ks28ajgs87
  port: 5432

^^ I altered those credentials, but thats the format they're in.

When I try connecting using pg_connect, like this:

$dbconn = pg_connect("e5-31-281-414-28.compute-1.amazonaws.com port=5432 dbname=b6m44bwjmaa41n user=sgjwiakskki password=e4ks28ajgs87") or die('Could not connect: ' . pg_last_error());

I get this error:

pg_connect(): Unable to connect to PostgreSQL server: could not translate host name "***.compute-1.amazonaws.com" to address: Unknown host 

When I try connecting using Doctrines DBAL package, I get the same error:

Uncaught exception 'PDOException' with message 'SQLSTATE[08006] [7] could not translate host name "e5-31-281-414-28.compute-1.amazonaws.com" to address: Unknown host' in C:\wamp64\www\project\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\AbstractPostgreSQLDriver.php on line 85

I read all the tutorials on connecting to heroku from outside, but I can't figure this one out. I can connect through the command line using the heroku toolbelt. I can connect with pgAdmin 3. But can't seem to connect with a web app like this.

Answer 1

as manual says, you should try sth. like below.

http://php.net/manual/en/function.pg-connect.php

$dbconn = pg_connect("host=e5-31-281-414-28.compute-1.amazonaws.com port=5432 dbname=b6m44bwjmaa41n user=sgjwiakskki password=e4ks28ajgs87");

Description
resource pg_connect ( string $connection_string [, int $connect_type ] )

The connection_string can be empty to use all default parameters, or it can contain one or more parameter settings separated by whitespace. Each parameter setting is in the form keyword = value. Spaces around the equal sign are optional. To write an empty value or a value containing spaces, surround it with single quotes, e.g., keyword = 'a value'. Single quotes and backslashes within the value must be escaped with a backslash, i.e., \' and \.

UPDATE:

I didn't have php. So, I've just installed php5.6 and php5.6-pqsql packages from old ubuntu repositories (I'm using Linux Mint 18.1, 32bit)

$ date
Tue Dec 27 00:49:11 +04 2016
$ grep install /var/log/dpkg.log

2016-12-27 00:05:11 install php-common:all <none> 1:47+deb.sury.org~xenial+1
...
2016-12-27 00:05:22 install php5.6:all <none> 5.6.29-1+deb.sury.org~xenial+1
...
2016-12-27 00:22:26 status installed php5.6-pgsql:i386 5.6.29-1+deb.sury.org~xenial+1
... 

Then created account on heroku. Set up a database, which I'm adding connection credentials (as I don't need it).

Host
    ec2-54-217-212-8.eu-west-1.compute.amazonaws.com
Database
    d464heusrdn2k5
User
    mgtvhefjhzilai
Port
    5432
Password
    fd6aa97d31b25c26a41a8211008f1838abd76d7715dcac7c742bfc55f37bd50f

With above given,

$ php -v
PHP 5.6.29-1+deb.sury.org~xenial+1 (cli) 
Copyright (c) 1997-2016 The PHP Group
Zend Engine v2.6.0, Copyright (c) 1998-2016 Zend Technologies
    with Zend OPcache v7.0.6-dev, Copyright (c) 1999-2016, by Zend Technologies

$ php -a
Interactive mode enabled

php > $dbconn = pg_connect("host=ec2-54-217-212-8.eu-west-1.compute.amazonaws.com port=5432 dbname=d464heusrdn2k5 user=mgtvhefjhzilai password=fd6aa97d31b25c26a41a8211008f1838abd76d7715dcac7c742bfc55f37bd50f");
php > $sql = "create table lorem (id int not null, foo varchar(15), primary key (id));";
php > $r = pg_query($dbconn, $sql);
php > $tbls = "select * from information_schema.tables";
php > $qr = pg_query($dbconn, $tbls);
php > while($r = pg_fetch_array($qr)) {
php { print_r($r);
php { }
Array
(
    [0] => d464heusrdn2k5
    [table_catalog] => d464heusrdn2k5
    [1] => public
    [table_schema] => public
    [2] => lorem
    [table_name] => lorem
    [3] => BASE TABLE
    [table_type] => BASE TABLE
    [4] => 
    [self_referencing_column_name] => 
    [5] => 
    [reference_generation] => 
    [6] => 
    [user_defined_type_catalog] => 
    [7] => 
    [user_defined_type_schema] => 
    [8] => 
    [user_defined_type_name] => 
    [9] => YES
    [is_insertable_into] => YES
    [10] => NO
    [is_typed] => NO
    [11] => 
    [commit_action] => 
)
Array

Answer 2

Thanks marmeladze! By going out of your way like that and showing me it works for you, I could see the problem was likely I was using wrong credentials. Turns out there were two problems, wrong credentials was the first one. And the second one was that I needed to put sslmode=require into the $connection_string. So the string became something like this:

$dbconn = pg_connect("host=ec2-**-***-***-45.compute-1.amazonaws.com port=5432 dbname=e4ngs3d4risaw9 user=s52mt674s3sjd password=p31mgss562sts7e3kebie3j sslmode=require")

After I "attempted" to fix the credentials (notice the password was a different format, I had the wrong variable assigned to that), the error message unable to resolve host error went away, and was replaced by two new errors. The first oen was:

Unable to connect to PostgreSQL server: FATAL: password authentication failed for user

and the second one I forgot to save, but it said something about the username supplied not appearing in the pg_hba.conf file, for the host provided (it gave an IP for the host). I tried editing the pg_hba.conf file, but later found an article on common error messages and what they mean: https://devcenter.heroku.com/articles/postgres-logs-errors#fatal-no-pg_hba-conf-entry-for-host-user-u-database-d-ssl-off and this one means that you're attempting to connect without SSL.

FATAL: no pg_hba.conf entry for host "…”, user “u…”, database “d…”, SSL off

You are attempting to establish a database connection without SSL. SSL is required for all connections. Please see the documentation for your postgres driver on how to establish encrypted connections.

So adding sslmode=require fixed that one. And then the authentication problem one was obviously an issue with the password, I double checked and there was a letter missing in the password. Fixing that resolved all the problems, now I can connected. Thanks again!

UPDATE: Just wanted to add another error I encountered, and the cause of it. I tried connecting with DBAL instead, and got this error:

PDOException' with message 'SQLSTATE[08006] [7] FATAL: password authentication failed for user "SYSTEM"' in C:\wamp64\www\myproject\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\AbstractPostgreSQLDriver.php on line 85

It was trying to connect with the SYSTEM user. The problem was my YAML file, it had username: hs62ksi62sj2, when it should have been user: hs62ksi62sj2

So the working YAML file is:

params:
  driver: pdo_pgsql
  host: ec2-**-***-***-45.compute-1.amazonaws.com
  dbname: e4ngs3d4risaw9 
  user: s52mt674s3sjd 
  password: p11mgss562sts7e3kebiesf
  port: 5432
  sslmode: require