Changing (forgotten) PostgreSQL password

PostgreSQLWhether 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 (ie. 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

or

# su postgres
$ pg_ctl -D /var/lib/pgsql/data/ 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.

Category: 

2 comments

26
May

Thankyou so much, finally a good tutorial to reset postgres password! Regards.
1
Oct

I have tried to do the same. In my case I forgot the postgres password then I reset. When I try to use it, the authentication failure opens in the terminal

Add new comment