Reset the PostgreSQL postgres user password

Set or reset the password of the postgres superuser, and regain access when normal logins fail by trusting local connections for a moment and then reverting.

  • Beginner
  • Linux VPS
  • 5 min read

By RunRacks Team · Published

On a fresh PostgreSQL install, the postgres superuser often has no password at all. You reach it through the matching Linux account instead. You only need a password when a tool connects over TCP, or when you have forgotten one you set earlier. This guide covers the normal reset and the recovery route for when you cannot log in at all.

Before you start

  • A Linux VPS running PostgreSQL.
  • SSH access as root or a sudo user. Changing the password needs root on the server, not database access.
  • A new password you have chosen yourself. Use a long, unique one and store it in a password manager. This guide never shows a real value.

The normal way: sudo and \password

On Ubuntu, Debian, AlmaLinux and Rocky Linux, the default pg_hba.conf lets the postgres Linux user log in to the postgres role through the local socket with peer authentication. No password is needed:

Terminal
sudo -u postgres psql

At the postgres=# prompt, run:

Text
\password postgres

psql asks for the new password twice. It encrypts the password on your side before sending it, so the plain text is not written to the server log or to psql's history file. Type \q to leave.

You can do the same with SQL, but avoid it if you can, because the password ends up in ~/.psql_history:

SQL
ALTER ROLE postgres WITH PASSWORD 'REPLACE_WITH_YOUR_OWN_PASSWORD';

The value above is a placeholder only. That is the whole reset for most servers. If sudo -u postgres psql worked, skip to Check it works.

When you cannot log in at all

If peer login has been removed or changed and you no longer know any superuser password, use a short, controlled window of trust authentication for local socket connections.

1. Find pg_hba.conf

Typical locations:

  • Ubuntu and Debian: /etc/postgresql/<version>/main/pg_hba.conf
  • AlmaLinux and Rocky Linux, PGDG packages: /var/lib/pgsql/<version>/data/pg_hba.conf
  • AlmaLinux and Rocky Linux, distribution packages: /var/lib/pgsql/data/pg_hba.conf

If you are unsure, search for it:

Terminal
sudo find / -name pg_hba.conf -not -path '*/proc/*' 2>/dev/null

2. Back up the file and add a temporary rule

Terminal
sudo cp /etc/postgresql/17/main/pg_hba.conf /root/pg_hba.conf.bak
sudo nano /etc/postgresql/17/main/pg_hba.conf

Adjust the path to your version. PostgreSQL reads the file top to bottom and uses the first matching line, so add this line above all other rules:

Text
local   all   postgres   trust

3. Reload PostgreSQL

A reload is enough. It rereads the file without dropping connections:

Terminal
# Ubuntu and Debian
sudo systemctl reload postgresql

# AlmaLinux and Rocky Linux (PGDG packages; use your version number)
sudo systemctl reload postgresql-17

With the distribution package on AlmaLinux or Rocky Linux, the service is just postgresql.

4. Set the new password

Terminal
sudo -u postgres psql -U postgres

Then, in psql:

Text
\password postgres
\q

5. Revert and reload

Put the original file back and reload again:

Terminal
sudo cp /root/pg_hba.conf.bak /etc/postgresql/17/main/pg_hba.conf
sudo systemctl reload postgresql

Delete the backup copy afterwards if you no longer need it. Confirm the trust line is gone:

Terminal
sudo grep -n trust /etc/postgresql/17/main/pg_hba.conf

No output, or only lines that were there before you started, is what you want.

Check it works

Test a password login over TCP, which does not use peer authentication:

Terminal
psql -h 127.0.0.1 -U postgres -c "SELECT current_user;"

Enter the new password. The query should return postgres. If you use a desktop client through an SSH tunnel, update the saved password there too.

Troubleshooting

  • "Peer authentication failed for user postgres": you ran psql -U postgres as a different Linux user. Use sudo -u postgres psql, or add -h 127.0.0.1 to log in with a password.
  • The trust rule has no effect: it is below a line that matches first, or you did not reload. Move it to the top and reload.
  • "password authentication failed" right after the reset: the host lines may use scram-sha-256 while the password was stored in an older format. Run SHOW password_encryption; in psql; it should say scram-sha-256. Then set the password again with \password.
  • The service will not reload: a typo in pg_hba.conf makes PostgreSQL reject the new file. Restore your backup copy and reload, then check the log with sudo journalctl -u 'postgresql*' --since "10 min ago". On Ubuntu and Debian, also look in /var/log/postgresql/.

Next steps

Was this guide helpful?

Still stuck?

Ask RunRacks AI for a quick answer, or open a ticket and our team will take a look at your server.

Databases

Reset a Forgotten MySQL Root Password on Linux

Recover access to MySQL or MariaDB on a Linux VPS by starting the server with skip-grant-tables, setting a new root password and restarting normally.

  • Intermediate
  • Linux VPS
  • 5 min read

Databases

MySQL Point-in-Time Recovery Using Binary Logs

Recover a MySQL or MariaDB database to an exact moment after a bad write or dropped table by restoring a full backup and replaying binary logs up to the incident.

  • Advanced
  • Linux VPS
  • 7 min read