How can I change a PostgreSQL user password?

Change the password of a PostgreSQL user and update every location where CAST Imaging stores it

Overview

When a PostgreSQL instance is installed and configured for CAST Imaging, two custom users/roles are available for use with CAST Imaging: operator/CastAIP and guest/WelcomeToAIP. An additional user postgres is provided out of the box in all situations.

Changing the password in PostgreSQL is not sufficient on its own. CAST Imaging stores the password of the user it connects with in several locations, and all of them must be updated with the new value. If any location retains the old password, the affected services fail to start with a password authentication error, and any subsequent update of CAST Imaging fails part way through because the update process reuses the stored credentials.

This page covers the change itself and all the locations that must be updated afterwards. Step 1 applies to any deployment; the remaining steps describe a Microsoft Windows deployment installed with the global installer.

Never change a PostgreSQL password and update CAST Imaging to a new release at the same time. Complete the password change, verify that all services start and that CAST Imaging is usable, and only then start the update. See In-place component update for Microsoft Windows.

Step 1 - Change the password

CAST recommends either using PgAdmin or a SQL query. Both are explained below.

Using PgAdmin

Right click the user and choose Properties. Then click the Definition tab and enter the new password in the Password field. Click Save to action the changes.

Using a SQL query

Use the ALTER USER statement, where username is the name of the user and password is the new password:

ALTER USER username   
WITH PASSWORD 'password';

Step 2 - Encrypt the new password

Several of the locations updated in the steps below store the password in encrypted form rather than in plain text. Neither the installer nor the update script encrypts the password for you, so you must generate the encrypted value yourself using the encryption tool shipped with the installation media:

<extracted_installer_folder>\tools\imaging-encryption-tool.bat

Run the tool, provide the new password, and copy the value it returns. The value takes the following form:

CRYPTED2:<encrypted_value>

Keep both the plain text password and the CRYPTED2: value to hand - the steps below need both.

The tool requires a 64-bit JDK with JAVA_HOME set. On Microsoft Windows this is a mandatory prerequisite of CAST Imaging - see Installation prechecks.
The same encrypted format is used by the CSS_ENCRYPTED_PASSWORD installation variable - see Microsoft Windows installation variables.

Step 3 - Update CAST Imaging to use the new password

Information about the PostgreSQL instances available to your node instances (including the user credentials) is stored in the control_panel schema, in a table called database_connection_settings. This schema is stored on the default PostgreSQL instance defined during the CAST Imaging installation.

To update the stored password, CAST highly recommends that you use the CAST Imaging admin UI. Go to Settings > Global Configurations > CSS and Measurement Settings, select the instance under the database connection list, click Edit, enter the new password and click Save:

See Using the database connection list for more information about this panel.

Step 4 - Update the control_panel schema

The Control Panel reads its own database credentials from two tables in the control_panel schema. Run the following queries against that schema, using the CRYPTED2: value generated in Step 2:

UPDATE control_panel.properties
SET value = 'CRYPTED2:<encrypted_value>'
WHERE prop_key = 'spring.datasource.password';

UPDATE control_panel.database_connection_settings
SET password = 'CRYPTED2:<encrypted_value>';

The second query updates the password of every PostgreSQL instance declared in CAST Imaging. Where you have declared multiple instances and have changed the password on only one of them, add a WHERE clause to target that instance, for example WHERE host = 'my_host_name'.

Step 5 - Update the Control Panel configuration file

Open the CAST Imaging Control Panel configuration file in a text editor:

%PROGRAMFILES%\CAST\Imaging\CAST-Imaging-Control-Panel\application-default.yml

On installations earlier than 3.6.2-funcrel the file is:

%PROGRAMFILES%\CAST\Imaging\CAST-Imaging-Control-Panel\application.yml

Set the datasource password to the CRYPTED2: value generated in Step 2:

spring:
  datasource:
    password: CRYPTED2:<encrypted_value>

After saving your changes, restart the CAST-Imaging-Control-Panel Windows service for them to take effect.

Step 6 - Update the stored installation properties

The installer records the properties used for each component installation in the following folder:

%PROGRAMDATA%\CAST\CAST\.install

The update process reads these files to reuse the settings of the previous installation, so a stale password here re-introduces the old credentials the next time you update CAST Imaging.

Open each _previous<COMPONENT>InstallProperties.txt file present in that folder and update the CSS_PASSWORD entry with the new plain text password. Where a CSS_ENCRYPTED_PASSWORD entry is also present, update it with the CRYPTED2: value from Step 2 - the two entries must always describe the same password. These entries are described in Microsoft Windows installation variables.

Where you have set the CAST_INSTALLATION_CONF Windows system environment variable, the files are stored in that custom folder instead.

Step 7 - Update standalone CAST Dashboards

If you are using standalone CAST Dashboards with CAST Imaging, you will need to update their configuration as well using the following file:

WAR
%CATALINA_HOME%\webapps\<dashboard>\WEB-INF\classes\application.properties

Installed JAR
Microsoft Windows: %PROGRAMDATA%\CAST\Dashboards\<folder>\application.properties
Linux: /root/ProgramData/CAST/Dashboards

Change the <password> entry to match the new password on the following line (there may be multiple PostgreSQL instances referenced):

restapi.datasource[0].password=<password>

Restart the CAST Dashboard to ensure the change is taken into account.

Step 8 - Verify the change

Restart all CAST Imaging services using the Microsoft Windows Services control panel and confirm that they start correctly. Then log in to CAST Imaging and run an analysis on one application to confirm that the new credentials are used everywhere.

Only once this verification succeeds should you consider updating CAST Imaging to a new release. When you do, the CSS_PASSWORD and CSS_ENCRYPTED_PASSWORD entries in the *.conf file you use for the update must also carry the new password - see In-place component update for Microsoft Windows.