CODEWITHSUNDEEP
postgresqlpsql
Omar_Jandali
Omar_Jandali

Reputation: 575

How to find or reset Postgresql psql postgres user password?

I am trying to get into my postgres shell to manage my database and I am running into an issue getting into my postgres shell.

omars-mbp:postgres omarjandali$ brew services restart postgres
Stopping `postgresql`... (might take a while)
==> Successfully stopped `postgresql` (label: homebrew.mxcl.postgresql)
==> Successfully started `postgresql` (label: homebrew.mxcl.postgresql)
omars-mbp:postgres omarjandali$ psql
Password for user omarjandali: 
psql: error: could not connect to server: FATAL:  password authentication failed for user "omarjandali"

I also tried the default psql postgres user:

omars-mbp:postgres omarjandali$ psql -U postgres
Password for user postgres: 
psql: error: could not connect to server: FATAL:  password authentication failed for user "postgres"
omars-mbp:postgres omarjandali$

Is there a way for me to reset the password. I don't remember setting a master password.

Upvotes: 2

Views: 13824

Answers (2)

Lambart
Lambart

Reputation: 2096

This doesn't directly answer the original question (how to reset/change a password), but if you're trying to do some basic db management such as granting/revoking privileges, etc. it's possible you don't need a password for the postgres user at all.

If you're running PostgreSQL on a *nix-based system you might be able to access the database with only your normal user password by using the sudo command.

For example (tested on Ubuntu 22.04, w/PostgreSQL 16):

$ sudo -u postgres psql
[sudo] password for my_linux_user: 
psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1))
Type "help" for help.

postgres=#

Upvotes: 0

Iman Rosstin
Iman Rosstin

Reputation: 2365

Step 1. Backup the pg_hba.conf file by copying it to a different location, or just copy it to e.g. pg_hba.conf.bk

Step 2. Edit the pg_dba.conf file and change the "method" (last column, could be "md5", "scram-sha-256", etc.) for all local connections to "trust".

Step 3. Restart the PostgreSQL server (Service).

Step 4. Connect to PostgreSQL database server using any tool such as psql or pgAdmin:

psql -U postgres

PostgreSQL will not require a password to login.

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

ALTER USER postgres WITH PASSWORD 'new_password';

Courtesy of PostgreSQLTutorial

Upvotes: 6

Related Questions