Home / ubuntu / Questions / 1371571
Accepted
Denis Kotov
Asked: 2021-10-26 11:28:05 +0800 CST

Cannot change PostgresSQL default user password

  • 772

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 ?

password

2 Answers

  1. 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.

    • 0
  2. Best Answer

    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
    • 0