DEV Community: Chris Tse

Oops, My Database Got Hacked

Chris Tse — Mon, 24 Feb 2025 07:19:13 +0000

I recently started self-hosting personal apps on my Unraid server, with a configured Postgres database for storage. I woke up one morning discovering that I could no longer connect to the subdomains I had reverse proxied to one of my self-hosted apps, so I log in to the VPN to connect to my server and check the logs. Immediately, I noticed that it was throwing some errors about not being able to find the specific database I had created for it.

That's weird, I literally just created it last night and had it up and running. What could have happened?

I fire up pgAdmin to take a look at my databases, of which I should have several... wait, what? Where did they all go?

What is this database? Where did the rest go?

I Googled the name of the database, looked through the Postgres container logs, and checked the contents of that unfamiliar database. Sure enough, I was compromised - I could see commands that dropped all my databases and added this one. Inside was a ransom note demanding cryptocurrency payment in exchange for restoring my data.

How did this happen?

Needless to say, database security is important. And here, I learned a lesson that it's equally important even for a self-hosted container meant for fun. As someone still relatively green to self-hosting, having only had my Unraid server up for about a year and a half, I didn't quite have my bases covered. While I didn't lose much valuable data, it did give me a good lesson on what I did wrong.

Exposing the database to the public internet

I had port forwarded the Postgres IP so that I could connect to it anywhere, even while I was out so that I could mess with it while sitting at a coffee shop without having to go through the VPN. My database now sat unprotected on the public internet, guarded only by a username and password. That was my first mistake.

Now I admit, this is advice I've very commonly seen. If this were a proper production app, I of course would never have done this. I figured my small toy database wouldn't attract any attention. No one really knew my IP, nor that I would have Postgres running, right?

Search results as of February 2025

Turns out, my experience wasn't unique. According to a threat research report from Palo Alto Networks, automated bots have been targeting exposed Postgres and MySQL databases since at least 2019. With over 600,000 databases exposed on Shodan alone as of writing this, attackers have plenty of targets - and they're constantly scanning for more.

I've linked to other articles that talk about this issue more in-depth, such as the exact method and queries that ran on the database for the exact attack that I was a victim of at the end of this post if you are interested.

Not securing the root user

Going off the same assumption as above, since no one should know or care that I have a database up, surely having the root postgres user with a simple password shouldn't be that big of a deal, right?

Obvious as it seems in hindsight, this was something I overlooked. I had the password set to something simple in the case that I wanted to log in and play around with it more easily. Of course, I've since recreated the container with a much more secure root user password.

Not having backups

While I didn't have mission-critical data in there, the loss still hurt. I had a linkding instance, a self-hosted bookmark manager, where I had stored a couple dozen interesting articles to read later. All gone.

I knew about security best practices for production databases, but I had never bothered with proper backups for my personal projects. It just hadn't seemed necessary - until now. Luckily, it's actually pretty simple to get started. Both Postgres and MySQL have dump commands that will create a big SQL file containing all the commands required to recreate the thing you just dumped.

To mitigate losing any more data in the future, I whipped up a quick bash script to run an hourly pg_dumpall , copy the dump out of the container, gzip it, and put it in a backup folder on the user shares. I also have the script delete the oldest one each time once I have 30 days worth of hourly backups, giving me plenty of leeway for restoring data should I need it in the future.

A lesson on security, paid up front

The main takeaways here were:

Don't expose your database to the open internet if you can help it
If you must, at least make sure your root user is secured with a strong password
Make backups in the case of catastrophe

These things likely would just be common sense for a seasoned database administrator or backend developer. But with the prevalence of the crypto ransom attacks on exposed databases, it might just not be common enough.

Certainly wasn't for a hobby self-hoster like myself.

Additional Reading

How to Use Heroku PostgreSQL for Local Development with Knex.js

Chris Tse — Sun, 01 Dec 2019 20:45:23 +0000

Local vs. Hosted Databases

Generally, folks tend to develop apps using a local database on their own machine. It can then be swapped out for a real one in production. This is usually done via the use of environment variables. But, sometimes you may want to have access to a database hosted online that you can access from many places. Or you just want a hosted database that you can use without having to worry about all the things that come with maintaining your own database.

Managed databases are great for this, but they can be costly. Most providers such as Digital Ocean and Amazon RDS will run you about $15 (USD) per month to keep a database going. This can be fairly pricey for someone wanting to just get their feet wet with Postgres.

Heroku Postgres

