Set up an external PostgreSQL server

Splunk Phantom uses a PostgreSQL 9.4 database. In many installations, the database runs on the same server as Splunk Phantom. It is possible to put the database on its own server. For more information about configuring and operating a PostgreSQL database, consult the PostgreSQL website and their documentation.

Install and configure PostgreSQL

If you run the PostgreSQL database on its own server, install and configure PostgreSQL before installing Splunk Phantom.

These instructions are based on CentOS 6 and 7 or Red Hat Enterprise Linux 6 and 7. If you choose to to install PostgreSQL on another operating system, consult the documentation on the PostgreSQL website.

1. Install and configure one of the supported operating systems according to your organization's requirements. See Supported operating systems.
2. Update the kernel semaphore parameters and refresh the system configuration.

   echo "kernel.sem=250 32000 32 5000" >> /etc/sysctl.conf 
   sysctl --system
   

3. Configure your firewall to allow access. For a complete list of ports, see Splunk Phantom required ports.
4. Add any additional yum repositories that you need. Use the tool on the Linux downloads (Red Hat family) page to identify the correct repository for your architecture and operating system combination.
   yum install <URL>
5. Install the PostgreSQL server.
   yum install postgresql94-server
6. Initialize the PostgreSQL database.
   /usr/pgsql-9.4/bin/postgresql94-setup initdb
7. Set PostgreSQL to start when the system starts.
   systemctl enable postgresql-9.4
8. Change to the postgres user.
   sudo - postgres
9. Change to the PostgreSQL data directory.
   cd /var/lib/pgsql/9.4/data
10. Generate the SSL certificate PostgreSQL uses.
    openssl req -new -x509 -days 3650 -nodes -text -out server.crt -keyout server.key -subj "/CN=postgres.cluster1"

    You can use an SSL certificate purchased from a Certificate Authority instead of generating a self-signed certificate.

11. Set the permissions on the server.key file.
    chmod og-rwx server.key
12. Run psql as the postgres user.
    sudo -u postgres psql
13. Set the postgres user password, if it has not already been set.
    ALTER USER postgres PASSWORD '<postgrespassword>';
14. Create the pgbouncer user.
    CREATE USER pgbouncer PASSWORD '<pgbouncerpassword>';
15. Set PostgreSQL to use SSL. Provide the keys and cipher level.

    ALTER SYSTEM SET ssl = on 
    ALTER SYSTEM SET ssl_cert_file = '<PHANTOM_HOME>/data/ssl/server.crt' 
    ALTER SYSTEM SET ssl_key_file = '<PHANTOM_HOME>/data/ssl/server.key' 
    ALTER SYSTEM SET ssl_ciphers = 'HIGH:+3DES:!aNULL'
    

    Exit PostgreSQL by typing CTRL+D.
16. Change back to the root user.
    exit
17. Edit the pg_hba.conf file to enable access to the database. Splunk Phantom must be able to connect as both the postgres and pgbouncer users. In each entry, supply the IP range that will be used by your Splunk Phantom install or cluster.

    # TYPE  DATABASE    USER        ADDRESS         METHOD
    local   all	    all         peer		
    hostssl all         postgres    <IP Range>/<XX> md5
    hostssl phantom     pgbouncer   <IP Range>/<XX> md5
    

18. Edit postgresql.conf. Set values for max_connections, work_mem, shared_buffers, and listen_address.

    max_connections=2500
    work_mem=128kB
    shared_buffers=2GB
    listen_addresses = '*' # what IP address(es) to listen on;
    

    For listen_address set a value that matches your security requirements. Valid settings are:

    • * for all addresses, 0.0.0. for all IPv4 addresses
    • :: for all IPv6 addresses
    • specific addresses you supply.
19. Restart the PostgreSQL service.
    systemctl restart postgresql-9.4

Configure Splunk Phantom to use the external PostgreSQL database

Your Splunk Phantom deployment must be configured to use the external PostgreSQL database.

Do these steps as the root user or a user with sudo permissions.

1. On your Splunk Phantom deployment, edit the file /etc/pgbouncer/pgbouncer.ini in the section [databases] to include the external PostgreSQL server information.
   phantom = user=pgbouncer password=<pgbouncer password> host=<external PostgreSQL server>
   postgres = user=postgres password=<postgres password> host=<external PostgreSQL server>
2. Stop all Splunk Phantom daemons.
   <PHANTOM_HOME>/phantom/bin/stop_daemon.sh all
3. Restart the pgbouncer service so that it will reload its settings.
   service pgbouncer reload
4. Restart the Splunk Phantom daemons.
   <PHANTOM_HOME>/phantom/bin/start_phantom.sh

Backup a Splunk Phantom database and restore on an external database

To backup a Splunk Phantom database and restore it on an external database, do these steps as the root user or a user with sudo permissions.

You must use identical versions of Splunk Phantom for this procedure. For example, if your PostgreSQL backup is from Splunk Phantom 4.8.24304, you must restore it to use with an instance of Splunk Phantom 4.8.24304.

1. Backup the database.

   cd /opt/phantom/bin
   phenv python backup.pyc --all
   

2. Copy the file path that shows the backup file that was created to use in a future step.

   All data backed up to /opt/phantom/data/phantom_backups/phantom_backup_2017-07-15-20-47-04.126913.tgz
   

3. Edit the [databases] section in the /etc/pgbouncer/pgbouncer.ini file as shown in the following code. host is the IP address or DNS name of the database server.

   phantom = user=pgbouncer password=<pgbouncerpassword> host=<pg server>
   postgres = user=postgres password=<postgrespassword> host=<pg server>
   

4. Stop the Phantom daemons, reload the pgbouncer settings, and test the connection to the database server.

   /opt/phantom/bin/stop_daemon.sh all
   service pgbouncer reload
   

5. Test connectivity.

   sudo -u postgres psql -h /tmp -p 6432
   

   If connectivity is successful, you will see the following message:

   psql (9.4.12, server 9.4.4)
   Type "help" for help.
   postgres=# >Ctrl-D>
   

6. Initialize the database to use with Splunk Phantom.

   cd /opt/phantom/bin
   phenv python prepare_db.pyc
   

   Ignore the errors/warnings about roles, inactive time zone support, or IPC failures. The IPC failures occur as you have stopped the Splunk Phantom daemons.

7. Restore the backup using the file name you copied in step 2.

   cd /opt/phantom/bin
   phenv python restore.pyc --file /opt/phantom/data/phantom_backups/phantom_backup_2017-07-15-20-47-04.126913.tgz
   

8. Connect to the Splunk Phantom server's web user interface.