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;
you may introduce immediate or future failures in YSQL.
Depending on your YugabyteDB version, the impact can range from broken internal operations to locking out every YSQL user, including yugabyte and custom login roles.
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 it has important internal uses.
Unlike a standalone PostgreSQL server, YugabyteDB runs YSQL as a distributed SQL layer on top of DocDB. Some internal YSQL operations rely on the default postgres role being able to log in.
That makes this command dangerous:
ALTER USER postgres WITH NOLOGIN;
In some versions, it may break internal database operations.
In newer versions, it may prevent YSQL from initializing correctly and block all new database logins.
Don’t Do This:
Do not use ALTER USER postgres WITH NOLOGIN as a hardening technique in YugabyteDB. The impact is version-dependent, but the command is unsafe across versions because YugabyteDB may rely on internal postgres connections.
Version-Specific Behavior
The risk is not exactly the same across YugabyteDB releases.
Here is the important distinction.
Behavior in YugabyteDB 2024.2.x
In YugabyteDB 2024.2.x, setting the postgres role to NOLOGIN may not immediately lock out every user.
For example, users such as yugabyte or custom login roles may still be able to connect.
That does not mean the change is safe.
Certain internal operations may still rely on postgres being able to log in. One example is CREATE INDEX CONCURRENTLY, where the index backfill workflow may require internal postgres connections to query system state such as pg_stat_activity.
If postgres cannot log in, that internal workflow can fail.
So in 2024.2.x, the danger is more subtle:
- The cluster may look healthy until an internal operation needs postgres.
That makes the problem especially risky, because the change may sit quietly until a later operation fails in a surprising way.
Hidden Risk:
On YugabyteDB 2024.2.x, ALTER USER postgres WITH NOLOGIN may not immediately lock out all users, but it can still break internal YSQL operations later. That makes it dangerous even if basic login tests appear to succeed.
Behavior in YugabyteDB 2025.2+
Starting in YugabyteDB 2025.2, a relation cache initialization optimization can make this issue much more visible and much more severe.
When this flag is enabled:
--ysql_enable_relcache_init_optimization=true
setting postgres to NOLOGIN can cause YSQL login to fail for all users.
That includes:
- ● postgres
- ● yugabyte
- ● custom login roles
The failure may look like this:
FATAL: Relcache init connection request to database yugabyte timed out
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
This is the “oh no, did I just brick the cluster?” version of the problem.
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.
Reproducing the 2025.2+ Failure
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.
The Fix for 2025.2+
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:
On YugabyteDB 2025.2 and later, 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 the goal is to prevent external or unauthorized users from logging in as postgres, restrict access at the connection layer instead.
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 client connections as postgres, while still allowing YugabyteDB’s internal tserver behavior to continue working.
Why?
Because YugabyteDB prepends an internal hardcoded rule similar to:
local all postgres yb-tserver-key
That internal rule keeps tserver connections alive, while your HBA rule can still block external client access as postgres.
Key Insight:
If you want to restrict access to the postgres role in YugabyteDB, use HBA rules. Do not change the role to NOLOGIN.
Safer Pattern
Use this general pattern:
- ● Reject external postgres connections with HBA rules.
- ● Leave the postgres role itself enabled for login.
Example:
--tserver_flags="ysql_hba_conf_csv={host all postgres all reject,host all all all trust}"
For production, replace the broad trust rule with your organization’s actual authentication rules.
The important part is this:
host all postgres all reject
That blocks direct client logins as postgres without modifying the role itself.
Final Takeaway
Do not run this in YugabyteDB:
ALTER USER postgres WITH NOLOGIN;
On YugabyteDB 2024.2.x, it may not lock out every user immediately, but it can break internal YSQL operations.
On YugabyteDB 2025.2 and later, 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
Going forward, use HBA rules to restrict access to the postgres role safely. Keep the postgres role login-capable.
Bottom Line:
In YugabyteDB, ALTER USER postgres WITH NOLOGIN is not a harmless hardening step. In 2024.2.x, it can break internal YSQL operations. In 2025.2 and later, it can lock out all YSQL users. Use HBA rules instead, and keep the postgres role login-capable.
Have Fun!
