KfWiki: Postgresql php connect example

HomePage » Database » PostgreSql » PostgresqlPHPSample

Postgresql php connect example

First off, create the user. Notice if the database's owner is a different user (role), one must grant privileges to individual tables. There is no "schema.*" here. Very annoying.

CREATE USER webuser WITH PASSWORD 'xxxxx';
CREATE DATABASE db1 OWNER=someone;
GRANT ALL ON DATABASE db1 TO webuser;
# You will need table grant here.
GRANT ALL ON t1 TO webuser;

Then edit pg_hba.conf to allow remote connect

host    d2     webuser             192.168.x.0/24         md5

For my case, I'm connecting from non-localhost, so I need to tell postgresql to listen on the internal network

listen_addresses = 'localhost, 192.168.x.10'

Reload postgres for hba.conf to be read

pg_ctl -D /var/lib/pgsql/data reload

Test with a php script like this:

<?php
$dbconn = pg_connect("host=192.168.x.10 dbname=d2 user=webuser password=xxxxx")
	or die('Could not connect: ' . pg_last_error());
$query = 'SELECT * from t1';
$result  = pg_query($query) or die('Query failed: ' . pg_last_error());
while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)) {
	foreach ($line as $col_value) {
		echo "$col_value\n";
	}
}

pg_free_result($result);
pg_close($dbconn);
?>

KfWiki : PostgresqlPHPSample

Postgresql php connect example