How to retrieve or change PostgreSQL password?

If you lost or forgot PostgreSQL password, it may be changed any time. It takes only to set a possibility of connecting without a password in a configuration file and later on – from the database level – create a new password for postgres user. 

Solution:

  1. Enter DATA catalogue in installation PostgreSQL database localization. By default, these are:
    • For Windows 32 bit – C:\Program Files\PostgreSQL\<X>\data\
    • For Windows 64 bit, PostgreSQL 64bit – C:\Program Files\PostgreSQL\<X>\data\
    • For Windows 64bit, PostgreSQL x86: C:\Program Files (x86)\PostgreSQL\<X>\data\
      In each case, X is a database version number
  2. Find pg_hba.conf file and create its copy.
  3. Open pg_hba.conf file in Notepad or other text editor
  4. Find the entry presented below
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

5. Change it as presented below

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4 local connections:
#host    all             all             127.0.0.1/32            md5
host	 all		 postgres	 127.0.0.1/32		 trust
# IPv6 local connections:
#host    all             all             ::1/128                 md5
 host	 all		 postgres	 ::1/128    		 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

6. Next, launch pgAdmin and connect to the server. If you are asked about the password for postgres user, do not input anything – just click OK.

7. Openy SQL query window and run the following statement

ALTER USER Postgres WITH PASSWORD 'NOWE HASŁO';

In NEW PASSWORD field enter your new password for postgres user.

6. Remember – after the whole procedure revert pg_hba.conf file to its original form.



Was this article helpful?

Related Articles

Leave A Comment?