PostgreSQL Security for Application Developers

all right cool so this is a post-grad security for application developers so let's get started so this talk is gonna be about application security that's in contrast to DB security or user security so to kind of go into the distinction between these these securities about locking down your database so there's a great talk yesterday by Joe Conway but you know the things you can do specifically lock down your database to you know PGH be a cough and like you know configurations at the OS level so that's you know that's what I call DB security because it's specifically you know locking down the environment itself around the database and everything else kind of touches on that but it's a you know it's a specific subset of security now separate from that you also have user security which is about when you know once you have an application up and running you have users you know on your team we need to query something for reporting or you know just diagnosing something that's wrong and you know locking down that access and you know auditing activity feeds things like that so my company actually has a product for that specifically use case but that's not the subject of this talk so this talk is specifically about application security which concerns with you know as an application how do you talk to your database how do your you know how do you make sure that when clients are accessing your application and you're interfacing with a database how do you prevent you know bad things from happening so I just the kind of quick intro of myself for myself my name is Seto PR Cooney I'm the founder of Jacobi I contribute to the JDBC driver and to the node node post-crisis driver and every so often I'm on IRC I set up so you know usually giving good advice so good security they say it's like an onion and not because it makes you cry but because you know it has layers so what's it mean to have layers and security yeah there's this concept of defense-in-depth where you know to get into this castle you're not just you know you can't just walk in first you have to get through the moat and then you have to get over the wall then you have to get over the next wall and each each step in between hopefully it's independent from the steps before and you know to get all the way through you have to you know to get to whatever's on the inside you have to get through all these barriers inside so this is what's the you know just overall concept of defense-in-depth now one of the major things in the concepts and defense-in-depth is limiting the power to do something bad so this is called the principle of least privilege so anyone who has kids will recognize this picture as something that's really really bad you know children should not have access to sharp objects that they can poke themselves with and the kind of equivalent for databases is if you shouldn't be able to do something that can break something unless you really need to be able to do that one thing so when you're looking at the way an application interacts with a database you know there's really three overarching things that you do you're either reading something like a select statement a query you're changing something which could be an update it could be an insert it could be a delete like some modification of the data itself inside that database or you're changing the overall structure so that's like a database migration you're altering a table to add a column you're dropping a table creating a new one so when you look at real-world examples of how people build applications almost everyone when they're first starting out with the system has that one account so like you know you create a user then you create a database where the owner of that database is that user and that guy can do everything which is great when you're first starting out but that also has the problem that that guy can do everything so like that convenience becomes kind of a security interest now the next level is you start splitting things out and saying okay let's have one guy who can handle read-only access and we'll have a separate account that original account they can do everything so between these two I saw that full functionality but if I want to be able to restrict the you know the read stuff to it to a lesser role I can use that read role for that now the next level for this is you know my personal favorite as far as a you know convenience and security mixing together is where you have a read or a read-only account and then you have a secondary account which is kind of a readwrite account like a DML account and then you have that third account again for for doing everything else so I need to create a new table I can run the DB migration or whatever and this split out the reason I first there's a split as the sweet spot is because it's simple enough to kind of apply to any application it's not that complicated to set up either now when you go beyond this then you start getting into like the enterprise kind of setup for things where you'll have this data set has this account that it has that account I can change it with this from migration to that and there's really no limit to you how many accounts you can have at this point so it's kind of its own category and if you're in this world you you know more than like you know you're in that world of having to deal with things like that so in that kind of sweet spot set up if you break it down to actual DB permissions that first the account that app read all it has is to the Select permission so whatever tables we're gonna be querying it has read-only access to it then we have this app the ml account which you know it can do the it's a super set of the permissions for the read account so you have select permissions but you can also do inserts updates lease and modify the data inside of inside of our system and then finally the app account itself which is the reason is named like that it's to match up with the database because it's semi convenient from psql to login and you can do DB migrations you can do creates drops alters and you know all those permissions so they actually set this up you know here we're creating like three different users we have a pre def d ml and app and then we create our database which is also called app with the owner being our app account so one thing kind of looking at this you know if you jump back to the actual passwords inside the example over here you know they're not particularly good passwords so like you know you could say yeah it's got a letter it's got a number or whatever it is but these these are actually very very terrible passwords if you spent like 10 seconds staring at this probably everyone in this room would be able to memorize at least one or two of these and you're not gonna forget it afterwards and that's not how password should work so there's a lot of ways of authenticating with Postgres and passwords aren't necessarily the best way of authenticating with a database but they're they're the most ubiquitous way of identifying with it particularly if you're using like a cloud environment where the provisioning your database for you already in advance it might be the only way you're kind of logging in so it's if you are going to be using passwords it's a good idea to have a little bit of control over how you're setting them up so if you are you know generating passwords for your user accounts the password should be cryptographically random they should be long because the only thing that makes a password strong is how long it is and you know if you just want to again or a rule of thumb if you can remember the password it's probably a bad password so if you want to you know a really quick and dirty way of generating generating passwords if you run this on basically any UNIX type environment this will just generate random character using like alphanumeric alphanumeric choices so this this would generate 32 characters with sixty two choices e so it's like uppercase lowercase in numbers and you'll get things that look like that so this is actually useful for like passwords for you know API keys like something that you want to generate that you're not going to remember because if you can actually remember these strings down there you have a much better memory than I do so kind of jumping back to the permissions for the application itself now so Postgres is actually pretty cool when it comes to permission because besides just granting permissions to to user just say that you know this user can access something you can also create the permissions on something to be able to have it be ongoing but to get there you first have to remove the default permissions so by default postcards lets any account they can login to database create objects inside of that public schema so it's like that for it's very convenient to use once you get set up but you you don't really want that because if you're gonna if you're gonna split this out into separate groups you want to have that lock down to that permissions only in a specific on a specific user so these first two things are doing up here is we're revoking the access to be able to do anything in that public schema and then we're accessing adding back the ability to specifically just to view the catalogue for the schemas so the first step in security in Postgres is can I see the object I'm trying to access and that's what that grant usage is doing now the other two lines below the first one is saying you know grant access to all the tables and that schema to the app reading if you're starting out with an empty database it's not really doing anything because there's nothing to grant but we put it in there just to show you know how you would kind of migrate through this kind of setup and finally the last one over there is that point I was touching on where I'm saying the default privileges for anything created in public automatically grant it to the APRI so I don't have to go back and and grant again that this user can select them to stable I can just create the table and they'll be able to access it that that's kind of like a general principle and security that if you make it easy to use people are more likely to actually use something because now I don't have to think about granting read access or something I just have this read-only user so for the DML account it's a very similar setup the difference now is instead of just select I'm granting insert update and delete in there and again the default privileges are set up for that account to be able to do the you know same kind of permissions on top of it so for the app user I don't have to do anything by default the app user can just about anything inside the database but it's not a super user and you more than likely don't want it to be a super user because your super user kind of bypasses a whole bunch of other security security checks and you just don't want to happen again you the idea is you want to lock things down to whatever the bare minimum is so I can do DB migrations I can alter columns and stuff with this but you know I'm not going to dynamically install extensions or something you see this user so if I'm actually going in and I want to provide this to my application you need to injecting these credentials somehow so the kind of standard way to do this is to have environment variables for the for each user so I have one for read I have one for D ml one for DDL and in whichever part of the application I'm going to be using something you know pull out whichever environment variable I want to connect to the database as that user but one thing to keep in mind for this is if I'm not going to be doing something I probably shouldn't have it in there in the first place so that you know that DDL permission I have I might need it for running DB migrations but there's no reason at runtime my abrogate and my application actually needs those permissions so it might be convenient just to have everything in one place but if I don't provide it to the application at runtime I never have to worry about being able to drop my you know accidentally dropping a table because of some other bug inside of my application so again this is that the whole concept of least privilege of you know what is the bare minimum we need to move forward to provide the functionality that we're actually doing so this is kind of like a another summarization of this you know I think what would Peter Parker do with this you know with great power comes great responsibility so you try to lock things down to the bare minimum so that you have less responsibility and one of the nice thing about this security breakout is usually people talk about security as like a you know its added weight on top of something he makes things more complicated in the case of application development it actually makes some things a lot easier so naturally if I have a read-only user for you know for some parts of my application I've already separated out which parts of that can go to a different server it doesn't have to go the master anymore I can have that go to like a replica to pull out the you know to query the replicas instead so if you're looking at the kind of the work effort involved in scaling out of the system if you already have a good security policy in place with separate users for read for DMS for DDL the you know scaling out that a new replica just is just a matter of kind of updating that read database to say okay instead of going through our original which is just a single source now go to this replica over here instead if you don't have security in place in the beginning where you just have one idea for everything now you have to go through every single point inside your application and you know say oh could I change this one no maybe not this is gonna break something you're gonna break like transaction semantics about something so just from the beginning you if you if you start from that point if say hey let's do this right from the get-go it makes life a lot easier when you eventually do have to scale out so now let's switch over to actually connecting to your database so when you're connecting to a database you connect with the database driver database drivers the thing which actually talk to the database for you so your application will have some kind of interface to the driver it's usually something specific to your programming language so if you're in Java the interface will be JDBC if you're doing it from Perl I think it's like DB D the interface so it tries to present something that's specific to your language to make it the kind of consistent how you're accessing the database and it handles you know all the talking back and forth it handles authentication it handles actually ferreting your queries getting the results that's back parsing things so there's two kinds of drivers there's Limpy cue drivers and then there's native drivers so Limpy cue drivers are used the library lip EQ which is part of Coast Postgres core so you know it's developed as part of the project the drivers which use it kind of leverage it as a it's a sea library usually installed system-wide and the the drivers that use it kind of call out to those C functions to do whatever interact with Postgres the cue has a lot of advantages because since it's part of Postgres Cori gets developed with Postgres and the it kinda has a consistent interface for how you're connecting and how you're interfacing with things but native drivers also have advantages you know namely being that since it's not a system dependency you can upgrade them out-of-band if you're kind of in the market for changing things yourself like you want to add a feature to a driver in my opinion it's a lot easier to hack the native drivers because there's like positive sense of the word heck in that you can change it because it's a language that you probably understand if you're developing a java application you probably know Java if you don't you probably have other problems but then you could actually edit the JDBC driver because it's written in Java if you know rust you know how to you know edit a rust driver so here's some examples of postcodes drivers you're you know if you're developing an application you're likely using one of these or some other related driver so one thing you have to do when you're you know if you are the person responsible for an application is keeping your drivers up-to-date now you get a lot of new things when you update drivers so there's new features on the server they get exposed by the client there's you know just performance enhancements to the drivers themselves you know for improved methods of batching things like that but one of the biggest things that happens with updating drivers is if there's a security issue in the driver itself it's up to you to actually update that driver so update your drivers and the way to know if you should be updating your driver actually is just to get updates so for most of the four core postcards itself there's like an announcement list which lets you know when new releases are coming out and then separate from that for whichever driver you do happen to be using there's usually mailing lists associated with that with that driver so if you're on you're using JDBC there's like a github list for you're getting notifications and its own mailing list if using something else you have to find out what it is and it's up to you to actually sign up for that and it really is like the bare minimum as an application developer since you're responsible for making sure that you're using these up-to-date dependencies you should be the one signing up for this otherwise if you have an application in production if you're not updating it with a new version of these libraries and there's a bug in it you know there's not necessarily a bug inside of your code but it could be exposed by the by the driver itself so there's the questions anyone here use node that yes yeah okay okay did you happen to see this okay so this is a bug in the nodejs driver that I found a couple weeks ago it was actually a pretty nasty bug he's trying to read up on it it's on the site but these are the kind of this is like the reason that you want to be updating your drivers in this particular case it was a if you if you had a very very naturally crafted query returning the remote code execution on your server itself just by returning that result set so it is like a pretty extreme outlier of like bad things that could happen but this is like you know kind of an example of why you should be updating your drivers and knowing about them because otherwise it could be like months without the though you know this thing that something like this so now let's actually connect our database and see how that stuff works so a big part of actually connecting to your database is making sure the connection itself is secure and that's done using what's called transport layer security so you know transport layer security or TLS it's also called SSL and it's about encrypting the connection between your application and the database you're connecting to so you know why do we need to encrypt connections yeah it almost sounds like you know why do we have to ask this question but the reason is you want to prevent eavesdropping and you want to prevent men in the middle man in the middle attacks so either dropping is when you're you know you're trying to communicate with something and there's someone else who can listen into that conversation so you know if you're using like a human example you have two people in the room who are talking and there's a third person who can kind of listen into that conversation and they know what they're saying the networking equivalent of this is I have two servers once talking to the other and I have a third device in that Network like listening in promiscuous mode and it's actually just collecting all the packets going on that network and so it can hear all the bytes going back and forth now man in the middle is a little more complicated and significally more devious in that the human example of it is instead of me talking to a person directly I'm talking to someone and I have a third person kind of ferreting those messages back and forth so you know Alice tells Bob tells Eve hey glass Bob if he needs anything and then Bob comes back and tells Eve hey tell Alice I don't need anything and then he said oh yeah Alice had to give me 20 bucks so Alice and Bob since they're not directly Komori or communicating with each other they don't know if that message is legit or not so in heave is the one kind of Manning Manning in the middle of the conversation there so the network equivalent of that is if you're connecting from your application to your server instead of you directly having the bikes go to the server it's kind of going to a third location and they're ferreting it back and forth so the server thinks that the original client connected but in reality it's you know it's kind of controlling both ends of the conversation so if you're trying to figure out if you should be using SSL it's like a kind of flow to a flow chart to figure it out so you know if you're connecting to localhost you don't really need to use SSL because you know your bites aren't actually leaving your computer and presumably it's secure and you can get there if you're not connecting to host you have to ask yourself do you trust the network that you're connecting on and what that means is it the network itself you know secure from someone else being able to either listen in or actually change the you know change the wire you imagine someone going there and like rewiring it so that the packets going from your application or going to going somewhere else as opposed to going to the database you're connected to if you're not sure or you know just answer just know because it's a public network let's say were connecting over the Internet you know cloud service or something you really should be using SSL if that's too complicated a kind of simpler flow for that is just are you connecting to localhost and if you're not you really should just be using SSL so that we can have to think about whether it's secure enough and if you want to go even simpler than that just use SSL the the overhead of using SSL is actually pretty minimal for you know most use cases that it's easier just to not think about and just turn on SSL and if you're not going to use it then you can kind of think about that as like a opposite use case so if you're connecting from lip PQ there's a couple different modes in the lip be Keuka and driven off of either connection parameters or environment variables to dictate how you're gonna connect in so the top level one one disabled actually means don't connect with SSL explicitly using the unencrypted connection and in that case there's no there's no eavesdrop protection there's no man in the middle protection but you also you know what you're getting you're saying hey don't use SSL I'm okay with it now a lot one prefer kind of interesting because allow is saying hey if I have to use SSL do it but otherwise just use an unencrypted connection and since you're not actually saying which one it is you don't know if it's gonna be encrypted prefers kind of the reverse of allow where it says hey try to use an SSL connection but if not fall back to a to an unencrypted connection and again you still don't know because you know it could be unencrypted connection there's no guarantee requires where it starts getting interesting and that you're saying hey I need to have an SSL connection but it's not actually checking to see am i connecting to the right server because the way SSL works is you connect the server it negotiates keys but then there's also an added step afterwards where you verify hey am I actually talking to the person I think I'm talking to and require just completely skip that step but says hey let's just negotiate some keys the wire itself is encrypted but we're not actually you know gonna verify who's on the other side the other two options verify CA and verify full have that better have that verification so they actually check to make sure is this the actual database I'm trying to connect to is the servers you know is the server who it says it is things like that so those ones actually do provide a man-in-the-middle protection now this is how like the modes are defined you've actually going to documentation most of this page is actually copied from the Postgres Doc's in the real world this is what it looks like when people talk about enabling SSL so you have some people say no we're just not using SSL and they'll you know have some kind of reason for that where it's a lockdown environment we control the network its localhost or whatever then you have people have no clue what SSL is and you know in that case maybe it's on just because they copy the connection string from somewhere and you know there's something SSL equals true and something so you've dropped protections there but there's no way that they had man-in-the-middle protection which we'll get into a second why then you have people who explicitly know that they're using SSL but at the same time they're not actually checking you're not verifying the server the server on the other side so there is that you're guaranteed that Eve's out protection but again you don't necessarily you're you're not necessarily verifying the server to know that it is the server that you're connecting to and then that last category is the people who really understand the subject and they set up their own internal certificate authority and all the certificates for their for their servers are signed by that and they distribute the certificates out to clients and it's kind of its own league of you know really doing things the right way and those guys actually have the full gamut of you know we have eavesdropped protection with a man-in-the-middle protection you know we know this stuff works from personal experience of you know what you see in the wild that third categories where you know most people are where they know to like turn it on but miel beyond that it's not actually enables further so you know I'm gonna go into the example now in Java actually how do you know kind of what these different modes look like and how to use them so in Java it's a little different because it's a native driver and it doesn't use lip BQ and the SSL configuration in Java is done with a number of different parameters the first one just says whether or not we should be using SSL and the other two dictate how to actually create that connection so in Java there's a concept of a SSL factory which SSL socket factory which says how do I create the socket that's connecting to the to the server that you're trying to connect to and the way that the JDBC driver set up you can set you can specify your own custom sock effect and so the default one and given you can also give it an argument if you want to customize it at runtime to we do something specific so by default if you just say SSL true in Java it's pretty cool because it does have use drop protection and does that man-in-the-middle protection so on the surface this looks like this is an awesome thing oh I just have to say as to cell equals true the problem is this just doesn't work and the reason it doesn't work is by default it tries to use the JVM keystore to do the certificate validation and unless you actually did that you know set up your own internal certificate authority copied the keys out load them into the JVM and push that out to all your servers it just won't work because then you'll get an exception saying that the java application doesn't trust your trust the server because it can't validate the certificate so what ends up happening is people you know they'll run into this error then you know the last guy on the mailing list you know how do I you know how do I connect with SSL and then you know eventually they'll get to the point where they just switch it out where instead of validating the certificate on the other side they say hey just use the non validating factory so this is the equivalent of the that require mode inside of inside of would be queue and this is saying hey connect with SSL but just discard the concept of actually you know verifying the server on the list I just trust who were connecting to if the network itself is you know is reasonably secure you're not worried about someone being able to actually physically change the bikes that are moving around this does work pretty well so this will you know you know I'm like a local network or inside like a vbc or something this is like a pretty legitimate set up if it's not though since you're not validating the remote server it is possible for man in the middle of the connection because you're not dilating you know what you're actually connecting to now if you do want to do that in Java there's a number of different ways and one of them was with this custom socket factory so this single cert the validating factory what it does is it's kind of like a wrapper around the default socket factory where instead of using the built-in one inside the JVM key store you're saying hey here's the specific certificate for the server I'm connecting to which if you know in advance which server you're connecting to you can inject it in either as part of the connection string or have it available in your application and then the your java app will validate to make sure hey am i actually talking to the server I think I'm talking to so this factor is kind of cool because you know you can have it in the certificate in the file on the class path you can actually put it as a string like it's just a PEM encoded like you know big in public key or public certificate equals whatever so there's a lot of different options we're kind of getting it in there and you know it provides a like a you know significally more security than just an on validating fact here because now the client actually is verifying you know who you're connecting to if you switch out that server with another server now the client will immediately realize that because it's you know the the certificate just won't validate anymore so the concept of you know the kind of the name for what it's doing here is called certificate pinning which is where you know you know the certificate of the server that you're connecting to and you know since you know that in advance you can make sure to validate that I'm connecting that particular server the the next level of doing that is instead of having a specific certificate in there where you can do is have a chain of certificates so if you're familiar with the way that the CA model works for the public Internet when you get a certificate for your website you're actually getting a certificate signed by you know Verisign or thought or on those other companies and it's that cane of trust is what's how your browser verifies to make sure that you know this website is who they claim they to be so a lot of you know cloud providers and you can even set up yourself to have like your own internal certificate authority and then you use that to sign the certificates for your Postgres databases and then when you're connecting and you just have to have that one top level certificate inside your application and that tells me hey I trust all the databases underneath that have the same same certificate and you know and if you happen to be using Amazon actually provide the chain on you follow the link inside the slides there's the the certificate for admins Amazon's therefore all the RDS databases for redshift and everything else so up until now we're talking about just you know generic password off which by the most setups happens with the md5 inside a post Chris the coming soon version 10 there's something called scram authentication it's a salty challenge response off it's you know basically just better than md5 in every way it's you know it has a the way md5 works in Pro scars it's not that it's insecure but the salt inside of its like a relatively small salt so this solves that and a couple of other things what's actually much cooler but not in Postgres ten but will hopefully be in postcards eleven is that scram also supports server validation so all that stuff involving certificates can just go bye-bye because what scram allows you to do is say okay I know what the password is for the user - does the server know the password for the user I'm connecting - because if they do then I know that it is the server because you know if you consider that to be a shared secret and you can actually use scram - to kind of secure the TLS connection itself to do that certificate validation step so this is something I'm actually looking forward to it postcards 11 because it will basically make it like trivial to have man vanilla protection for SSL connections going forward so kind of switching gears a bit so this is a nodejs right here you can even point out what's wrong with this this piece of code and there is more than one thing but there's like the one gaping really bad thing yeah so if you look at the where ID equals I'm saying ID equals and like a single quote and then I'm you know putting in whatever the user sent mean the request parameters so this is like a classic case of sequel injection so you know if you instead of putting some normal thing inside the get request if I have this nonsense over here with like a quote zero a whatever it is it'll lead to running this completely different table query over here which really could be anything so this is the kind of classic case of uh you know how bad things happen sequel injection is almost universally caused by it's called by composing sequel using things using user input and it's always bad but sometimes it's like really really bad and how bad it is depends on what you're doing and how much you know how much you've actually separated out the permissions for the for the users they're using for things but it's also really easy to avoid so there's really a two ways to avoid sequel injection the first one is just use parameters for everything if you have you know any kind of user input coming in it really should be going through a parameter you almost never should be composing sequel by just blindly concatenating strings together but if you are doing it you really should be escaping it so we'll get into in a second what that means to kind of escape the strings there so when you're looking in when you look at sequel there's really two kinds of we said the commands themselves the keywords there's two kinds of fields there's there's literals and there's identifiers so literals are things like you know text you have postcards that open st intact you have 2017 all itself is actually literal and these are things which can be parametrized in almost every context there's one slight thing where you can't inside it do blocks which is kind of annoying that's its own thing but otherwise in everything else you can do in post grades you can specify anything which is a literal value as a parameter now identifiers are things like tables views functions any object you create inside of the database and these can't be parameterized and the reason they can't be parametrized is when the parser is actually trying to evaluate what a command is gonna do it need to know what the output of that command could be so the outputs not going to depend on what the parameters are but it would depend on the type of the thing so if you try to do something like select star from dollar one where dollar ones can be replaced by the mine actual table the you know the results gonna change if it's a County might have three fields if it's people or a person or something you might have ten fields so since the parser needs to know what that output is before it starts evaluating the the query itself you can't parameterize identifiers but little as you can so if you look in that first example there we have you know select where ID equals dollar one and dollar one is a placeholder in postgrads using numbers parameters for you know for what that value is going to be so here's an example in that kind of similar to what we were doing before and no js' of how the you know use parameters so almost every driver inside the poster has some interface and you know the dynamic kind of dynamic language will be very similar to this Postgres uses numbered parameters so what that means is you can have a dollar $1 $2 $3 one with this value replace dollars to of this value which in this case we're doing by you know that array over there where it says the username is saying replace dollar one with that username value so if you're doing the same thing from Java it's it's similar but different Java has the annoyingness of trying to match up with the JDBC API which doesn't have a concept of numbered parameters per se what it has is placeholders for parameters which is what those question marks are you would think it's kind of the same but it's actually worse because there's specific situations where if I want to reuse a parameter in postcards I can say dollar one in like ten different places in the query and I only send it once when I'm executing it whereas in Java it actually requires you to have like ten question marks that you can't tell the difference between them so you have to specify it ten times kind of annoying but otherwise it is pretty similar in that you're saying hey parameter you know that first question mark is that dollar one equivalent and you know set it to that string for the ID so the the other kind of parameter is what's called named parameters and out-of-the-box postcards doesn't support name parameters it only supports number of parameters but the there's a lot of libraries you know for almost every problem like programming language which provide named parameters on top of that interface that they're providing so this is an example from spring JDBC for for Java which we're instead of having dollar one dollar two dollar three I have named parameters for you know : name : air and sceles it's a little easier to understand what's going on because I say oh I'm saying the name parameter to the posters open the year to be 2017 if you're trying to do like a you know this like simplistic example you could say oh it's not that different from like one or two if you have like a huge sequel statement where something just buried inside like a dollar one then you're trying to see what the value is for it it's much easier to catch like you know errors of what what this thing is supposed to do if it is a name parameter because then you don't have to kind of figure out hey what did dollar one refer to in this particular context so if you are you know if you do happen to be composing sequel using identifiers like you know I want to put together like a reporting system that says hey the user gives me a table name I'm gonna run a select star on top of that table to bring it in you have to make sure to escape those identifiers so what that means is you call this function in Java it's a function called escape identifier on the connection and what that function does is you know if you give it like a table name it'll give you back the table name in a way that you can inject into a sequel statement that's not gonna that would be properly coded so if that you know table name has spaces in it it'll wrap it in double quotes if it has quotes inside of it it'll escape those quotes and this function is part of the part of the connection on the PG connection class so if you wanna do the same thing inside of like node yes it's not built into the driver the way it is in Java but no js' has like thousands and thousands and thousands of packages like separately there's a really nice one called PG format which if you're familiar with the equivalent format function inside of postgrads as a PL PG SQL function it's almost like the mirror image of that function so this is actually much more powerful than the java version of it because you can give it a full string to say hey select star from in that percent I gets replaced as an identifier the percent L gets replaced as a literal the SK surplus does you know just as is the string so you have like the full full ability to compose anything together and it makes it much easier to actually put something that's not gonna blow up in your face because the other things I want to be Escape they're gonna be escaping the things that shooting pool not so one other kind of general a lot of this stuff you'll see it's just general guidelines for application development but making things easier actually makes things more secure too so when you have sequel strings inside of source code my rule of thumb for this is if you can't just fit it on one line with like a very small number of things being accessed like one table one column one where Clause it probably shouldn't be there so a lot of the times when you you know when you're accessing a database if you're going to run or mall this stuff is uh you know all this stuff is kind of abstracted away from you the arm is the one that generating the queries you don't have to worry about the actual sequel composition of the sequel but almost every application kind of gets that point where that you know for the secret sauce for that app whatever you're doing it just doesn't cut it to go through a norm you end up having to like you know compose sequel together and you know if you have that with one-liner it's you know it's something that if you just look at okay this looks ok it's getting the name there's nothing funky going on here when you start getting like multiple lines inside of there with like multiple things happening not only is it not like not only is it hard to understand what's going on when you see that inside your app code it actually looks really bad inside of your app code too so some programming languages have like better stringing lining than like Java let's say but you know I've seen way too many applications uh you know where you look in there's like a diff or something inside of the code and it's like a diff of string composition and there's no syntax highlighting and the things inside quotes and you can't really tell what this change did or whatever it is and that's the kind of stuff that leads to you know bad string composition for uh for sequel so how do you avoid this one easy way we found is you just have a directory or sequel files I mean this this seems like one of the it's like simplest things that you could do but actually works really well and all this is is just a directory where inside that directory you have a file and each file is just one command or one query or one whatever it is and you know it could something with like two lines three lines you give me something with ten lines doesn't matter and what's particularly cool about this is it doesn't matter what language you're using to access it either so if you want to access this from like you know from Java you can have a really simple wrapper it says hey just load the sequel and the naming structure for what you're loading is just you know the directory structure and you don't have to put the dot sequel on there they're just you know flowed the foo find my idea whatever it is same thing from nodejs if you have to be running a an application which has multiple programming languages it's particularly cool because you can share this across multiple languages so if you know if I have some query if it's hard-coded in this Java file it's also inside of this go file it's also inside of there's no J's file I have to change in three places whereas if I have it in one place and it's externalized I only have to change in one place I know everywhere else is gonna get it at the same time and when someone's looking at the different you change on this thing you immediately see just just the lines that you change so again this isn't them these are the kinds of that make it easier to actually keep your code secure because you're separating out the stuff with the sequel related from the stuff that's just purely application related so I'm gonna give a you know one more quick example over here so if you look at this uh this code right here I have a sequel file it's you know it's performing a function call and then I have a name parameter template over here where I'm executing this function can anyone point out you know like what could go wrong in this situation no takers I mean the same time the board is uh I mean it kind of gives it away the name is being passed as a parameter you know so the thing is you don't know and the reason you'll know is I haven't said what that function does so if I'm just calling something I could evoking something with the with a parameter it could be a name parameter but if you don't know what that foo bass function is doing it could be composing sequel internally so you know as the application developers your job if you're invoking something inside to know kind of all the way down how safe is this thing gonna be so so kind of wrapping up the the kind of topics went over here you want to minimize the privileges for what your thing does because you know that tiered approach we were talking about that defense-in-depth if something does go wrong if you do have a sequel injection inside of your application if it's done with a read-only ID you don't have to worry about dropping your database so you know it might be bad you will have like you know data exposed to someone if there's a bug somewhere but you're not going to delete everything you're not gonna drop your database you're not gonna make catastrophic changes if you're not updating your drivers you really should and you should get notifications for it if you're not using SSL or you don't know if you're using s as well as something you should also check on the again there's no reason not to be using it unless you're in a very specific situation in which case you know exactly what you're doing probably and just be using parameters for just about everything there's you know unless you have a hard-coded constants ID your query for the actual thing that you're executing all user input should be going through parameters there's there's no excuse relief for it not and I'm a big fan of doing everything with named parameters because I think it makes the code more legible for whatever you're reading and then finally for externalizing sequel it just makes everything a little easier to to work with because at that point you know you're only dealing with the sequel itself you know kind of on its own if you want to get fancier with that externalize sequel you can actually do is run static analysis on it and then see if the outputs per like the queries that you have in that external I sequel matches up with the with the uses to type your application so I don't have as part of this presentation but you want to talk to me afterwards there's some really cool stuff you can do with dynamically generating that off of the sequel and then doing type check it's how you braaap to see hey when I read this name column my putting inside of a number is inside of a string and what that so with that many questions going once going twice sold [Applause]

Channel: Postgres Open

Views: 4,061

Rating: 4.9285712 out of 5

Keywords:

Id: jlkWv57bBDQ

Length: 39min 49sec (2389 seconds)

Published: Wed Sep 27 2017