Thread: Accessing Postgres db from apache using PHP

Accessing Postgres db from apache using PHP

From
"bruce"
Date:
Hi...

I'm new to Postgres... I've installed Postgres on a Linux RH 8.0 server. I'm
trying to access a pgsql db from an Apache website, using PHP(v-4.2.2). I've
taken a look at google and through the archives with no real luck...

I created a Linux user and given the user rights to the Postgres group.
I created a Linux user (gforge) and given it rights to the Postgres group.
Using the gforge user, I created a postgres user, using
    -> createuser gforge - W
I gave the user the password of ->gforge

I gave the user rights to add/create databases, but not add new users
I then installed a database.
    pgsql -U gforge -d gforge < gforge3.sql

At this point, I can see that I have a db in postgres....
I did a "\l" and it displayed:
    name    owner    encoding
-->>    gforge    gforge    sql_ascii


In my php code, I have....

$sys_dbhost = "localhost";
$sys_dbname = "gforge";
$sys_user = "gforge";
$sys_dbpasswd = "gforge";

i then do a:
    $tmp = "host=localhost dbname=gforge user=gforge password=gforge";
    $conn = @pg_connect($tmp);

at this point $conn is not set... it should be a resouce if I've connect to
the DB..!!!!!!!

So.. the question... Anybody have any idea as to what I've done wrong/left
out/etc...

Any pointers/comments/criticisms would be helpful.....
Any sample docs, or code that illustrates how this should be accomplished
would be helpful...


Thanks...

Bruce Douglas
(925) 866-2790
bedouglas@earthlink.net


Re: Accessing Postgres db from apache using PHP

From
brew@theMode.com
Date:
Bruce......

> In my php code, I have....

I always have to fool around with this to get it right, too. One thing
that helps is to get it working using psql first, you may get better error
messages.

Also you should check pg_hba.conf to make sure postgresql is allowing
connections from localhost.

brew

 ==========================================================================
                  Strange Brew   (brew@theMode.com)
     Check out my Musician's Online Database Exchange (The MODE Pages)
                        http://www.TheMode.com
 ==========================================================================



Re: Accessing Postgres db from apache using PHP

From
Rod Taylor
Date:
> i then do a:
>     $tmp = "host=localhost dbname=gforge user=gforge password=gforge";
>     $conn = @pg_connect($tmp);

Since host=localhost, the client will attempt to connect to the server
on 127.0.0.1.  By default, TCPIP access is not enabled.  To do so, see
postgresql.conf, make the change to the tcpip variable, and HUP the
server.

However, if you leave host=<blank>, the client will attempt to connect
to the server via the local domain socket which is both enabled by
default and should be a touch faster due to more efficient data
transmission.

Attachment

Re: Accessing Postgres db from apache using PHP

From
"bruce"
Date:
ok....

i'm still somewhat confused... this is probably a pg_hba.conf issue.....

my pg_hba.conf looks like:
# TYPE     DATABASE    IP_ADDRESS    MASK               AUTH_TYPE
AUTH_ARGUMENT

#local      all                                          trust
#host       all         127.0.0.1     255.255.255.255    trust

# Using sockets credentials for improved security. Not available everywhere,
# but works on Linux, *BSD (and probably some others)

#local  all ident   sameuser
#local   all                                             trust
#host   all             127.0.0.1       255.255.255.255 trust
#host   template1       127.0.0.1       255.255.255.255 trust
host    gforge  127.0.0.1       255.255.255.255 trust

i've logged into the server as gforge, and tried to do the psql command
    -> psql -h localhost gforge

psql: FATAL 1:  No pg_hba.conf entry for host 127.0.0.1, user gforge,
database gforge

i've also made sure the php.ini file is correct....
i can do a psql, and then a "\l" to see the gforge database

i've tried a number different settings in the pg_hba.conf file... with no
luck...

i'm willing to bet it's something pretty simple... but i can't see it..
searchs of google haven't shed any light!!!

a bottle of your favorite drink to whoever can help!!!!


thanks....

bruce
bedouglas@earthlink.net

ps...i'd even let someone remotely log in if it would help!!!


-----Original Message-----
From: pgsql-php-owner@postgresql.org
[mailto:pgsql-php-owner@postgresql.org]On Behalf Of Rod Taylor
Sent: Sunday, July 27, 2003 7:24 PM
To: bedouglas@earthlink.net
Cc: pgsql-novice@postgresql.org; pgsql-php@postgresql.org
Subject: Re: [PHP] Accessing Postgres db from apache using PHP


> i then do a:
>     $tmp = "host=localhost dbname=gforge user=gforge password=gforge";
>     $conn = @pg_connect($tmp);

Since host=localhost, the client will attempt to connect to the server
on 127.0.0.1.  By default, TCPIP access is not enabled.  To do so, see
postgresql.conf, make the change to the tcpip variable, and HUP the
server.

However, if you leave host=<blank>, the client will attempt to connect
to the server via the local domain socket which is both enabled by
default and should be a touch faster due to more efficient data
transmission.


Re: Accessing Postgres db from apache using PHP

From
Philipp Ottlinger
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

bruce wrote:
<snip>

>> host    gforge  127.0.0.1       255.255.255.255 trust
>>
>> i've logged into the server as gforge, and tried to do the psql command
>>     -> psql -h localhost gforge
>>
>> psql: FATAL 1:  No pg_hba.conf entry for host 127.0.0.1, user gforge,
>> database gforge
>>

