Introduction to PostgreSQL
PostgreSQL is a very powerful, open source-code customer/server relational database management system (RDBMS). PostgreSQL was originally envisioned in 1986, when it was called the Berkley Postgres Project. The project was in evolution and modification until 1994, until developers Andrew Yu and Jolly Chen added a SQL (Structured Query Language) translation program to Postgres, the version called Postgres95, was distributed in the open source community.
Let me introduce to you how to handle PostgreSQL forgot postgres password. The specific content is as follows:
In the PostgreSQL database, what should you do if you forget the password of your postgres account or due to work handover issues, and you don’t have the postgres account password when handed over to you? In fact, there is no need to panic. Like databases such as MySQL, SQL Server, etc., as long as you have operating system permissions, it is also very convenient and simple to modify the password of the postgres super account. The following test environment is CentOS Linux release 7.2.1511 (Core), and the PostgreSQL database version is 9.5. The operations of other different versions are actually the same, with a slight nuance.
1: Locate the location of the pg_hba.conf file
First find the location of the pg_hba.conf file, and there are the following methods:
Method 1: locate the location of the pg_hba.conf file
$ locate pg_hba.conf /usr/pgsql-9.5/share/pg_hba. /var/lib/pgsql/9.5/data/pg_hba.conf
Method 2: Find command to find.
$ find / -name "pg_hba.conf" 2>%1 | grep -v "Permission denied" /var/lib/pgsql/9.5/data/pg_hba.conf
2: Modify the pg_hba.conf configuration file
It is best to make a backup before modifying pg_hba.conf. This is a good habit to avoid rollbacks and you can easily retrace all operations.
#cp /var/lib/pgsql/9.5/data/pg_hba.conf /var/lib/pgsql/9.5/data/pg_hba.conf.20210125
Find something like the following in pg_hba.conf:
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all md5 # 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. #local replication postgres peer #host replication postgres 127.0.0.1/32 ident #host replication postgres ::1/128 ident # Allow access from all host to connect to this UAT server host all all 0.0.0.0/0 md5
Regarding modifying pg_hba.conf, if you plan to log in to the database locally in the socket mode, then you only need to modify the local record and modify the value of this option in pg_hba.conf from md5 to trust
Before modification # "local" is for Unix domain socket connections only local all all md5 After modification # "local" is for Unix domain socket connections only local all all trust
Trust means that trustworthy local connections are allowed. You do not need to enter a password to connect to the database at this time.
Tips:
TYPE represents the host type, and its values include the following:
local: means a socket connection that is a unix-domain
host: means TCP/IP socket
hostssl: represents SSL-encrypted TCP/IP socket
If you plan to access the database in TCP/IP, i.e. psql -h127.0.0.1 -Upostgres, then you must modify the host configuration. The details are as follows:
Before modification： # IPv4 local connections: host all all 127.0.0.1/32 md5 After modification： # IPv4 local connections: host all all 127.0.0.1/32 trust
3: Restart PostgreSQL service
There are many ways to restart PostgreSQL service. I won’t introduce it too much here, just choose the method you use frequently.
# service postgresql-9.5 stop Redirecting to /bin/systemctl stop postgresql-9. # service postgresql-9.5 start Redirecting to /bin/systemctl start postgresql-9. # service postgresql-9.5 status Redirecting to /bin/systemctl status postgresql-9.
4: Reset the password of the account postgres
Use psql to log in without password and modify the user postgres password
alter user postgres with password 'New Password';
Method 1:
#psql -U postgres
Method 2:
#psql
Regarding both, if ssh is connected to the server using postgres user, then psql can be used directly. If root user connects to the server, you must use psql -U postgres
-bash-4.2$ psql psql (9.5.6) Type "help" for help. postgres=# alter user postgres with password 'xxxxxxxx'; ALTER ROLE
5: Restore the modification of pg_hba.conf configuration file
6: Restart PostgreSQL service
This is the end of this article about the solution to the PostgreSQL password for forgetting postgres. For more related PostgreSQL password content, please search for my previous article or continue browsing the related articles below. I hope everyone will support me in the future!