I installed postgres on my mac running 10.6.8 and I would like to reset the password for the postgres user (I believe this is the super user password) and then restart it. All the directions I found do not work because I think my user name is not recognized by pg as having authority to change the password. The IP address to connect to will be that of the (virtual) machine Docker is running on, with the port you have specified ( 54320 if you followed my example), the username postgres and the password you have specified in the recentmost command. Once successful, proceed with changing the password. Changing the Password. With a connection now established to Postgres at the psql prompt, issue the ALTER USER command to change the password for the postgres user.
This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing few steps for recovering the forgot password of postgres user (admin user) of PostgreSQL.
Yesterday, I was checking few forums of PostgreSQL and found that few DBAs forgot their password after installation of PostgreSQL.
The postgres user is a superuser of PostgreSQL, and it is a default user also. While installing PostgreSQL, you have to set the password of postgres user, and if you forget the password, you can’t do any operation like create first DB or create first DB User.
How to recover forgotten password of PostgreSQL?
Mac Postgresql Client
Edit pg_hba.conf file:
Change below line for MD5 to TRUST
Restart the PostgreSQL Server:
Connect the PostgreSQL:
Change the password of postgres user:
Last, rollback the change in pg_hba.conf file and restart the PostgreSQL Server:
Please visit other related articles...
Firstly, it is important to understand that for most Unix distributions, the default Postgres user neither requires nor uses a password for authentication. Instead, depending how Postgres was originally installed and what version you are using, the default authentication method will either be ident or peer.
ident authentication uses the operating system’s identification server running at TCP port 113 to verify the user’s credentials.
peer authentication on the other hand, is used for local connections and verifies that the logged in username of the operating system matches the username for the Postgres database.
Login and Connect as Default User
For most systems, the default Postgres user is postgres and a password is not required for authentication. Thus, to add a password, we must first login and connect as the postgres user.
If you successfully connected and are viewing the psql prompt, jump down to the Changing the Password section.
If you received an error stating that the database “postgres” doesn’t exist, try connecting to the template1 database instead and if successful, continue to Changing the Password.
Authentication Error
If you receive an authentication error when attempting to connect to the psql client, you may need to alter the Postgres authentication config file (pg_hfa.conf).
Open the config file, typically located at /etc/postgresql/#.#/main/pg_hba.conf, where #.# is the Postgres version you are using:
The auth config file is a list of authentication rules. Scroll down the file until you locate the first line displaying the postgres user in the third column (if such a line exists). Uncomment the line if necessary (remove the semicolon), or otherwise if the line is missing entirely, add the following line to the top of the file and save your changes:
This authentication rule simply tells Postgres that for local connections established to all databases for the user postgres, authenticate using the peer protocol.
Note: Some older versions of Postgres prefer the default authentication method of ident, but most modern installations will utilize peer as specified above instead. You may need to test both if your results differ.
Now with your configuration file updated, repeat the steps in the Login and Connect as Default User section to try to connect to as the default postgres user. Once successful, proceed with changing the password.
Mac Postgres Password Change
Changing the Password
With a connection now established to Postgres at the psql prompt, issue the ALTER USER command to change the password for the postgres user:
Mac Postgres Password Recovery
If successful, Postgres will output a confirmation of ALTER ROLE as seen above.
Finally, exit the psql client by using the q command.
Postgres Mac Client
You’re all done. The default postgres user now has a password associated with the account for use in your other applications.