Cannot change PostgresSQL default user password

Recently I have faced with issue that I cannot change password that is used in pgAdmin for connection to remote database ...

In pg_hba.conf (PostgresSQL 12) I have the following login capabilities:

# 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 0.0.0.0/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 all peer
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5
hostnossl all all 0.0.0.0/0 trust

I tried to change password for postgres user, also tried to change password of postgres role in database nothing helped ...

Is there a way to change default password for connection to PostgresSQL ?

2 Answers

The passwd command is used to change the password of a Linux user account. To change the password of a PostgreSQL account, you will need to do it from within PostgreSQL. There are generally two ways to do this:

Via the Command Line

1. Open Terminal (if it’s not already open)
2. Switch to the postgres account (or one that has admin rights for the database engine):

   sudo -i -u postgres

3. Connect to the database engine:

   psql

4. Set the password:

   \password postgres

5. When prompted, enter the new password
6. Exit (quit):

   \q

Via a SQL Command

1. Connect to PostgreSQL with an admin-level account
2. Set the password:

   ALTER USER postgres WITH PASSWORD 'superSecretPassword!123';

From here, you can edit your pgAdmin configuration for the new password.

Note: It is generally poor practice to connect to a PostgreSQL database remotely using the postgres account as this is an easier target for people with bad intentions to hit. If you are administering a database remotely, consider creating a user account with admin privileges. If the remote server is running Linux, you can further protect the database by connecting via an SSH tunnel.

The issue was that I used the following line in pg_hba.conf:

...
hostnossl all all 0.0.0.0/0 trust

That is way it was not necessary to use password for connection with database

I changed it to:

hostnossl all all 0.0.0.0/0 md5