Database security basics, just been hacked... feel naked... :)

Databases
1

I know we have been too careless about the security of this server. Most of the databases have been cleaned of their tables for a ransom.

Consequences are not dramatic since we have backups and that the info was not “sensible”.

Just to make sure we don’t get into trouble, I would like to hear about some security basics from some of you.

Thanks in advance!

Roger

2

What kind of database? Any idea how they got in?

3

It is a postgresql database.

Hard to tell, some databases that we don’t use often were not attacked. Those connected to web apps running as services on a Windows Server seemed to be the vulnerable ones…

4

Since it is peculiarly to “Like” the original message here, I just say “me too”

I am also curious about some best practices for PostgresSQL on a Windows Server.

5

SQL injection perhaps?

6

First of all, databases should NEVER be directly accessible from the internet. They should only allow connections from a trusted connection like localhost (if you are using an API) or SSH or VPN.

Next, make sure you install security patches (both windows and Postgres) as soon as you can. It can be a pain, but it saves you even more later.

7

+1
Any, and all security discussions are beneficial to all.

8

Glad to feel supported Thanks!

Just checked again the “attack pattern”, there is no apparent link between database use and the attack. But the weird thing is that some of them were left alone. For example, “baseplan” was cleared of its tables but “baseplanBK1”, “baseplanBK2” and “baseplanBK3” were still as I left them before Easter…

10

[quote=326689:@Roger St-Arneault]Glad to feel supported Thanks!

Just checked again the “attack pattern”, there is no apparent link between database use and the attack. But the weird thing is that some of them were left alone. For example, “baseplan” was cleared of its tables but “baseplanBK1”, “baseplanBK2” and “baseplanBK3” were still as I left them before Easter…[/quote]
If that’s the case, I would think that they breached Postgres itself and only managed to access that one database.

11

Windows Server? Open RDP-Port? Open DB Ports? No Reverse Proxy in-between? I wouldn’t run a Windows Server directly on the net! WIndows IIS has a plethora of .idx .idc etc. file extensions linked to classic ASP or .NET runtimes. They all shouldn’t (correction: must not) be enabled but they are in most cases.

12

There can almost* be never enough security. Especially when dealing with the Internet.

  • to have enough makes it unusable by anyone.
13

This is what we do:

  • The database server is a standalone machine. It only hosts Postgres, and it’s only accessible from localhost and the middleware server.
  • The software that accesses the database runs on the middleware server.
  • Access to middleware is restricted to the local network and VPN connections.
  • Access to the server is restricted via ssh.
  • Almost every SQL statement is run through prepared statements. (I mean the ones outside our ORM system.) The only exceptions (very rare) is where SQL injection is impossible (searching for an integer, for example.)
  • The Postgres accounts that can access the database are limited and have secure passwords.
  • Sensitive database is encrypted within the database and must be decrypted by the accessing software. The encryption key is nowhere on the SQL server.
  • Messages between middleware and the GUI are RSA-signed. The initial user password is RSA-encrypted.

I’m probably forgetting other steps we’ve taken, and until you know how it happened, we won’t know if any of this would have helped you.

14

Well the port 5432 was opened for sure… We closed it since then.

15

Thanks Kem, your input will be very usefull

16

@Kem: ssh on windows?

17

Is using a VPN essential? I mean, if I want the app to be accessible for many users with minimal restrictions and considering that I’m often geographically far from my clients, managing access through VPN appears to necessitates too much technical support. Am I right?

18

Our software is accessible only to our employees so it’s a little different. As always, there is a balance, and tradeoff, between security and convenience.

19

I guess that is something I should discuss with my actual an future clients. I’ll evaluate the situation with them and find the right equilibrium.

Thanks again!

20

You actually see no new foreign files on the server? Seems like 90% of the things we have seen over the years, basically winds up being a breach that allowed the hacked to write a new file. Which normally was a PHP while, that would then be able to include any of the config files, etc… that may have the database connection info.

Basically they install an entire little PHP Dashboard, or they refer to it as a PHP Shell…

This image here shows you a lot of the names of the ones they often use: http://prntscr.com/exxlki

Just part of the stuff we all have to deal with online.

21

Thanks Don, we’ll take a look a that for sure, we have a feeling that phpPGAdmin configuration files have been read and used to access the databases.

I took a look at the PostgreSQL Server logs and I note that the attack was targeting all databases but they were unable to “access” them because “cross-database references” were not implemented on these specific databases :

[quote]2017-04-18 04:05:18 EDT STATEMENT: DROP TABLE coursBK_2016_09_14.public.“cours_horaire_cours” CASCADE
2017-04-18 04:05:18 EDT ERROR: cross-database references are not implemented: “coursbk_2016_09_14.public.cours_horaire_cours”[/quote]

What does that mean? Anyone has a clue?