PHP and Postgres: Why would I get “Error : Unable to open database ” when the credentials are correct?

Do you have questions regarding other database enginges (not MySQL) -- ask here!

Moderators: macek, egami, gesf

Post Reply
SwiftLearn
New php-forum User
New php-forum User
Posts: 3
Joined: Fri Mar 03, 2017 4:20 am

PHP and Postgres: Why would I get “Error : Unable to open database ” when the credentials are correct?

Post by SwiftLearn » Fri Mar 03, 2017 4:53 am

I have Apache, PHP, and Postgres installed on one CentOS 7.3. I have a .PHP script that does not use interactive user input that successfully connects to the Postgres database. So I know PHP and Postgres work. I now want to create new .PHP files that accept user input then use that input as credentials to log into the database.

I am trying to get the PHP page to take the credentials to then authenticate to a Postgres database. I use this PHP script (called a.php):

Code: Select all

<!DOCTYPE HTML>
<html>
<body>

<form action="welcome.php" method="post">
Name: <input type="text" name="name"><br>
E-mail: <input type="text" name="email"><br>
<input type="submit">
</form>

<!DOCTYPE HTML>
<html>
<body>
I use this PHP script named welcome.php (it was based on a TutorialsPoint example):

Code: Select all

echo "Favorite name is " . $_POST["name"] . ".<br>";
echo "Favorite email is " . $_POST["email"] . ".";

   $host        = "host=127.0.0.1";
   $port        = "port=5432";
   $dbname      = "dbname=foobar";
   $credentials = "user=" . $_POST["name"] . " password=" . $_POST["email"];
   echo "                                                       ";
   echo $credentials;                         //I am certain the correct credentials are being used.
   echo "                                                       ";
   $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db){
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }
   $sql =<<<EOF
      CREATE TABLE COMPANY
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL);
EOF;

  $ret = pg_query($db, $sql);
   if(!$ret){
      echo pg_last_error($db);
   } else {
      echo "Table created successfully\n";
   }
   pg_close($db);


echo $name
?>

I have now posted the first PHP file and the subsequent welcome.php file. Therefore my code is all on this post that I want to get to work. The credentials work when I use this PHP script by itself.

Here is the standalone script that does not accept user input but works:

Code: Select all

<?php
   $host        = "host=127.0.0.1";
   $port        = "port=5432";
   $dbname      = "dbname=foobar";
   $credentials = "user=jdoe password=jdoe@so.com";

   $db = pg_connect( "$host $port $dbname $credentials"  );
   if(!$db){
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }
   $sql =<<<EOF
      CREATE TABLE COMPANY
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL);
EOF;

   $ret = pg_query($db, $sql);
   if(!$ret){
      echo pg_last_error($db);
   } else {
      echo "Table created successfully\n";
   }
   pg_close($db);

?>
(Whenever I run the script above, I subsequently drop the table that it created.)

Ports 80 and 5432 are open on the CentOS server between the Windows computer with a web browser. I tested the port and the IP address. I created a firewall rule to block it. Then I unblocked it. I can say that the relevant ports are open between the web browser and the CentOS server that runs Apache, PHP, and Postgres.

When I open a web browser on a Windows computer and go to the PHP page, I enter the correct credentials into the PHP page. I see this error after I click submit
Error : Unable to open database.
What can I do to have the "welcome page" show a successful authentication to the Postgres database?

landi
php-forum Active User
php-forum Active User
Posts: 386
Joined: Thu Mar 15, 2012 3:59 pm

Re: PHP and Postgres: Why would I get “Error : Unable to open database ” when the credentials are correct?

Post by landi » Fri Mar 03, 2017 6:15 am

Hi,
I see you're trying to be logical - and that's good! I can't see anything obviously wrong, and I'm not an postgres person, but in my experience, if the connect doesn't work, it's because one or more of the credentials are wrong!

You could try the following: I've changed the 'stand alone' version to this: (I've marked the changed lines with ###)
I didn't see your name for the standalone script so I've called it standalone.php

Code: Select all

<?php
   $host        = "host=127.0.0.1";
   $port        = "port=5432";
   $dbname      = "dbname=foobar";
   $credentials1 = "user=jdoe password=jdoe@so.com"; // ###
   if (isset($_REQUEST["name"] && isset($_REQUEST["email"]) { // ###
   	$credentials2 = "user=" . $_REQUEST["name"] . " password=" . $_REQUEST["email"]; // ### 
   } else { // ###
   	echo 'No user input'; // ###
   } // ###

   $db = pg_connect( "$host $port $dbname $credentials1"  ); //###
   if(!$db){
      echo "Error : Unable to open database\n";
   } else {
      echo "Opened database successfully\n";
   }
   $sql =<<<EOF
      CREATE TABLE COMPANY
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL);
EOF;

   $ret = pg_query($db, $sql);
   if(!$ret){
      echo pg_last_error($db);
   } else {
      echo "Table created successfully\n";
   }
   pg_close($db);

?>
(You could change the create table to a simple 'select' so you don't have to keep deleting the table each time!)
Now to test:
1) The code as given above should run - the string $credentials1 is passed to the connect and the code is the same as your example.
Note: You will have run your script something like this:

Code: Select all

http://www.your-domain.com/standalone.php
If it does not run, then your working example is wrong :)
2) Next, change the connect to use the credentials2 string like this:

Code: Select all

   $db = pg_connect( "$host $port $dbname $credentials2"  ); //###
This time to test, call the script like this:

