Accessing Postgres db from apache using PHP

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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: Accessing Postgres db from apache using PHP

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
============================================================ ==============



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: Accessing Postgres db from apache using PHP

--=-tFmKkhM80RDC3ZXQmVbz
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

> i then do a:
> $tmp =3D "host=3Dlocalhost dbname=3Dgforge user=3Dgforge password=3Dgfor=
ge";
> $conn =3D @pg_connect($tmp);

Since host=3Dlocalhost, 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=3D, 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.

--=-tFmKkhM80RDC3ZXQmVbz
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (FreeBSD)

iD8DBQA/JIlH6DETLow6vwwRAqzkAJ9q4v40uaEksJjFAnxpkQh0PNZzdwCf VFwR
+WrxDdusclX8kUiCxlURpVQ=
=NwvQ
-----END PGP SIGNATURE-----

--=-tFmKkhM80RDC3ZXQmVbz--

Re: Accessing Postgres db from apache using PHP

> $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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Re: Accessing Postgres db from apache using PHP

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=, 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.


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: Accessing Postgres db from apache using PHP

I'm not sure, but I think you have to start postgres with the option -i to =
allow it to use TCP/IP.

if you get a socket error look at the php.ini, sockets.unix_socket_director=
y =3D /tmp, go to the directory indicated there and have a look, if there i=
s a socket. PHP and postgres both have a setting for the socket directory =
and they must be the same.

Hope it helps,
Christoph Della Valle

>-----Ursprüngliche Nachricht-----
>Von: bruce [mailto:bedouglas@earthlink.net]
>Gesendet: Montag, 28. Juli 2003 03:00
>An: pgsql-novice@postgresql.org; pgsql-php@postgresql.org
>Betreff: [NOVICE] Accessing Postgres db from apache using PHP
>
>
>Hi...
>
>I'm new to Postgres... I've installed Postgres on a Linux RH=20
>8.0 server. I'm
>trying to access a pgsql db from an Apache website, using=20
>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=20
>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 =3D "localhost";
>$sys_dbname =3D "gforge";
>$sys_user =3D "gforge";
>$sys_dbpasswd =3D "gforge";
>
>i then do a:
> $tmp =3D "host=3Dlocalhost dbname=3Dgforge user=3Dgforge=20
>password=3Dgforge";
> $conn =3D @pg_connect($tmp);
>
>at this point $conn is not set... it should be a resouce if=20
>I've connect to
>the DB..!!!!!!!
>
>So.. the question... Anybody have any idea as to what I've=20
>done wrong/left
>out/etc...
>
>Any pointers/comments/criticisms would be helpful.....
>Any sample docs, or code that illustrates how this should be=20
>accomplished
>would be helpful...
>
>
>Thanks...
>
>Bruce Douglas
>(925) 866-2790
>bedouglas@earthlink.net
>
>
>---------------------------(end of=20
>broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: Accessing Postgres db from apache using PHP

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

bruce wrote:


>> 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
>>


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/JOHMQogH4WkR2CwRAtdCAJ9zSpBMAJ4cY8t81NlAYfi20x9A4ACg usV+
O6aMAVbje5O1vKfeBn43bN8=
=Xw6F
-----END PGP SIGNATURE-----


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: Accessing Postgres db from apache using PHP

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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Re: Accessing Postgres db from apache using PHP

--=-poV80NVIUrE+sey9m6Sq
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

> my pg_hba.conf looks like:
> # TYPE DATABASE IP_ADDRESS MASK AUTH_TYPE
> AUTH_ARGUMENT
>=20
> #local all trust
> #host all 127.0.0.1 255.255.255.255 trust
>=20
> # Using sockets credentials for improved security. Not available everywhe=
re,
> # but works on Linux, *BSD (and probably some others)
>=20
> #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
>=20
> i've logged into the server as gforge, and tried to do the psql command
> -> psql -h localhost gforge
>=20
> 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?

--=-poV80NVIUrE+sey9m6Sq
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (FreeBSD)

iD8DBQA/JQ1N6DETLow6vwwRAmtkAJ0bePQl+UuBDns4Uju37yOyzgh2BQCf dz/1
MhYOKbzy6lx/BObS7rdA478=
=fgNE
-----END PGP SIGNATURE-----

--=-poV80NVIUrE+sey9m6Sq--

Re: Accessing Postgres db from apache using PHP

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?


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Accessing Postgres db from apache using PHP

Christoph,

> I'm not sure, but I think you have to start postgres with the option -i=
to
> allow it to use TCP/IP.

As of 7.2, it's better to do this in the postgresql.conf file, just becau=
se=20
then you don't have to remember to use "-i" every time you start up=20
PostgreSQL.

--=20
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)