Recover a lost or forgot Postgres password

Products

• CA Application Performance Management Agent (APM / Wily / Introscope)
• INTROSCOPE

Issue/Introduction

After installing the APM database to use PostgreSQL the PostgreSQL password is lost or forgotten.

 

Environment

All supported APM and Postgres releases.

Resolution

Follow these steps:
1) Open the pg_hba.conf file in a text editor.
This file is in the <Postgres_Home>/data directory. For example, /opt/database/data. 

2) In the pg_hba.conf file, look for the line for the postgres user. It looks something like this:

local all all password
or
local all postgres md5

Network users begin the line with "host" and also provide an IP address and netmask:
host all postgres 10.255.255.10

If your system is configured for all users to authenticate in the same way, you see "all" in place of a username:
local all all md5
Note: The method may be set to "md5" or "password" or one of the other many options.

3) Comment out the line that applies to either all users or the postgres user, and add the following line:

local all postgres ident sameuser

The above line allows you to connect as the postgres user without having to specify a password.
Local is for UNIX domain socket connections only.

local all postgres ident sameuser

Tip: Copy the lines that you are changing and comment the original lines.

4) Save your changes to the pg_hba.conf file.
5) Restart the postgres service. You can find it typically here:

/etc/rc.d/init.d/

Run  something like the the following command:

/etc/init.d/postgresql restart

6) Run the following command to change to sudo access for the postgres user:

su - postgres

This allows you to run commands as the postgres user.

7) Launch psql, the command-line client for PostgreSQL. 

This causes psql to open the PostgreSQL database. It should not prompt you for a password. This is what the login prompt looks like:

psql 

Type "help" for help.

postgres=#

8) From the psql command prompt, run the following psql command to change the database password:

ALTER USER postgres WITH ENCRYPTED PASSWORD 'password';

9) Psql lists the following to indicate success:

ALTER ROLE
postgres=#

10) Enter the following to exit psql:

\q

11) Re-open the pg_hba.conf file and set it back to the original settings. Use either md5 or password authentication, but md5 is more secure.

12) Restart the postgres service again

13) To test, launch psql again. It should prompt you for the (reset) password.