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
- Open Terminal (if it’s not already open)
- Switch to the
postgresaccount (or one that has admin rights for the database engine):sudo -i -u postgres - Connect to the database engine:
psql - Set the password:
\password postgres - When prompted, enter the new password
- Exit (
quit):\q
Via a SQL Command
- Connect to PostgreSQL with an admin-level account
- 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
