Postgres not initializing due to 'password authentication failed for user "postgres_admin"' error

Applicability

Description

Sometimes after upgrade to Splunk 10.2.0 'password authentication failed for user "postgres_admin"' error can be seen in  sup-pkg-postgres-stdout.log. This also occurs if the GUID of the instance is being changed.

This prevents postgres sidecar from starting and may affect Splunk functionality.

Investigation

In Splunk Enterprise version 10.2.0 new functionality was introduced to generate postgres password, based on the GUID of the Splunk instance. 

After upgrade, or upon changing the GUID of the splunk instance. Old password becomes invalid and the error can be seen in sup-pkg-postgres-stdout.log:

2026-02-03 12:58:10.967 CET [1104263] FATAL: password authentication failed for user "postgres_admin"
2026-02-03 12:58:10.967 CET [1104263] DETAIL: Connection matched file "/opt/splunk/var/packages/data/postgres/db/pg_hba.conf" line 115: "host all all 127.0.0.1/32 scram-sha-256"
2026-02-03 12:59:30.969 CET [1103451] LOG: received smart shutdown request
2026-02-03 12:59:37.848 CET [1105130] FATAL: the database system is shutting down

The Issue can be verified by performing the following steps:

1) Retrieve password for postgres_admin user:

curl -k -u <username>:<login password> https://localhost:8089/servicesNS/nobody/system/storage/passwords/postgres:postgres_admin?output_mode=json

2) Verify password directly in the Postgres files (should be the same as the secret store):  

cat /opt/splunk/var/packages/data/postgres/.pgpass

3) Attempt manual connection via psql using the retrieved password:

LD_LIBRARY_PATH=/opt/splunk/lib /opt/splunk/bin/psql -h localhost -d postgres -U postgres_admin -p 5432

If interactive PostgreSQL shell session is established with administrative privileges, some common commands:

\l: List all databases.
\dt: List tables in the current database.
\du: List roles and users.
\conninfo: Display connection details (host, port, user).
SELECT version();: Verify the specific database version.

Type \q and press Enter, or use the Ctrl+D keyboard shortcut to exit.

If connection fails, a password reset is required

Resolution

There are 2 options to workaround the issue:

On a standalone node:

1) Create backup of pg_hba.conf

 cp $SPLUNK_HOME/var/packages/data/postgres/db/pg_hba.conf $SPLUNK_HOME/var/packages/data/postgres/db/pg_hba.conf.backup

2) Add host all all 127.0.0.1/32 trust line, immediately before the line with # PostgreSQL Client Authentication Configuration File like next:

# --- PgBouncer auth (standalone) ---
hostssl postgres pgbouncer_auth 127.0.0.1/32 trust
hostssl postgres +pgbouncer_services_group 127.0.0.1/32 scram-sha-256
host all all 127.0.0.1/32 trust
# PostgreSQL Client Authentication Configuration File
# ===================================================

3) Restart Splunk instance 

4) Manually connect via psql by running the following command(Replace $SPLUNK_HOME with Splunk installation directory). No password should be required at this point: 

LD_LIBRARY_PATH=$SPLUNK_HOME/lib $SPLUNK_HOME/bin/psql -h localhost -d postgres -U postgres_admin -p 5432

5) Insert command:

ALTER USER postgres_admin WITH PASSWORD '<password retrieved from .pgpass or secret store>';

6) Exit psql by pressing Ctrl+D key combination

7) Delete the altered pg_hba.conf file and reset pg_hba.conf.backup to the original name

8) Restart Splunk instance

9) Validate the pgpass again using this command:

LD_LIBRARY_PATH=$SPLUNK_HOME/lib $SPLUNK_HOME/bin/psql -h localhost -d postgres -U postgres_admin -p 5432

If the pgpass works, then the interactive PostgreSQL shell session should be established

In Search Head Cluster:

Similar steps to the Standalone steps. Please note:

In an SHC environment, PostgreSQL is managed by Patroni for high availability. Follow these modified steps to resolve authentication errors:

1. Backup and Modify patroni.yml Patroni manages pg_hba.conf dynamically. Changes must be made in patroni.yml (typically located in $SPLUNK_HOME/var/run/splunk/postgres/ or $SPLUNK_HOME/var/lib/splunk/postgres/ depending on the environment).

• Create a backup: cp $SPLUNK_HOME/var/run/splunk/postgres/patroni.yml $SPLUNK_HOME/var/run/splunk/postgres/patroni.yml.bak
• Locate the pg_hba section under postgresql configuration and add the trust line:

  postgresql:
    pg_hba:
      - host all all 127.0.0.1/32 trust
      - hostssl postgres pgbouncer_auth 127.0.0.1/32 trust

2. Restart Splunk Restart the Splunk instance to apply the Patroni configuration changes.

3. Establish Connection via Port 5433 SHC instances use port 5433 for the database service. Connect without a password: LD_LIBRARY_PATH=$SPLUNK_HOME/lib $SPLUNK_HOME/bin/psql -h localhost -d postgres -U postgres_admin -p 5433

4. Update Passwords for Admin and Replicator Both the administrative and replication accounts require password synchronization. Execute the following in the interactive shell:

• ALTER USER postgres_admin WITH PASSWORD '<password_from_pgpass_or_secret_store>';
• ALTER USER replicator WITH PASSWORD '<password_from_pgpass_or_secret_store>';

5. Revert Configuration and Validate

• Exit the shell using \q or Ctrl+D.
• Restore the original patroni.yml from the backup.
• Restart the Splunk instance.
• Verify the fix by attempting a connection with the updated password: LD_LIBRARY_PATH=$SPLUNK_HOME/lib $SPLUNK_HOME/bin/psql -h localhost -d postgres -U postgres_admin -p 5433