Installing and Configuring your local Postgres server

Access rights setup can be performed ahead of installing GNUmed, or can be delayed until after the gnumed-server software has been installed (and the GNUmed databases have been created, upgraded, populated with test data and localized). However, access rights are needed before connections are allowed from clients, or from any other data-access or backup processes.

Remember also that Postgres must be restarted before it can recognize any reconfiguration. In Linux as system user postgres call /etc/init.d/postgresql-8.3 restart. In Windows select Reload configuration from the Windows start menu in the Postgresql entry.

1) Installing Postgres

GNUmed is known to work with PostgreSQL 8.1, 8.2, and 8.3. Best results are obtained with 8.3.

If you are using Linux, it is best to use your distribution's package manager (Debian or Ubuntu apt, synaptic; openSUSE ZYpper; Fedora or Mandriva or Red Hat RPM) to install a pre-packaged version of the PostgreSQL database server. A bug (as at July 29, 2009) of interest to those who would freshly install Debian Testing is mentioned here. Users of Windows can download an installer from here or PostgreSQL website (be sure to pick the latest version), and install that.

On most systems on which a standard PostgreSQL cluster would have been installed, if you have no need of special data administration, you should be able to jump to the section Set up database access rights.

If you run into problems, or if you use MS Windows, go to Troubleshoot PostgreSQL.

2) Special data administration, and/or encrypted devices or volumes

Read this only if you want to use table spaces or encrypted storage.

Special data administration can make sense if an encrypted device or volume is to be used and also in other special cases. These can require the creation of PostgreSQL tablespaces on the encrypted or other special storage area, by a database admin. How to do that is described in the PostgreSQL Manual. The bootstrapper then supports putting GNUmed databases into those tablespaces, documented further in gnumed/server/bootstrap/bootstrap-standard.conf.template.

Before committing to encrypted devices or volumes, give careful consideration that if the device should become unmounted, for example after a power outage or server reboot, someone would need to re-mount the encrypted device. More on this available at EncryptingDevices as well as from the GNUmed developer list archive.

Otherwise, for any "normal" install, it is recommended to permit the GNUmed database to reside within the original data cluster created with the initialization of Postgres on the server. More on this here.

3) Secure connections to your database

The need to require secure connections cannot be overstated.

People who are less familiar with Postgres than with MySQL may be interested in a brief overview of secure connections to Postgres, at techrepublic.

4) Setting up database access rights

This is achieved through edits to the postgres configuration files pg_hba.conf and pg_ident.conf located as follows:

• on non-Debian (Fedora, Red Hat, SuSE) these likely remain in /var/lib/postgresql/
• on Debian, these will have been moved to /etc/postgresql/8.x/main
• if you can't find them, try (on *nix) the command locate filename

As root (or by means of sudo), add the lines below to the designated files, at the proper position, where proper means that the sequence is important:

4.1) pg_hba.conf (this is the only change needed for a simple localhost database setup)

It is known that Debian Sid (unstable) has additional requirements (see DebianGuideSid). Otherwise, add the three lines below, placing them

• toward the end of the "local" section, meaning after the line " local all postgres ident sameuser "
• but just before the line " local  all  all  ident sameuser "

# allow anyone knowing the proper password to
# log into our GNUmed databases
local   samegroup      +gm-logins                    md5

4.2) postgresql.conf

Read this only if you want to access your GNUmed server from other machines on a network.

Set the listen_address parameter to whichever network interfaces you want your GNUmed database to be available on. This is necessary in particular if you want to connect to your server from other machines.

5) Enabling bootstrapping by "other" system accounts (this is not necessary for a simple localbase database installation)

Read this only if you want to bootstrap as a user other than root.

If you intend to allow the bootstrapper to be run under a different system account than root or postgres (say, gmadm) then this "other user" must first be configured to allow the following Postgres connections:

• connect to template1 with the database account postgres on localhost
• connect to the GNUmed database (gnumed_v[N]) with the database account postgres on localhost

5.1) pg_ident.conf

Caveat: be extra sure to preserve the second line postgres-alikes   postgres   postgres or you may lose access to your databases as postgres without which you will be unable to do automated maintenance, installation, and upgrading (say, package upgrading or autovacuum operation).

gm-dbo-alikes gmadm               gm-dbo
postgres-alikes postgres        postgres
postgres-alikes gmadm             postgres

5.2) pg_hba.conf

Caveat: you must ensure that the ident postgres-alikes line gets inserted above the postgres ident sameuser line. If not, authentication will fail, as postgres does not check consecutive lines after it found a match.

# allow some people to impersonate "postgres" when connecting to template1
local   template1       postgres                        ident postgres-alikes
# replace N with the database version you want to install
local   gnumed_vN     postgres                       ident postgres-alikes

# allow real "postgres" to connect to all other databases, too
local   all             postgres                                ident sameuser

# allow some people to impersonate "gm-dbo"
local   template1         gm-dbo                           ident gm-dbo-alikes

6) Proceed to install or upgrade a GNUmed database "backend"

Again, Postgres must be restarted before it can recognize any reconfiguration. Do this as system user root calling /etc/init.d/postgresql-8.3 restart.

You can then proceed to the GNUmed database installation and upgrade section.