如何重置pgsql的密码(Reset Forgotten Password For postgres User)

Background

How should we do if forgot our postgres User password . For some reason, after installing PostgreSQL, you may forget the password of the postgres user. In this case, you need to know how to reset the password.

如果忘记了我们的postgres用户密码，该怎么办。 由于某些原因，在安装PostgreSQL之后，您可能会忘记postgres用户的密码。 在这种情况下，您需要知道如何重设密码。

Importance Info : PostgreSQL uses the pg_hba.conf configuration file stored in the database data directory (e.g., C:\Program Files\PostgreSQL\12\data on Windows) to control the client authentication. The pg_hba.conf means host-based authentication.

• Set the value to “trust” means that u can login as postgres without a password
• Set the value to default value “md5” means that u should login as postgres with a password

这里有个重要信息：PostgreSQL使用存储在数据库数据目录中的pg_hba.conf配置文件来控制客户端的连接认证参数。

-将该值设置为“trust”意味着您可以以postgres`的身份登录，而无需输入密码。

-将值设置为“md5”的默认值意味着您应使用密码以postgres`身份登录

Solution

So our first step is to edit the pg_dba.conf file and change IPv4 connections from md5 to trust

所以我们第一步就是去设置pg_dba.conf 文件,把IPv4的属性改为md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

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

Restart the PostgreSQL server. If you are on Windows, you can restart the PostgreSQL from Services:

重启pgsql的服务,可以通过windows的服务中心,或者使用命令进行重启

Or run the following command from the window terminal:

pg_ctl -D “C:\Program Files\PostgreSQL\12\data” restart

Connect to PostgreSQL database server using any tool such as psql or pgAdmin (PostgreSQL will not require a password to login as we set to trust):

因为我们前面设置过trust,所以这里连接数据库是不需要密码的,使用以下命令直接登陆:

psql -U postgres

Execute the following command to set a new password for the postgres user.

输入sql语句执行重置密码操作:

ALTER USER postgres WITH PASSWORD ‘new_password’;

Restore the pg_dba.conf file ,set the trust back to md5, restart the PostgreSQL database server and connect to the PostgreSQL database server with the new password.

最后,还原 pg_dba.conf 文件,属性从trust设置回md5 ,重启pgsql服务.并尝试使用新密码连接数据库!

Done!

恭喜你,重置成功!