1. Knowledge Base
  2. Admin
  3. Database Administration

What are the best security practices for xTuple databases?

[Original blog post by BC Wilson - 01/15/2018]

Due to a recent increase in intrusion attempts on Internet-connected business databases, we are writing to strongly encourage all our users to review the security of your PostgreSQL database.

The following information will guide you through the process of updating important security settings for your database server.

The following steps (described in detail below) should be taken to prevent intruders from accessing your data.

  1. Update your pg_hba.conf file
  2. Enable enhanced authentication
  3. Use strong passwords
  4. Require new passwords periodically
  5. Remove unused accounts

The first action, updating your pg_hba.conf file, is the only one that requires technical knowledge. You may be able to update the file by following the directions below. If you are unsure of how to change, or uncomfortable with changing the file, you may need to contact an xTuple partner, or xTuple directly, to assist with the update. Normal hourly consulting fees will apply.

If you have any questions, please contact xTuple.

1. Update Your pg_hba.conf File

Your xTuple database runs on a PostgreSQL server, access to which is controlled by a file called pg_hba.conf. That file is described here: https://www.postgresql.org/docs/9.3/static/auth-pg-hba-conf.html.

At the end of the pg_hba.conf file are a number of lines that prevent unauthorized access of your database from outside of your network.

You will need to check this file to make sure that the proper settings are in place. After you change it you will need to reload the PostgreSQL configuration. The following steps describe how to edit and reload the pg_hba.conf file. These steps are aimed at users of Linux, and they will work in Terminal on Mac OS. If you use Windows, you may have other ways of editing the pg_hba.conf file and reloading your postgres config.

A. Edit your pg_hba.conf file.

The standard location of the file is /etc/postgresql/[postgres version]/[cluster name]/pg_hba.conf
So, for example, you might find it at: /etc/postgresql/9.3/main/pg_hba.conf

You will need superuser (sudo) access to modify and save changes to the file. Open the file in a text editor such as nano.

>sudo nano /etc/postgresql/9.3/main/pg_hba.conf

Scroll to the end of the file, and make sure that your host settings match the following.

#local replication postgres trust
#host replication postgres 127.0.0.1/32 trust
#host replication postgres ::1/128 trust
hostnossl all all 0.0.0.0/0 reject
hostssl all postgres 0.0.0.0/0 reject
hostssl all all 0.0.0.0/0 md5

This is the recommended configuration for your PostgreSQL server. The settings above ensure that no users are able to access the database from outside your network via a non-SSL connection. It also blocks the user named 'postgres' from accessing the database from outside the network. Finally, it declares that all other users will have to log in with a password via a secure connection.

You may have additional host entries in your pg_hba.conf file if you have specifically allowed other users and IP locations to access your database. Just make sure that you know the purpose of any additional lines in this file.

B. Save the changes to pg_hba.conf and reload

Once you have made the changes to the configuration file, you must save the file and reload the configuration.

To reload the config, you run the pg_ctlcluster utility, specifying the postgres version and cluster name. You will need to run this command as a superuser (sudo). As above, you will need to know the name and version of the postgres cluster you are working with. For example,

>sudo pg_ctlcluster 9.3 main reload

This command will update the configuration on the PostgreSQL server.

2. Enable Enhanced Authentication

xTuple has an option called "enhanced authentication" that helps secure the user connection. When this option is enabled, passwords are encrypted in the database and users will only be able to log in to the xTuple database via the xTuple client. They will not be able to log in to the database via PGAdmin, psql, or other direct means because their password will not match the encrypted version.

You will have to enable enhanced authentication separately for each user of the system:

  1. Open the xTuple desktop client
  2. Go to System->Maintain User Accounts...
  3. Open each user and click on the "Use Enhanced Authentication" checkbox.
  4. You will see a message that says "You have change this User Account's Enhanced Authentication option. The password must be updated in order for this change to take effect."
  5. Enter a new strong password for the user
  6. Click Save.
  7. Repeat for all users

3. Use Strong Passwords

Often intruders can simply guess a password to break into your system. It is easy to guess, for example, that an xTuple database will have a user called "admin". If the admin password is "admin", or "1234" or "asdfasdf", or another obvious pattern, it will take only a few tries to enter your database with full admin privs.

Therefore, it is best to require all users to follow these guidelines for creating a strong password.

A strong password has:

  • at least 15 characters
  • uppercase letters
  • lowercase letters
  • numbers
  • symbols, such as ` ! " ? $ ? % ^ & * ( ) _ - + = { [ } ] : ; @ ' ~ # | \ < , > . ? /

A strong password is NOT:

  • your login or username
  • your name, your friend’s name, your family member’s name, or a common name
  • a dictionary word
  • like your previous passwords
  • your date of birth
  • a keyboard pattern, such as qwerty, asdfghjkl, or 12345678

4. Require password updates

It's a pain for users, but it is a good idea to require all users to update their password periodically (every 6 to 12 months). Starting in xTuple version 4.10, administrators can set the system to require password updates. Go to System->Settings->Database to turn this feature on/off and set the required reset days.

5. Remove unused accounts

Make sure that you disable and/or remove the accounts of any users as soon as they should no longer have access to the database.