pg_connect

add a note User Contributed Notes

Hotmail resident Tree2054
17-Dec-2005 12:41


From the above HTML:



>> Return Values

>> 

>> PostgreSQL connection resource on success, FALSE on failure.



Your connection failed, database toto does not exist so you were returned false.

willou.d AT gmail DOT com
08-Dec-2005 01:29


pg_connect not seems to return a resource when the connection fail. 



For this example the database 'toto' doesn't exist.



With the following code : 

<?php



$cnxString = "host=localhost port=5432 dbname=toto user=username

password=secret";



$pgsql_conn = pg_connect($cnxString, PGSQL_CONNECT_FORCE_NEW);



if ($pgsql_conn) {

  print "Connexion russie  : " . pg_host($pgsql_conn) . "<br/>\n";

} else {

  echo pg_last_notice($pgsql_conn);

  exit;

}

?>



The result on screen (php.display_errors=1) : 



Warning: pg_connect() [function.pg-connect]: Unable to connect to PostgreSQL server: FATAL: la base de donnes toto n'existe pas in /home/willou/public_html/atlanpolis/wwws/tools/checkdb.php on line 5



Warning: pg_last_notice() expects parameter 1 to be resource, boolean given in /home/willou/public_html/atlanpolis/wwws/tools/checkdb.php on line 10



Why ? Please show me my error.

Thanks.



PS : (Versions) PHP Version 5.0.4 ; PostgreSQL(libpq) Version 8.0.3 ; Apache/2.0.54

Anonymous
11-Apr-2005 12:51


The values accepted by pg_connect's sslmode argument are: disable, allow, prefer, require

sigve at wolfraidah dot no
22-Feb-2005 07:04


Regarding Helio Ferenhof's comment above:



That practice may be extremely dangerous, because (at least) Apache doesn't have any special handle for *.inc files. When they are included into your PHP-script, it works fine, but if you enter the filename in a web browser's address field you will get the contents of the file dumped as cleartext - including the username and password!



This can be remedied by a) renaming the include file to .php, b) adding a .htaccess directive which hides the .inc-files, or globally handle .inc files with php in apache.conf (not recommended, I guess).

phpnet at benjamin dot schulz dot name
01-Sep-2004 07:28


if you need to open a new connection handle (i.e. for multiple pg_send_query()) use PGSQL_CONNECT_FORCE_NEW as second parameter to pg_connect()

u233 at shaw dot ca
17-Jun-2004 09:52


The suggestion of grheatley is exceedingly dangerous.



Because "fred" owns the database and apache is connecting as "fred", if there is an error in your php or it is vulnerable to SQL injection attacks nasty things like "delete from products;" can easily happen. Furthermore, the default to trust all local users has not been overridden, thus anyone who can run arbitrary code (even as "nobody" or "apache") on your server can connect as postmaster and drop the database or insert data. 



It is vitally important to remember that any user with the createdb or createuser privaleges is considered a "superuser" for ALL databases managed by that postmaster instance.



What you should be doing is:

fred@localhost ~fred$psql -U postmaster template1



BEGIN WORK;

ALTER USER postmaster WITH PASSWORD '(password)';

CREATE USER fred WITH PASSWORD '(password)' CREATEDB CREATEUSER;

COMMIT;

\q



Now, edit your pg_hba.conf to:



local    all    all   md5

host    all    all   reject

hostssl all  all    reject



Then:

fred@localhost ~fred$createdb fred

fred@localhost ~fred$psql -U fred fred



BEGIN WORK;

CREATE USER "webserver" WITH PASSWORD '(password)';



create table public_readable (

     rowid   serial primary key,

     mydata  varchar(50)

);

grant select on public_readable to public;



create table webserver_editable (

     rowid   serial primary key,

     ourdata  varchar(50)

);

grant insert on webserver_editable to "webserver";

grant delete on webserver_editable to "webserver";

grant update on webserver_editable to "webserver";

grant select on webserver_editable to "webserver";



create table webserver_write_once(

   -- this table is for write-only logging

   rowid   serial primary key,

   logdata varchar(50)

);

grant insert on webserver_write_once to "webserver";



COMMIT;



Now, your php scripts connect like this:

