How to reset forgotten PostgreSQL password in Qlik Sense Enterprise on Windows

Bjorn_Wedbratt · Oct 3, 2023 6:45:28 AM

A password is provided during the installation of Qlik Sense Enterprise on Windows. Should you forget this password, there's no way to retrieve it; however, it is possible to reset the password by following the steps below.

If after the procedure is completed errors are logged of a wrong password, see Qliksenserepository password error in the event logs after changing the database service user passwo... for further information.

Stop all the Qlik Sense services

Verify that the Qlik Sense Repository Database service (or postgresql-x64-xx if the database is not a bundled install and/or was installed using QPI) is stopped.
The next step will require modifying the pg_hba.conf and postgresql.conf files. We will swap the currently active authentication method to trust, allowing us to connect without a password.

Before modifying the file, take a backup of both files.

File locations:

Default (bundled):

C:\ProgramData\Qlik\Sense\Repository\PostgreSQL\12.5

Alternative (unbundled, custom install):

C:\Program Files\PostgreSQL\12.5\data

*12.5 refers to the PostgreSQL version. Adjust accordingly depending on what version is installed.
Open a text editor (such as Notepad or Notepad++) As Administrator and browse to the .conf file location.

If you cannot see the .conf files, switch the file view to All Files (*) or All Types, depending on your editor.
Open pg_hba.conf

Change the METHOD from md5 (alternative: scram-sha-256) to trust

Only the first two lines for IPv4 and IPv6 entries must be changed. trust must be lowercase.

Original:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Updated:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

Save the file, close it
Start the Qlik Sense Repository Database (or postgresql-x64-xx).
Launch the Windows Command Line As Administrator.
In the command line, navigate to the PostgreSQL bin folder
```
cd "C:\Program files\Qlik\Sense\Repository\PostgreSQL\12.5\bin"
```
*12.5 refers to the PostgreSQL version. Adjust accordingly depending on what version is installed.
Run psql to connect to the database:
```
psql -h 127.0.0.1 -p 4432 -U postgres
```
The response will provide the postgres=# prompt, which we will use to change the passwords.
Proceed from the postgres=#

The Qlik Sense PostgreSQL database has two users:

postgre: the super user
qliksenserepository: the service user; if the database was installed manually or set up with QPI, this may be the only user set up

The passwords must be the same for both.
1. Change the postgres user account (you must include the single quotes and the semicolon):
```
alter user postgres with encrypted password 'EnterYourNewPasswordHere';
```
  Response:
2. Change the qliksenserepository user account (you must include the single quotes and the semicolon):
```
alter user qliksenserepository with encrypted password 'EnterYourNewPasswordHere';
```
  Response:
Close the command prompt.
Adjust the connection string using QlikSenseUtil
1. Open QlikSenseUtil (C:\Program Files\Qlik\Sense\Repository\Util\QlikSenseUtil)
2. Open the Connection String Editor tab
3. Click Read
4. Adjust the password
5. Click Save Value in config file encrypted
Change the Connection Strings for Micro-Services as per Changing the Connection String for Micro-Services.
Stop the Qlik Sense Repository Database (or postgresql-x64-xx).
Revert the changes done to the pg_hba.conf file, changing back METHOD from trust to md5 (alternative: scram-sha-256).
Start all services (Manual start and stop order of Qlik Sense services).

This concludes the required steps.

To verify if the password was correctly changed, you can either run psql again, connect with the PGAdmin, or review the Qlik Sense Repository log for success messages on boot.

If after the procedure is completed errors are logged of a wrong password, see Qliksenserepository password error in the event logs after changing the database service user passwo... for further information.