How to install Postgres SQL 8.3

[kilburn]

Answering to se2bass request, here's a translated version of the victor531 tutorial on how to install Postgres SQL 8.3:

Postgres & phpPgAdmin installation

Code:

aptitude install phppgadmin postgresql

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

Code:

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

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

Code:

# Add the following line after the last Alias directive
Alias /phppgadmin {ROOT_DIR}/gui/tools/phppgadmin/

/etc/ispcp/apache/parts/dmn_entry.tpl

Code:

# Add the following line after the last Alias directive
RedirectMatch permanent ^/phppgadmin([\/]?) http://{BASE_SERVER_VHOST}/phppgadmin/

/etc/apache2/sites-available/00_master.conf

Code:

# add the following line after the last Alias directive
Alias /phppgadmin /var/www/ispcp/gui/tools/phppgadmin/

/etc/apache2/sites-available/ispcp.conf

Code:

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

/etc/ispcp/apache/working/ispcp.conf

Code:

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

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"

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

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

Restart apache to activate the new configuration:

Code:

/etc/init.d/apache2 restart

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;

Create the postgres administrator
Now we create the postgres administrator user with the following steps:

Switch to the user under which postgres is running

Code:

su postgres

Create a new postgres superuser

Code:

createuser admin
¿Shall the new role be a superuser? (y/n) ? y

Set a password for it

Code:

psql postgres
# This enters us into the postgres shell
alter user admin with password 'superstrong_password';
# Leave the postgres shell with \q

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 postgres server
Postgres may be restarted or stopped using its init.d script, as any other service:

Code:

/etc/init.d/postgresql-8.3 restart
/etc/init.d/postgresql-8.3 stop