Postgres cluster: password authentication failed for user “postgres”

Question

In my Postgres server have created a cluster using the command pg_createcluster listening on port 5433. To set the password for the user postgres I followed the following steps:

1. Edit the file nano /etc/postgresql/9.3//pg_hba.conf

Changing the line

host all all 127.0.0.1/32 md5

By line

host all all 127.0.0.1/32 trust

2. Change the password using the following commands

sudo -u postgres psql -h localhost -p 5433

postgres = # \ password postgres

Enter new password:

Enter it again:

postgres = # \ q

3. Edit the file nano /etc/postgresql/9.3//pg_hba.conf

Changing the line

host all all 127.0.0.1/32 trust

By line

host all all 127.0.0.1/32 md5

Shows no errors but to connect both the console and from the pgadmin3 triggers an error: password authentication failed for user "postgres". With this same process in the instance by default (main) I can connect successfully.

I appreciate any help because I do not find much documentation to work with instances of Postgres. Thanks in advance.

Answer 1

thanks for the reply. I've had in the pg_hba.conf with the configuration of your response:

# 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            md5

Changing the password:

# nano /etc/postgresql/9.3/<instance_name>/pg_hba.conf
# /etc/init.d/postgresql restart
# su - postgres
$ psql -U postgres -p 5433 -h localhost
Password for user postgres:

The authentication server prompts me to enter psql. The following configuration is that it allows me to enter the psql without password:

# 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

Changing the password:

# nano /etc/postgresql/9.3/<instance_name>/pg_hba.conf
# /etc/init.d/postgresql restart
# su - postgres
$ psql -U postgres -p 5433 -h localhost
postgres=# \password postgres
Enter new password:
Enter it again:
postgres=# \q
$ exit
# nano /etc/postgresql/9.3/<instance_name>/pg_hba.conf
# /etc/init.d/postgresql restart

Changing settings and entering again to psql:

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


# su - postgres
$ psql -U postgres -p 5433 -h localhost
Password for user postgres:
psql: FATAL:  password authentication failed for user "postgres"
FATAL:  password authentication failed for user "postgres"

Answer 2

First of all, you need reload configuration, so all your changes can be applied. Connection port might not be opened and besides that you haven't clearly shared config for local connections. This should work fine:

# 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            md5