This article explains how to use PostgreSQL as a backend storage for mAirList.

PostgreSQL is an open source database management system, available for Windows, Linux and other operating systems. Being free of charge, fast and networking-enabled, we recommend to use PostgreSQL as the backend for any networked mAirListDB installation (where two or more mAirList computers or processes will share the same database).

Binary packages/installers for Windows are available here: http://www.postgresql.org/download/windows/ (for the actual download, you will be taken to a site called EnterpriseDB)

mAirList needs at least PostgreSQL 8.3 but will work fine with newer versions. You should pick the latest stable version available. As of October 2014, this is PostgreSQL 9.3.5.

The Windows installer includes the graphical administration frontend, pgAdmin III.

Download the appropriate setup file for your system (in this example postgresql-9.3.5-1-windows-x64.exe) and run it.

The installer will ask you for a couple of things - most can be left untouched:

• Installation directory: Where the program files go - leave untouched.
• Data directory: This is where PostgreSQL will store all SQL data. Usually inside the program directory, but it is possible to use a different location, e.g. a larger partition or hard drive.
• Password: This is the password for the superuser postgres. Choose a password and write it down. You will need it later to access the database with pgAdmin III.
• Port number: use default 5432
• Locale: leave untouched
• Stack Builder: We don't need that, uncheck this option.

At the end of the installation, you will find pgAdmin III in the Windows Start Menu.

The next step is to create an SQL user and an empty database for mAirList.

Open pgAdmin III, the main window will show:

In the tree on the left, you see the “PostgreSQL 9.3 (localhost:5432)” node - this is the server we have just installed. The red x icon means that pgAdmin is not connected to the server yet. Double-click that node, and pgAdmin will prompt for the password of the postgres user that you have set during installation:

Enter the password and click OK. The database tree will expand and show a couple of new nodes: Databases, Tablespaces, Group Roles, Login Roles

First, we have to create a new user (“login role”) under which mAirList will access the database server. Right-click the Login Role node, and select New Login Role.

In the dialog that appears, first enter the desired name of the role (e.g. mairlist - you should use all lower case letters).

Then switch to the Definition tab and set a password for the user.

Click OK, and the login role will be created.

Next, we create an empty database in which mAirList will store its contents later. Right-click the Databases node, then select New Database. In the dialog that appears, enter a name for the new database, e.g. mairlist5 (again all lower case!). Don't forget to set the owner to the mairlist user we have just created!

Leave all other fields untouched. In particular, the encoding of the database (on the Definition tab) must be set to UTF-8. Click OK to create the new database.

Out of the box, PostgreSQL does not allow any connections from other computers in your LAN. Before we can access the server from another computer, we must edit to configuration files – postgresql.conf and pg_hba.conf – and then restart the PostgreSQL service.

These configuration files can be found in the PostgreSQL data directory you chose during setup - by default this is C:\Program Files\PostgreSQL\9.3\data.

You can open the files with any text editor, or use the editing functions built into pgAdmin III. We will demostrate the latter option here.

In pgAdmin, go to File → Open postgresql.conf, and locate the postgresql.conf file in the PostgreSQL data directory. The following dialog will appear:

Look for the setting name listen_addresses. It must be enabled (check mark set) and set to *.

Note: Newer PostgreSQL installations on Windows apparently have this enabled by default. It's a good idea to confirm that the setting is correct though.

Don't forget to click Save if you have made any changes.

The second file, pg_hba.conf, defines which databases should be accessible from which hosts and users, and which authentication method should be used.

In pgAdmin, click File → Open pg_hba.conf, and select the pg_hba.conf file from the data directory. You will see a window like this:

Double-click the empty line (that only has a checkbox) at the bottom of the list, and a dialog will appear where you can add a new entry:

In this dialog, enter the following values:

• Enabled: yes
• Type: host
• Database: all (or only mairlist5 if you want to be very restrictive)
• User: all (or only mairlist)
• IP address: The address range of your LAN, see comments below
• Method: md5

The IP address range must be given in <IP>/<prefix> form. For example, if your network uses the 192.168.178.x addresses with netmask 255.255.255.0 (= prefix /24), enter 192.168.178.0/24. For more information on subnet prefixes, see here: https://en.wikipedia.org/wiki/Subnetwork

Don't forget to click OK and save the new pg_hba.conf file.

PostgreSQL is available as a package in all major Linux distributions. For Debian/Ubuntu, simply install the postgresql package:

apt-get install postgresql

The graphical frontend pgAdmin III is also available as a package (pgadmin3). Most experienced Linux administrators will prefer to use the command line tools though.