Otdfbt

When I was setting up postgresql on linux, I was following a tutorial that I think had me change the postgres account password and I did not know any better. Please forgive my ignorance.

When I type "psql postgres" it asks for a password and only one password works. I have tried following previous answers by implementing "sudo passwd --lock postgres" and "sudo -u postgres psql postgres" with "password postgres" and setting a new password (which does not work).

I am afraid to edit /etc/passwd and put * instead of the password because that comment has very few upvotes and I don't know what it will actually do.

Everything I try (even changing md5 to trust in pg_hba.conf), after I enter "sudo /etc/init.d/postgresql restart", psql postgres STILL REQUIRES a password and it only accepts the one password that works. Anything else returns 'psql: FATAL: password authentication failed'.

What can I do?

Authentication method "peer"

If your authentication method is peer like

local all postgres peer

and/or

local all all peer

in pg_hba.conf, then connect as linux user postgres to the database.

sudo -u postgres psql

You won't need a password, even if set.

Note: psql -U postgres postgres run as a different linux user as postgres does not work in this case and fails with psql: FATAL: Peer authentication failed for user "postgres".

Authentication method "md5"

If your authentication method is md5, then you will need the correct password to connect.

Reset password

1. 
   

   Comment all existing lines starting with "local" in pg_hba.conf and add line

   
   
   

   local all postgres trust
   

   
   


2. 
   

   Restart postgres

   
   
   

   service postgresql restart
   

   
   


3. 
   

   Connect to database

   
   
   

   # as any linux user
   psql -U postgres
   # or as user postgres
   sudo -u postgres psql
   

   
   


4. 
   

   Change password

   
   
   

   ALTER USER postgres WITH PASSWORD 'password';
   

   
   


5. 
   

   Exit

   
   
   

   q
   

   
   


6. 
   

   Restore your changes in pg_hba.conf, default for postgresql 9.6 on Debian 9.8.0 is

   
   
   

   # Database administrative login by Unix domain socket
   local all postgres peer
   
   # TYPE DATABASE USER ADDRESS METHOD
   
   # "local" is for Unix domain socket connections only
   local all all peer
   # 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 md5
   #host replication postgres ::1/128 md5
   

   
   


7. 
   

   Restart postgres

   
   
   

   service postgresql restart
   

   
   

Note 2:
If you want to change the password for a host record, then do the same as above by using method trust and connect with the hostname option using psql -U postgres -h localhost postgres.