PostgreSQL - how to reset postgres password

  • Last updated: Nov 3, 2023
  • Views: 843
  • Author: Admin
PostgreSQL - how to reset postgres password

Colleagues hello to all.

In today's article, we will talk about how to reset the password for the main postgres account in the PostgreSQL DBMS. There are times when you accidentally forget or lose the password for the postgres account, but this does not matter, it can be reset very easily.

 

Article content:

  1. Changing the database connection port.
  2. Editing the connection file pg_hba.conf.
  3. Set a new password for the postgres account.
  4. We return everything as it was.

 

1. Change the database connection port.

Just in case, for security purposes, before editing the pg_hba.conf connection file, we will change the connection port from 5432 to 5433. To do this, we need to find the PORT parameter in the postgresql.conf file and change it.

port = 5433

postgresql_reset_postgres_password

After changing the port, be sure to restart the database.


 

2. Editing the connection file pg_hba.conf.

The main file in the PostgreSQL DBMS that is responsible for accessing databases is called pg_hba.conf. You need to find it and add it to it:

local all postgres trust

postgresql_reset_postgres_password

With this line we indicate that the local user of the server can connect to the database without entering the password from the postgres account.

After the entered line, we need to restart the database or run a query in the console that will reread the  pg_hba.conf file and apply all the changes.

sql> SELECT pg_reload_conf();

postgresql_reset_postgres_password


 

3. Setting a new password for the postgres account.

Now we can set a new password for the postgres account, to do this, connect to the psql console and run the command:

sql> ALTER USER postgres WITH PASSWORD 'Qwerty123';

postgresql_reset_postgres_password


 

4. We return everything as it was.

After we changed the password, do not forget to return everything back, namely the port that we changed before the robots.

postgresql_reset_postgres_password

Be sure to also remove the connection string that we inserted in the pg_hba.conf file, otherwise it will turn out that we have set a new password for the postgres account, but still access to the database will be available without entering a password.


 

Thank you all, I hope that my article helped you in some way.

SIMILAR ARTICLES

PostgreSQL - pg_dump creating backup with bzip2 compression

PostgreSQL - pg_dump creating backup with bzip2 compression

Download and install Postgresql 14 on Linux CentOS/RHEL 8/7 TAR

Download and install Postgresql 14 on Linux CentOS/RHEL 8/7 TAR

Download and install PostgreSQL on CentOS 8 from the repository

Download and install PostgreSQL on CentOS 8 from the repository