 OK. Hello, everyone. Thank you for coming to my presentation about database security for developers. My name is Leo Grabicki, and today I'm going to talk a bit about security. And first, let me give you a bit of a background. I spent the last 15 years working for e-commerce and finance industries. So as you can imagine, those industries are kind of keen about security, and that's important. Currently, I'm a co-founder of Web Stoting SRO, where we help our clients to build e-commerce projects. And since we are in e-commerce market, we have PCI compliance, and they have some strict security policies, some restrictions for us that we have to follow. And my interests are solution architecture, system integration, and e-commerce. And before we'll go deep into technical question, I want to show you this slide. And this is an estimated cost of bug fixing. So imagine you're starting a new project. And if you're in the requirements, we're doing design architecture, even when you're coding something. If you have a bug, then the fix is cheap. We just pay a developer to spend another hour and go and fix it. When it's in the QA stage and you're testing it, that's getting a bit more complicated because you run some integration tests, you will probably pay for next four hours to fix it. Then you go to production. OK, production issues cost money. For example, if you are in e-commerce field and you have a serious bug, then probably your site can go shut down for a couple of hours. You can lose some money because of that. But OK, lost opportunities, for example. And the last one, we are hacked. So those issues are the most critical and really depending on of your business. For example, if you are online, if you're Facebook, for example, and you are hacked and all data leaked somewhere, then you're in a big trouble. Of course, if you're like small, grand-part shops that sell sign-on milk for somewhere in the Czech Republic, for example, then if you're hacked, it's not such a huge issue for your business in particular because you have a local store and you say to locals, OK, somebody has stolen some e-mail addresses. OK, that's fine. But for large businesses, you really have to care about this thing. And it's not about money. It's also about reputation. And for example, in finance sector, reputation is even more important than the money lose. So if, let's say, bank lost reputation, then the business screwed. And so today, we are going to talk about database security in particular. Because from what I've learned from my experience, I think developers don't think much about the databases. Let's say you have a classical web application. And you can use some frameworks to keep it secure on an application layer. You do some XSS protection, injection protection on your web app layer. But then what happens if you have an error over there? If a hacker can go into your system and execute something. And usually, a system talks to a new database. And what I've seen from my experience, 99% of the apps, they just have admin connection from your application layer to your database layer. Hopefully, it's not like a database admin. It might be like a just schema owner, somebody who owns the schema. But you can do writes, insert, deletes, whatever. So once your app is hacked, when your app engine is hacked, then your database is also hacked, and you lost your data. And when I was preparing this talk, I was trying to find out common bugs, common issues from history. Unfortunately, I haven't found anything for 90s, so probably nobody really cared about security in 90s. I found some common vulnerabilities from 2010. And as you can see, like blank passwords, weak passwords, secure injection, bad privileges, some enabled database features. So probably somebody installed a database from out of the box and forget to disable something. Broken configuration, buffer overflows, yeah, people did a lot of native code at that time, so it was a big deal. Privileges, do you know what I'm saying? Yes, you can see those are the issues. And now the interesting part, we are here in 2018, and this is the list. Brought user privileges, secure injections, no audits, insecure backups, all the same things what we had in 2010, and probably in 1990, something, they're still here, there. And nobody really cares. Let's go and take a look to each of those common vulnerabilities and try to find out the way how to protect against them. And I'm going to talk about PostgreSQL here. But really, any database, secure server, Oracle, they will all have the same things, probably called differently, probably different secure things, but they have the same concept. OK, brought user privileges. From my experience, this is the most common problem nowadays, because when somebody is developing an app nowadays, they just take the ORM and they let the ORM to generate a database for them. And usually, the app has all possible privileges on a particular database. That is fine if you're in a developing environment. But once you're running on the production, that's not good. And in my company, we found out as a principle how to solve these issues. First of all, if you have some monitoring users, like who's doing some logging, whatever, just gives them permission, only permission to read. And when we're building a new app, we create new database. And then we start creating SQL. And I think developers should know how to write SQL. So what we do, we create our user and give him just read only permissions, only particular database. And once you're starting developing, you see, OK, I need to write to this particular table, to this particular column, then just go and update your script. And moreover, when you're creating metadata objects, let's say tables, what we are doing, we are adding the exact security policy for every single script we are writing. So it means next time, we want to reinstall the same database, we just have our script package run it, and we have our security policy in place. What we'll call, it takes time. So we're developing the slows down a bit. So there are things that can help you. For example, for PostgreSQL, there's a good tool called PgTab. That's a unit testing framework for PostgreSQL. So we really like to start your unit testing your database. And if you have your scripts, and you're running those unit tests against the database, where you have your security policy, then your unit test will fail if somebody cannot read. And then you just go to your log and see, OK, what happened, why this particular user cannot write here, and if he should write there, or something that's broken in our unit test. Also, if you can go farther, if you're using your ORM, usually they follow the same structure. And nowadays, you have an ask parser available for almost any programming languages, language open source. For example, for C-Sharp, that I'm using, there's Roslin, it's actually compiler and the parser for C-Sharp language. There's one for TypeScript, like Icon or Esprima, those are for JavaScript. So if you're using your ORM, then you can write your own static analyzer. It will not be like this secure, driven static analyzer, like from a commercial product. But it will work for your particular database. For example, what we are doing, we have some scripts that use Roslin, and they just look to our entity framework code, and find the places where we write to the database, and just say, as the output is here, OK, in this particular file, you are writing to the database. Please check this code. Another thing, if you go to the more generic level, most of our database of modern database engines support low-level security. It, for example, positive supports it, and the SQL server supports it. What it means is you write your special function, or your security policy, saying, OK, for this particular, when somebody is writing a table or querying from the table, you can write the code, the checks, OK, if this user can access this particular row, and you can configure some bare statement. That might be very useful, especially if you're in e-commerce field, like a PCI compliant thing, and saying not everyone can read credit card information and some billing stuff and things. In case of postures, there's also a tool called CEPG SQL, and it brings your mandatory level security, and it's part of CE Linux. Yes, they had a really great talk about CE Linux, how to configure it, and if you're writing on the Linux server, just take it to this product. The next common issue that is still applicable in 2018 is SQL injection. The funny thing is, usually developers thinking about SQL injection is just now code, so we write some SQL with string concatenations and boom, something is broken. But it's not just about SQL that runs on the database. So you can have those classical exceptions, but you also can have SQL exceptions, and I saw it in my career in stored procedures, because you can run dynamic SQL within the database in stored procedure. And usually people don't think about, hey, can I break it somehow? Usually it's just I run dynamic SQL and it will work out of the box. No, you can have SQL injections this way. Another one is SQL injection in connection strings. That's not about SQL, but for example, the samples I've seen is you have a connection string that is taking a database username and password. And in case you have a shared database account that works for different database, for example, you have staging server, you have production server. And let's say you have your database on the same environment, and you have some issues on your staging server. And if somebody can inject database name into a connection string somehow, then he can go to a production server with a staging code base and who knows what will happen. Also, a few words about ORMs. Many modern ORMs have their own SQL-like language. And again, you can have SQL injection order easily. And I'm just talking from an experience, such called examples, when they have an injection in ORM SQL. And people are saying, yeah, it's ORM, so we cannot have SQL injection in ORM SQL. Well, it works. And we're talking about relation database here. But I would like to say that MongoDB, the fine thing with MongoDB or other SQL databases, you can have injections, not SQL injections, but injections in those databases as well. For example, when you're doing this Mongo, you can pass some special query, or you can modify the JavaScript that it's running on MongoDB server. So they are all vulnerable. And I think that's a really important and big problem nowadays. And if you want to automate some tools, for example, that's good for testing. If you have a security testing team, or you have a quote engineering team, they can use SQL map tool. That's a hacking tool, I would say, that can run against your website. And it will scan it and try to find some potential holes in the system. The next problem is missing audit. Again, if you have in finance or in e-commerce, PCI compliant environment, then you have strict regulations about what should be audited. On other projects, sometimes people don't really care about much about that. And I think it's also a problem. Yes, on developing environment, you don't need to go and configure the audit, because it might be complicated complex. But when you are production, just take a DBA, say, hey, man, please run audit for us. And especially for portraits, it's really simple. First of all, there is some error reporting and logging built within the portraits that is enabled by default. OK, if you're running on Windows, you can also send your portraits errors to Windows logs and then do some Windows auditing tool for that. There's an amazing extension called pg-audit. And this extension is just awesome. It logs everything for you. And if you're using it, I would suggest to take a look to pg-audit analyzer script. What it's doing, it just takes your person to your log file from pg-audit and load it into database schema. And you can do normal SQL and query and analyze it. And it works pretty well. Insecure backup storage. That's also a big story. It doesn't seem that too many people encrypt their backups if it's not in a highly regulated environment. And moreover, they don't even test backup procedures and restore procedures. So first thing to do is you have a backup. Just test your backups. Try to recover your database server once a month and see that it's really working and just keeping your data. And again, when you have backup, try to document it. So just don't think that, OK, I have this magic script that runs somewhere in Bash. And when your DBA left the company, then it's like, oh my gosh, what should we do? Like, who is doing backups? Just make sure that your DBAs or developers or DevOps just have a document. OK, we are doing backup this, this way. And we can restore these, this, and that. That's very important. And make sure that your backups are encrypted. Like one of the common issues in this database world is you can have encryption on the database level. You have a secure app, and it works awesome. You have your database on an encrypted file system. Then you have backups. And backups are non-encrypted. What will happen is somebody can just break your file server and download those backups. They will own everything. And the last thing is more about this development and staging environment. Because, again, from my experience, what I've seen so far is you have a development team. And usually they need a backup. And they just take a production backup if it's not a large project. And be happy to just go and explore the data. But if your production environment is highly secured, are you sure that your developer's laptop is that secure as your production? But if somebody can break your developer's laptop, why not be a web browser exploit? Then he can just go and download your database from developer machine. And that's kind of a big problem. So, again, if we are in a positive world, then those two scripts are just really easy. You just can use OpenSSL to encrypt your backup. And then the same thing to decrypt your backup, just two lines of code. And you improve your security. OK, the next problem that's not so often appears nowadays is vulnerabilities and configuration issues. I think people learn this lesson, especially people who are in DevOps world and the system administrator of the DBA world, that when you have posed this crazy patch for security issue, just install it as soon as possible. So the common practice is to make sure that you deploy it as those critical patches, like not only in the months, because after the months, it's like 99% guaranteed that exploit will be somewhere on the back end. But if you can deploy patches earlier than do it, for example, what may help is if you have two environments, let's say you have a production environment and DR environment. And if they are all in sync, so you can just deploy your patches on DR environment, then switch your production to go to the DR environment, then deploy it on production environment. Again, common practice doesn't take too much time to implement, but it will increase your security. That's funny, but I think it happened probably a year ago or maybe two years ago when there was a major data leak from AWS servers. So what happened was on AWS, without configuring any firewall, any protection, and even no password, because they thought that they can connect to MongoDB only from local host. It wasn't the case. So some guys just throw the parser and they just really scan AWS IP addresses for Mongo ports and they just go connect and download tons of data. That was like a big deal. In case of postgres, there is a pghba config file. And this is a file in postgres where you can set up your information about your connections, what protocols you support, what authentication protocols you support, and so just make sure that you just explicitly list your trusted connections, who can connect to your postgres, not just allow everyone. And then use stronger authentication mechanism. For example, Postgres 10 has this new encryption mechanism based on scrum protocol. So in this case, you're not sending a password over the network, you send in just cache. Or if you are in enterprise world, you can use like a radio server or some LDAP to do authentication instead of postgres. That is good, from my opinion. So again, limit your connections only for known IP addresses. So allow just local host or something like from your internal network. And make sure that you use an SSL connection. It's not that complicated to implement. You just build this SSL certificate. Add them to HB postgres and works. But in this case, you can be sure that nobody can intercept into your network and steal the data. And yeah, antivirus. Yes, it runs on Linux world, but probably I'm a bit paranoid. And yes, antivirus is for Linux. And yes, if you can use them. The next common problem is insecure data processing. And in Postgres QL, we have encryption on different levels, so different levels encryption. The first level is your password level authentication. So you can use rather scrum or MD5 algorithms to do encryption for you. Of course, MD5, it's quite weak nowadays. So that's why they brought scrum to Postgres 10 because it's based on SHA-256, I guess. So that's quite hard to break. Or yeah, that makes you sure that you are using SSL and you're not sending data across the network. Then we can go down to the database level. There is a PGCrypt extension for Postgres that has a lot of functions like hashing, asynchronous, synchronous encryption, whatever. It just works pretty well and fast. On the bottom level, you have partitioning encryption. So if you're running on Linux, you can put your database to NKFS or eCryptFS. If you're running on Windows, you can use EFS. So it means your physical hard drive is secure. Then we have network encryption that is set up on PG-HBA. And you can also implement certificate notifications. So it means that only hosts that have a certificate can connect. And you can also use client-side encryption. If you don't trust your DBA, just encrypt everything on your client-side and then send encrypted byte to your database. In this case, you have no indexing, no searches, and you cannot run calculations. So you're really missing all the benefits that Postgres or any other database brings to you. So think twice about that. But in the case of credit card information, for example, it might be helpful. Big passwords. Yeah, that's like a common thing. So in Postgres, when you create a new user account, you can set up a timestamp when the user has to change the password. Or you can use a password check module. What the module is doing, when you're creating a new role, it checks your password again called common things. Like it has to be like an alpha number and digits. You can also add crackleaps port to this thing. So it will also check about against common password databases. You can use carrier authentication, like some LDAP servers. That's even better, because then you have one common place for truth for your password policies. And you can stop all this delay to protect against brute-force attack. And you can use Metasploit MD Crack tools to check your password and try to break your Postgres, for example. Dangerous service attack. The last one, not quite common in the database world, because usually all your database are behind the perimeter. But anyway, so make sure you have the latest patches, have a firewall, and have an antidote solution if you need it. And that's summary. So install updates. It's like a common summary, some steps that are easy to implement. And thank you. We've got some questions for you.