Postgresql connect ECONNREFUSED after random amount of time-CodePudding

i checked so many other questions but none have this specific problem.

i have a NodeJs Express Postgresql app deployed on Digital Ocean, Ubuntu 22.04 x64, Droplet. the app is running on port 8080.

I used nginx and pm2 and postgresql version 14.

at first everything works normally but sometimes it randomly gives me the ECONNREFUSED error (shows up on pm2 logs) and i can't connect to the database anymore (probably worth mentioning that it happens especially after a another person accesses the site).

i followed this tutorial to set up nginx : https://dev.to/logrocket/how-to-run-a-node-js-server-with-nginx-588

and this tutorial for seeting up postgresql on the server : https://www.youtube.com/watch?v=zujeb8VWncI&t=80s

this is nginx config file

    server {
index index.html index.htm index.nginx-debian.html;

    listen 0.0.0.0:80;
    server_name 165.257.202.2;

    location / {
      proxy_set_header X-Real-IP $remote_addr;
      proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
      proxy_set_header Host $http_host;
      proxy_set_header X-NginX-Proxy true;

      proxy_pass http://165.257.202.2:8080/;
      proxy_redirect off;
    }

    location /api/registration {
      proxy_set_header X-Real-IP $remote_addr;
      proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
      proxy_set_header Host $http_host;
      proxy_set_header X-NginX-Proxy true;

      proxy_pass http://165.257.202.2:8080/api/registration;
      proxy_redirect off;
    }

 }

i only changed listen addresses in postgres.conf

# - Connection Settings -

listen_addresses = '*'      # what IP address(es) to listen on;
                    # comma-separated list of addresses;
                    # defaults to 'localhost'; use '*' for all
                    # (change requires restart)
port = 5432             # (change requires restart)

