Failed to ping DB: Password authentication failed for user \"mmuser"\

Nov 28 13:02:04 mattermost[15837]: {"timestamp":"2023-11-28 13:02:04.980 -06:00","level":"info","msg":"Server is initializing...","caller":"platform/service.go:173","go_version":"go1.20.7"}

Nov 28 13:02:04 mattermost[15837]: {"timestamp":"2023-11-28 13:02:04.980 -06:00","level":"info","msg":"Pinging SQL","caller":"sqlstore/store.go:251","database":"master","dataSource":"postgres://%2A%2A%2A%2A:%2A%2A%2A%2A@localhost/mattermost_test?binary_parameters=yes&connect_timeout=10&sslmode=disable"}

Nov 28 13:02:04 mattermost[15837]: {"timestamp":"2023-11-28 13:02:04.984 -06:00","level":"error","msg":"Failed to ping DB","caller":"sqlstore/store.go:259","error":"pq: password authentication failed for user \"mmuser\"","retrying in seconds":10}

Can anyone assist me with this? I have no idea why there’s an issue with the password auth.

Did you do:

CREATE USER mmuser WITH PASSWORD ‘mmuser-password’;

And set that password in DataSource in /opt/matermost/config/config.json?

https://docs.mattermost.com/install/prepare-mattermost-database.html

1 Like

Hello, thank you for the reply.

Yes, I’ve already set up the PSQL database and made sure that I’ve followed everything correctly.

"DriverName": "postgres",
"DataSource": "postgres://MMUSER:MMPASSWORD@localhost:5432/mattermost?sslmode=disable&connect_timeout=10",

Is PostGres installed on the same host as Mattermost or different? Can you use the psql command to log in?

Yes, PSQL is installed on the same host. I can also log in to my MM PSQL database perfectly fine.

So:

psql -h localhost -U <MMUSER>

and providing works?

Did you edit pg_hba.conf as per the link above?

psql by default connects to a local socket. “localhost"5432” is connecting to a TCP socket. So I’m guessing your postgres doesn’t know to listen on that socket, or something like SELinux isn’t allowing the connection.

Regarding the pg_hba.conf file, the only host connections that were changed to trust were the ones shown in the docs.

What the docs show: ---------------------------------------------------------------------------------
local all all trust

host all all ::1/128 trust

What my pg_hba.conf file looks like: ----------------------------------------------------------

DO NOT DISABLE!
If you change this first entry you will need to make sure that the
database superuser can access the database using some other method.
Noninteractive access to all databases is required during automatic
maintenance (custom daily cronjobs, replication, and similar tasks).

Database administrative login by Unix domain socket
local all postgres peer

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

As for what happens when I run psql -h localhost -U <MMUSER>, I get the following:
psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL: password authentication failed for user "mattermost"

It seems to not accept my database user, and only the database name when I run your command. My MM DB user is “mmuser” since I didn’t feel the need to change it. When I run that command with my actual MM DB user I get the following: psql: error: connection to server at "localhost" (127.0.0.1), port 5432 failed: FATAL: database "mmuser" does not exist. However, when I run your command with the actual database name “mattermost” it gets to the password authentication error.

“psql by default connects to a local socket. “localhost"5432” is connecting to a TCP socket. So I’m guessing your postgres doesn’t know to listen on that socket, or something like SELinux isn’t allowing the connection.”

The original port # for my PSQL DB was 5433 but for some reason MM was trying to connect to 5432 so I changed my PSQL DB port # to 5432 which fixed my old connection error where MM would try to connect to 5432 even though my DB port was 5433. After I solved that error I’m now experiencing this error which I genuinely don’t know how to solve. your postgres doesn’t know how to listen on that socket does this have to do with the /main/postgresql.conf file where listen_addresses is located?

Thank you in advance for the reply.

Try a pg_hba.conf like:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# 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
# IPv6 local connections:
host    all             all             ::1/128                 trust

Your IPv4 statement is requiring scram-sha-256.

Also, it’s odd that your instance was listening on 5433 instead of 5432. Do you have more than one postgres running? Was this a fresh install of the OS? If someone else was doing something odd, and you’re coming in after the fact, you could get stuff like this.

Apologies for the late response.

I tried what you suggested and it appears the error is still present.

systemctl status mattermost.service showed the following:
● mattermost.service - Mattermost
Loaded: loaded (/lib/systemd/system/mattermost.service; enabled; vendor preset: enabled)
Active: activating (auto-restart) (Result: exit-code) since Sun 2023-12-03 13:10:47 CST; 9s ago
Process: 6011 ExecStart=/opt/mattermost/bin/mattermost (code=exited, status=1/FAILURE)
Main PID: 6011 (code=exited, status=1/FAILURE)
CPU: 294ms

while journalctl -xeu mattermost.service showed:

Dec 03 13:12:38 mattermost[6297]: {"timestamp":"2023-12-03 13:12:38.433 -06:00","level":"info","msg":"Server is initializing...","caller":"platform/service.go:173","go_version":"go1.20.7"}

Dec 03 13:12:38 mattermost[6297]: {"timestamp":"2023-12-03 13:12:38.434 -06:00","level":"info","msg":"Pinging SQL","caller":"sqlstore/store.go:251","database":"master","dataSource":"postgres://%2A%2A%2A%2A:%2A%2A%2A%2A@localhost/mattermost_test?binary_parameters=yes&connect_timeout=10&sslmode=disable"}

Dec 03 13:12:38 mattermost[6297]: {"timestamp":"2023-12-03 13:12:38.437 -06:00","level":"error","msg":"Failed to ping DB","caller":"sqlstore/store.go:259","error":"pq: password authentication failed for user \"mmuser\"","retrying in seconds":10}

Also, regarding your question about why PSQL was running on 5433 instead of 5432, when I installed Ubuntu it had already came with PSQL version 13. I did not know this at the time so I went and installed version 16. This is most likely why V16 wasn’t on the 5432 port as V13 was taking it. Having two different versions of PSQL obviously messed a few things up for me so I completely uninstalled V13 and I now only have PSQL V16. To answer the other question: yes it was a fresh install of Ubuntu.

Thank you in advance.

Those lines from journalctl… they’re literally what is echoed out? “%2a” is an asterisk… it looks like your config.json is borked up somehow.

If you can connect to postgres with mmuser and , then that should work in config.json There’s probably a way in postgres to troubleshoot connection issues… I can only guess that the password set in postgres isn’t what’s actually being passed to it.

1 Like

Yeah every time I check journalctl that’s what is outputted. How could it be that the password set in the config file isn’t the one being passed to postgres? I’ve been thinking about completely uninstalling psql v16 and retrying the deployment unless that doesn’t really have anything to do with the issue at hand? I’ll try checking if the config file that I’m editing is the one that’s actually being used.