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);
?>