PostgreSQL Installation on Mandriva

Abstract: This blog explains in three easy steps how to make a simple PostgreSQL database install on Mandriva with nice PHP-based web management system. The actual install was made on Mandriva 2007 Spring (2007.1).

1. Install PostgreSQL on Mandriva

 

 
As root install postgresql package from your media (assume you have a correctly set up URPMI). Usually you do something like:

  # urpmi postgresql

Start-up the PostgreSQL server. Upon first server start-up the databases will be initialized:

  # service postgresql start
  Initializing database:                                          [  OK  ]
  Starting postgresql service:                                    [  OK  ]

As for the database management you need to do everything as user postgres:

  $ su postgres

Next you can create a role (i.e. user):

  $ createuser -D -P -E -e user

  CREATE ROLE user ENCRYPTED PASSWORD '...' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;
  CREATE ROLE

The following are the most important switches of the “createuser” command:

-D     --no-createdb
The  new  user will not be allowed to create databases.  This is
the default.

-P     --pwprompt
If given, createuser will issue a prompt for the password of the
new user. This is not necessary if you  do  not  plan  on  using
password authentication.

-E     --encrypted
Encrypts  the  user's  password  stored  in the database. If not
specified, the default password behavior is used.

-e     --echo
Echo  the  commands  that  createuser generates and sends to the
server.

-R     --no-createrole
The  new  user will not be allowed to create new roles.  This is the default.

For complete list do:

  $ man createuser

Next you can create a database:

  $ createdb -O db_name -e user
  CREATE DATABASE db_name OWNER user;
  CREATE DATABASE

  $ pg_config --version

2. Make PostgreSQL config more strict

a) Edit pg_hba.conf

  $ vi /var/lib/pgsql/data/pg_hba.conf

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
#local   all         all                               trust
local   all         all                               md5

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5

# IPv6 local connections:
host    all         all         ::1/128               md5

b) Change password for superuser “postgres”

  $ su postgres

  $ psql

  postgres=# ALTER USER postgres WITH PASSWORD 'postgres_password';
  ALTER ROLE
  postgres=#

This should change the password.
 

 

3. Install web-based admin phpPgAdmin

Requirements: You need to have installed Apache web server with PHP (with mod_pgsql) support.

a) Download phpPgAdmin from: http://phppgadmin.sourceforge.net/?page=download

b) Unpack

  $ tar zxvf ./phpPgAdmin-X.X.tar.gz

where X.X is the version of your phpPgAdmin

c) Allow “superuser” login into phpPgAdmin (it is dissallowed by default):

IMPORTANT: PostgreSQL must be configured: edit pg_hba.conf, set superuser password!

  $ vi phpPgAdmin/conf/config.inc.php

Line

  $conf['extra_login_security'] = false;

change to:

  $conf['extra_login_security'] = true;

d) Go to the web https://yoursite/phpPgAdmin/, login and enjoy.
That’s it!