 Okay great thanks everyone can hear me good we had a good talk before so that was we're done that's it that's the whole thing let's go home I am embarrassed to say that I actually gave this talk before at this conference two years ago we had a postgres day I wasn't aware but somebody came up to me today and they said aren't you giving the same talk he gave two years ago and they're like they said it was really good I'm like I did and I get my phone out really quick to find out because I record where I give my talks so I don't do this mistake and in fact that's what happened so I submitted this talk or somebody no I must have submitted this talk and not realizing I'd already given it did anyone to see it two years ago great okay I'm sorry about that I'm sorry about that this is not the updated version unfortunately is the same version so I have I actually I actually have over 30 talks on my website and this is one of them so I usually pick something of the one of the other 30 talks to submit and unfortunately I did this mistake so we only have one person who's seen it before I feel pretty good about it it is an interesting talk and the reason it's interesting and I'm gonna try and walk over here and see if I don't get a lot of feedback but the reason it's interesting is because it tries to look at postgres from a different angle so it looks at postgres basically from the outside and tries to see what somebody from the outside can actually do to postgres like how what what type of malicious things can somebody do to postgres and to have to prevent that from happening all right and and the reason it's interesting is because it's a database so again normally you you you have certain things you should use for server configurations and so forth but this is obviously database so it has a little different set of requirements when you're looking at security and I'm basically going to be talking about them from a database perspective okay so again yeah but this is my website down here the website the slides are there I've also submitted to the press of the organization organizers so I'm sure they'll show up on the website at the same time so we're going to talk about attack vectors how can you it's actually attack a database specifically I'm talking about postgres but frankly it probably relates to any other database I'm going to talk about postgres specific myself by the way my name is Bruce Momjin been working with postgres for 16 years work for Enterprise DB they sponsor my community work and have for about seven years now so what we're going to talk about today is some of the security you can set in postgres we're going to talk about passwords and how they're kind of configured in postgres then we're going to talk about some network issues and sort of network security in relation to postgres which I think is going to be kind of interesting and then we're going to talk kind of at the back and about sort of some of the the issues with backups and things like that so again not a super complicated but we are going to kind of build kind of from the from the bottom up what we're not going to be talking about is external attack internal attack vectors these are cases where somebody's already been given access to your database and they use it somehow to escalate their privileges so this would be something like SQL injection attacks where you use sort of single quotes inside your string to kind of get another command into the stream if you've ever done that and seen that before application vulnerability permission stuff like that we're not going to cover that doesn't mean they're not important it's just not the focus of this talk here so any questions okay so let's start with a file here this happens to be the file called pghba.com this is the configuration file that comes with postgres and the first thing we notice here is that we have a authentication method over here on the right-hand side and the way postgres comes in its source distribution it actually comes with a trust flag and what the trust flag effectively says is that I allow anyone to connect to my database who's on the local machine there is a way of changing that there's a dash capital A flag that allows you to change the default permissions you get when you install from source most packages will not use trust they'll use md5 they'll use pier which I think is a new security we have so again you might want to look at your HBA file and see how your security set up and basically what you know what controls you have this some this fall is kind of interesting there's quite a bit of of cool stuff you can do here I'm not going to go into it too much but basically it's like a big filter file so you can do cool things like say I want this network to come in and I wanted to use SSL and I want I wanted to use password try no md5 trust or I want let's see I want LDAP to be used from this network I want to exclude a couple of machines in that same network so there's a lot of cool stuff you can do here in terms of security I'm not going into just kind of highlighting that trust method and showing you other ways of setting it up there is a an authentication method in PostgreSQL password I know it sounds real easy but you shouldn't be using it the reason you don't want to use it is because the password effectively gets sent across the network with no encryption at all so it basically is just a clear text version of the password that gets sent across now this isn't a problem if you're if you're doing everything on the same machine so if you're going local host well obviously nobody can snoop that network because it's on the same machine you're using units to main sockets or even in TCP all that network is on the same computer but if you're going from computer to computer password is probably never the one that you want to use what you probably want to use if you're going to pass passwords back and forth it's something called md5 that might be kind of a funny name but in fact it's called that because it's a basically a hashing algorithm that we use to identify how to basically how to hash the passwords as they go across the network okay the first thing you might kind of wonder about that is well okay so you've md5 hash the password but how does that really help me because somebody can just look at the password going across the wire and then we send the same hash across the network right so if you ever imagine what would happen effectively they've hashed the password I don't get to find out what the password is but I do get to find out I can basically replay that password back as a new user okay fortunately that can't work with with postgres and the reason is because the the client before it sends the password back will actually run md5 twice so what happens is the client connects and says I want to connect the server comes back and says I need a password but also I've got this random salt I'm going to send you and when you send the password back I need you to use that random salt as part of that packet coming back so the client basically says okay I'm gonna take the password the user supplied to me I'm gonna concatenate or append to that the username okay so we've got the we've got the password and the username I've unfortunately spelled misspelled username I will fix that okay and I'm going to md5 that okay and then I'm going to take the salt that was passed to me by the server and I'm going to append that to my md5 and I'm going to md5 again okay and then I'm going to send that back to the cost of the server and the server actually stores its passwords as md5 hashes of the username and password and then so what this client that the server will do is say okay I send him that random salt I'm going to take the password entry that I have stored I'm going to md5 that again with the same salt that I sent to the person I'm going to compare the two strings if they match I'm good okay so how does that actually work in practice normally I want to connect here's a password and here's a random salt the person sends it back they basically get an authentication and they're fine okay however a malicious database client could make the connection it could receive the password and the salt if it tries to replay the same string that it saw on the wire before effectively the database will say no that doesn't work it it does not match the salt that I passed you and therefore you can't get it okay so yes sir I'm sorry oh how do you spell that not I've never heard that term before okay so why is it not a salt all you mean they saw all the passwords the same they use the same salt for all the passwords okay and you're right we're not using we're not using anything when we store it we store it without any randomization okay we just kind of came up with this because we realized that replay wasn't good the big problem frankly that we have with this design is the salt is only 32 bits and you might think oh that's not going to repeat very often but when you think of the birthday problem you know like how many people have a birthday it turns out I think you only have to do like 16,000 trials 16,000 trials before you get a replay or something like that so I'm not really happy with that I think we need to go to 64 bits we sort of haven't done that yet but I would like to see us go in that direction and unfortunately we didn't think of that at the time when we originally designed this this was in like the late 90s and 30 64 bit support wasn't in most of our operating systems so it would have been kind of hard we probably could have done a way of kind of making a hybrid 64 bit but we didn't we didn't think of that at the time and that's that's sort of a flaw I think in this design okay other questions so what doesn't this cover well it doesn't cover weak passwords doesn't cover reasonable passwords and it doesn't really cover brute force password attacks okay if you need this type of security and many people do we're basically going to recommend that you use an external tool that is designed specifically for this type of capability so LDAP, PAM, SSPI these would be the normal things that you would use to sort of prevent that type of vulnerability and again most people who are doing you know who are doing large organizations are you know easier than doing that kind of thing that we do which is kind of kind of cool so again there are a whole bunch of ways to do authentication the problem though is that we just kind of talked about getting somebody into the database the problem is we really should also be worrying about what gets sent across the wire okay so just because we've encrypted the password and we've authenticated cleanly doesn't mean we're in the clear it literally means we're still in the clear because we're clearly sending our data across the network with no no security at all okay so the queries are coming across the results are coming back show me this person's past you know how much does he make number comes back on the wire you know no there's nothing there okay so effectively yes like start from customers and then all the customer data comes back this is obviously not something we want anybody who's snooping on that network and see what's going on so we have to start thinking of what happens not maybe not you may be not worried about this within your organization but what happens when somebody is doing this across the internet okay kind of coming in you can do that you know is that is that really what you want so a lot of people saying oh well okay I want to use SSL and they sort of think that solves all the problems but it doesn't all solve all the problems and I'm going to get to that so what SSL typically does in a little detail of how it works is effectively it connects it basically generates a negotiated secret key between the client and the and the server and then it uses it like an encryption algorithm like AES 256 I don't know what AES stands for but it can't be American auto standards but American encryption standard oh I'm not too far off so basically it's gonna it's gonna encrypt it as it goes back and then it's gonna use the same secret key to send the result so you know you thinking oh wow okay I'm done this is great you know I'm now encrypted I've got my authentication working you know my job stuff well not really because there's this guy and I talked about snooping before but this guy is spoofing and I know it sounds like the same word but it's really it's really different okay snooper is kind of just looking at what's going across and you're sort of like looking to the blind the spoofer effectively sort of pretends he's someone else and how does that actually apply here well let's look at one example which I think is kind of creepy and we do document this and it's kind of something I don't hear a lot about but for example if the server is down your database server is down it's possible because the server typically listens on an unsecured port for somebody just start up a server and like F for passwords okay they can't do anything with them they can't actually accept any queries because they don't have any data they only access there but effectively client says I want to connect server says give me a password and give me a plain text password okay client says okay here's the password and boom you're done okay you sent that password and then the person just says well you know user user disallowed or whatever invalid password just goes away and I collect a whole bunch of passwords and then later on got a hopeful password that can use kind of scary yeah well and I have little little little little text right down here but basically what the little text says is that it uses a fake socket or binds support 543 to while the real server is down okay and because the socket by default is in the temp directory okay then if you can basically create a saw a socket as not root and it's not a root it's not a root only port it's under 124 so 0123 or root only but we don't require root so you know it's like hey here let's go you know and and again all it has to know about is enough to do the protocol like the negotiation of the thing it doesn't have to execute a query that doesn't have to do anything it can just basically like hiccups and this is a problem you know it may not happen very often but you know who would have thought that a server being down is a security problem I hadn't realized it until somebody mentioned to me I was like I know I might have thought of it myself I don't know it just wasn't good so we do document this is something that's not good okay so that's one example of a somebody spoofing other real database well yeah I'm gonna get to that in a minute so effectively yeah so basically what happens here you just record the password and you know he's basically pulling in yeah I'm sorry no because the person basically says I want to connect as user Fred and that is the fake guy has no idea Fred's a valid username or not and doesn't care okay just basically says okay give me your password he's like okay here we go yeah so it's kind of a scary example I think and maybe a reason to use it you can't use a secure port and postgres there are there's an answer to this fortunately and but you can kind of start to see where it's going as a spoofer some of the things you can do okay this is a man in the middle attacks okay this is not kind of a man in the middle because there's nobody on the other side right but this is a man in the middle attack and effectively what this does is the database says okay I want to connect and the fake server says give me a password in plain text and the plain text password is sent to the fake server and as soon as it gets it actually then passes it through to the field server logs in okay and then all the queries that are just passing through here and then coming back out and this person's acting as a middle person but obviously is now can see everything that goes across all right doesn't doesn't make me feel real good but it's certainly a reasonable concern and you might say oh I'm gonna fix this I'm gonna use SSL prefer which happens to be the fault for postgres not a great default we've discussed it for a long time but as I prefer happens to be the fault when you turn SSL on at the client so anyway the database says I would love to do SSL with you and the fake server says no I don't have SSL okay and because it's prefer it's not require it's like well okay we'll do not miss a cell and I'll have your password and I'll send it over here and I'll send your query back and the client has no idea that somebody in the middle I prefer not yes so we do have a mode called require says I require SSL it sounds so secure doesn't it okay so the client says I want to connect by SSL and the fake server says okay I can do SSL let's let's do SSL and the fake server the real client talks to the fake server gives it the password but of course the SSL is between this guy and this guy so everything is decrypted over here the fact that we've secured this channel really has not bought us any type of security okay so effectively it decrypts the password it sends it over the real server and we're back to the same cycle here okay so even the require doesn't really kind of do it for us the only thing that really locks away that that person in the middle is SSL certificates and I don't know how many of you have dealt with those certificates great they're kind of important I know they're kind of also a pain because if you've dealt with SSL certificates you've got to like put them on every machine and you've got to manage them and you have to have some kind of key signing and there's all sorts of crazy things where you sort of encrypt them and you put them in a bundle and you've got a password to decrypt it