Chapter 14. Database server (PostgreSQL)

I want to be able to use databases on my system, and my database of choice is PostgreSQL. This is almost purely a subjective choice, due to the fact that You could probably program everything nicely on the client side. At least with the things I use PostgreSQL for.

14.1. Installing PostgreSQL

And yet again we install packages.

apt-get install postgresql
apt-get install php4-pgsql
apt-get install libdbd-pg-perl
apt-get install postgresql-doc
apt-get install webmin-postgresql

This should provide access to the PostgreSQL database for PHP4 and perl, besides allowing You to administer the server in WebMin.

UNICODE (UTF-8) seems to be the way most Linux distributions are going, so like a bleeting lamb, I'm going to follow suit and select UNICODE as the default encoding for my installation.

14.2. Configuring PostgreSQL

This part is about securing the server a bit more than it usually is. When You have first installed the PostgreSQL server You also got a file, /etc/postgres/pg_hba.conf, which has the active content of:

local	all					ident	sameuser
host	all	ident	sameuser
host	all	ident	reject

What we want is to let the postgres user connect to all databases, and each user connect to their own database only. Additionally we want to allow connections to a database using password. This would look something like this:

local  sameuser                                 ident   sameuser
host   sameuser       ident   sameuser
local  all                                      ident   postgres
host   all       ident   postgres
host   sameuser   md5
host   all         ident   reject

What this does is allow users on the local machine to connect to a database with the same name as the user. It allows the postgres user on the local machine to connect to all databases, and it allows a user on a remote machine in the local network to connect to a database with the same name as the user being used to authenticate (PostgreSQL user).

For the above to work as it's supposed to, we need to map the system postgres user to the PostgreSQL postgres user. This is done in the file /etc/postgres/pg_ident.conf.

The file itself contains a pretty good explanation if it's the default file that is installed by the Debian package.

postgres  postgres postgres

After this final excersize, we just restart PostgreSQL and everything should now be in order.

The care and maintenance of PostgreSQL itself is a subject fit for another document - or the documentation...