{"level":"error","ts":1686718150.9808686,"caller":"sqlstore/supplier.go:212","msg":"Failed to ping DB retrying in 10 seconds err=dial tcp ip-address:5432: connect: connection refused"}

Troubleshooting
1

Hi,

I am completing this setup.

I have chosen Postgres over mysql. I have chose postgres version 13. I have chosen MM version 5.31. I have installed the Postgres dB and the mattermost server both on the same machine.

When I complete step 9 of the “Install mattermost server on Rhel 7” I get the output below. Intros post I removed my actual ip address and substituted it with ip-address.

$ sudo -u mattermost ./bin/mattermost

{"level":"info","ts":1686718140.9781845,"caller":"utils/i18n.go:83","msg":"Loaded system translations for 'en' from '/opt/mattermost/i18n/en.json'"}

{"level":"info","ts":1686718140.9783003,"caller":"app/app.go:195","msg":"Server is initializing..."}

{"level":"info","ts":1686718140.9801776,"caller":"sqlstore/supplier.go:200","msg":"Pinging SQL master database"}

{"level":"error","ts":1686718140.980438,"caller":"sqlstore/supplier.go:212","msg":"Failed to ping DB retrying in 10 seconds err=dial tcp ip-address:5432: connect: connection refused"}

{"level":"info","ts":1686718150.9805777,"caller":"sqlstore/supplier.go:200","msg":"Pinging SQL master database"}

{"level":"error","ts":1686718150.9808686,"caller":"sqlstore/supplier.go:212","msg":"Failed to ping DB retrying in 10 seconds err=dial tcp ip-address:5432: connect: connection refused"}

However back in the “Install posgreSQL database” section step 18 tests that connect to the database with the correct credentials and it works.

$ psql --dbname=mattermost --username=mmuser --password

Password:

psql (13.11)

Type "help" for help.

mattermost=> \q

This is my first post in this forum and it is the first time I have looked to it for assistance. In fact It is the first time I have got my hands into a self hosting Mattermost process. I have seen some posts in this forum that describe similar issues. I have seen some answers but they don’t seem to align with my issue. For instance there was one regarding having special characters in the db user password could cause this issue. I have 3 special characters. However I see in the last example above I can connect to the database with that password.

Any assistance would be appreciated !

thanks,

Rochus

2

Hi @Rochus and welcome to the Mattermost forums!

Can you please confirm that PostgreSQL is running on the configured IP address and the configured port?
Please post the output of the following command:

# lsof -n -i | grep ^postg.*LISTEN
postgres  1150708        postgres    5u  IPv4 23749753      0t0  TCP 127.0.0.1:postgresql (LISTEN)

In this example it shows that my PostgreSQL instance is listening on localhost only (which is the default).
In your error message I can see that you masked the IP address (tcp ip-address:5432), so most likely you tried to connect to the PostgreSQL server using the IP of your server and PostgreSQL is not listening there. If that’s the case, then just change the database server IP from ip-address to localhost and try again.

3

hi @agriesser,

thanks for the quick reply.

When I run the command below, I don’t get any output

sudo lsof -n -i | grep ^postg.*LISTEN

$

However when I run this command below, I see that it postgres is listening on localhost. I can’t however see which port it is listening on. Is it 29711 ?

$ sudo lsof -n -i | grep post

