Security

Security and availability in a shared persistence deployment

In shared persistence deployments the network traffic between the servers, the database and the file share is not encrypted by default after an installation. You may also need to consider setting up replication of the database to handle cases where the central database fails.

Maintaining database password integrity

Here are some guidelines to maintain password integrity in a Qlik Sense shared persistence deployment.

  • It is important that you disable the Store password option for your user in PostgreSQL. If this option is enabled, the password is stored in a file, and incoming connections without a password will be able to connect to the database.
  • Change password by executing this query in the PostgreSQL database:

    ALTER USER <user> WITH PASSWORD '<newpassword>';

    ALTER ROLE is displayed after successfully changing the password.

    Do not change password in the PostgreSQL user interface for the same reasons as above.

  • Use md5 hashing.
  • Do not set your password to PASSWORD '', that is, an empty string, since this is not handled well in PostgreSQL.

Database traffic encryption

Qlik Sense supports database traffic encryption using SSL, but you need to perform some manual configuration to setup SSL and MD5 password protection in a shared persistence deployment:

执行以下操作:

  1. Edit the following values in postgresql.conf:

    listen_addresses = '*'
    port = 4432
    ssl = on
    ssl_cert_file = 'server.pem'
    ssl_key_file = 'server_key.pem'
    #ssl_ca_file = ''
    #ssl_crl_file = ''
  2. Add the following lines in pg_hba.conf

    hostssl all all all md5
  3. Remove any other lines starting with hostssl or host in pg_hba.conf.
  4. Copy server.pem, and server_key.pem from %PROGRAMDATA%\Qlik\Sense\Repository\Exported Certificates\.Local Certificates to %PROGRAMDATA%\Qlik\Sense\Repository\PostgreSQL\9.6.
  5. Use the Connection String Editor to add the following setting to the repository.exe.config on the central node, and all rim nodes that belong to the cluster. To open the Connection String Editor, navigate to C:\Program Files\Qlik\Sense\Repository\Util\QlikSenseUtil and open the QlikSenseUtil.exe file as an administrator.
  6. In the Connection String Editor tab, click Read to open the Repository.exe file connection string.
  7. Add ‘Ssl Mode=Require;’ to the connection string:
  8. <add name="QSR" connectionString="User ID=qliksenserepository;Password=’randompass’; Ssl Mode=Require;Host=fullhostname.com;Port=4432;Database=QSR;Pooling=true;Min Pool Size=0;Max Pool Size=90;Connection Lifetime=3600;Unicode=true;"providerName="Devart.Data.PostgreSql"/>
  9. Click Save value in config file encrypted to save your changes.
  10. Start all Qlik Sense services and verify that everything works.
  11. Verify the authentication using the pgAdmin tool in PostgreSQL:

    Users postgres and qliksenserepository must enter a valid password to connect.

Forcing the database connection to use TLS 1.2 only

You can configure the database connection to support TLS 1.2 only, and block connections using TLS 1.1 or lower.

执行以下操作:

  • Add the following parameter to the connection string: "SSL TLS Protocol=1.2"

We recommend these additional configuration changes to maintain database integrity:

  • Configure the database to only accept connections from servers where the repository is running.
  • Configure SSL to reject weak cipher suites by adding this line to the file postgresql.conf:

    ssl_ciphers = 'DEFAULT:!LOW:!EXP:!eNULL:!aNULL:!MD5:!RC2:!RC4:!DES:@STRENGTH'

Database replication and failover

This section describes how to set up database replication and failover in a shared persistence environment. Additionally, the file storage content will also need to be replicated. To fail over to a standby node in case the central database or node is lost, one or more standby databases can be configured for streaming replication from the database on the primary node.

When editing text files related to the Qlik Sense installation, do the following:

  1. Copy the file to another location on the server.
  2. Edit the file and save the changes.
  3. Copy the updated file back to its original location.

Setting up replication to standby nodes for failover

The instructions in this section describe how to set up asynchronous streaming replication to one or more standby nodes. Before starting, ensure that the environment is configured and running, and install PostgreSQL on a standby machine.

备注: The paths in the instructions are adapted to a default PostgreSQL installation used as database on a dedicated machine. If you are using a PostgreSQL database installed by Qlik Sense you need to adapt the paths used, as the database is installed in %ProgramData%\Qlik\Sense\Repository\PostgreSQL\<version>\.

Configure the primary database server

On the primary database server, do the following:

  1. Open the file %ProgramFiles%\PostgreSQL\9.6\data\postgresql.conf

    Locate and set the following settings

    wal_level = replica
    max_wal_senders = 3
    wal_keep_segments = 8
    hot_standby = on
  2. Create a user account that can be used for replication. To do so from a command prompt, run the following command. Adjust the hostname as needed, and specify a suitable password. You may be prompted for a password, this is the password that was specified during installation.

    "C:\Program Files\PostgreSQL\9.6\bin\psql.exe" -h <machinename> -p 4432 -W -c "CREATE USER replicator REPLICATION LOGIN ENCRYPTED PASSWORD 'secretpassword';"
  3. Open the file %ProgramFiles%\PostgreSQL\9.6\data\pg_hba.conf.

    At the bottom of the file add:

    host replication replicator 0.0.0.0/0md5

    You can restrict the subnet access further, if required.

  4. Restart the PostgreSQL service.

Configure the standby database server

On the standby PostgreSQL database server, do the following:

  1. Stop the Postgres service.
  2. Delete all content from %ProgramFiles%\PostgreSQL\9.6\data.
  3. From the command line run the following command adjusted to use the name of the primary server:

    "C:\Program Files\PostgreSQL\9.6\bin\pg_basebackup.exe" -h <primaryServer> -D "C:\Program Files\PostgreSQL\9.6\data" -U replicator -v -P -p 4432

    You can ignore any warnings about copying files manually.

  4. In a text editor, create a file called recovery.conf and place it in %ProgramFiles%\PostgreSQL\9.6\data.
  5. Open recovery.conf and add the following text, adjusting the hostname and port:

    standby_mode = 'on'
    primary_conninfo = 'host=< primaryServer > port=4432 user=replicator password=secretpassword'
    trigger_file = 'failover'
    recovery_target_timeline = 'latest'
  6. Start the PostgreSQL service.

You should now be able to connect to the database and view the data being streamed over from the primary node in read only mode.

Manual database failover

If the database on primary node is lost, a standby node needs to take over.

执行以下操作:

  1. On the standby node that is to become the new primary node, create a file called failover in the folder %ProgramFiles%\PostgreSQL\9.6\data

    备注: The failover file should have no file extension.

    The file triggers PostgreSQL to cease recovery and enter read/write mode. PostgreSQL also changes the name of the file recovery.conf to recovery.done to reflect the transition.

  2. On each node, change the repository database connection string to point to the hostname or IP address of the new database node. As the connection string is encrypted in the config file, you need to use the Connection String Editor to decrypt the string, edit it, and write back an encrypted string.

    1. To open the Connection String Editor, navigate to C:\Program Files\Qlik\Sense\Repository\Util\QlikSenseUtil and open the QlikSenseUtil.exe file as an administrator.
    2. In the Connection String Editor tab, click Read to open the Repository.exe file connection string.

      The decrypted database connection string is displayed.

    3. Replace the value for Host with the hostname or IP address of the new database node.

    4. Click Save value in config file encrypted to save your changes.