Why is Postgres service failing?

Question

I'm setting up a ManageIQ appliance for development.  When I updated the 'v2_key' encryption key to be same as production, my db connection croaked.  Initially I got these errors:
Aug 31 13:26:59 miq-dev.chq.ei sh[3642]: PG::ConnectionBad: FATAL: password authentication failed for user "root"

Aug 31 13:26:59 miq-dev.chq.ei sh[3642]: FATAL: no pg_hba.conf entry for host "::1", user "root", database "vmdb_production", SSL off

I updated my pg_hba.conf to:

# TYPE  DATABASE    USER  ADDRESS METHOD
local   all     all             trust
local   all         all           peer map=usermap
local   replication all           peer map=usermap
hostssl all         all   all     md5
host    replication all   all     md5
::1     vmdb_production root            trust        # <--- added last line

Now I get a new set of errors:
● postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Mon 2020-08-31 18:39:44 EDT; 49s ago
  Process: 3648 ExecStart=/usr/bin/postmaster -D ${PGDATA} (code=exited, status=1/FAILURE)
  Process: 3645 ExecStartPre=/usr/libexec/postgresql-check-db-dir %N (code=exited, status=0/SUCCESS)
 Main PID: 3648 (code=exited, status=1/FAILURE)

Aug 31 18:39:44 miq-dev.chq.ei postmaster[3648]: 2020-08-31 18:39:44 EDT::5f4d7c30.e40:@:[3648]:LOG:  listening on IPv4 address "0.0.0.0", port 5432
Aug 31 18:39:44 miq-dev.chq.ei postmaster[3648]: 2020-08-31 18:39:44 EDT::5f4d7c30.e40:@:[3648]:LOG:  listening on IPv6 address "::", port 5432
Aug 31 18:39:44 miq-dev.chq.ei postmaster[3648]: 2020-08-31 18:39:44 EDT::5f4d7c30.e40:@:[3648]:LOG:  listening on Unix socket "/var/run/postgresq...QL.5432"
Aug 31 18:39:44 miq-dev.chq.ei postmaster[3648]: 2020-08-31 18:39:44 EDT::5f4d7c30.e40:@:[3648]:LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
Aug 31 18:39:44 miq-dev.chq.ei postmaster[3648]: 2020-08-31 18:39:44 EDT::5f4d7c30.e40:@:[3648]:LOG:  redirecting log output to logging collector process
Aug 31 18:39:44 miq-dev.chq.ei postmaster[3648]: 2020-08-31 18:39:44 EDT::5f4d7c30.e40:@:[3648]:HINT:  Future log output will appear in directory "log".
Aug 31 18:39:44 miq-dev.chq.ei systemd[1]: postgresql.service: main process exited, code=exited, status=1/FAILURE
Aug 31 18:39:44 miq-dev.chq.ei systemd[1]: Failed to start PostgreSQL database server.
Aug 31 18:39:44 miq-dev.chq.ei systemd[1]: Unit postgresql.service entered failed state.
Aug 31 18:39:44 miq-dev.chq.ei systemd[1]: postgresql.service failed.
Hint: Some lines were ellipsized, use -l to show in full.

which tells me postgresql service is not running.
I tried the suggestion from this SO post: Postgresql12.3 wont start on boot, systemd by adding a few lines to my postgresql.service file, but I still get the same ol errors.
Does anyone have any suggestions?  Thank you

a_horse_with_no_name · Accepted Answer

As documented in the manual the first "column" of the pg_hba.conf must be one of local, host, hostssl, hostnossl, hostgssenc or hostnogssenc. You can't put an IP address into the first column, it goes into the 4th column (if the first is not local).
The database needs to be put into the third column, then the username, then the IP address.
So this line:
::1     vmdb_production root            trust

should be:
host   vmdb_production   root  ::1  trust

This assumes you do have a Postgres user root which would be highly unusual. It might even indicate that you are trying to run that software under root which is a really, really bad idea (espcially in combination with trust)
But the root cause, why Postgres isn't starting, is the invalid entry in pg_hba.conf

Why is Postgres service failing?

One Answer

Add your own answers!

Ask a Question