and i added this in pg_hba.conf (at first it was just the first one but when i couldn't solve it i added the rest)

host    all             all             all                     trust
host    all             all              0.0.0.0/0                       md5
host    all             all              ::/0                            md5

this is the line it writes on pm2 logs

| connect ECONNREFUSED 165.257.202.2:5432

command service postgresql status says psotgresql is active

command ufw status verbose says 'inactive'

command netstat -anp | grep postgres outputs this :

tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      14262/postgres
tcp6       0      0 :::5432                 :::*                    LISTEN      14262/postgres
udp6       0      0 ::1:56997               ::1:56997               ESTABLISHED 14262/postgres
unix  2      [ ACC ]     STREAM     LISTENING     179264   14262/postgres       /var/run/postgresql/.s.PGSQL.5432

it's Important to say the issue gets solved when i restart postgresql and pm2, but after a while the issue happens again (probably after a user accesses the website which uses this backend)

Would really appreciate any help i'm getting a bit desprate with this issue

EDIT :

this is the error that i found in the log file which is repeated many times

    2022-09-25 16:30:17.966 UTC [28724] LOG:  database system is ready to accept connections
2022-09-25 17:34:04.468 UTC [29907] [unknown]@[unknown] FATAL:  unsupported frontend protocol 0.0: server supports 3.0 to 3.0
2022-09-25 17:34:04.618 UTC [29908] [unknown]@[unknown] FATAL:  unsupported frontend protocol 255.255: server supports 3.0 to 3.0
2022-09-25 17:34:04.782 UTC [29909] [unknown]@[unknown] FATAL:  no PostgreSQL user name specified in startup packet
2022-09-25 17:34:05.038 UTC [29915] cms@cms FATAL:  role "cms" does not exist
2022-09-25 17:34:05.042 UTC [29913] etl@etl FATAL:  role "etl" does not exist
2022-09-25 17:34:05.045 UTC [29916] admin@admin FATAL:  role "admin" does not exist
2022-09-25 17:34:05.095 UTC [29922] backup@backup FATAL:  role "backup" does not exist
2022-09-25 17:34:05.118 UTC [29928] admin@admin FATAL:  role "admin" does not exist
2022-09-25 17:34:05.125 UTC [29930] crm@crm FATAL:  role "crm" does not exist
2022-09-25 17:34:05.129 UTC [29931] django@django FATAL:  role "django" does not exist
2022-09-25 17:34:05.149 UTC [29938] app@app FATAL:  role "app" does not exist
2022-09-25 17:34:05.174 UTC [29919] postgres@postgres LOG:  could not receive data from client: Connection reset by peer
2022-09-25 17:34:05.176 UTC [29918] postgres@postgres LOG:  could not receive data from client: Connection reset by peer
2022-09-25 17:34:05.178 UTC [29914] postgres@postgres LOG:  could not receive data from client: Connection reset by peer
2022-09-25 17:34:05.185 UTC [29917] postgres@postgres LOG:  could not receive data from client: Connection reset by peer
2022-09-25 17:34:05.187 UTC [29912] postgres@postgres LOG:  could not receive data from client: Connection reset by peer

then it continues with the last line like that until i restart postgresql! i did not create any user on postgresql or on the server. i did everything with root.

Another server (droplet) postgresql log file with the same problem shows this mostly :

2022-09-26 06:58:28.310 UTC [740] LOG:  database system is ready to accept connections
2022-09-26 09:58:24.542 UTC [3402] [unknown]@[unknown] FATAL:  unsupported frontend protocol 0.0: server supports 3.0 to 3.0
2022-09-26 09:58:24.699 UTC [3403] [unknown]@[unknown] FATAL:  unsupported frontend protocol 255.255: server supports 3.0 to 3.0
2022-09-26 09:58:24.876 UTC [3404] [unknown]@[unknown] FATAL:  no PostgreSQL user name specified in startup packet
2022-09-26 10:30:33.024 UTC [3784] [unknown]@[unknown] FATAL:  unsupported frontend protocol 0.0: server supports 3.0 to 3.0
2022-09-26 10:30:33.164 UTC [3785] [unknown]@[unknown] FATAL:  unsupported frontend protocol 255.255: server supports 3.0 to 3.0
2022-09-26 10:30:33.308 UTC [3786] [unknown]@[unknown] FATAL:  no PostgreSQL user name specified in startup packet
2022-09-26 13:51:49.510 UTC [6934] [unknown]@[unknown] FATAL:  unsupported frontend protocol 0.0: server supports 3.0 to 3.0
2022-09-26 13:51:49.711 UTC [6935] [unknown]@[unknown] FATAL:  unsupported frontend protocol 255.255: server supports 3.0 to 3.0
2022-09-26 13:51:49.926 UTC [6936] [unknown]@[unknown] FATAL:  no PostgreSQL user name specified in startup packet
2022-09-26 14:42:38.833 UTC [7997] [unknown]@[unknown] FATAL:  unsupported frontend protocol 0.0: server supports 3.0 to 3.0
2022-09-26 14:42:38.993 UTC [7998] [unknown]@[unknown] FATAL:  unsupported frontend protocol 255.255: server supports 3.0 to 3.0
2022-09-26 14:42:39.158 UTC [7999] [unknown]@[unknown] FATAL:  no PostgreSQL user name specified in startup packet
2022-09-26 17:13:03.947 UTC [10100] [unknown]@[unknown] LOG:  could not accept SSL connection: unsupported protocol
2022-09-26 17:13:03.947 UTC [10100] [unknown]@[unknown] HINT:  This may indicate that the client does not support any SSL protocol version between TLSv1.2 and TLSv1.3.
2022-09-26 18:58:23.879 UTC [11540] [unknown]@[unknown] FATAL:  unsupported frontend protocol 0.0: server supports 3.0 to 3.0

EDIT 2

found another very interesting thing : this happens to the memory of the server even though the database isn't that big !! enter image description here

CodePudding user response：

thanks to @jjanes comments and this github post :

there was a process that was using the CPU at 100% capacity called "kdevtmpfsi", which turns out to be a mining malware.

i also found a cronjob to re-run it after i restart postgresql which explains how db "randomly" stops connecting.

i was able to solve the issue by changing postgres default password .