Как изменить пароль postgres windows

Your privacy

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy.

Sometimes you may need to change user password in PostgreSQL, for database management and website administration. In this article, we will look at how to change user password in PostgreSQL. You can use these steps to change postgres password in Ubuntu, Windows and other Linux systems. You can also use it to change default postgres password.

Here are the steps to change user password in PostgreSQL.

1. Log into PostgreSQL

Open terminal and run the following command to log into PostgreSQL. Replace username and dbname with your username and database name respectively.

$ sudo -u username psql dbname

Also read : How to change user to superuser in PostgreSQL

2. Change User Password

We will use the ALTER USER command to change user password. Here is its syntax.

ALTER ROLE username WITH PASSWORD 'password';

In the above command, you need to specify the username whose password you want to change, and also the new password for that user.

Run the following ALTER USER command to change user password. Replace testuser and newpassword with

ALTER USER testuser WITH PASSWORD 'newpassword';

Similarly, if you want to change password for postgres user, modify the above command as shown.

ALTER USER postgres WITH PASSWORD 'newpassword';

Also read : How to Copy data from one table to another in SQL

Sometimes you may need to expire a password after a specific date. In such cases, use VALID UNTIL clause with ALTER USER statement. Here’s the above SQL query modified to set password expiration date as December 31, 2020.

ALTER USER testuser WITH PASSWORD 'newpassword'
VALID UNTIL 'December 31, 2020';

As you can see, it is very easy to change user password in PostgreSQL in Ubuntu, CentOS and other systems.

Need a reporting tool for PostgreSQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

In PostgreSQL, postgres is the superuser. If you have forgotten the password of postgres, you can reset it by the following steps.

1. Locate the configuration file pg_hba.conf for the PostgreSQL database server.

   On Windows, the configuration files for the PostgreSQL database server are located in the data directory of the PostgreSQL installation directory, for example: C:\Program Files\PostgreSQL\14\data.

   On Linux, the configuration file for the PostgreSQL database server is located at /etc/postgresql/14/main/pg_hba.conf.

2. Back up the configuration file before modifying it so that you can restore it later.

   cp pg_hba.conf pg_hba.conf.bak
   

3. Modifying the configuration file to trust local connections does not require a password. Modify scram-sha-256 or md5 in the configuration file to trust as follows:

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

4. Restart the PostgreSQL database server.

   On Windows, you can restart PostgreSQL in the Services List window.

   In Linux, you can restart PostgreSQL with the systemctl restart postgresql command.

5. Log in to the PostgreSQL database server.

   You do not need to enter a password.

6. Use the following command to modify the postgres user’s password:

   ALTER USER postgres WITH PASSWORD 'new_password';
   

7. Restore the pg_hba.conf configuration file. Overwrite the pg_hba.conf file with the contents of the pg_hba.conf.bak file.

8. Restart the PostgreSQL database server. When you log in, PostgreSQL should prompt you for a password.

Conclusion

This article explains the detailed steps to reset the password of superuser postgres.