extending xampp

Extending XAMPP with PostgreSQL and phpPgAdmin 1 / 14

Extending XAMPP with PostgreSQL and phpPgAdminwritten by

Peter J. SiegelJuly 2005

[email protected]

This paper's intention

XAMPP is a fine adjusted software solution based on Apache Web Server, MySQL,PHP and Perl. It has been created by the ApacheFriends Project that is hosted athttp://www.apachefriends.org. The intention behind the great idea of creatingXAMPP is a preconfigured package that is easy to handle and up-to-date. Itguarantees a minimal waste of time during installation and configuration.

The question which database server should be applied is discussed widely andeven controversially all around the internet. MySQL and PostgreSQL havedifferent advantages, it seems that they appear to converge to meet somewherein the middle. For some time PostgreSQL provides foreign keys, views,subselects, triggers and stored procedures. MySQL is topping with performanceand a large user base. In actual versions MySQL is adding features like storedprocedures, subselects, etc. while PostgreSQL improves performance andstability. Nevertheless which database server fits best depends on theapplications that should work with it.

This paper is designed as a short manual that shows how XAMPP could beextended easily by PostgreSQL and phpPgAdmin. Is has been written during thesetup of my private experimental Web/DBServer. So all described steps have ledto a smooth integration of PostgreSQL into a running system based on XAMPPand Debian Sarge. This paper is dedicated to anybody who doesn't want to spendmuch time searching the web for hints and tips. However, this howto-manualdoesn't raise a claim on completeness. No liability for any loss or damage will beassumed.


pre-requisites

software components

System Linux, Distribution Debian 3.1 (Sarge)(Kernel 2.6.8, glibc 2.3.2, Gnome 2.81, Xfree 4.3.0)

PostgreSQL Version 8.0.3

SuSE 9.2 Packages: postgresql-8.0.3-0.1.i586.rpm postgresql-contrib-8.0.3-0.1.i586.rpm postgresql-devel-8.0.3-0.1.i586.rpm postgresql-docs-8.0.3-0.1.i586.rpm postgresql-libs-8.0.3-0.1.i586.rpm postgresql-pl-8.0.3-0.1.i586.rpm postgresql-server-8.0.3-0.1.i586.rpm

available at: http://search.belnet.be/packages/suse/projects/postgresql/postgresql-8.0.3/9.2-i386/

PostgreSQLdatabasemodules forPHP 5

Version 5.0.4-2available at http://www.tinysofa.org/pub/tinysofa/updates/server-2.0/i386/tinysfa/rpms.updates

Packages: php5-pgsql-5.0.4-2ts.i386.rpm

readlinelibraries

Version 5.0-1.2available at ftp://sunsite.informatik.rwth-aachen.de/pub/Linux/suse/i386/9.2/suse/i586/readline-5.0-1.2.i586.rpm

Package: readline-5.0-1.2.i586.rpm

XAMPP Version 1.4.14available at http://www.apachfriends.org

Package: xampp-linux-1.4.14.tar.gz

phpPgAdmin Version 3.5.3available at http://phppgadmin.sourceforge.net/

Package: phpPgAdmin-3.5.3.tar.gz


Installing XAMPP

The XAMPP 1.4.14 package contains following components:

– Apache 2.0.54– MySQL 4.1.12– PHP 4.3.11 – PHP 5.0.4– Perl 5.8.6– ProFTPD 1.2.10– phpMyAdmin 2.6.2-pl1– OpenSSL 0.9.7g– GD 2.0.1– Freetype 2.1.7– libjpeg 6b– libpng 1.2.7– gdbm 1.8.0– zlib 1.1.4– expat 1.2– Sablotron 1.0– libxml 2.6.11– libxslt 1.1.8– Ming 0.2a– Webalizer 2.01– pdf class 009e

– ncurses 5.8– mod_perl 2.0.0-RC4– FreeTDS 0.62.4– gettext 0.11.5– IMAP C-Client 2002b– OpenLDAP (client) 2.2.13– mhash library 0.8.18– mcrypt library 2.5.7– cURL 7.13.1– SQLite 2.8.9 (in case of PHP5: 2.8.14)– phpSQLiteAdmin 0.2– eAccelerator 0.9.3

Step 1: Install the XAMPP package

Unpack the XAMPP package into the directory '/opt'. An automatically createdsubdirectory '/opt/lampp' is filled with the package content. Apply the followingconsole entry:

# tar xvfz xampp-linux-1.4.14.tar.gz -C '/opt'

Step 2: Create a startup script for XAMPP

