0

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

junior_ali
  • 11
  • 3
  • 1
    "host all all all trust" If you haven't already been hacked (which could explain the problem) you will be soon. – jjanes Sep 25 '22 at 19:03
  • appreciate the comment, but this is a fairly new project and i'm just trying to make it work no matter what. – junior_ali Sep 25 '22 at 21:11
  • 1
    It takes very little time for hackers to find you, and then you might get all kinds of hard to understand behaviors. – jjanes Sep 25 '22 at 23:54
  • looks like you know your stuff around postgresql ! could max connections (or concurrent connections) cause postgres to start refusing connections like this? because while in development i refresh over and over again all the time. – junior_ali Sep 26 '22 at 02:00
  • A max_connections violation would produce a different error message--it would initially accept the connection, then close it with a descriptive error message. A connection refused means either the database is not running (which your netstat suggest is not the case), or some networking equipment is not passing the connection on to the database (or maybe your connection is going to the wrong host or port). – jjanes Sep 26 '22 at 15:50
  • You say you can't connect to the database anymore. Where are you trying to connect from, and with what tool? Take all this other stuff out of the picture by trying to connect directly from the db server machine, using `psql`, if you are not already. – jjanes Sep 26 '22 at 15:52
  • so i tried connecting directly, it was not successfull, so i restarted postgres, then it was successfull. but this is the problem, everything seems fine, but then after some time the same connection refused error happens, and i have to restart postgresql so everything goes back to working fine, and the loop repeats. this happens in every server i've created on digital ocean. this is really strange. – junior_ali Sep 27 '22 at 18:09
  • Look in the db server's log file. – jjanes Sep 27 '22 at 18:25
  • so i found an interesting pattern in the log files ! i edited the question to show it, what is your opinion about it ? – junior_ali Sep 27 '22 at 21:06
  • the ip adresse is not the same it has been changed, even then who would hack a random learning database on SO lol. but have u got any idea about the errors in the log file ? – junior_ali Sep 28 '22 at 03:21
  • do i have to create a user ? because it tells me "role *** does not exist" ! – junior_ali Sep 28 '22 at 05:05
  • hey sorry man, i think some low life was trying to hack the database ! you are right ! sorry again . – junior_ali Sep 28 '22 at 20:34
  • backend cpu was 100%, it's probably being exploited for mining since i found kdevtmpfsi malware. i am once again proven ignorant. i apologise. – junior_ali Sep 28 '22 at 21:07

1 Answers1

1

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 .

junior_ali
  • 11
  • 3