Psql ошибка пользователь не прошел проверку подлинности

When I connect to my local PostgreSQL database server with command $ psql postgres 'postgres' I encounter the error message psql: FATAL: Peer authentication failed for user “postgres”.

:~$ psql postgres 'postgres'
psql: FATAL:  Peer authentication failed for user "postgres"

After investigating I finally find the solution to fix this error. This error is because PostgreSQL has two authenticate methods as below.

1. PostgreSQL Authentication Methods.

1. Peer Authentication (peer) Mode: This authentication method will use the base operating system’s user name and password as the PostgreSQL database server user account to login, this method is only effective for local PostgreSQL connections.
2. Password Authentication (md5) Mode: This authentication method will need the login user to provide a username and password, and this method is effective for both local and remote PostgreSQL database server connections. The password can be saved both in clear-text or md5-encrypted. But we recommend using md5-encrypted. This can make the user account more safety.
3. Trust (trust) Mode: This means the PostgreSQL DB server trust any connection, no password for any connection.

2. How To Change Postgresql Authentication Method.

PostgreSQL authentication method is saved in a configuration file, the file name is pg_hba.conf. So you can change the authentication method follow the below steps.

1. Locate the pg_hba.conf configuration file uses Linux locate command.

   :~$ locate pg_hba.conf
   /etc/postgresql/10/main/pg_hba.conf
   /usr/share/postgresql/10/pg_hba.conf.sample
   

2. From the above result, we can see that the pg_hba.conf file is generally saved in /etc/postgresql/10/main/ directory.
3. Now edit the file using your favorite text editor, I use gedit in ubuntu.

   $ sudo gedit /etc/postgresql/10/main/pg_hba.conf 
   [sudo] password for zhaosong: 
   (gedit:317): IBUS-WARNING **: 11:34:40.540: The owner of /home/zhaosong/.config/ibus/bus is not root!
   

4. Comment database administrative login text line which ends with peer and adds a new line use md5 to replace peer. Do not forget to save the changes.

   #local   all             postgres                                peer
   local    all             postgres                                md5
   

5. Reload the PostgreSQL service with the below command.

   ~$ /etc/init.d/postgresql reload
   [ ok ] Reloading postgresql configuration (via systemctl): postgresql.service.
   

6. Or restart the PostgreSQL server.

   $ sudo service postgresql restart

7. Now login PostgreSQL server again, you may find another error message psql: error: FATAL: password authentication failed for user “postgres” like below.

   $ psql postgres postgres
   Password for user postgres: 
   psql: error: FATAL:  password authentication failed for user "postgres"
   

8. Change the line local all postgres md5 to local all postgres trust<.span> in the file /etc/postgresql/10/main/pg_hba.conf.

   #local   all             postgres                                peer
   #local    all             postgres                                md5
   local    all             postgres                                trust

9. Now login PostgreSQL server again with the command psql user=postgres or psql postgres postgres, the error will be fixed.

   ~$ psql user=postgres
   psql (10.7 (Ubuntu 10.7-0ubuntu0.18.04.1))
   Type "help" for help.
   postgres=# 
   

10. Below is the explanation of the settings text line like host   all   all   127.0.0.1/32   trust in the pg_hba.conf file. When you meet a login authentication error, you can change the METHOD column value to trust, this method will not require any password and trust any connection. But for the production environment, you had better use the method peer or md5 for safety issues.

    # TYPE  DATABASE        USER            ADDRESS                 METHOD
    # "local" is for Unix domain socket connections only
    local   all             all                                     trust
    # IPv4 local connections:
    host    all             all             127.0.0.1/32            trust

3. How To Fix Psql: Error: Fatal: Password Authentication Failed For User “postgres”.

3.1 Question.

1. When I connect to the PostgreSQL in my python source code, I get the below error.

   FATAL: Peer authentication failed for user "postgres"

2. I have edited the pg_hba.conf file and change the authentication method to md5, but it does not take effect.
3. Then I try to create a new user with a new database, but when I run the command sudo -u postgres psql -l to list the databases, it returns the below error. How to fix it.

   psql: error: FATAL: password authentication failed for user "postgres"

3.2 Answer1.

1. If this is your test environment, you can change the authentication method to trust like below, then it will not require you to provide the password to connect to the PostgreSQL DB server.

   local   all             postgres                                trust

2. On Ubuntu, you can run the command sudo gedit /etc/postgresql/12/main/pg_hba.conf to open the PostgreSQL configuration file to edit it and save it.
3. Run the command sudo systemctl restart postgresql.service to reload the above settings to make it take effect.
4. Now when you run the command sudo -u postgres psql -l, you only need to provide the OS logged in user’s password, it will not ask you to provides the user postgres‘s password, and then it will run the command psql -l to show the PostgreSQL database tables list.

   $ sudo -u postgres psql -l
   [sudo] password for jerry: 
   List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
   -----------+----------+----------+-------------+-------------+-----------------------
   postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
   template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
   |          |          |             |             | postgres=CTc/postgres
   template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
   |          |          |             |             | postgres=CTc/postgres
   (3 rows)