The following startup script should be saved into directory '/etc/init.d'. At systemstartup, different services are going to be executed by start scripts. These scriptsare originally located in directory '/etc/init.d' and they are registered by a softlink in at least one run level directory from 'rc0.d' to 'rc6.d'. All soft linked startscripts in one associated run level directory are called by the entry the run level


master script '/etc/init.d/rc'. Startup script: /etc/init.d/lampp

#! /bin/sh# Written in 2005 by PJS## Author: # Peter J. Siegel <[email protected]>, 2005## /etc/init.d/lampp## Description: Start the XAMPP environment#### END INIT INFO

XAMPP_DIR=/opt/lampp

case "$1" in start)

echo "Starting LAMPP" $XAMPP_DIR/lampp start ;;

stop)

echo "Shutting down LAMPP" $XAMPP_DIR/lampp stop ;;

restart) ## Stop the service and regardless of whether it was ## running or not, start it again. $0 stop $0 start ;;

*) echo "Usage: $0 {start|stop|restart}" exit 1 ;;esac

exit 0

The start script can be executed automatically on system bootup. Therefore,following command line can be used:

# update-rc.d lampp defaults

With usage of parameter "defaults" update-rc.d will make links to start thenamed service (by script "/etc/init.d/lampp") in runlevels 2345 and stop theservice in runlevels 016. These links are created and shown by following screenoutput:


Adding system startup for /etc/init.d/lampp ...

/etc/rc0.d/K20lampp -> ../init.d/lampp/etc/rc1.d/K20lampp -> ../init.d/lampp/etc/rc6.d/K20lampp -> ../init.d/lampp/etc/rc2.d/S20lampp -> ../init.d/lampp/etc/rc3.d/S20lampp -> ../init.d/lampp/etc/rc4.d/S20lampp -> ../init.d/lampp/etc/rc5.d/S20lampp -> ../init.d/lampp

By default all the links will have sequence code 20, but this can be overridden bysupplying one or two numerical arguments.

Step 3: Securing XAMPP

Execute following command line:

# /opt/lampp/lampp security

An interactive configuration of passwords for XAMPP, MySQL phpMyAdmin andProFTPd should be fulfilled with these lines:

# /opt/lampp/lampp security

XAMPP: Quick security check...XAMPP: Your XAMPP pages are NOT secured by a password.XAMPP: Do you want to set a password? [yes]XAMPP: Password:XAMPP: Password (again):XAMPP: Password protection active. Please use 'lampp' as user name!XAMPP: MySQL is accessable via network.XAMPP: Normally that's not recommended. Do you want me to turn it off? [yes] noXAMPP: The MySQL/phpMyAdmin user pma has no password set!!!XAMPP: Do you want to set a password? [yes]XAMPP: Password:XAMPP: Password (again):XAMPP: Setting new MySQL pma password.XAMPP: Setting phpMyAdmin's pma password to the new one.XAMPP: MySQL has no root passwort set!!!XAMPP: Do you want to set a password? [yes]XAMPP: Write the password somewhere down to make sure you won't forget it!!!XAMPP: Password:XAMPP: Password (again):XAMPP: Setting new MySQL root password.XAMPP: Setting phpMyAdmin's root password to the new one.XAMPP: The FTP password is still set to 'lampp'.XAMPP: Do you want to change the password? [yes]XAMPP: Password:XAMPP: Password (again):XAMPP: Reload ProFTPD...XAMPP: Done.

Launching the XAMPP page in a web browser under Echnaton's IP address will


show a login window like this:

Launching the phpMyAdmin page in a web browser under the server address"http://192.168.10.3/phpmyadmin" will show a login window like this:


Installing PostgreSQL

Step 1: Install the PostgreSQL packages

Installing PostgreSQL from binary packages has advantages over configuring andcompiling source packages. One benefit is an automatically placement of theserver's binaries and shared libraries into standard directories that are 'known'by the system. No additional file links made by “ldconfig” and no entries in theconfiguration file “/etc/ld.so.conf” are needed. With the following described usageof converted RPM packages all Postgres command line applications and sharedlibraries are saved in subdirectories of the system-wide trusted directories“/usr/bin” and “/usr/lib”. Actually, the compiled binary packages for PostgreSQL 8.0.3 are only available in'*.rpm'-format. Due to the fact that we're using PHP5, an appropriate PostgreSQLdriver should be installed. The required driver version for PHP5 is available withPostgres Version 8.0.x, in RPM format, too. For an installation in a Debiandistribution all downloaded packages have to be converted into '*.deb'-format.This job can be done by an "alien". Then all assembled Debian packages areinstalled with "dpkg".