**post**maste 29711 **post**gres 6u IPv4 933746574 0t0 TCP [127.0.0.1](http://127.0.0.1):**post**gres (LISTEN)

Nevertheless I have changed the ip address to local host in /opt/mattermost/config/config.json However I incur a different error.

sudo -u mattermost ./bin/mattermost

{"level":"info","ts":1686739984.5735385,"caller":"utils/i18n.go:83","msg":"Loaded system translations for 'en' from '/opt/mattermost/i18n/en.json'"}

{"level":"info","ts":1686739984.5736103,"caller":"app/app.go:195","msg":"Server is initializing..."}

{"level":"info","ts":1686739984.5752847,"caller":"sqlstore/supplier.go:200","msg":"Pinging SQL master database"}

{"level":"error","ts":1686739984.577172,"caller":"sqlstore/supplier.go:212","msg":"Failed to ping DB retrying in 10 seconds err=pq: unknown authentication response: 10"}

{"level":"info","ts":1686739994.577321,"caller":"sqlstore/supplier.go:200","msg":"Pinging SQL master database"}

{"level":"error","ts":1686739994.579107,"caller":"sqlstore/supplier.go:212","msg":"Failed to ping DB retrying in 10 seconds err=pq: unknown authentication response: 10"}

{"level":"info","ts":1686740004.579236,"caller":"sqlstore/supplier.go:200","msg":"Pinging SQL master database"}

{"level":"error","ts":1686740004.5810142,"caller":"sqlstore/supplier.go:212","msg":"Failed to ping DB retrying in 10 seconds err=pq: unknown authentication response: 10"}

{"level":"info","ts":1686740014.5811622,"caller":"sqlstore/supplier.go:200","msg":"Pinging SQL master database"}

{"level":"error","ts":1686740014.5830815,"caller":"sqlstore/supplier.go:212","msg":"Failed to ping DB retrying in 10 seconds err=pq: unknown authentication response: 10"}

Is this a step in the right direction ?

thanks,

Rochus

4

Yes, it now means that the connection to the port works, but the authentication is not working.
The message “unknown authentication response: 10” usually means that the algorithm is not understood.
Your pg_hba.conf (PostgreSQL configuration file for access) might have separate configuration for socket connections, so maybe switching the database server to 127.0.0.1 from localhost works better - can you please try that?
If it does not work, can you post the contents of your PostgreSQL server’s pg_hba.conf file (without the comments)?

5

Thanks @agriesser

I have tried the suggestion in your last post. However unfortunately there seems to be no change. The output is

{"level":"info","ts":1686797284.4967287,"caller":"utils/i18n.go:83","msg":"Loaded system translations for 'en' from '/opt/mattermost/i18n/en.json'"}
{"level":"info","ts":1686797284.496828,"caller":"app/app.go:195","msg":"Server is initializing..."}
{"level":"info","ts":1686797284.4991515,"caller":"sqlstore/supplier.go:200","msg":"Pinging SQL master database"}
{"level":"error","ts":1686797284.5004213,"caller":"sqlstore/supplier.go:212","msg":"Failed to ping DB retrying in 10 seconds err=pq: unknown authentication response: 10"}
{"level":"info","ts":1686797294.5006568,"caller":"sqlstore/supplier.go:200","msg":"Pinging SQL master database"}
{"level":"error","ts":1686797294.5021532,"caller":"sqlstore/supplier.go:212","msg":"Failed to ping DB retrying in 10 seconds err=pq: unknown authentication response: 10"}
{"level":"info","ts":1686797304.5024035,"caller":"sqlstore/supplier.go:200","msg":"Pinging SQL master database"}
{"level":"error","ts":1686797304.5040696,"caller":"sqlstore/supplier.go:212","msg":"Failed to ping DB retrying in 10 seconds err=pq: unknown authentication response: 10"}

The contents of my pg_hba.conf without the header comments is below



# 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            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 trust
                
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

For completeness this is the value of DataSource key in my config.json file, albeit with a substituted password

"DataSource": "postgres://mmuser:mmuser_password@127.0.0.1:5432/mattermost?sslmode=disable\u0026connect_timeout=10",

Once again thanks for your help

6

This is the problematic line, it forces local connections to use scram-sha-256 and I’m not sure if Mattermost supports that at this time and if it does, how to modify the DSN, but after googling for a minute I found this open PR which indicates that the library used for doing the PostgreSQL connection does not support this authentication scheme:

You could try to add the following line above the problematic line:

host    mattermost             all             127.0.0.1/32            md5

This will use the md5 authentication scheme for all connections to the database named mattermost (please adjust if this is not really your database name).

7

thanks @agriesser

I have made the change to the pg_hba.conf file, and restarted the Postgres service. Unfortunately the error remains.

This is the file contents.

# configuration parameter, or via the -i or -h command line switches.



# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    mattermost       all            127.0.0.1/32            md5
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 trust
                
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

I have checked the name of the database and it is mattermost like the document instructs

postgres=# \l
                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
------------+----------+----------+-------------+-------------+-----------------------
 mattermost | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres         +
            |          |          |             |             | postgres=CTc/postgres+
            |          |          |             |             | mmuser=CTc/postgres

I read on this forum that database passwords can’t have special characters. Mine has several. However from the sql side I was able to access the DB with that password.

Thanks for your help

8

Interesting find:

If I understand that correctly, it could still revert to scram-sha-256 if the user account was created with this password mechanism.
You can verify that when you connect to your database server and run the following query (adjust the name accordingly):

postgres=# select rolpassword from pg_authid where rolname='mmuser';
             rolpassword
-------------------------------------
 md546cf1f4d8xxxxx
(1 row)

As you can see here on my system, the password uses the md5 method, on your system it’s probably scam-sha-256. If this is the case, can you please change the value of password_encryption in your postgresql.conf file to md5, restart the database server, then change the password of the mmuser postgresql user and verify that it’s now saved with an md5 prefix in the pg_authid table? If that’s the case, you can then change the value of password_encryption back to the previous value (scram-sha-256) and restart the server again. As long as you do not change the mmuser password, you should be safe. If this PostgreSQL server is only used for Mattermost, then you can also leave this setting at md5.

9

thanks @agriesser Much appreciated. Those instructions got the issue resolved.

10

Awesome, thanks for confirming that this helped!