When i connect to my local postgresql database server with command
$ psql postgres 'postgres' i encounter error message psql: FATAL: Peer authentication failed for user “postgres”.
:~$ psql postgres 'postgres' psql: FATAL: Peer authentication failed for user "postgres"
After investigate i finally find the solution to fix this error. This error is because postgresql has two authenticate methods as below.
1. Postgresql Authentication Methods.
- Peer Authentication : 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 effetive for local postgresql connection.
- Password Authentication : This authentication method will need login user to provide username and password, and this method is effective for both local and remote postgresql database server connection. The password can be saved both in clear-text or md5-encrypted. But we recommed use md5-encrypted. This can make the user account more safety.
2. How To Change Postgresql Authentication Method.
Postgresql authentication method is saved in a configuration file which name is pg_hba.conf. So you can change the authentication method follow below steps.
- Locate the pg_hba.conf configuration file use linux locate command.
:~$ locate pg_hba.conf /etc/postgresql/10/main/pg_hba.conf /usr/share/postgresql/10/pg_hba.conf.sample
- From above result, we can see that the pg_hba.conf file is generally saved in /etc/postgresql/10/main/ directory.
- Now edit the file use 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!
- Comment database administrative login text line which end with peer and add a new line use md5 to replace peer. Do not forget save the changes.
#local all postgres peer local all postgres md5
- Reload postgresql service.
~$ /etc/init.d/postgresql reload [ ok ] Reloading postgresql configuration (via systemctl): postgresql.service.
- Or restart postgresql server.
$ sudo service postgresql restart
- Now login postgresql server again, the error will be fixed.
~$ psql postgres postgres psql (10.7 (Ubuntu 10.7-0ubuntu0.18.04.1)) Type "help" for help. postgres=#