Spotfire Server create database scripts may fail to create a new PostgreSQL schema in a Cloud Service Provider environment

Problem:

In some PostgreSQL-type Cloud service provider (CSP) environments, the user account provided by the CSP may not have the SUPERUSER privilege. This will cause the database scripts of the Spotfire installation package to fail, accompanied by error "must be member of role "<SPOTFIREDB_USERNAME>" or similar error in Spotfire script's log.txt file. The failure will occur in the 'create_server_env.sql' script - either in 'create database' command or 'alter database owner' command. 

Solution:

The published Spotfire Server System Requirements recommends a workaround to avoid this issue. Referring to the content of create_databases.sh (see below Appendix), set the value of SPOTFIREDB_USERNAME to be the same value as the CSP-provided user: DBSERVER_ADMIN_USERNAME.

Details:

The 'create_server_env.sql' script is designed to connect as the provided database admin user and create your new Spotfire Server schema, then create a Spotfire-specific database user account, and then make your new user account the owner of your new Spotfire schema. This allows for Spotfire connecting to its database as a user with lower privilege (SPOTFIREDB_USERNAME) than the given admin user account has, which carries some security advantages. The workaround here is a bit of a compromise in that respect. It is necessary to comment out a portion of the 'create_server_env.sql' database script, and also modify a parameter in the create_databases.sh (or create_databases.bat) script.

This workaround is described in the below content, pasted here from the database section of the Spotfire System Requirements (14.0 LTS).

**** The created user when creating a CSP managed database account may not have the SUPERUSER privilege, thus it is not possible to create another user to the database. If that is the case, then the (1) DBADMIN (DBSERVER_ADMIN_USERNAME) credentials must be set to the same value as SERVERDB (SPOTFIREDB_USERNAME) in the create_databases.bat script and (2) the user creation must be disabled (in create_server_env.sql, by removing or commenting out the "create user" clause and the "alter database" clause).

Here is a link to the full set of the Spotfire Server System Requirements documentation: Spotfire Server 14.0 LTS

Appendix:

An excerpt of the create_databases.sh database script is shown below (this is the default content).

# Uncomment to set variables:
# PGHOST=<PGHOST>
# PGPORT=<PGPORT>
# DBSERVER_ADMIN_USERNAME=postgres
# DBSERVER_ADMIN_PASSWORD=<DBSERVER_ADMIN_PASSWORD>
# PGDATABASE=postgres
# SPOTFIREDB_DBNAME=spotfire_server
# SPOTFIREDB_USERNAME=<SPOTFIREDB_USERNAME>
# SPOTFIREDB_PASSWORD=<SPOTFIREDB_PASSWORD>