Overview
A brief look at how to reset a PostgreSQL user password. These steps will work for both a Docker Container or PostgreSQL Server.
TL;DR
From within the Docker PostgreSQL container or Server:
ALTER USER <<username>> WITH PASSWORD '<<new password>>';The Long Version
A recent PostgreSQL Docker migration caused me much heartache after the username and password stopped working. No longer could my application container talk to the database container. The logs overflowed with incorrect or mismatched password warnings and no amount of restores, migrations or copying fixed the issue. Determined to find a solution and rescue my data, I embarked on a quest to reset a PostgreSQL password.
Luckily the process is straight forward and only requires minimal CLI and PostgreSQL knowledge. Together we are going to ssh or exec into our PostgreSQL server, login into the database and set a new password for the user.
Allowing Local Connections
Before we can do anything else, we need to the PostgreSQL server to allow local database authentication without a password. By default the PostgreSQL container allows passwordless local host access; however, to rule out future issues, make sure the IPv4 local connections line in pg_dba.conf file looks like the following and restart the server.
# IPv4 local connections
host all all 127.0.0.1/32 trustConnect to the PostgreSQL Server or Container
With the PostgreSQL container or server running, access the server shell. With Docker use docker exec to access the shell of a container, however, for a PostgreSQL server your connection my vary, but will generally be SSH.
#Connect to the Docker Container
sudo docker exec -it <<container>> bashConnect to the PostgreSQL Database
Once within the container or connected to the server, connect to the database.
psql -U <<username>>Reset the Password
Finally, we have arrived at the TL;DR. The step we have all been waiting for. The command to set the password for a specific PostgreSQL user.
ALTER USER <<username>> WITH PASSWORD '<<password>>';Restore Settings
Exit out of server, restore any changes to the pg_dba.conf file and restart the server. Leaving the local connections to trust opens your server to insider threats, accidental escalations and intruders. Be safe out there!
Test Connection
Once the PostgreSQL server reboots, we are ready to test the connection. Connect to the PostgreSQL server using the new password just created.