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
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 postgres database role and the postgres Linux user are two different accounts. This guide changes the database role password. You do not need to give the Linux user a password.
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:
sudo -u postgres psqlAt the postgres=# prompt, run:
\password postgrespsql 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:
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.
While the trust rule is active, any user on the VPS can log in to PostgreSQL as any role through the local socket, without a password. Make the change, reset the password, and revert straight away. Never add trust for host (TCP) lines.
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:
sudo find / -name pg_hba.conf -not -path '*/proc/*' 2>/dev/null2. Back up the file and add a temporary rule
sudo cp /etc/postgresql/17/main/pg_hba.conf /root/pg_hba.conf.bak
sudo nano /etc/postgresql/17/main/pg_hba.confAdjust 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:
local all postgres trust3. Reload PostgreSQL
A reload is enough. It rereads the file without dropping connections:
# Ubuntu and Debian
sudo systemctl reload postgresql
# AlmaLinux and Rocky Linux (PGDG packages; use your version number)
sudo systemctl reload postgresql-17With the distribution package on AlmaLinux or Rocky Linux, the service is just postgresql.
4. Set the new password
sudo -u postgres psql -U postgresThen, in psql:
\password postgres
\q5. Revert and reload
Put the original file back and reload again:
sudo cp /root/pg_hba.conf.bak /etc/postgresql/17/main/pg_hba.conf
sudo systemctl reload postgresqlDelete the backup copy afterwards if you no longer need it. Confirm the trust line is gone:
sudo grep -n trust /etc/postgresql/17/main/pg_hba.confNo 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:
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 postgresas a different Linux user. Usesudo -u postgres psql, or add-h 127.0.0.1to 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
hostlines may usescram-sha-256while the password was stored in an older format. RunSHOW password_encryption;in psql; it should sayscram-sha-256. Then set the password again with\password. - The service will not reload: a typo in
pg_hba.confmakes PostgreSQL reject the new file. Restore your backup copy and reload, then check the log withsudo journalctl -u 'postgresql*' --since "10 min ago". On Ubuntu and Debian, also look in/var/log/postgresql/.
Next steps
- Create a separate role for each application instead of sharing the superuser: Create PostgreSQL users, roles and databases.
- Learn the psql commands used here: psql cheat sheet.
- Lock down SSH, the way into your server: Disable root SSH login.
- Run PostgreSQL on a Linux VPS.
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.
Related guides
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
Databases
How to create a database and user for a web app in MySQL or MariaDB
Set up a utf8mb4 database and a dedicated user with a strong password for WordPress, Laravel or any web app, then test the login before you configure the app.
- Beginner
- Linux VPS
- 4 min read