PostgreSQL Databases

From JumbaWiki

The Problem with PostgreSQL at Jumba

On Jumba, PostgreSQL is installed to use "IDENT" authentication. This means that the process must be running as an authorised PostgreSQL user. It also means that any PostgreSQL user you create in CPanel will not work - you must use your own user ID to access your PostgreSQL databases.

The Apache server runs as the user "nobody", which is not an authorised PostgreSQL user, and so cannot access PostgreSQL databases. By default, PHP scripts run using mod_php, so they run as the user "nobody". Hence your PHP script will always fail to connect to the database.

CGI scripts, on the other hand, are run under your own user ID using suexec, so any CGI script will work. The trick to getting PHP to play nicely with PostgreSQL is to make it run through CGI.

Making PHP scripts run through CGI

The first thing you will need is a wrapper executable that fixes up environment variables that have been munged by suexec. The following C source should work:

/*******************************************************
 * This is a new version of the wrapper originally written
 * by me and then slightly modified by Simon 'janus' Dassow.
 * The new version fixes memory allocation problems that 
 * lead to sigfaults sometimes. It also introduces
 * debug information output to apache's error logs and the
 * output is made more "apache-like".
 *
 *                         Alexander Amelkin <spirit@reactor.ru>
 *                         23.05.2003
 *******************************************************
*/

#define VERSION "1.2"

/* Previous info:
 * 
 * This is a modified version of the PHP wrapper from
 * Alexander Amelkin.
 * 
 * I modified it to run under conditions where php is
 * running outside cgi-bin.
 * 
 * Now the wrapper fixes the environmentvariables
 * SCRIPT_NAME and SCRIPT_FILENAME.
 *
 * Wed Aug 21 16:46:46 CEST 2002
 * Simon "janus" Dassow, janus@linux-de.org
 *
 * 
 * (c) 2002, Alexander Amelkin, spirit@reactor.ru
 *
 * -----------------------------------------------------
 *                 Installation Manual
 * -----------------------------------------------------
 * To enable suexec php scripts, first of all you must
 * disable php processing by mod_php. To do so, comment
 * out all php-related strings in your httpd.conf
 *
 * Add the following strings to httpd.conf:
 *  AddType application/x-httpd-php .php .php4 .php3 .phtml
 *  Action application/x-httpd-php /cgi-bin/php_cgi        
 *
 * Do the following:
 * gcc php_cgi.c -o php_cgi
 * chmod 755 php_cgi
 *
 * Copy (do not use ln) php_cgi to the ScriptAlias /cgi-bin/ directory
 * of each VirtualHost, where php must be enabled. chown php_cgi
 * to whatever is set by User and Group directives for each
 * VirtualHost. You may also want to 'chattr +i php_cgi' in order
 * to prevent user from deleting or modifying the wrapper.
 *
 * You're done. No modification to the existing php scripts
 * is required.
 *
 * To enable debug information output, create a file called
 * 'php_debug' in the directory where php_cgi resides.
 *  
*******************************************************/


#include <errno.h>
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <libgen.h>
#include <time.h>
#include <sys/stat.h>
#include <sys/types.h>
#include <unistd.h>

// PHP binary file
#define PHPPATH "/usr/bin/php"


void report(char *s)
{
 time_t t;
 char *tm;
 char ss[2048];
 time(&t);
 tm=ctime(&t);
 tm[strlen(tm)-1]=0;
 fprintf(stderr, "[%s] [debug] php_cgi %s\n", tm, s);
}

void reperror(char *s)
{
 time_t t;
 char *tm;
 char ss[2048];
 time(&t);
 tm=ctime(&t);
 tm[strlen(tm)-1]=0;
 sprintf(ss, "[%s] [error] php_cgi %s", tm, s );
 perror(ss); 
}

extern char **environ;