<snip>
try:
psql -h %your_pcs_name% -U gforge

(I've had some trouble with connecting to localhost)


>>
>> i've tried a number different settings in the pg_hba.conf file... with no
>> luck...

Please try for the sake of testing:
host all 0.0.0.0 0.0.0.0 trust
local all trust

This works fine for me!

Yours,
Phil

- --
Philipp Ottlinger
cS Computer & Systeme GmbH
Menckenstr. 29

12169 Berlin
Tel. +49-30-79748317
Fax  +49-30-7226748
E-Mail:ottlinger@computer-systeme.de


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: Weitere Infos: siehe http://www.gnupg.org

iD8DBQE/JOHMQogH4WkR2CwRAtdCAJ9zSpBMAJ4cY8t81NlAYfi20x9A4ACgusV+
O6aMAVbje5O1vKfeBn43bN8=
=Xw6F
-----END PGP SIGNATURE-----


Re: Accessing Postgres db from apache using PHP

From
"Cornelia Boenigk"
Date:
Hi Bruce

> $conn = @pg_connect($tmp);

For testing you should remove the '@' from the PHP-commands to see the
errormessages from the PostgreSQL server. It may help you to find the
errors.

Regards
Conni


Re: Accessing Postgres db from apache using PHP

From
Rod Taylor
Date:
> my pg_hba.conf looks like:
> # TYPE     DATABASE    IP_ADDRESS    MASK               AUTH_TYPE
> AUTH_ARGUMENT
>
> #local      all                                          trust
> #host       all         127.0.0.1     255.255.255.255    trust
>
> # Using sockets credentials for improved security. Not available everywhere,
> # but works on Linux, *BSD (and probably some others)
>
> #local  all ident   sameuser
> #local   all                                             trust
> #host   all             127.0.0.1       255.255.255.255 trust
> #host   template1       127.0.0.1       255.255.255.255 trust
> host    gforge  127.0.0.1       255.255.255.255 trust
>
> i've logged into the server as gforge, and tried to do the psql command
>     -> psql -h localhost gforge
>
> psql: FATAL 1:  No pg_hba.conf entry for host 127.0.0.1, user gforge,
> database gforge

Your using a 7.2.x database or 7.3.x?

The file segments you have shown look like the 7.2.x format but the
error message looks like something you would get out of a 7.3.x database
(7.3.x added a user portion).

If 7.3.x, try:

# TYPE DATABASE USER    IP-ADDRESS IP-MASK         METHOD
host   gforge   gforge  127.0.0.1  255.255.255.255 trust


If 7.2.x, did you HUP or restart the server after making changes to the
file?

Attachment

Re: Accessing Postgres db from apache using PHP

From
"bruce"
Date:
Rod....

psql -V shows that I have a 7.2.2 version....

I did restart the postgres server by using the services GUI provided by
RH...

I'm not sure what "HUP" is!!!!

I also have the "-i" added to the postgresql file which should permit TCP/IP
connections....

This is most frustrating..as I'm pretty sure it's a simple issue...!!!

Rod.... If you don't mind... What's your phone number... You seem to be
reasonably knowledgeable... If we spoke, I might be able to get the
proverbial light going off!!

Thanks

Bruce
bedouglas@earthlink.net


-----Original Message-----
From: Rod Taylor [mailto:rbt@rbt.ca]
Sent: Monday, July 28, 2003 4:47 AM
To: bedouglas@earthlink.net
Cc: pgsql-novice@postgresql.org; pgsql-php@postgresql.org
Subject: RE: [PHP] Accessing Postgres db from apache using PHP


> my pg_hba.conf looks like:
> # TYPE     DATABASE    IP_ADDRESS    MASK               AUTH_TYPE
> AUTH_ARGUMENT
>
> #local      all                                          trust
> #host       all         127.0.0.1     255.255.255.255    trust
>
> # Using sockets credentials for improved security. Not available
everywhere,
> # but works on Linux, *BSD (and probably some others)
>
> #local  all ident   sameuser
> #local   all                                             trust
> #host   all             127.0.0.1       255.255.255.255 trust
> #host   template1       127.0.0.1       255.255.255.255 trust
> host    gforge  127.0.0.1       255.255.255.255 trust
>
> i've logged into the server as gforge, and tried to do the psql command
>     -> psql -h localhost gforge
>
> psql: FATAL 1:  No pg_hba.conf entry for host 127.0.0.1, user gforge,
> database gforge

Your using a 7.2.x database or 7.3.x?

The file segments you have shown look like the 7.2.x format but the
error message looks like something you would get out of a 7.3.x database
(7.3.x added a user portion).

If 7.3.x, try:

# TYPE DATABASE USER    IP-ADDRESS IP-MASK         METHOD
host   gforge   gforge  127.0.0.1  255.255.255.255 trust


If 7.2.x, did you HUP or restart the server after making changes to the
file?


Re: [NOVICE] Accessing Postgres db from apache using PHP

From
"A.Bhuvaneswaran"
Date:
>     $tmp = "host=localhost dbname=gforge user=gforge password=gforge";
>     $conn = @pg_connect($tmp);
>
> at this point $conn is not set... it should be a resouce if I've connect to
> the DB..!!!!!!!

Did you uncomment the below line in /etc/php.ini?
;extension=pgsql.so
If not do it and restart your httpd. Hope it helps.

regards,
bhuvaneswaran