How to Change a PostgreSQL User Password (Supabase)

The one-line answer

ALTER ROLE alice WITH PASSWORD 'new_password';

That is the SQL. The rest of this article is about doing it safely — without
leaving the cleartext in your shell history, without breaking Supabase
managed connections, and without forgetting the connection string in your
production app.

Illustration — not a live capture. Example diagram only; the steps are synthesized from this article and the official PostgreSQL ALTER ROLE / pg_terminate_backend documentation. The orange banner inside the image is the renderer-level "not a live capture" badge.

The PostgreSQL ALTER ROLE documentation is the
authoritative source for the syntax; this article adds the operational
context the docs do not.

Method 1: ALTER ROLE ... PASSWORD (the SQL way)

The canonical command from the PostgreSQL docs:

-- Set a password
ALTER ROLE alice WITH PASSWORD 'hunter2';

-- Remove a password (set to NULL — role can still connect if other auth methods allow)
ALTER ROLE alice WITH PASSWORD NULL;

ALTER USER is a legacy alias for ALTER ROLE and behaves identically. The
docs are explicit: ALTER USER "is accepted for backward compatibility, but
the canonical command is ALTER ROLE." Use ALTER ROLE in new code.

The ENCRYPTED keyword is optional and the default

-- These two are equivalent
ALTER ROLE alice WITH ENCRYPTED PASSWORD 'hunter2';
ALTER ROLE alice WITH PASSWORD 'hunter2';

The ENCRYPTED keyword is optional. The server encrypts the password with
the algorithm set by password_encryption — scram-sha-256 by default
since PostgreSQL 13. You almost never need to write ENCRYPTED explicitly.

Why ALTER ROLE leaks the password into logs

The docs warn about this directly:

Caution must be exercised when specifying an unencrypted password with
this command. The password will be transmitted to the server in cleartext,
and it might also be logged in the client's command history or the server
log.

If you type ALTER ROLE alice WITH PASSWORD 'hunter2'; in psql, the string
hunter2 lands in ~/.psql_history. If statement logging is on, it lands
in the server log too. For a personal dev database that is fine. For
production it is not. Use Method 2.

Method 2: \password in psql (the safe way)

psql ships a meta-command that prompts for the new password interactively
and never writes the cleartext to history or logs:

\password alice

It asks for the new password twice (to confirm) and sends an ALTER ROLE
to the server with the password already encrypted by the client. This is
the method the PostgreSQL docs recommend for any password change you do
interactively. Use it whenever you are in psql.

Method 3: Reset the postgres role in Supabase

Supabase manages the postgres role for you. You can run
ALTER ROLE postgres WITH PASSWORD '...' in the SQL editor, but the
recommended path is the Dashboard, because Supabase-managed services
(PgBouncer, PostgREST, GoTrue, the Studio itself) need their connection
strings rotated too. The Dashboard reset does that for you.

1. Open your Supabase project Dashboard.
2. Go to Database → Settings (also reachable via Project Settings → Database).
3. Locate the Database password section ("Used for direct Postgres connections").
4. Click Reset password.

What happens automatically:

• The postgres role password is rotated.
• Supabase-managed services (PostgREST, PgBouncer, Realtime, GoTrue, Studio) pick up the new password with no downtime.
• The connection string shown in the Dashboard updates.

What does not happen automatically, and this is where production
breaks:

• Hardcoded connection strings in your Next.js app do not update. If your DATABASE_URL env var has the old password baked in, the app starts failing with password authentication failed for user "postgres" on the next connection.
• CI secrets do not update. GitHub Actions secrets holding the old password keep sending it until you rotate them.
• .pgpass files on your machine do not update. psql will fall back to prompting or fail non-interactively.
• External services (cron jobs, n8n workflows, backup scripts) do not update. Every one of them breaks on the next connection attempt.

The password is not retrievable after reset — Supabase does not store it in
cleartext. Save it somewhere safe the first time.

password_encryption: scram-sha-256 vs md5

The PostgreSQL ALTER ROLE docs state that the password
is encrypted with the algorithm set by password_encryption. Since
PostgreSQL 13 the default is scram-sha-256:

SHOW password_encryption;
--  password_encryption
-- ---------------------
--  scram-sha-256

If the value is md5, your cluster is running a pre-13 config or someone
changed it. You can change it for the current session:

SET password_encryption = 'scram-sha-256';
ALTER ROLE alice WITH PASSWORD 'hunter2';
RESET password_encryption;

A password set while password_encryption = 'scram-sha-256' is stored as a
SCRAM verifier in pg_authid.rolpassword (starts with SCRAM-SHA-256$). A
password set while password_encryption = 'md5' is stored as an md5 hash
(starts with md5). A role with an md5 password cannot authenticate to a
server that requires scram-sha-256 in pg_hba.conf, and vice versa —
this is the most common cause of
password authentication failed
after a migration.

Why authentication breaks after a password change

After you run ALTER ROLE alice WITH PASSWORD 'newpass', every existing
connection stays open until it disconnects — the password is only
checked at connection time. The breakage starts on the next connection
attempt from anywhere that still has the old password:

1. Your Next.js app. The DATABASE_URL env var in Vercel/Cloudflare has the old password. The Next time the app opens a new connection (which happens on cold start or when the pool expands), it fails. Update the env var and redeploy. The Supabase connection pooling guide covers where this env var belongs in a Vercel deployment.
2. Your CI pipeline. GitHub Actions secrets still hold the old password. Migration jobs fail with password authentication failed for user "postgres". Rotate the secret.
3. .pgpass. ~/.pgpass has the old password on a line like db.x.supabase.co:5432:*:postgres:oldpass. Update it, or delete the line and let psql prompt.
4. External services. Any n8n workflow, cron job, or backup script with a hardcoded connection string must be updated.