int main(int argc, char * argv[]) 
{
      char ss[2048];
      char *buf;
      char *script_name;
      char *script_filename;
      char *document_root;
      int i=0;
      int debug=0;
      struct stat flag;
      time_t t;
        
      sprintf(ss, "PHP wrapper version %s started", VERSION);
      report(ss); 
      sprintf(ss,"%s/php_debug",dirname(argv[0]));
      if( ! stat(ss,&flag) ) // File exists
           debug=1;

      if(debug)
         {
          report("Reporting environment variables:");
          while( environ[i] != NULL )
                report(environ[i++]); 
          report("End of environment");
         }
        
        // Check if a script name was supplied
        if(getenv("PATH_TRANSLATED")!=NULL) {
                // Allocate some memory for a temporary buffer
                if(debug) report("Allocating memory for PATH_TRANSLATED");
                buf=strdup(getenv("PATH_TRANSLATED"));
                if(buf==NULL) {
                        printf("Content-Type: text/plain\n\n");
                        reperror("step 1 memory allocation error");
                        return 1;
                }

                // Get the script's working directory
                dirname(buf);
                
                // chdir there
                if(debug) 
                 { 
                  sprintf(ss,"Changing working directory to %s",buf);
                  report(ss);
                 }


                if(chdir(buf)==-1) {
                        printf("Content-Type: text/plain\n\n");
                        reperror("can't chdir");
                        return 1;
                }
                // We don't need the buffer anymore
                free(buf);


                // Reset broken SCRIPT_* vars
                // Allocate memory
                if(debug) report("Allocating memory for REQUEST_URI and DOCUMENT_ROOT");
                script_name=strdup(getenv("PATH_INFO"));
                document_root=strdup(getenv("DOCUMENT_ROOT"));

                if((script_name==NULL)||(document_root==NULL)) {
                        printf("Content-Type: text/plain\n\n");
                        reperror("step 2 memory allocation error");
                        return 1;
                }

                if(debug) report("Resetting REQUEST_URI and DOCUMENT_ROOT");


                if(strchr(script_name,'?')) rindex(script_name,'?')[0] =0;
                setenv("SCRIPT_NAME",script_name,1);

                if(debug) report("Allocating memory for SCRIPT_FILENAME");

                script_filename=(char*)malloc((strlen(document_root) +strlen(script_name)+1));

                if(script_filename==NULL) {
                        printf("Content-Type: text/plain\n\n");
                        reperror("step 3 memory allocation error");
                        return 1;
                }

                //sprintf(script_filename,"%s%s",document_root,script_name);
		strcpy(script_filename, getenv("PATH_TRANSLATED"));
                if(debug) 
                  {
                   sprintf(ss,"SCRIPT_FILENAME=\"%s\"\n",script_filename);
                   report(ss);
                  }

                // Invoke php to run the script, all CGI parameters are left
                // untouched in the system environment
                setenv("SCRIPT_FILENAME",script_filename,1);

                if(debug) report("Freeing script_name...");
                free(script_name);
                if(debug) report("Freeing script_filename...");
                free(script_filename);
                if(debug) report("Freeing document_root...");
                free(document_root);

                sprintf(ss,"executing [%s]...", getenv("PATH_TRANSLATED"));
                report(ss);

                execl(PHPPATH,PHPPATH,getenv("PATH_TRANSLATED"),NULL);
                return 0;
        }
        printf("Content-Type: text/plain\n\n");
        sprintf(ss,"wrong script requested [%s]",getenv("PATH_TRANSLATED"));
        printf("%s",ss);
        reperror(ss);
        return 1;
}

You will need to compile this on a 386 Linux system and upload it to a file in your public_html/cgi-bin directory as "php-wrapper".

Then you will need to add the following to ".htaccess" in your public_html directory:

RemoveType .php
RewriteEngine On
RewriteRule ^(.*).php$ /cgi-bin/php-wrapper/$1.php [L]

(For some reason this rewrite rule is not working on Jumba - you may need to play around with this a little - I find a rewrite rule for each PHP script works, such as "RewriteRule ^myscript.php$ /cgi-bin/php-wrapper/myscript.php [L]")

You may also need to modify your PHP script to remove any attempt to pass "user", "pass" and "host" parameters to PostgreSQL.