How to find or reset Postgresql psql postgres user password?

Question

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.

Hi, if my answer solved your problem, please consider accepting it.Thanks — Iman Rosstin
– Iman Rosstin, Commented Jul 31, 2020 at 15:37

Lambart · Accepted Answer · 2024-04-17 00:39:41Z

6

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

edited Apr 17, 2024 at 0:39

Lambart

2,1162 gold badges23 silver badges37 bronze badges

answered Jul 30, 2020 at 20:28

Iman Rosstin

2,3772 gold badges18 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lambart · Accepted Answer · 2024-04-17 00:50:35Z

0

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=#

answered Apr 17, 2024 at 0:50

Lambart

2,1162 gold badges23 silver badges37 bronze badges

Collectives™ on Stack Overflow

How to find or reset Postgresql psql postgres user password?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related