Conversion with "alien":alien -d postgresql-8.0.3-0.1.i586.rpmalien -d postgresql-contrib-8.0.3-0.1.i586.rpmalien -d postgresql-devel-8.0.3-0.1.i586.rpmalien -d postgresql-docs-8.0.3-0.1.i586.rpmalien -d postgresql-libs-8.0.3-0.1.i586.rpmalien -d postgresql-pl-8.0.3-0.1.i586.rpmalien -d postgresql-server-8.0.3-0.1.i586.rpmalien -d php5-pgsql-5.0.4-2ts.i386.rpm

Installation with "dpkg":dpkg -i postgresql-8.0.3-0.1.i586.debdpkg -i postgresql-contrib-8.0.3-0.1.i586.debdpkg -i postgresql-devel-8.0.3-0.1.i586.debdpkg -i postgresql-docs-8.0.3-0.1.i586.debdpkg -i postgresql-libs-8.0.3-0.1.i586.debdpkg -i postgresql-pl-8.0.3-0.1.i586.debdpkg -i postgresql-server-8.0.3-0.1.i586.debdpkg -i php5-pgsql-5.0.4-2ts.i386.deb

Step 2: Install the readline libraries

The PostgreSQL packages include several applications for managing functions onserver side as well as client operations. For example, "psql" provides a terminalbased front-end. It enables a user to type in queries interactively, transmit themto PostgreSQL, and see the query results. Comfortable editing facilities in


command line mode are needed. This request is fulfilled by the GNU ReadlineLibraries to which PostgreSQL depends on. Different editing modes are provided,well known from Emacs and Vi.

Like the predecessing step, the RPM package for "libreadline" has to beconverted and then installed.

Conversion with "alien":alien -d readline-5.0-1.2.i586.rpm

Installation with "dpkg": dpkg -i readline-5.0-1.2.i586.deb

Step 3: Create new or adapt existing user 'postgres'

The database administration can be managed by a superuser exclusively. Asuperuser as a role owns completely all access rights to the DB server and itswhole stored data content. Within these granted privileges a superuser normallycreates new databases, tables, schemes and users with restricted rights.

Typically, the superuser is equal to a linux user named "postgres" who belongs togroup "postgres". Following steps show the linux console entries for creating oreven changing the specified group and user:

1. Add a new user group 'postgres' # groupadd postgres

2. Create a home directory for user 'postgres' # mkdir /home/postgres

3. Create a user 'postgres' # useradd -g postgres -d /home/postgres/ -p <password> postgres

If user 'postgres' exists # usermod -g postgres -d /home/postgres/ -p <password> postgres

4. Adjust rights for user 'postgres' allowing access to the home directory # chown postgres:postgres /home/postgres/ # chmod 700 /home/postgres/


Step 4: Initialize the PostgreSQL database

PostgreSQL handles all data in a collection of databases. This collection is a so-called database cluster and has to be initialized before the server can startworking. An initialization has to be performed with the console program “initdb”.It attempts to create different directories for the cluster. Be aware that thesuperuser has to be the owner of all cluster's directories, simply because hecreates new databases and directories in the cluster. Furthermore, thePostgreSQL server process is owned by superuser “postgres”.

Switch to user “postgres” with all rights and initialize the database cluster byfollowing command lines:

# su - postgres#/home/postgres/bin/initdb -D /home/postgres/data

Die Option ”-D“ gibt ein Verzeichnis an, in dem die Daten gespeichert werden.Alternativ kann dieses Verzeichnis auch durch einen Eintrag in die Datei”/etc/profile“ gesetzt werden:

Step 5: The PostgreSQL start script

PostgreSQL Startskript

Folgendes Bash-Skript läßt den PostgreSQL Datenbank-Server beim Systemstartautomatisch hochfahren. Dafür muß das Skript im Verzeichnis „/etc/init.d/“stehen. Es muß die Berechtigungen „rwxr-xr-x“ (chmod 755) tragen.

#!/bin/bash # postgreSQL – start script ## Anmerkung: * "~postgres" is equal to "/home/postgres"# * "~postgres/logfile" ist the server log, no directory# * all environment variables have to be exported explicitly because the# configuration file “/etc/profile” will be loaded later # * the command line construct "su postgres -c '.....' " guarantees that user “root” # can start or stop the server without any error. By default, the PostgreSQL server can # only be started and stopped by superuser “postgres”

export PGBIN=/usr/bin/export PGDATA=~postgres/data

case "$1" in

start) echo Starting PostgreSQL su postgres -c '$PGBIN/pg_ctl -D $PGDATA -l ~postgres/logfile start' ;;

