Installing and Configuring your local PostgreSQL server

The PostgreSQL server can be run on the same machine as the client or on another machine. Bootstrapping the GNUmed database works best from the machine on which PostgreSQL is running.

Installing PostgreSQL

GNUmed is known to work with PostgreSQL 8.3 and 8.4. Best results are obtained with 8.4. If you are using an earlier version, or will be upgrading and maybe needing to upgrade your databases, see the page TroubleshootPostgreSQL.

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 you should be able to jump to the section Database Access Rights Setup right away unless you have a need for special data administration (this means if you are using PostgreSQL pretty much only for GNUmed).

If you use MS Windows it is recommended to use the GNUmed All-in-one Installer. However PostgreSQL can be installed separately.

For Mac OS, see PostgresOnMac.

If you run into problems go to Troubleshoot PostgreSQL.

Using table spaces and/or encrypted storage

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.

Secure connections to the database

Remember: We cannot overstate the importance of requiring secure connections for access to clinical data.

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

Database Access Rights Setup

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

Bootstrapping the database

The bootstrapping step in GNUmed database creation can typically be done with PostgreSQL's default access permissions untouched.

Permitting clients to access your database

Windows users can skip this step if they only wish to connect from the same machine as their database.

Access rights must be granted before your Postgres will accept connections from any

• GNUmed clients
• other-software data-access, or
• backup processes.

Permission to connect as a GNUmed user can be controlled at multiple levels. Postgres permissions management includes one or more Postgres configuration files with some differences between *nix and Windows. Postgres configuration files are often located as follows but, if you can't find the file, try (on *nix) the command locate filename .

• Linux
  • on non-Debian (Fedora, Red Hat, SuSE) these likely remain in /var/lib/postgresql/
  • on Debian, these will have been moved to /etc/postgresql/x.y/main
• Mac OS X (10.6+ at least)
  • • these likely live in e.g. /Library/PostgreSQL/8.4/data/
• MS Windows
  • • in C:\Program Files\PostgreSQL\x.y\data

Linux (Debian, Ubuntu etc) and Mac OS

As root or by means of sudo insert these two lines

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

into the pg_hba.conf file immediately below the line

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

but above any lines starting with local.

After you are done, it should look like:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# allow anyone knowing the proper password to log into our GNUmed databases:
local   samegroup     +gm-logins                    md5

local   ...                    ...                                  ...
local   ...                    ...                                  ...

Windows

No changes to pg_hba.conf are currently needed after a default installation:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# IPv4 local connections:
host    all         all         127.0.0.1/32          md5

# IPv6 local connections:
#host    all         all         ::1/128               md5

Permitting access to the database server from other machines on a network

The following changes are needed only if you want to access your GNUmed server from other machines on a network.

This change must be made to postgresql.conf:

• set the listen_address parameter to whichever network interfaces you want your GNUmed database to be available on for other machines on your network
• setting this option to listen_address='*' will make PostgreSQL listen on all network interface

You will also have to add remote access permissions for your other machines (which is similar to access from the database server itself, except for the host rather than local token). Let's assume machines in your network carry IP addresses in the 192.168.178.1 to 192.168.178.254 range. Add this to pg_hba.conf:

# allow anyone knowing the proper password to log into our GNUmed databases:
host    samegroup    +gm-logins      192.168.178.0/24      md5

Add this line just above the first line that starts with host. This will typically be below any lines starting with local.

After you are done, it should look like:

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
local   samegroup     +gm-logins                    md5

local   ...                    ...                                  ...
local   ...                    ...                                  ...

... other lines ...

# allow anyone knowing the proper password to log into our GNUmed databases:
host    samegroup    +gm-logins     192.168.178.0/24       md5

host ...                      ...                     ...                              ...
host ...                      ...                     ...                              ...

Enabling bootstrapping by "other" system accounts: NOT needed for a simple local database installation

Read this only if you want to bootstrap as a user other than root (note that this is rarely needed and neither recommended nor supported - better know what you do !).

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_

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

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

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.4 restart.

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