Fixing YugabyteDB Login Failures After ALTER ROLE postgres NOLOGIN

In PostgreSQL, disabling login for the default postgres role can be a common hardening step.

In YugabyteDB, that same idea can go very sideways.

Running:

				
					ALTER USER postgres WITH NOLOGIN;

will break YSQL login for all users, not just postgres.

That includes the yugabyte superuser and any other login roles you created!

And yes, if you just ran this in production, this is probably the moment where your stomach drops, your coffee gets cold, and you start wondering whether your badge still works.

But there is good news: there is a way back.

The Symptom

The postgres role may look like just another superuser role, but in YugabyteDB, disabling login on it can interfere with YSQL initialization.

After running:

				
					ALTER USER postgres WITH NOLOGIN;

new YSQL connections may start failing with this error:

				
					FATAL:  Relcache init connection request to database yugabyte timed out

This can affect all users, not just postgres.

For example:

				
					ysqlsh -h 127.0.0.1 -U yugabyte

May fail with:

				
					ysqlsh: error: connection to server at "127.0.0.1", port 5433 failed:
FATAL:  Relcache init connection request to database yugabyte timed out

Don’t Do This:

Do not use ALTER USER postgres WITH NOLOGIN as a hardening technique in YugabyteDB. It can prevent YSQL from initializing correctly and effectively lock out every database user.

Why This Is So Scary

In vanilla PostgreSQL, if you accidentally lock out the postgres role, there are often low-level recovery options available, such as starting PostgreSQL in single-user mode.

That same recovery path does not appear to work cleanly in YugabyteDB.

YugabyteDB is not a single PostgreSQL server. YSQL runs as a distributed SQL layer on top of DocDB. Some PostgreSQL-style recovery assumptions do not directly apply.

That is what makes this issue especially dangerous.

You are not just locking a role.

You may be preventing YSQL from completing relcache initialization.

And when that happens, every new login attempt can fail.

Reproducing the Issue

Here is a simple example from a local yugabyted environment.

First, create a shortcut for connecting as yugabyte:

				
					alias y='ysqlsh -h 127.0.0.1 -U yugabyte'

Verify that the yugabyte user can connect:

				
					y -c "SELECT user;"

Result:

				
					.  user
----------
 yugabyte
(1 row)

Verify that another login user can connect:

				
					y -U jim -c "SELECT user;"

Result:

				
					.user
------
 jim
(1 row)

Now disable login for the postgres role:

				
					y -c "ALTER USER postgres WITH NOLOGIN;"

Result:

				
					ALTER ROLE

Now try to connect again:

				
					y -c "SELECT user;"

Result:

				
					ysqlsh: error: connection to server at "127.0.0.1", port 5433 failed:
FATAL:  Relcache init connection request to database yugabyte timed out

Uh oh.

At this point, it may feel like the cluster has been bricked.

Job Security Moment:

If this was production, this is the part where you quietly wonder whether ALTER USER postgres WITH NOLOGIN was actually a SQL command or a career-limiting event. Fortunately, there is a recovery option to try.

The Fix

To reverse the change, restart the cluster with this tserver flag:

				
					--ysql_enable_relcache_init_optimization=false

This disables the relcache init optimization temporarily, which may allow YSQL connections to succeed long enough to re-enable login for the postgres role.

For yugabyted, stop the cluster:

				
					yugabyted stop

Then start it with the temporary recovery flag:

				
					yugabyted start --tserver_flags="ysql_enable_relcache_init_optimization=false" > start.log

Now try to connect again:

				
					y -c "SELECT user;"

Result:

				
					.  user
----------
 yugabyte
(1 row)

If the connection works, immediately re-enable login for postgres:

				
					y -c "ALTER USER postgres WITH LOGIN;"

Result:

				
					ALTER ROLE

Now stop the cluster again:

				
					yugabyted stop

Start it normally, with the relcache init optimization enabled again:

				
					yugabyted start --tserver_flags="ysql_enable_relcache_init_optimization=true" > start.log

Finally, verify that logins work:

				
					y -c "SELECT user;"

Result:

				
					.  user
----------
 yugabyte
(1 row)

And verify another login user:

				
					y -U jim -c "SELECT user;"

Result:

				
					.user
------
 jim
(1 row)

Recovery Tip:

If ALTER USER postgres WITH NOLOGIN breaks YSQL login, temporarily restart the cluster with ysql_enable_relcache_init_optimization=false. Then connect and run ALTER USER postgres WITH LOGIN.

What About `ACCOUNT LOCK`?

You may wonder whether this is safer:

				
					ALTER USER postgres ACCOUNT LOCK;

At least today, that is not the answer either.

It returns:

				
					ERROR:  PROFILE not supported yet

So, do not use NOLOGIN, and do not rely on ACCOUNT LOCK for this use case.

Safer Alternative: Use HBA Rules

If your goal is to prevent external users from logging in as postgres, do not change the role to NOLOGIN.

Use HBA-based restrictions instead.

For example, with yugabyted:

				
					yugabyted start --tserver_flags="ysql_hba_conf_csv={host all postgres all reject,host all all all trust}"

This rejects normal client connections as postgres, while allowing YugabyteDB’s internal tserver connection path to continue working.

Key Insight: Restrict access to the postgres role at the connection layer, not by changing the role to NOLOGIN.

Final Takeaway

Do not run this in YugabyteDB:

				
					ALTER USER postgres WITH NOLOGIN;

It can break YSQL login for every user and make the cluster appear bricked.

If you already ran it, try restarting the cluster with:

				
					--ysql_enable_relcache_init_optimization=false

Then immediately run:

				
					ALTER USER postgres WITH LOGIN;

After that, restart normally with:

				
					--ysql_enable_relcache_init_optimization=true

Bottom Line:

In YugabyteDB, ALTER USER postgres WITH NOLOGIN is not a harmless hardening step. It can lock out all YSQL users. Use HBA rules instead, and keep the postgres role login-capable.

Have Fun!