PostgreSQL & phpPgAdmin installation

This Howto is written for use with Debian Lenny

<cli> aptitude update aptitude safe-upgrade aptitude install phppgadmin postgresql </cli>

Once installed, we have to copy it inside the ispcp tools directory

<cli> cp -r /usr/share/phppgadmin/ /var/www/ispcp/gui/tools </cli>

Now we have to add some new aliases to the control panel. In order to achieve this, we must modify the following 5 files:

Add the following line after the last Alias directive

Alias /phppgadmin {ROOT_DIR}/gui/tools/phppgadmin/

Add the following line after the last Alias directive

RedirectMatch permanent ^/phppgadmin([\/]?) http://{BASE_SERVER_VHOST}/phppgadmin/

Add the following line after the last Alias directive

Alias /phppgadmin /var/www/ispcp/gui/tools/phppgadmin/

Add the following line after the last Alias direcive *of each domain*

RedirectMatch permanent ^/phppgadmin([\/]?) http://admin.server.dominio.tld/phppgadmin/

Add the following line after the last Alias direcive *of each domain*

RedirectMatch permanent ^/phppgadmin([\/]?) http://admin.server.dominio.tld/phppgadmin/

phpPgAdmin requires some specific settings to work properly, so now we are going to modify the php configuration for the master domain. To do this, perform the following modifications to /var/www/fcgi/master/php5/php.ini
Allow access to the phpPgAdmin configuration file, and the ”/tmp” directory by adding them to the open_basedir directive. The resulting configuration directive should look something like this (your mileage may vary):

open_basedir = "/var/www/ispcp/gui/:/etc/ispcp/:/var/run/ispcp.lock:/proc/:/bin/df:/bin/mount:/var/log/rkhunter.log:/var/log/chkrootkit.log:/usr/share/php/:/tmp/:/usr/share/phppgadmin/conf/config.inc.php"

Next, we have to remove the “passthru” restriction to be able to export the databases (passthru has been removed from this list of commands):

disable_functions = show_source, system, shell_exec, exec, phpinfo, shell, symlink

Finally, we should also modify the maximum upload size by modifying the following directive to look like this:

post_max_size = 12M
upload_max_filesize = 12M

Restart apache to activate the new configuration: <cli> /etc/init.d/apache2 restart </cli>

Now we configure phpPgAdmin, modifying the following directives in /var/www/ispcp/gui/tools/phppgadmin/conf/config.inc.php

# Users should only see their own databases
$conf['owned_only'] = true;
...
# Likewise, they should only see their own reports
$conf['owned_reports_only'] = true;
...
# Increase the required password strength
$conf['min_password_length'] = 5;

Now we create the postgres administrator user with the following steps:
Switch to the user under which postgres is running <cli> su postgres </cli>

Create a new postgres superuser <cli> createuser admin ¿Shall the new role be a superuser? (y/n) ? y </cli>

Set a password for it <cli> psql postgres </cli> This enters us into the postgreSQL shell <cli> alter user admin with password 'superstrong_password'; \q </cli>

Creating users and databases may now be done through phpPgAdmin. To access it, open the following URL and login as the user that we've just created.
http://www.client-domain.tld/phppgadmin
User: admin
Password: superstrong_password

Remember to create client users with no inherited permissions nor rights to create databases, but allowing them to login.

PostgreSQL may be restarted or stopped using it's init.d script, as any other service:

<cli> /etc/init.d/postgresql-8.3 restart /etc/init.d/postgresql-8.3 stop </cli>