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

===== Apache configuration =====
Now we have to add some new aliases to the control panel. In order to achieve this, we must modify the following 5 files:
\\
==== /etc/ispcp/apache/00_master.conf ====
Add the following line after the last Alias directive
<code>
Alias /phppgadmin {ROOT_DIR}/gui/tools/phppgadmin/
</code>
\\
==== /etc/ispcp/apache/parts/dmn_entry.tpl ====

Add the following line after the last Alias directive
<code>
RedirectMatch permanent ^/phppgadmin([\/]?) http://{BASE_SERVER_VHOST}/phppgadmin/
</code>
\\
==== /etc/apache2/sites-available/00_master.conf ====

Add the following line after the last Alias directive
<code>
Alias /phppgadmin /var/www/ispcp/gui/tools/phppgadmin/
</code>
\\
==== /etc/apache2/sites-available/ispcp.conf ====

Add the following line after the last Alias direcive *of each domain*
<code>
RedirectMatch permanent ^/phppgadmin([\/]?) http://admin.server.dominio.tld/phppgadmin/
</code>
\\
==== /etc/ispcp/apache/working/ispcp.conf ====

Add the following line after the last Alias direcive *of each domain*
<code>
RedirectMatch permanent ^/phppgadmin([\/]?) http://admin.server.dominio.tld/phppgadmin/
</code>

===== PHP Configuration =====
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):
<code>
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"
</code>

Next, we have to remove the **"passthru"** restriction to be able to export the databases (passthru has been removed from this list of commands):
<code>
disable_functions = show_source, system, shell_exec, exec, phpinfo, shell, symlink
</code>

Finally, we should also modify the maximum upload size by modifying the following directive to look like this:
<code>
post_max_size = 12M
upload_max_filesize = 12M
</code>

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

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

<code>
# 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;
</code>

===== Create the postgres administrator =====
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>

===== Create users and databases for our clients =====
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.
\\
===== Managing the PostgreSQL server =====
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>