Code: Select all

http://www.your-domain.com/standalone.php?name=jdoe&emailjdoe%40so.com

Note the %40 which is the urlencoded version of @.
If this works, you are on your way to solving your problem. If it doesn't work, then post the results!
HTH

SwiftLearn
New php-forum User
New php-forum User
Posts: 3
Joined: Fri Mar 03, 2017 4:20 am

Re: PHP and Postgres: Why would I get “Error : Unable to open database ” when the credentials are correct?

Post by SwiftLearn » Sat Mar 04, 2017 5:30 am

Thank you for the elaborate response. Unfortunately I cannot get the posted version of standalone.php to work. I cannot even run it from the back end. I run

Code: Select all

php standalone.php
and I get this:
PHP Parse error: syntax error, unexpected '&&' (T_BOOLEAN_AND), expecting ',' or ')' in /var/www/html/ma.php on line 6
Being diligent, I tried a variety of different syntaxes. I got a variety of different errors.

When I browse to the posted version of standalone.php in the web browser, all I see is white. I see no output.

To try this new suggestion with the "###" lines, what do I do?

landi
php-forum Active User
php-forum Active User
Posts: 386
Joined: Thu Mar 15, 2012 3:59 pm

Re: PHP and Postgres: Why would I get “Error : Unable to open database ” when the credentials are correct?

Post by landi » Sat Mar 04, 2017 6:14 am

Sorry, I left a couple of brackets off the 'isset' line. The corrected code is here:

Code: Select all

<?php
error_reporting(E_ALL);
ini_set('display_errors', 1);

$host        = "host=127.0.0.1";
$port        = "port=5432";
$dbname      = "dbname=foobar";
$credentials1 = "user=jdoe password=jdoe@so.com"; // ###
if (isset($_REQUEST["name"]) && isset($_REQUEST["email"])) { // ###
	$credentials2 = "user=" . $_REQUEST["name"] . " password=" . $_REQUEST["email"]; // ### 
} else { // ###
	echo 'No user input'; // ###
} // ###

$db = pg_connect( "$host $port $dbname $credentials1"  ); //###
if(!$db){
	echo "Error : Unable to open database\n";
} else {
	echo "Opened database successfully\n";
}
$sql =<<<EOF
      CREATE TABLE COMPANY
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL);
EOF;

$ret = pg_query($db, $sql);
if(!$ret){
	echo pg_last_error($db);
} else {
	echo "Table created successfully\n";
}
pg_close($db);
?>
I've also added some code (the first two lines) to turn on error reporting. To use the new code, replace the $credentials1 with $credentials2 in the connect e.g.:

Code: Select all

$db = pg_connect( "$host $port $dbname $credentials2"  ); //###
HOWEVER, I did not understand your original post when you stand 'standalone'. I now see you mean running php on the command line. In this case your problem is more likely to be with the location of your database. Host 127.0.0.1 means 'localhost' - the machine running the code. When you use php on the command line, then localhost is your desktop machine. When you upload the php file to a web server, then that webserver is 'localhost'. In this case, your original title is incorrect because actually the credentials are NOT correct - credentials include the hostname :) Also, now your whole firewall bit make some sense.

To test this you do not need two different pieces of code. If you haven't solved it, simplify standalone.php so it is just a connect statement. Then show it works on your desktop i.e. "when I type:"

Code: Select all

php standalone.php
"I get: Opened database successfully, but when I upload standalone to my webserver and try:"

Code: Select all

http://website.com/standalone.php
"I get: Error : Unable to open database"

IF this is your problem, then the answer is: Who are you hosting with and do they support postgres databases. If not, find another isp or use mysql (many isps support mysql). If they support postgres, then you need to use their method to create the database and it will probably give you the host and dbname you need to use in your program. They will also probably have an example of how to connect to the db. If you're still stuck, post specifics of your ISP and the (simplified) code that doesn't work.

If you're trying to set up your own webserver and postgres db server, then I'd need to understand what your doing in terms of what is running where before I could offer help.

SwiftLearn
New php-forum User
New php-forum User
Posts: 3
Joined: Fri Mar 03, 2017 4:20 am

Re: PHP and Postgres: Why would I get “Error : Unable to open database ” when the credentials are correct?

Post by SwiftLearn » Sun Mar 05, 2017 4:27 am

Rather than use an IP address, I am now using a hostname. I can ping the hostname from my Windows computer. I can ping the hostname from the Linux server.

The Linux server runs Apache, PHP, and Postgres. Ports 80 and 5432 are open on the Linux computer. I tested from the Linux computer itself and the Windows computer to ensure nothing is blocking these ports.

When I run the new version standalone.php (posted most recently) from the Linux server, it works. I did change the IP address to a hostname ("poda"). When I run standalone.php via browsing to it from the Windows computer, it fails. This is what I see in the web browser:
No user input
Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: Permission denied Is the server running on host "poda" (127.0.0.1) and accepting TCP/IP connections on port 5432? in /var/www/html/oc.php on line 15
Error : Unable to open database
Warning: pg_query() expects parameter 1 to be resource, boolean given in /var/www/html/oc.php on line 30

Warning: pg_last_error() expects parameter 1 to be resource, boolean given in /var/www/html/oc.php on line 32

Warning: pg_close() expects parameter 1 to be resource, boolean given in /var/www/html/oc.php on line 36

The Linux server is running as a guest on VirtualBox (a hypervisor) running on my Windows laptop. What should I do?

Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest