Configuring PostgreSQL for XNAT

Configuring PostgreSQL is big topic, much of which is outside the scope of using it with XNAT. This page specifically addresses configuring access and security for the connection between XNAT and PostgreSQL. Other issues like tuning and improving performance may be addressed later. This page focuses on using command line tools and psql, the PostgreSQL CLI client. There are other tools, such as pgAdmin, phpPgAdmin, and many others that you can use as well.

As noted in the XNAT Installation Guide, XNAT 1.8 works with PostgreSQL 10.x to 12.x. The initial release of XNAT 1.8.0 should work with PostgreSQL 9.6 as well, but releases after 1.8.0 may no longer work as expected with 9.6. Also, per their versioning policies, PostgreSQL will no longer support 9.6 after 11 November, 2021.

Creating XNAT's database user

XNAT requires a user account to access the database, as well as a database that can be accessed by that user. You can create these with the createuser and createdb tools. With PostgreSQL's default configuration, the only configured user is the superuser postgres. This corresponds to the default PostgreSQL service user. Because of the default security configuration for PostgreSQL and the postgres user, you usually need to issue your initial commands as that user. There are different ways to accomplish this.

Many of the code samples below use a prompt that includes the username and current working directory so that you can see which user is active at each given point. The format of this prompt is:

username:pwd$

So if the xnat user is active in the folder /etc/postgresql/12, the prompt would look like this:

xnat:/etc/postgresql/12$

On Linux, Unix, and OS X, the tilde (~) character is shorthand for the current user's home folder.

You can use the sudo command to create a login shell as the postgres user. This has the benefit of allowing you to enter multiple commands as that user, as well as use interactive command-line tools like psql. The code below shows how to create a new user and database, as well as set the user's password.

xnat:~$ sudo su - postgres 
postgres:postgres$ createuser --createdb xnat
postgres:postgres$ createdb --owner=xnat xnat;
postgres:postgres$ psql
psql (12.6)
Type "help" for help.

postgres=# \password xnat
Enter new password:
Enter it again:
postgres=# \q
postgres:~$ logout
xnat:~$

You can also use the sudo command to run one command at a time. This code has the same result as the code above.

$ sudo su - postgres -c "createuser -D xnat"
$ sudo su - postgres -c "createdb -O xnat xnat"
$ sudo su - postgres -c "psql -c \"ALTER USER xnat WITH PASSWORD 'xnat'\""

These values–the database, username, and password–are the values you'll use when configuring your XNAT through xnat-conf.properties, as described in XNAT Installation Guide.

Configuring database access

Once you have a database and user configured, XNAT needs to be able to connect to your PostgreSQL instance. This begins by configuring the properties in xnat-conf.properties, but may also require changes in your PostgreSQL configuration, depending on how you're accessing the database.

The location of the PostgreSQL configuration files differs based on the platform on which you're installing:

• Debian/Ubuntu: /etc/postgresql/version/main
• Fedora/Red Hat: /var/lib/pgsql/data

The location on Windows and OS X depends on where you install PostgreSQL itself, so it's difficult to say where these files are generically.

When you modify PostgreSQL's configuration files, you need to reload or restart the service for the changes to take effect:

CODE

$ sudo systemctl restart postgresql

On some older versions of Linux, systemctl may not exist, in which case you'll need to use the services command:

CODE

$ sudo services postgresql restart

Configuring TCP/IP listeners

XNAT uses Java's JDBC to access the database, which does not support Unix-socket connections. This means all connections to the database from XNAT must use TCP/IP. By default, PostgreSQL only listens for TCP/IP connections from the localhost (i.e. the same machine). If you're running your database and XNAT on the same server, you won't need to change the PostgreSQL configuration. If you are deploying your XNAT and database on separate servers, you will need to change at least one configuration option, the listen_addresses setting in the postgresql.conf file.

listen_addresses = '*' 

The character * means basically allow incoming connections from any IP address. You can also specify particular IP addresses separated by commas, but in practice most people handle restricting access by configuring the pg_hba.conf file (see the next section for more information on this).

Managing database access by user and host

PostgreSQL's host-based authentication (HBA) scheme lets you decide which users can access which databases from which IP addresses, as well as the types of authentication required for each configuration. This is controlled through the pg_hba.conf file, which is located in PostgreSQL's configuration folder (it's worth getting familiar with PostgreSQL's reference documentation for this configuration file if you need to do any significant amount of configuration).