The safe rotation sequence is:

1. Add a new role with the new password (or reset the existing one in Supabase).
2. Update every consumer (app, CI, .pgpass, external services) to the new password.
3. Redeploy / reconnect.
4. Only then, if you rotated by creating a new role, revoke the old role.

For the postgres role in Supabase you cannot create a parallel role —
Supabase owns postgres. The sequence is: reset in the Dashboard, update
every consumer, redeploy.

The .pgpass file

~/.pgpass is the psql way to avoid typing passwords. Each line is:

hostname:port:database:username:password

For Supabase:

db.abcdefghijklmnop.supabase.co:5432:*:postgres:your-new-password

After a password change, edit the line. If you used the Supabase Dashboard
reset and your password contains special characters (:, @, /, etc.),
you must percent-encode them in a connection string (postgres://...) but
not in .pgpass — .pgpass stores the literal password.

Production recommendations

• Prefer \password over ALTER ROLE ... PASSWORD in psql. The cleartext never hits history or logs. This is the single most important habit.
• Use scram-sha-256. It is the default since PostgreSQL 13; do not downgrade to md5. If SHOW password_encryption; returns md5, investigate why — it usually means the cluster was initialized on an old config.
• Rotate the postgres role from the Supabase Dashboard, not from SQL. The Dashboard reset rotates managed services for you; an ALTER ROLE postgres in the SQL editor does not, and you will spend the next hour debugging why PostgREST 401s.
• Update every consumer before you rotate, or accept a brief outage. The only safe zero-downtime rotation for a single-role setup is to create a new role, switch consumers to it, then revoke the old one.
• Percent-encode special characters in connection strings. A password like p@ss:w/ord must become p%40ss%3Aw%2Ford in a postgres:// URL. In .pgpass it stays literal.
• Audit pg_authid.rolpassword after rotation. Every active role's password should start with SCRAM-SHA-256$. Anything starting with md5 is a role that has not had its password reset since before the scram-sha-256 default — a finding worth noting in a security best-practices review.
• Do not store the password in the repo. Use env vars and a secret manager. If a password ends up in git, rotate it immediately — git filter-repo is not a substitute for rotation, because the hash is already public.

Common pitfalls

ALTER USER vs ALTER ROLE confusion

They are the same. If you find old scripts using ALTER USER, they work;
ALTER USER is a legacy alias. Update them to ALTER ROLE for clarity but
do not treat it as a bug.

Password change does not kick existing sessions

Existing connections stay open. If you are rotating because a password
leaked, you must also terminate existing sessions:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'alice'
  AND pid <> pg_backend_pid();

Otherwise a session opened before the rotation keeps running with the old
auth context until it disconnects. For a production rotation triggered by a
credential leak, run the pg_terminate_backend query immediately after the
ALTER ROLE so no attacker holds an open session with the old password.

md5-to-scram migration leaves roles unable to connect

If you change password_encryption to scram-sha-256 and a role has an
md5 password, that role cannot authenticate through a scram-sha-256
pg_hba.conf entry. Re-set the password with ALTER ROLE ... PASSWORD
after switching password_encryption, so the new verifier is stored as
SCRAM. The same trap in reverse is why
peer authentication failed
appears after a version upgrade.

Supabase connection pool keeps old password

Supabase's PgBouncer pool is managed and rotates automatically on a
Dashboard reset — you do not need to restart the pooler yourself. But
if you use a direct (non-pooled) connection string in your app, you bypass
PgBouncer — the app authenticates to Postgres directly, and a stale
connection string fails. Use the pooled connection string
(*.pooler.supabase.com:6543) for the app and the direct string only for
migrations. The
connection pooling guide
has the exact env vars.

Concurrent ALTER ROLE during a migration

Two migrations changing the same role's password at the same time race.
The second one wins; the first password is lost. If you run migrations in
parallel, serialize password changes. For the broader concurrency story —
serializable transactions and could not serialize access — see
could not serialize access: concurrent update in Supabase.

FAQ

How do I change the postgres password without downtime?

In a single-role Supabase setup you cannot — the reset is atomic and
managed connections rotate, but hardcoded connection strings break on the
next reconnect. The zero-downtime pattern is: create a new role with the
new password, switch every consumer to it, then revoke the old role. For
the Supabase-managed postgres role, use the Dashboard reset and update
every consumer immediately after.

What does \password do that ALTER ROLE does not?

It prompts interactively, sends the password to the server already
encrypted by the client, and does not write the cleartext to
.psql_history or the server log. Use it for any interactive password
change.

Can I see the current password?

No. PostgreSQL stores a one-way hash (SCRAM-SHA-256 verifier or md5) in
pg_authid.rolpassword. You can read the hash but you cannot reverse it
to the cleartext. If you lost the password, reset it — there is no recovery.

Should I use ALTER USER or ALTER ROLE?

ALTER ROLE. ALTER USER is a legacy alias that works identically, but
ALTER ROLE is the canonical command in the PostgreSQL documentation.

Related

• Postgres peer authentication failed fix — the error you get after a password change with a stale pg_hba.conf or md5/scram mismatch.
• PostgreSQL DESCRIBE TABLE: the psql \d equivalent — the sibling psql-meta-command reference for inspecting a table's schema.
• Supabase connection pooling on Vercel — where the password lives in a Vercel deployment and the pooled vs direct connection strings.
• Next.js + Supabase security best practices — where password rotation fits into a broader production security review.
• Could not serialize access: concurrent update in Supabase — the concurrency side of running migrations and role changes in parallel.
• How to create an enum column in Supabase — another common Supabase SQL-editor operation that differs from raw Postgres.

---

Originally published at https://www.iloveblogs.blog