0

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?

Humble-Data-22
  • 3
  • 1

1 Answers1

0

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.

Freddy
  • 2,039
  • 7
  • 13