Heroku to the rescue! For every app hosted on Heroku, you have access to a free tier Postgres instance. You can read more about the limitations of the free tier, but it will be more than enough to get started with developing your app.

When I was working on a personal project, I wanted to be have my database accessible regardless of which machine I was working on. This post aims to document the various steps I needed to get this to work, and hopefully it can be of some help to a few of you!

Prerequisites

You will need to have the Postgres client installed on your machine to connect to a database from your application.

The installation method differs between each OS so I won't go into too much detail here, but it is well documented. Personally, I use Mac at home and Fedora at work.

For Mac, I've found that Postgres.app was the least hassle to get everything running.

For Fedora, you can follow the official docs from Fedora.

Getting Started

Here are the basic steps to get started with using the free Heroku Postgres for local development. In the later parts of this post, I will be using Node.js and the Knex.js query builder library for working with the database. The steps up to getting the connection URL should be applicable to any other kind of environment.

1. Create a Heroku App

First, create a Heroku account if you don't already have one.

Then, you can create a new Heroku app (or use an existing one). You can do this by logging into the dashboard then at the top right, click New > Create new app. Enter an app name and select the region closest to your location.

Once created, click into it. Under the "Overview" tab, you should see the "Installed add-ons" section. Click Configure add-ons and search for "Postgres" in the search bar, then click the "Heroku Postgres" result that comes up. A modal should come up. Make sure the "Hobby Dev - Free" option is selected and click the "Provision" button.

You should now have a Heroku app created with a free Postgres instance attached to it.

2. Install the Heroku CLI

For the next step, install the Heroku CLI. You can use this to get the database credentials and interact with the database via the terminal. You can find instructions on how to install it in their official documentation.

Once installed run the following to log in to your account via the CLI:

$ heroku login

Follow the on-screen prompts to log in with your account.

3. Getting the database credentials

There are various ways to connect to a database from the app you are developing, depending on the language and library/framework. A common one is to use the connection string, which looks kind of like a URL that starts with postgres://

First verify you have access to the app you created in the previous steps by running this command:

$ heroku apps

You should see a list of all the apps on your account. Note down the name of the one you created in the previous step.

You can then check to make sure Postgres is attached correctly to the app. To do this, run this command, where <app-name> is the name of the app created in the first step:

$ heroku pg:info -a <app-name>

=== DATABASE_URL
Plan:                  Hobby-dev
Status:                Available
Connections:           0/20
PG Version:            11.6
Created:               2019-11-20 16:58 UTC
Data Size:             8.4 MB
Tables:                2
Rows:                  1/10000 (In compliance)
Fork/Follow:           Unsupported
Rollback:              Unsupported
Continuous Protection: Off
Add-on:                postgresql-cubed-73038

You should see some output like what is shown above. Now that we've verified Postgres is accessible on this app, we can now get the database connection information with this command:

$ heroku pg:credentials:url -a <app-name>

You should see a Connection URL in the output that starts with postgres://. That is what I will be using for the rest of this article. For other use cases, the connection info string should have the required info.

Connecting via Knex.js

I won't be going into the details on how to set up Knex.js. You can view it at their official documentation.

If you try to use the connection URL as is via the exported connection property and attempt to run a migration, you should see an error along these lines:

error: no pg_hba.conf entry for host "<ip address>", user "<username>", database "<database name>", SSL off

There are a couple of steps to be done:

Open the pg_hba.conf file for your local Postgres installation, and add this line to it to allow postgres to make the connection this database:

host    all             all             <ip-address>/32        trust

Second, you will need to add this line to your knexfile.js where the pg module is required:

pg.defaults.ssl = true;

Finally, set this environment variable so that Node.js will not check for a certificate:

process.env.NODE_TLS_REJECT_UNAUTHORIZED = 0;

This can be done in the code as shown above, via setting it before running your app, or using a dotenv file.

Note that this is not secure for use in production, and to only use for local development.

Give your migration another try, and it should now succeed.

Hopefully this post was able to help you get started with using Postgres on Heroku's free tier. Let me know in the comments if you have any questions or need clarifications.

Alternative terms for "Tech Talk"?

Chris Tse — Fri, 05 Jul 2019 17:07:49 +0000

For a community hosting events targeted towards beginners, many of whom who are looking to break into the industry and may have never even written code before, what is a good alternative to the term "tech talk"? While it's short and concise which makes it easy to put into an event title, it can seem intimidating. Any suggestions?