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                         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

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

%d bloggers like this: