How to reset YSQL superuser password?

Problem

The password of the YSQL superuser (yugabyte) is lost or unknown. Every login attempt is rejected.

Error / alert observed:

ysqlsh: error: connection to server at "172.19.0.2", port 5433 failed: FATAL:  password authentication failed for user "yugabyte"

Or, when no password is supplied at all:

ysqlsh: error: connection to server at "172.19.0.2", port 5433 failed: fe_sendauth: no password supplied

Root Cause

YSQL password authentication is on, and nobody holds a working password for a superuser role. ALTER ROLE ... WITH PASSWORD needs a superuser session, so the password cannot be changed through the normal path. You have to reach a session that skips password authentication first.

YugabyteDB decides the authentication method from ysql_hba.conf. That file is generated at yb-tserver startup.

One line is always present, whatever these two flags say. This line is what Option A depends on:

local all yugabyte trust

Diagnosis

Run the following before applying any fix to confirm this is the issue and to pick the right option.

# On any node running yb-tserver. Adjust the path for your deployment:
#   YBA-managed / on-prem : /home/yugabyte/tserver/bin/ysqlsh, HBA under /home/yugabyte/tserver/pg_data
#   yugabyted / Docker    : /home/yugabyte/bin/ysqlsh,         HBA under /home/yugabyte/yb_data/data/pg_data
grep -v '^#' <pg_data/ysql_hba.conf | grep -v '^$'

Output when the issue is present:

host all all all md5
local all yugabyte trust

Read the output like this:

If you already have a working YSQL session as any superuser, skip all four options and run only the ALTER ROLE statement in step 3 of Option A.

Resolution

Four options. Try them in this order. Option A and Option B need no restart, so they are the first choice in production. Option C and Option D change a gflag, which means a restart.

Option A: connect over the Unix domain socket (no restart)

The auto-generated local all yugabyte trust rule means a socket connection as yugabyte never asks for a password. You need shell access on a node running yb-tserver.

1. Find the socket directory.

   ls -d /tmp/.yb.*:5433

   Expected output:

   /tmp/.yb.yb-node1:5433

2. Connect through the socket. Pass the directory to -h, not a hostname or an IP.

   /home/yugabyte/bin/ysqlsh -h /tmp/.yb.yb-node1:5433 -U yugabyte \
     -c "select current_user, inet_server_addr() is null as via_socket"

   Expected output:

    current_user | via_socket
   --------------+------------
    yugabyte     | t
   (1 row)

3. Reset the password.

   /home/yugabyte/bin/ysqlsh -h /tmp/.yb.yb-node1:5433 -U yugabyte \
     -c "ALTER ROLE yugabyte WITH PASSWORD 'NewPass123'"

   Expected output:

   ALTER ROLE

   Quote the password in single quotes. Escape shell metacharacters such as ! if you pass the statement with -c.

Limits of Option A:

• The trust rule covers the yugabyte user only. Any other role over the socket is rejected:

  FATAL:  no pg_hba.conf entry for host "[local]", user "appuser", database "yugabyte", no encryption

  This is not a problem in practice. Once you are in as yugabyte, you are superuser and can reset any other role.

• You need OS-level access to a node. If shell access is not available, use Option C or Option D.

Option B: edit ysql_hba.conf by hand, then reload with SIGHUP (no restart)

ysql_hba.conf is generated at yb-tserver startup, but postgres re-reads it on SIGHUP. So you can add a trust rule, reload, reset the password, and put the file back. Nothing restarts. You need shell access on a node running yb-tserver.

WARNING: Take a copy of the file before you edit it. The file also holds the internal rule local all yugabyte trust, which yb-tserver uses. Do not delete existing lines. Only add one line.

1. Find the postgres PID and the ysql_hba.conf path. Both are in the process command line, as -D and hba_file=.

   ps -eo pid,args | grep '[p]ostgres -D'

   Expected output (trimmed):

   349 /home/yugabyte/postgres/bin/postgres -D /home/yugabyte/yb_data/data/pg_data -p 5433 ... -c hba_file=/home/yugabyte/yb_data/data/pg_data/ysql_hba.conf ...

2. Back up the file, then add one trust rule above the password rule. samehost matches only connections whose source address is one of the server's own addresses, so the exemption does not reach the network.

   H=/home/yugabyte/yb_data/data/pg_data/ysql_hba.conf
   cp $H ${H}.bak
   sed -i '/^host all all all md5/i host all yugabyte samehost trust' $H
   cat $H

   Expected output:

   # This is an autogenerated file, do not edit manually!
   # Internal configuration:
   # local all postgres yb-tserver-key
   host all yugabyte samehost trust
   host all all all md5
   local all yugabyte trust

3. Reload the configuration. SIGHUP (kill -1) makes postgres re-read ysql_hba.conf. It does not kill the process, and it does not drop existing sessions.

   kill -HUP 349

   Confirm postgres is still up and the file was not regenerated:

   ps -p 349 -o pid,etime,comm --no-headers

4. Connect from the node itself and reset the password. Use the node's own IP so the samehost rule matches. Do not use localhost, because samehost and localhost are not the same match.

   IP=$(hostname -i | awk '{print $1}')
   /home/yugabyte/bin/ysqlsh -h $IP -U yugabyte -d yugabyte -w \
     -c "ALTER ROLE yugabyte WITH PASSWORD 'NewPass123'"

   Expected output:

   ALTER ROLE

5. Put the file back and reload again. Do not leave the trust rule in place.

   mv ${H}.bak $H
   kill -HUP 349

   Confirm the rule is gone and a password is required again:

   /home/yugabyte/bin/ysqlsh -h $IP -U yugabyte -d yugabyte -w -c "select 1"

   Expected output:

   ysqlsh: error: connection to server at "<node-ip", port 5433 failed: fe_sendauth: no password supplied

Notes on Option B:

• The edit is per node. You only need it on the one node you connect to.
• The edit does not survive a yb-tserver restart. On restart the file is regenerated from the gflags and your added line is gone. This is a safety net, not a problem.

Option C: turn YSQL authentication off, reset, turn it back on

WARNING: Between step 1 and step 4 every user can connect to YSQL without a password. Do this in a maintenance window, or restrict access at the network layer first. This is why Option A and Option B are preferred.

Use this when ysql_hba.conf has only the auto-generated host all all all md5 line.

1. Set ysql_enable_auth to false on TServer. Reason: with authentication off, the generated rule becomes host all all all trust, so a normal TCP connection needs no password.

   In YBA: Universe → Actions → Edit Flags → TServer → set ysql_enable_auth = false → Rolling Upgrade.

   Verify the generated file after the restart:

   grep -v '^#' <pg_data/ysql_hba.conf | grep -v '^$'

   Expected output:

   host all all all trust
   local all yugabyte trust

2. Connect over TCP without a password.

   /home/yugabyte/bin/ysqlsh -h <node-ip -U yugabyte -d yugabyte -c "select 1"

3. Reset the password.

   /home/yugabyte/bin/ysqlsh -h <node-ip -U yugabyte -d yugabyte \
     -c "ALTER ROLE yugabyte WITH PASSWORD 'FinalPass456'"

   Expected output:

   ALTER ROLE

4. Set ysql_enable_auth back to true on TServer and restart again. Reason: this restores password authentication for every user. Do not leave the cluster on trust.

Caveat, verified: if ysql_hba_conf_csv is set, Option C does nothing. The custom rules replace the auto-generated host rules, so ysql_enable_auth=false never produces a trust line. With ysql_enable_auth=false and ysql_hba_conf_csv=host all all 0.0.0.0/0 md5, the generated file was:

host all all 0.0.0.0/0 md5
local all yugabyte trust

Connections still failed with fe_sendauth: no password supplied. Use Option D in that case.

Option D: add a trust rule for the yugabyte user only

WARNING: While this rule is in place, anyone who can reach port 5433 can connect as the yugabyte superuser without a password. Narrow the CIDR to the host you will connect from, and remove the rule as soon as the reset is done.

Use this when ysql_hba_conf_csv is already set, or when you want a narrower exemption than Option C.

1. Record the current value of ysql_hba_conf_csv before you change anything. You need it to roll back exactly.

2. Prepend a trust rule for yugabyte and keep the existing rules after it. Order matters, because the first matching rule wins.

   --ysql_hba_conf_csv='host all yugabyte 0.0.0.0/0 trust,host all all 0.0.0.0/0 md5'

   Reason: the first rule lets the yugabyte user in without a password. The second rule keeps every other user on password authentication. Value rationale: 0.0.0.0/0 is shown for readability. Use the narrowest CIDR that covers your jump host, for example 10.0.1.25/32.

   The docs variant, which also covers IPv6, is:

   --ysql_hba_conf_csv='host all yugabyte 0.0.0.0/0 trust,host all all 0.0.0.0/0 md5,host all yugabyte ::0/0 trust,host all all ::0/0 md5'

3. After the restart, confirm the rule order:

   grep -v '^#' <pg_data/ysql_hba.conf | grep -v '^$'

   Expected output:

   host all yugabyte 0.0.0.0/0 trust
   host all all 0.0.0.0/0 md5
   local all yugabyte trust

4. Reset the password, then restore ysql_hba_conf_csv to the value recorded in step 1 and restart again.

Notes and caveats

• Restart scope. ysql_enable_auth and ysql_hba_conf_csv both need a restart. Neither is a runtime flag. In YBA, use a rolling upgrade so the universe stays available. A hand edit plus SIGHUP (Option B) is the way to avoid the restart.
• Never delete lines from ysql_hba.conf. The generated file carries internal rules that yb-tserver depends on. Add a line, never replace the file.

References

• YugabyteDB Docs: Resetting user password
• YugabyteDB Docs: Host-based authentication
• YugabyteDB Docs: TLS and authentication

• YugabyteDB Docs: Reset password for a YCQL superuser