SCRAM on PostgreSQL in Jitterbit private agents

Introduction

This page explains how to change your PostgreSQL installation's password encryption from MD5 to SCRAM-SHA-256. Follow the steps in these sections in sequence. (Run the SQL command as the PostgreSQL admin user.)

Fresh installations of Linux private agents use SCRAM-SHA-256 by default. This guide applies to upgraded Linux agents, which retain MD5 encryption, and Windows private agents.

Prerequisites

• The PostgreSQL admin password.

• For Windows installations, access to a Microsoft Windows administrator account. For Linux installations, an account with root privileges.

Part 1: Reconfigure PostgreSQL

1. In a psql session, check the current encryption method:

   SHOW password_encryption;
   

   Expected output

    password_encryption
   ---------------------
    md5
   (1 row)
   

2. Edit the PostgreSQL instance's postgresql.conf and pg_hba.conf files. These are usually located in the /opt/jitterbit/DataInterchange/pgsql/data directory on Linux and Docker, and the C:\Program Files\PostgreSQL\X\data folder on Windows (where X is the major version number).

   • In postgresql.conf, find the password_encryption parameter and change its value from md5 to scram-sha-256.

   • In pg_hba.conf, change the values in the METHOD columns from md5 to scram-sha-md5 for the desired connections.

3. In a psql session, reload the configuration:

   SELECT pg_reload_conf();
   

4. Recheck the current encryption method:

   SHOW password_encryption;
   

   Expected output

    password_encryption
   ---------------------
    scram-sha-256
   (1 row)
   

Part 2: Update user passwords

1. In a psql session, list accounts using MD5 password encryption:

   SELECT rolname, rolpassword ~ '^SCRAM-SHA-256$' AS has_scram FROM pg_authid WHERE rolcanlogin;
   

   Tip

   In a standard private agent system, there should be only two users listed, postgres and jitterbit.

   Expected output

     rolname  | has_scram
   -----------+-----------
    postgres  | f
    jitterbit | f
   (2 rows)
   

2. For each account with has_scram showing f, update their passwords using the following command:

   ALTER ROLE <username> PASSWORD '<password>';
   

   • <username>: The user's PostgreSQL username.

   • <password>: A new password for the user.

   Tip

   You can reuse each account's existing password.

Part 3: Reconfigure PgBouncer

To reconfigure PgBouncer, follow these steps:

1. Get the SCRAM verifier hash string and record it:

   SELECT rolname, rolpassword FROM pg_authid WHERE rolname = 'jitterbit';
   

   Make a note of the rolpassword value. (It should start with SCRAM-SHA-256.)

2. Edit the PgBouncer's users.txt file, usually located in /opt/jitterbit/pgbouncer/share/doc/pgbouncer/ on Linux, and C:\Program Files\Jitterbit Agent\pgbouncer on Windows.

   Replace the hash value with the value of copied from the previous step.

   Example users.txt with original hash

   "jitterbit" "md5b51676b2b6ff8113190a8e4df1b3a144"
   

   Example users.txt with updated hash

   "jitterbit" "SCRAM-SHA-256$4096:DaRaCof9KFmKvfxW/OIAsg==$gAmuj8MsIamBk/MsLmCrg2FO9FXeFBx+vmm0cUxXkXw=:mlH5eE3+AEdSniA0NPocfSt1BbcLAryftXhYWbMsaS0="
   

Part 4: Restart services

Restart the private agent:

jitterbit restart

"C:\Program Files\Jitterbit Agent\StopServices.bat"
"C:\Program Files\Jitterbit Agent\StartServices.bat"