stop) echo Shutting down PostgreSQL su postgres -c '$PGBIN/pg_ctl stop' ;;


restart) $0 stop $0 start ;; reload) su postgres -c '$PGBIN/pg_ctl -D $PGDATA -l ~postgres/logfile reload' ;;

*) echo "Usage: $0 {start | stop | restart | reload}" ;; esac

exit 0

Step 7: Install and configure the phpPgAdmin package

1st: Unpack the phpPgAdmin package into the directory '/opt/lampp'. Anautomatically created subdirectory '/opt/lampp/phppgadmin' is filled with thepackage content. Apply the following console entry:

# tar xvfz phpPgAdmin-3.5.3.tar.gz -C '/opt/lampp'

2nd: Create a link to the shared object library that belongs to the PostgreSQLdriver for PHP5. In XAMPP 1.4.14 the link is expected in the directoy for PHPextensions “/opt/lampp/lib/php/extensions/no-debug-non-zts-20041030”.Apply the following console entry:

# ln -s /usr/lib/php5/pgsql.so /opt/lampp/lib/php/extensions/no-debug-non-zts-20041030/

3rd: Edit the PHP configuration file at “/opt/lampp/etc/php.ini”. Add thePostgreSQL driver extension for PHP at the paragraph “dynamic extensions”.You can add these lines:

; PostgreSQL library for phpPgAdminextension=pgsql.so

4th: Edit the configuration file for your PostgreSQL database cluster at“/home/postgres/data/postgresql.conf”. Set the properties for databaseaccess through a network. Make sure that the connection settingscorrespond with these values:

listen_adresses = 'localhost' # ip settings to listen on; # defaults to localhostport = 5432


5th: Edit the PostgreSQL client authentication configuration file at“/home/postgres/data/pg_hba.conf”. The properties in this file control whichclients are allowed to connect and how they should authenticate themselves.Set the following properties:

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections onlylocal all all trust# IPv4 local connections:host all all 127.0.0.1/32 md5# IPv6 local connections:host all all ::1/128 trust

You should pay attention to the settings for local connections from localhost.Set the authentication method to “md5”, otherwise phpPgAdmin doesn'tverify any password for logins of superuser “postgres”. That means anybodycan login as superuser without giving any password.

6th: The configuration file for phpPgAdmin “config.inc.php” is located in directory“/opt/lampp/phpPgAdmin/conf”. It should look like this:

<?php

/** * Central phpPgAdmin configuration. As a user you may modify the * settings here for your particular configuration. * * $Id: config.inc.php-dist,v 1.35.2.2 2005/02/11 11:23:39 chriskl Exp $ */

// An example server. Create as many of these as you wish,// indexed from zero upwards.

// Display name for the server on the login screen$conf['servers'][0]['desc'] = 'PostgreSQL';

// Hostname or IP address for server. Use '' for UNIX domain socket.$conf['servers'][0]['host'] = '127.0.0.1'; // pjs

// Database port on server (5432 is the PostgreSQL default)$conf['servers'][0]['port'] = 5432;

// Change the default database only if you cannot connect to template1$conf['servers'][0]['defaultdb'] = 'template1';

// Specify the path to the database dump utilities for this server.// You can set these to '' if no dumper is available.$conf['servers'][0]['pg_dump_path'] = '/usr/bin/pg_dump';$conf['servers'][0]['pg_dumpall_path'] = '/usr/bin/pg_dumpall';

// Default language for the login screen if there's no translation// matching user's browser request. Eg: 'english', 'polish', etc.$conf['default_lang'] = 'english';


// If extra login security is true, then logins via phpPgAdmin with no// password or certain usernames (pgsql, postgres, root, administrator)// will be denied. Only set this false once you have read the FAQ and// understand how to change PostgreSQL's pg_hba.conf to enable// passworded local connections.$conf['extra_login_security'] = false; // pjs

// Only show owned databases?// Note: This will simply hide other databases in the list - this does// not in any way prevent your users from seeing other database by// other means. (eg. Run 'SELECT * FROM pg_database' in the SQL area.)$conf['owned_only'] = false;

// Display comments on objects? Comments are a good way of documenting// a database, but they do take up space in the interface.$conf['show_comments'] = true;

// Display "advanced" objects? Setting this to true will show types,// operators conversions, languages and casts in phpPgAdmin. These// objects are rarely administered and can clutter the interface.$conf['show_advanced'] = false;

// Display "system" objects?$conf['show_system'] = false;

// Display reports feature? For this feature to work, you must// install the reports database as explained in the INSTALL file.$conf['show_reports'] = true;

