To get PHP communicate with PostgreSQL

Question

My PHP code which I try to use in Firefox

<?php
 // Connecting, selecting database
 $dbconn = pg_connect("host=localhost dbname=masi user=postgres password=abc")
     or die('Could not connect');
 ?>

I get

Warning: pg_connect() [function.pg-connect]: Unable to connect to PostgreSQL server: FATAL: password authentication failed for user "postgres" in /var/www/ex1.php on line 3

The problem seems to be in my /etc/postgresql/8.3/main/pg_hba.conf. It seems that I need to add some ip-address to the file.

Codes in my pg_dba.conf

# Database administrative login by UNIX sockets
 local   all         postgres                          ident sameuser

 # TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

 # "local" is for Unix domain socket connections only
 local   all         all                               ident sameuser
 # IPv4 local connections:
 host    all         all         127.0.0.1/32          md5
 # IPv6 local connections:
 host    all         all         ::1/128               md5

The bug seems to be in PostgresSQL, since PHP and Apache2 work. I can access Psql by sudo -u postgres psql, since I have not managed to change the default settings. This is likely the cause of the problem. However, my PHP code uses the default settings so this should not be a problem.

I changed a line in my /etc/apache2/envvars unsuccessfuly:

export APACHE_RUN_USER=postgres     // I changed this line from masi to postgres
export APACHE_RUN_GROUP=www-data
export APACHE_PID_FILE=/var/run/apache2.pid

I get the same error messages.

How can you get the PHP work with PostgreSQL by pg_hba.conf in Ubuntu?

Answer 1

#1 problem

Log in to the user Postgres by

sudo su postgres

Create a new user, for instance, Masi for PostgreSQL by

 CREATE USER masi with SUPERUSER

Then, log in back to you default user.

#2 problem

The default user in Pg has no password. This caused the problem in PHP.

I changed the dbconn to the following

 // independent variables
 $dbHost = "localhost";
 $dbPort = 5432;
 $dbName = "masi";
 $dbUser = "masi";
 $dbPassword = "your-password";

 $conn = "host=$dbHost port=$dbPort dbname=$dbName user=$dbUser password=$dbPassword";

The problem was the password of the default Postgres' account which I do not know. This forced me to create a new account with a password.

I did not get pgAdmin 3 to work without a password in a database.

Answer 2

try with

host    all         all         127.0.0.1/32          trust

or

local   all         all                               trust

link text