1. Knowledge Base
  2. Admin
  3. Database Administration

Security Best Practices for xTuple Databases

This article includes a list of the best practices and tips for keeping your PostgreSQL database secure and maintaining that security.

It is strongly encouraged to keep an up-to-date review of your PostgreSQL database's security due to the ever-changing intrusion attempts on Internet-connected business databases. The following information will guide you through the process of updating important security settings for your database server.

The following steps 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. 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". A more detailed explanation of this file can be found here: https://www.postgresql.org/docs/9.6/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. However, note that 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.6/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.6/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 trust
#host replication postgres ::1/128 trust
host       all   postgres       reject
hostssl all postgres reject
hostnossl all all reject
# Trust the admin when logging in from 10.10.g10.34/32
hostssl all +xtrole 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 must belong to the 'xtrole' group and must 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 need to 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.