How to install Postgres SQL 8.3

How to install Postgres SQL 8.3 - Printable Version

+- ispCP - Board - Support (http://www.isp-control.net/forum)
+-- Forum: ispCP Omega Contributions Area (/forum-40.html)
+--- Forum: Howtos (/forum-41.html)
+--- Thread: How to install Postgres SQL 8.3 (/thread-7871.html)

How to install Postgres SQL 8.3 - kilburn - 09-16-2009 08:40 AM

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

RE: How to install Postgres SQL 8.3 - gOOvER - 09-16-2009 09:57 AM

Added to Wiki:

http://www.isp-control.net/documentation/doku.php?id=howto:postgresql

RE: How to install Postgres SQL 8.3 - kilburn - 09-18-2009 01:47 AM

I didn't thought about it, thanks gOOvER!

RE: How to install Postgres SQL 8.3 - mafia - 12-07-2009 01:32 AM

hello

c is it possible to add a link in the panel for
customers.
a small matter as j ai install Postgres SQL c
is possible that customers may choose their base
data thank you

RE: How to install Postgres SQL 8.3 - kilburn - 12-07-2009 07:51 PM

Short answer: no, it's not possible at the moment.

Long answer: implementing this requires some changes to the ispcp gui, which nobody seems to be willing to do right now...

RE: How to install Postgres SQL 8.3 - uwe - 08-20-2010 08:49 PM

(12-07-2009 07:51 PM)kilburn Wrote: Long answer: implementing this requires some changes to the ispcp gui, which nobody seems to be willing to do right now...

Can you please elaborate on kind of changes to be done ?

RE: How to install Postgres SQL 8.3 - kilburn - 08-23-2010 02:01 AM

Take a look at how mysql is currently managed, and mimic it for pgsql...