XMLTagsEditHistoryDiscussion

Edit Link Top

Instalation of Postgres DB in PHP on Debian

I have Debian 4.0 (etch), and I have installed Postgres 8.1, Apache2, php5, php5-pgsql; I use Synaptic Package Manager to install package, but it is the same than use apt-get.

I suggest you that if you want to connect to a Database, go in progressive order in security. Begin with the non secure method first.

If you have more than one installation of Postgres, look which ones are running, by using:

/etc/init.d/postgresql-8.1 status
Version Cluster   Port Status Owner    Data directory                     Log file
8.main      5432 online postgres /var/lib/postgresql/8.1/main /var/log/postgresql/postgresql-8.1-main.log

This tell you the directory of the Data and the log file used.

Bob May in document from the Postgres 8.1, has more than one postgres installation, or at least two retrial of installations, it is clear because he has two pg_hba.conf, one in the directory /var/lib/pgsql/data/ an other in the directory /usr/local/pgsql/data/. The first is the installation using the package .deb or the .rpm, and the other is the tipical used by the compilation of the postgres. Well I only have installed one pg_hba.conf.

Try first with this configuration.

Edit Link Top

1)Insecure. With no authentication.

In /etc/postgresql/8.1/main/pg_hba.conf:

local   all          all                              trust
host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               trust

In /etc/postgresql/8.1/main/postgresql.conf be sure that the following variables are set to:

listen_addresses = '*'
port = 5432

Restart the Postgres:

/etc/init.d/postgresql-8.1 restart

To prove do the following

su – postgres
createdb tempo
createuser client
psql -d tempo -U client

Create a tempo.php file with the following:

<HTML>
<HEAD><TITLE>Program of PostgresSQL connection using PHP</TITLE></HEAD>
<BODY>
<?php 
pg_connect("host=localhost port=5432 dbname=tempo user=client password=") or die("Couldn't Connect ".pg_last_error()); // Connect to the Database
/* Use the Query */
$query = "SELECT 1+1";
$query = pg_query($query); // Execute the Query
if($query)
   echo "well"; // Check to see if The Query Worked.
else{
   echo "bad".pg_last_error();
}
?>
</BODY>
</HTML>

Put tempo.php in /var/www/ and run your browser from localhost and the name of the php file:

http://localhost/tempo.php

Edit Link Top

2)Increasing the security, using password.

In the /etc/postgresql/8.1/main/pg_hba.conf:

local   all         postgres                          ident sameuser
local   all          all                              	password
host    all         all         127.0.0.1/32       password
host    all         all         ::1/128                password

To prove do the following

su – postgres
dropuser client
psql -d tempo -U postgres
CREATE USER client WITH PASSWORD 'tempo';
\q

Don't use the command line createuser -W client, I don't know why but doesn't work.

Try this:

psql -h localhost -d tempo -U client

If it work, cool.

Modify the tempo.php file with the following:

<HTML>
<HEAD><TITLE>Program of PostgresSQL connection using PHP</TITLE></HEAD>
<BODY>
<?php
pg_connect("host=localhost port=5432 dbname=tempo user=client password=tempo") or die("Couldn't Connect ".pg_last_error()); // Connect to the Database
/* Use the Query */
$query = "SELECT 1+1";
$query = pg_query($query); // Execute the Query
if($query)
   echo "well"; // Check to see if The Query Worked.
else{
   echo "bad".pg_last_error();
}
?>
</BODY>
</HTML>

Put tempo.php in /var/www/ and run your browser from localhost and the name of the php file:

http://localhost/tempo.php

Last update: 2007-07-05 (Rev 12062)

svnwiki $Rev: 12966 $