0

Background: I'm running a last version postgresql database on an cloud instance which for the moment has almost nothing on it and since two or three weeks, It started to shutdown itself every 4 or 5 days. It my first real database and It must communicate with my production backend on the same instance as the db, and a test backend on my pc which interacts with the db as well (in local).

I found with a little top that a really weird process were taking 100% of my cpu usage (on the user postgres). I found on other posts that I infact was hacked by some people using my instance to probably mine cryptocurrencies... Though because postgres's user wasn't root, it seems the hacked couldn't do much except for running his process.

The fact is the problem was certainly because of a too week postgres password and surely because my db was exposed to the internet, so that I can access it from my development environment in local.

I found on the web that there are also no reasons to expose your db to the internet. But how could I connect myself to the db on the instance from my pc then ? Is there any other ways ?

I was going to reinstall my instance from scratch and new ip, after backing up the db, and then reinstall the db, and change the password of the users, but will it be enough ? What are the most secure ways to do this ?

Chetrit
  • 33
  • 2
  • 8

1 Answers1

2

There are several ways you can secure your database:

  • Add a restrictive pg_hba_conf entry that allows access only from a single IP address or a small address range.

  • Use a strong database password (you figured that out). To make brute force attacks harder, set the parameter password_encryption = scram-sha-256 before changing the password.

  • Use SSL certificates to authenticate the client. The documentation has the details. That way, nobody can log in unless they have your certificate.

    That requires that the database supports SSL, and that it has a CA certificate (parameter ssl_ca_file) so that it can verify your client certificate. I am not sure if your hosting provider does support that.

About salvaging your data from the compromised database: run a pg_dump -s to export the database, then read through it carefully and identify and eliminate all malicious objects you see. Then use pg_dump -a to dump the data and examine these as well. If you are satisfied that you have removed all contamination, restore it to a new database.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thanks, I manages to connect to my db from my pc using ssh access, like if the DB was in local, this solves my first problem of exposing the db to the internet, however, is it still necessary to restrict ip address in pg_hba conf file ? And when you mean strong database password, are we talking about the "postgres"'s user password, or the user I created to access my db ? or both ? And finally, If my db isn't exposed to internet, even if my postgres's user has a non-secure password, can a hacker still try to access it even if he doesn't have ssh access ? – Chetrit May 17 '21 at 17:03
  • If the database is hosted somewhere else, it *has* to be exposed to the internet, because otherwise you wouldn't be able to access it. The fact that you can connect with SSL is *not* enough. That only encrypts the connection. You'd have to change `pg_hba_conf` to use `cert` authentication. *All* database users that can connect remotely need a very good password. – Laurenz Albe May 17 '21 at 17:12