To get PHP communicate with PostgreSQL

php postgresql database

11,280

#1 problem

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.

11,280

Author by

Léo Léopold Hertz 준영

Updated on September 17, 2022

Comments

Léo Léopold Hertz 준영 almost 2 years
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?
- Admin almost 15 years
  
  Did you set a password to the user postgres ?
- Admin almost 15 years
  
  @Uka: I did not set any password initially. I removed the password from my code. I get a similar error message but with the message that ...to PostgreSQL server: fe_sendauth: no password supplied...
- Admin almost 15 years
  
  Strange, I would think the third block (host all all 127.0.0.1/32 md5) would work for what you want.
- Admin almost 15 years
  
  Please, move this question to Serverfault.com to solve the problem.
- Léo Léopold Hertz 준영 almost 15 years
  
  This thread is solved.
Admin almost 15 years

I run both your codes with and without a password in my PHP code unsuccessfully. I get the same error messages as before.
exhuma about 11 years

@andres-descalzo: It seems PHP does not support trust connections. I've been banging my head against the wall as well for the past 20 minutes... :(
exhuma about 11 years

I strongly advise against using WITH SUPERUSER for application roles! If you have root access to the machine you can easily change the auth method of the postgres user in your pg_hba.conf file temporarily. Then, create a user for yourself using md5 as auth method.