<? $conn = pg_connect("dbname=fred user=webserver password=(password)"); ?>



For more flexibility, you can create a postgres user for each user who can log on to your website and use the pg_connect as your website authentication method (cacheing is your friend). By making all the users in the same group, you can grant them all access to the tables with a single command but restrict what rows they may access by using views.



Do this like so:



fred@localhost ~fred$psql -U fred fred



BEGIN WORK;



CREATE GROUP "salespeople";

CREATE GROUP "customers";



CREATE TABLE customerAddresses (

    customername    name references pg_users(usename) primary key;

    address   varchar(50)

)



GRANT SELECT ON customerAddresses TO GROUP "salespeople";

GRANT INSERT ON customerAddresses TO GROUP "salespeople";

GRANT UPDATE ON customerAddresses TO GROUP "salespeople";

GRANT DELETE ON customerAddresses TO GROUP "salespeople";



CREATE VIEW myAddress as select address from customerAddresses where customername = current_user;



CREATE RULE updateMyAddress as on update to myAddress do instead update customerAddresses set address = NEW.address where customerName=current_user;



CREATE RULE insertMyAddress as on insert to myAddress do instead insert into customerAddresses values (current_user, NEW.address);



GRANT INSERT ON myAddress to group "customers";

GRANT UPDATE ON myAddress to group "customers";

GRANT SELECT ON myAddress to group "customers";



COMMIT;



Now the problem becomes: How do un-privaledged users (ie salespeople or webserver) create all those customer accounts?

It's easy to solve with a little PL/pgSQL.



BEGIN WORK;



CREATE GROUP authorizedtocreatecustomers;



create or replace function makeuser (name,text) returns int4 as '

BEGIN

    execute ''create user '' || quote_ident($1) || '' with password '''''' || quote_literal($2) || ''''''  in group customers;'';

    return 0;

END;

' language 'plpgsql' security definer;



REVOKE EXECUTE ON function makeuser (name, text) FROM PUBLIC;

grant execute on function makeuser (name,text) to group authorizedtocreatecustomers;



COMMIT;



Now, just make sure webserver and all your salespeople are in the group "authorizedtocreatecustomers"

grheatley at uclan dot ac dot uk
08-Jun-2004 10:19


This is a quick note about getting postreSQL permissions to work for a Debian / php / PostgreSQL / Apache combination all *running on the same localhost*, so you can use unix sockets and not a TCP/IP connection.



Lets say you have a PostgreSQL user called 'fred' who has ownership of a database called 'freds_db'. To allow apache to connect to freds_db do this:

1) Set up a PostgreSQL password for user fred:

ALTER USER fred WITH PASSWORD 'freds_pass';

2) As root, edit /etc/postgresql/pg_hba.conf, adding the line (in lower case, below):

TYPE    DATABASE    USER    IP-ADDRESS IP-MASK    METHOD

local     freds_db     fred                             md5

3) As root, restart PostgreSQL:

/etc/init.d/postgresql restart

4) In php you should be able then to use:

<?php

pg_connect("dbname=freds_db user=fred password=freds_pass") or die("Couldn't Connect ".pg_last_error());

?>

A side effect of this is that fred now has to supply his password when he connects to freds_db in PostgreSQL via the command line, and any Debian user can connect to freds_db as fred if they know his password.



I'm not sure if this is the optimally secure method of achieving the desired result, but it works.

Cheers!

-- 

Guy

Cybertinus
16-Dec-2003 05:47


If you use pg_connect('host=localhost port=5432 user=my_username password=my_password dbname=my_dbname') and you get the following error:

"Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: Connection refused Is the server running on host localhost and accepting TCP/IP connections on port 5432?"

then you should try to leave the host= and port= parts out of the connection string. This sounds strange, but this is an "option" of Postgre. If you have not activated the TCP/IP port in postgresql.conf then postgresql doesn't accept any incoming requests from an TCP/IP port. If you use host= in your connection string you are going to connect to Postgre via TCP/IP, so that's not going to work. If you leave the host= part out of your connection string you connect to Postgre via the Unix domain sockets, which is faster and more secure, but you can't connect with the database via any other PC as the localhost.

xzilla at users dot sourceforge dot net
10-Dec-2003 12:22


regarding the note from  matias at nospam dot projectcast dot com

