Installing PostgreSQL

July 22, 2007

It turned out that PostgreSQL 7.5.22 (not 8.2) with database server 7.4 is the most recent stable version for a Debian 4.0 system, such as my new one.

# apt-get install postgresql postgresql-client

The following NEW packages will be installed:
  openssl postgresql postgresql-7.4 postgresql-client postgresql-client-7.4
  postgresql-client-common postgresql-common ssl-cert

This automatically created a new Unix user (and group) postgres, a new PostgreSQL domain main; it placed the data directory in /var/lib/postgresql/7.4/main and instructions specific to Debian in /usr/share/doc/postgresql-7.4/README.Debian.gz. I then manually added a database and PostgreSQL user guava (notice that this name would not have to match a Unix user name; PASSWORD is only a placeholder, of course :)

# su postgres
# createuser -DA guava
# createdb -O guava guava
# psql template1
Welcome to psql 7.4.16, the PostgreSQL interactive terminal.
template1=# alter user guava with password 'PASSWORD';
template1=# \q

I have also restricted client authentication from its defaults: only the PostgreSQL admin user keeps local access to all databases. PostgreSQL user guava can access only one database; her PostgreSQL password won’t be inquired for local connections, but TCP/IP connections need to convey digested passwords and must either originate on localhost or use SSL tunneling from my machine at home. Overall, I had to modify three lines in pg_hba.conf and add a new one (CLIENT is a placeholder for the actual IPv4 NAT address of my machine at home; actual SSL connectivity will require some further steps, which I’ll document later).

local   all   postgres                                                   ident sameuser
local   guava guava                                                      ident sameuser
host    guava guava    127.0.0.1 255.255.255.255                         md5
host    guava guava    ::1       ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff md5
hostssl guava guava    CLIENT/32                                         md5

My ISP assigns a NAT address to my ADSL router with DHCP, so the NAT address will change (it was already different yesterday). So far I don’t know the range of addresses in use, and will have to trace them by updating the configuration file. The PostgreSQL database server must be informed of changes to pg_hba.conf e.g. by restart.

# /etc/init.d/postgresql-7.4 restart

$ psql
guava=> select current_database();
guava=> \q
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: