February 20, 2009
Operating System |
Platform |
Application(s) |
Database(s) |
Other |
|
|
|
|
N / A |
This document describes the process of installing and setting up a PostgreSQL database server with the CentOS distribution of Linux on x86 hardware. For this task, we will compile PostgreSQL from source (so we can have the latest, compiled to our taste). The version shipped with CentOS and Red Hat is OLD. This is better, and more fun.
Test Platform:
- IBM xSeries 345
- 2x Intel XEON 2.66Ghz CPUs / 1GB RAM
- CentOS 5.3 (x86)
Keep in mind that this document does not cover hardening the system. That process is outlined in other documents from myself or others. This machine needs to be well protected. It may be in a very vulnerable position, facing that filthy and scary Internet.
You will need root access to do many of these tasks.
I'm working with a minimal installation of CentOS with development tools (GNU). As we go through this, if you notice something necessary is not on your system, you get fetch it somewhere and install it (RPM, Yum, source, etc). Keep your installation up to date using the tools provided (Software Updater, Yum, carefully one by one, whatever)!
You may want to nudge up the size on the database partition, depending on your estimated database needs. This guide uses /postgres as the home for the database(s).
I also suggest you add the following to your system's PATH:
/usr/local/pgsql/bin
This is usually in the file /etc/profile - which CentOS (err.. Red Hat) makes very ugly. Just toss in the line PATH=$PATH:/usr/local/pgsql/bin somewhere before the export statement.
PostgreSQL:
# wget http://www.procyonlabs.com/mirrors/postgresql/postgresql-8.3.7.tar.bz2
# tar -xvjf postgresql-8.3.7.tar.bz2
# rm postgresql-8.3.7.tar.bz2 && cd postgresql-8.3.7
# ./configure --with-openssl
# make && make install |
Now we need to initialize the database cluster. An account called postgres for use with PostgreSQL is automatically created for you. It is locked for normal access, so to perform actions as this user, you must, as the root user, su - postgres to become that user. The following gives an example of how to initialize a database cluster in a directory called /postgres:
# mkdir /postgres
(if not already there from partitioning)
# rm -rf /postgres/*
(to empty the contents)
# useradd -d /postgres postgres
# passwd postgres
(sets /postgres as user's home and then sets password)
# chown postgres /postgres
(allow the user 'postgres' full rights)
# su - postgres
$
initdb -D /postgres |
Once this is done, we need to configure it for remote access. These examples are simple, but will give you a running start on the methods of customization and securing your installation.
The /postgres/postgresql.conf file contains many options. To allow remote access to your database(s) you need to uncomment and edit a few lines. First, to allow all hosts access to the server, uncomment this line:
#listen_addresses = 'localhost'
and change 'localhost' to '*'. Not the most secure, but good enough for our purposes. Further down in the file, we want to change some settings to enhance security for administration. To allow SSL, uncomment the following line:
#ssl = off
and change off to on. Just below that, we want to uncomment the line:
#password_encryption = on
to allow encrypted passwords. You also need to edit the /postgres/pg_hba.conf file to allow remote connections. Read through the file and add your network and/or hosts you wish to have access (change "trust" to "md5" to use encrypted passwords). That's all we are concerned with for now. The other features are for fine tuning and such.
Since we've turned on the SSL feature in postgresql.conf, we need to create the private server key and certificate for PostgreSQL to use when starting up. This is rather simple if you don't involve any signing authorities... and we're not here. As the postgres user, perform the following:
$ cd /postgres
$ openssl req -new -text -out server.req
$ openssl rsa -in privkey.pem -out server.key
$ rm privkey.pem
$ openssl req -x509 -in server.req -text -key server.key -out server.crt
$ chmod og-rwx server.key |
To start the database, enter postgres -D /postgres as the postgres user. To connect via a GUI or remote psql connection with the default postgres database user, you need to setup a password for this user. On the database server, perform the following:
$ psql
postgres=# ALTER USER postgres WITH PASSWORD 'yournewpassword'; |
All is good now. Have fun!
|
