The connection failed because by default psql
connects over UNIX sockets using peer
authentication, that requires the current UNIX user to have the same user name as psql
. So you will have to create the UNIX user postgres
and then login as postgres
or use sudo -u postgres psql database-name
for accessing the database (and psql
should not ask for a password).
If you cannot or do not want to create the UNIX user, like if you just want to connect to your database for ad hoc queries, forcing a socket connection using psql --host=localhost --dbname=database-name --username=postgres
(as pointed out by @meyerson answer) will solve your immediate problem.
But if you intend to force password authentication over Unix sockets instead of the peer method, try changing the following pg_hba.conf
* line:
from
# TYPE DATABASE USER ADDRESS METHOD
local all all peer
to
# TYPE DATABASE USER ADDRESS METHOD
local all all md5
-
peer
means it will trust the identity (authenticity) of UNIX user. So not asking for a password. -
md5
means it will always ask for a password, and validate it after hashing withMD5
. -
trust
means it will never ask for a password, and always trust any connection.
You can, of course, also create more specific rules for a specific database or user, with some users having peer
and others requiring passwords.
After changing
pg_hba.conf
you'll need to restart PostgreSQL if it's running. E.g.sudo service postgresql restart
trust
connection by adding inpg_hba.conf
file
local all postgres trust
- Restart postgresql service
sudo service postgresql restart
-
psql -U postgres
-
At the
postgres=#
prompt, change the user namepostgres
password:
ALTER USER postgres with password ‘new-password’;
- Revert the changes in
pg_hba.conf
file fromtrust
tomd5
and restartpostgresql
.
The file pg_hba.conf
will most likely be at /etc/postgresql/9.x/main/pg_hba.conf
To check location of pg_hba.conf connect to postgres db using psql then type SHOW hba_file;
command.
After change pg_hba.conf file, you can execute SELECT pg_reload_conf();
or pg_ctl reload with superuser instead of restart postgresql service.
* Source
Thank you so much! Helped a lot!