// Only show owned reports?// Note: This does not prevent people from accessing other reports by// other means.$conf['owned_reports_only'] = false;

// Minimum length users can set their password to.$conf['min_password_length'] = 1;

// Width of the left frame in pixels (object browser)$conf['left_width'] = 200;

// Which look & feel theme to use$conf['theme'] = 'default';

// Show OIDs when browsing tables?$conf['show_oids'] = false;

// Max rows to show on a page when browsing record sets$conf['max_rows'] = 30;

// Max chars of each field to display by default in browse mode$conf['max_chars'] = 50;

// Send XHTML headers? Unless debugging, it's best to leave this off$conf['use_xhtml'] = false;

/***************************************** * Don't modify anything below this line * *****************************************/

$conf['version'] = 13;

?>


7th: If you get error messages like those:

Warning: session_start() [function.session-start]: open(/tmp/sess_a707c754040a78eae2205da7ca74a140, O_RDWR)failed: Keine Berechtigung (13) in /opt/lampp/phpPgAdmin/libraries/lib.inc.php on line 77

Warning: session_start() [function.session-start]: Cannot send session cookie - headers already sent by (outputstarted at /opt/lampp/phpPgAdmin/libraries/lib.inc.php:77) in /opt/lampp/phpPgAdmin/libraries/lib.inc.php online 77

Warning: session_start() [function.session-start]: Cannot send session cache limiter - headers already sent(output started at /opt/lampp/phpPgAdmin/libraries/lib.inc.php:77) in /opt/lampp/phpPgAdmin/libraries/lib.inc.php on line 77

You should load the file “/opt/lampp/phpPgAdmin/libraries/lib.inc.php” withan editor and change the following section

// Start session (if not auto-started)if (!ini_get('session.auto_start')) { session_name('PPA_ID'); session_start();}

and add a directory with public access like it's shown here:

// Start session (if not auto-started)if (!ini_get('session.auto_start')) { session_name('/tmp/PPA_ID'); session_start();}

The directory “/tmp” is the default location for saving sessions in PHP. It'sappropriate entry can be found in configuration file “/opt/lampp/etc/php.ini”,parameter “session.save_path”.

8th: If you get error messages like those:

Warning: session_start() [function.session-start]: open(/tmp/sess_a707c754040a78eae2205da7ca74a140, O_RDWR)failed: Keine Berechtigung (13) in /opt/lampp/phpPgAdmin/logout.php on line 11

Warning: session_start() [function.session-start]: Cannot send session cookie - headers already sent by (outputstarted at /opt/lampp/phpPgAdmin/logout.php:11) in /opt/lampp/phpPgAdmin/logout.php on line 11

Warning: session_start() [function.session-start]: Cannot send session cache limiter - headers already sent(output started at /opt/lampp/phpPgAdmin/logout.php:11) in /opt/lampp/phpPgAdmin/logout.php on line 11

Warning: session_destroy() [function.session-destroy]: Session object destruction failed in /opt/lampp/phpPgAdmin/logout.php on line 14

Warning: Cannot modify header information - headers already sent by (output started at /opt/lampp/phpPgAdmin/logout.php:11) in /opt/lampp/phpPgAdmin/logout.php on line 16


You should load the file “/opt/lampp/phpPgAdmin/logout.php” with an editorand change the following section

if (!ini_get('session.auto_start')) { session_name('PPA_ID'); session_start();}

and add a directory with public access like it's shown here:

if (!ini_get('session.auto_start')) { session_name('/tmp/PPA_ID'); session_start();}

9th: The changes made in steps 7th and 8th can be replaced by a change in thePHP configuration file “/opt/lampp/etc/php.ini”:

Switch the value for “session.auto_start” from 0 to 1.

; Initialize session on request startup.session.auto_start = 0

10th: Finally edit some settings to Apache web server's configuration file atlocation “/opt/lampp/etc/httpd.conf”. The additional settings refer tosection 3 (virtual hosts). First, Apache needs an extension which is added tothe host URL. By that extension the phpPgAdmin is called remotely. This isachieved by following settings:

Alias /phpPgAdmin "/opt/lampp/phpPgAdmin"<Directory "/opt/lampp/phpPgAdmin"> AllowOverride AuthConfig Limit</Directory>

The alias maps an URL-extension to the application directory wherephpPgAdmin is installed.

Furthermore, make sure that the following line is part of “httpd.conf”. It tellsApache how to handle client requests with PHP-suffixes.

AddType application/x-httpd-php .php .php3 .php4

extending xampp

Documents