on 12-Feb-2002 01:16, you do not need a user in the database with the same name a your web user with ANY version of postgresql.  The only time that would be a requirement ifs if you set your postgresql server to only allow IDENT based authentication  (which IIRC is the default on Red Hat systems, which might be what lead to the confusion).  For more info on the various authentication methods allowed by postgresql, check out http://www.postgresql.org/docs/7.4/static/client-authentication.html

derry at siliconriver.com dot au
08-Aug-2003 11:48


pg_connect seems to support SSL connections, on systems where Postgres has been compiled with ssl, i'm assuming this is since psql uses libpq to connect.

pg_connect can successfully connect, and use the "requiressl" argument.

jtate at php dot net
01-Jan-2003 06:36


If you use host=HOSTNAME in your pg_connect string when connecting to PostgreSQL databases newer than 7.1, you need to make sure that your postmaster daemon is started with the "-i" option.  Otherwise the connection will fail.  See http://www.postgresql.org/idocs/index.php?client-authentication.html for client authentication documentation.

khyri at khyri dot com
01-Nov-2002 08:23


After upgrading to PHP 4.2.3 from PHP 4.1.2 (Red Hat Linux Advanced Server with Stronghold 4.0) in order to manually compile in MHASH support, I discovered that Postgres support has disappeared, despite being specified on the command line, and compiling with no errors.



FATAL: Undefined function: pg_connect() 



Confirmed by looking at the output of phpinfo() and comparing it to the output pre-upgrade - no mention of PostgreSQL in the new one.



Detective work led me to find that the old pgsql.so in /usr/lib/php4 was untouched, and the new one had ended up in /usr/lib/20020429 instead.



The fix was to edit config_vars.mk after configuration to change the value of EXTENSION_DIR, and then compile. 



Not quite sure where 20020429 came from, looks like a left-over value from development testing...



Anyway, in case any one else has a similar problem, thought I'd document it here, as a problem with pg_connect is where this will first surface as a symptom.

Helio Ferenhof <d-m at eudoramail dot com>
19-Feb-2002 05:20


Connection Hint:


Do you always write at the code the username and password to connect to your PostgreSQL database !?


What if your username or password changes?





Create a connection include file.





---


connection.inc


---


<?php


  $connection = pg_connect("host=localhost port=5432 dbname=DATABASENAME user=USERNAME password=PASSWORD")


      or die ("Nao consegui conectar ao PostGres --> " . pg_last_error($conn));


?>


 


// you can use $database name and pass it from the php file if you connect into different databases.





---


Phpfile.php


---





<?php


    include('connection.php'); // Include the connection to the databank THEN start your SQL Job :)





    $result=pg_exec("SELECT field FROM table WHERE field = '$something' "); // Sample of SQL QUERY


        $fetch = pg_fetch_row($query_st); // Sample of SQL QUERY





    pg_close($connection); // Close this connection


?>





[]s


Helio Ferenhof


d-m@eudoramail.com

matias at nospam dot projectcast dot com
13-Feb-2002 05:16


At least with Postgres 7.2, connecting to local postgresdatabase requires a user in the database with the same name as the user running apache, or the connection fails.

rolf at sir-wum dot de
13-Oct-2001 08:54


pg_connect() won't work with the authentication method 'crypt' in the pg_hba.conf. Took me an hour to figure that out till I remeberd some other issues with windows missing the crypt() call.

kayotix at yahoo dot com
15-Sep-2000 02:54


Little note that is buried in the install somewhere.  In Php 3, PostgreSQL support was activated by adding --with-postgresql=[DIR] to the options passed to ./configure.  With Php 4.0.2 (on Linux) the parameter was --with-pgsql.  The only place I found this was in the installing PHP on Unix section of the manual.

leace at post dot cz
22-Jul-2000 05:26


If you use PostgreSQL users for authenticating into your pg database rather than using your own authentication, always specify host directive in pg_connect and edit pg_hba.conf to authenticate from this host accordingly. Otherwise, PHP will connect as 'local' using UNIX domain sockets, which is set in pg_hba.conf to 'trust' by default (so you can connect using psql on console without specifying password) and everyone can connect to db _without password_ .

add a note

pg_connect

说明