Whether you lost password for postgres user or you’d just like to change it, the process is very simple. Unlike resetting lost password for MySQL’s root user, resetting postgres’ password is pretty straightforward and you’ll not experience any kind of downtime. This article explains how to change/reset password for postgres user in PostgreSQL 8.4 server that runs on CentOS 5.7, but I’ve thrown in some tips to help you out if you’re using Debian or Ubuntu Linux distro.
Changing known password
Changing postgres’ password, assuming you know current password (i.e. you can connect to PostgreSQL server), is pretty simple. You either become postgres user using su and then run psql utility
# su postgres
$ psql
or you can connect to PostgreSQL server directly as current user with the following command and supply postgres’ password when asked
$ psql -U postgres
After connecting to PostgreSQL server, you should get something like
psql (8.4.7)
Type "help" for help.
postgres=#
To set new password, you can use \password meta-command
postgres=# \password
Enter new password:
Enter it again:
postgres=#
This meta command sends an SQL statement to PostgreSQL server containing an already encrypted password. You can run the equivalent SQL query, which is a bit harder to remember
ALTER USER postgres WITH ENCRYPTED PASSWORD 'MyNewPassword';
Changing forgotten password
If you’ve forgotten your PostgreSQL password, you can relax. Resetting forgotten password is very easy and it’s nowhere near as complex as with MySQL, which requires stopping and starting the server with –skip-grant-tables switch and losing valuable uptime.
So, to reset postgres’ password you should open pg_hba.conf config file which is located in /var/lib/pgsql/data/ if you use CentOS or RHEL. If you use Debian or Ubuntu, you should find this file in /etc/postgresql/[version]/main/.
In pg_hba.conf you should have the following line
local all postgres md5
This line defines that user postgres can connect to any database using md5 authentication method. In order to reset user’s password, you should set ident method instead of md5. So, the modified line should look like
local all postgres ident
If you don’t see the mentioned line in your pg_hba.conf config file, then your PostgreSQL server is probably configured for all users to authenticate in the same way with
local all all md5
If this is the case, you can simply add custom rule just for user postgres as explained above.
After changing authentication method for user postgres and saving changes in pg_hba.conf, you’ll need to reload PostgreSQL server. On CentOS and RHEL you can use
# service postgresql reload
On Debian and Ubuntu you can reload PostgreSQL with
# /etc/init.d/postgresql reload
If you try to connect to PostgreSQL server with psql utility, you’ll notice that you’ll be logged in right away. To change your password simply use \password meta-command as explained above. After you reset your password, don’t forget to restore original configuration in pg_hba.conf and again reload PostgreSQL server.