Access database on instance from my pc without exposing it to internet

Question

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 ?

Answer 1

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.