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

A

Anonymous

Guest
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:
<!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:
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:
<?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?
 
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:
<?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:
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:
   $db = pg_connect( "$host $port $dbname $credentials2"  ); //###
This time to test, call the script like this:
Code:
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
 
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:
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?
 
Sorry, I left a couple of brackets off the 'isset' line. The corrected code is here:
Code:
<?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:
$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:
php standalone.php
"I get: Opened database successfully, but when I upload standalone to my webserver and try:"
Code:
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.
 
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?
 
Back
Top