Unable to connect to PostgreSQL server: FATAL: Peer authentication failed for user "postgres"
Solution 1
Got my answer from here:
local all postgres md5
and then restart the service:
sudo systemctl restart postgresql.service
Solution 2
Looks like you're using "peer authentication" where you want to be using "password authentication". With the default pg_hba.conf, you can switch to password authentication by specifying a hostname, or "127.0.0.1".
See the Postgres documentation for authentication methods.
Comments
-
Run almost 2 years
How can how to access postgres databases from adminer?
I have changed the password for the user
postgres
:$ sudo -u postgres psql $ postgres=# alter user postgres password 'secret';
Result:
ALTER ROLE
But I still get this error on the adminer:
Unable to connect to PostgreSQL server: FATAL: Peer authentication failed for user "postgres"
Any ideas why?
I have these two users:
postgres=# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} root | Superuser, Create role, Create DB | {}
But I did not set password for user
root
when I created it with this command:sudo -u postgres createuser --interactive
Output (why doesn't it ask for password??):
Enter name of role to add: root Shall the new role be a superuser? (y/n) y
But I still get the error on adminer:
Unable to connect to PostgreSQL server: FATAL: Peer authentication failed for user "root"
EDIT:
THis is some bits of my
pg_hba.conf
sudo nano /etc/postgresql/9.5/main/pg_hba.conf # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # IPv4 local connections: host all all 127.0.0.1/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 postgres peer #host replication postgres 127.0.0.1/32 md5 #host replication postgres ::1/128 md5
I changed
peer
toident
:# Database administrative login by Unix domain socket local all postgres ident # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all ident # IPv4 local connections: host all all 127.0.0.1/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 postgres peer #host replication postgres 127.0.0.1/32 md5 #host replication postgres ::1/128 md5
Then restarted my machine. but still no luck.