‫What is going on, guys?
‫My name is Hussein, and to end this video, I want to do something a little bit different.
‫I built this little bit to do application, which has posters on the back end and no jass on also the
‫back end, I guess.
‫And it was a very simple ish Demo five app that literally you can click add to do.
‫Then if you click, it will literally delete that to you.
‫So it was a little bit of a routing app, rest API, nothing fancy.
‫However, I want to point up some little bit of a not so best practice that I used in this app and I'm
‫going to show you how to do better and essentially, especially on the back and what we're talking about,
‫the connection itself.
‫Right.
‫And what what can we do?
‫And I talked about that where in another video where some of the best practices that you can use when
‫you connect to a database, you need to establish a specific database user with specific permissions
‫for each route.
‫And yeah, it's a lot of work, but it will pay off in the long run.
‫It will give you more security.
‫It will basically group your you routes.
‫Like if you're reading, you don't need to like what I'm doing right now.
‫I'm logging as a postscript.
‫I'm looking at as admin at the back end and I'm reading.
‫Right, that's just a waste.
‫Plus if someone injected some sort of a malformed sequel to inject sequel, they can drop my entire
‫database because I am I am admon.
‫Right.
‫I'm logged in as admin.
‫So it's not really a good practice to log in as admin on your Web application.
‫So I'm just showing I'm going to show you how to create a certain user's given certain permissions and
‫create different pool of database connection.
‫And I talked about bullying that I was connection point and go check out this video if you're interested.
‫So how about we jump into this video?
‫This is what I'm going to try to do in this in this video.
‫All right.
‫So the first thing to explain here, what we're doing is I have a pool, a connection pooling.
‫This is a feature and the PJI library that I'm using for NOGs.
‫And what it does essentially is it starts with a certain number of database connection.
‫And when you issue a query, for example, I want to read these to DOS, you can just use the pool to
‫issue a query.
‫And what that does is it will reserve one database connection, one TCP connection, send that query
‫right.
‫And then once that query comes back, it will return the result and then release that connection back
‫to that to the pool.
‫And the reason you want to do that and you don't want to use a single database connection and send all
‫your queries in that single client is because you cannot guarantee that which request comes first and
‫which response comes first because of pipelining.
‫We have the same problem.
‫Right.
‫So it's a very bad idea to use the same TCP connection to send multiple database queries because some
‫of them can return before the other.
‫And you cannot guarantee what is a request in this case.
‫Right.
‫So that's in general, that's the idea of this.
‫And then we're using pooling for this idea of pooling and you can control pooling.
‫And I talked about that in another video.
‫So what I want to do here, what I'm using for the pool is is the postscripts user, which is so bad.
‫Right.
‫No need to use that this this God mode to connect to the database.
‫So what we're going to do here, as soon as I have one route that does the reader to DOS and I think
‫one Roedad create that to do and one route that delete this is do I'm going to create three users.
‫I can do with two, but I want to be fancy a little bit.
‫Gref three, but one with only read one would only create and one would only delete.
‫Right.
‫And I'm going to use these each pool through three type of pools and use one each for each.
‫So how about we do the code first and then we create the user.
‫Let's do that.
‫Right.
‫So another thing, bad thing I'm doing is I'm not getting the password in the code.
‫That's a bad idea.
‫The best thing is to use environment variables.
‫And there are many ways to do that.
‫And it's just it's a little bit hectic for me to use the variables for tutorial.
‫But do you know the ideas guy?
‫This is a well known thing.
‫I might create another video and show you how to do that.
‫Maybe I'm going to do it in this video.
‫Who right.
‫But it's really simple, you just process that and then dot the variable that you pass in, maybe maybe
‫I'm going to do it later.
‫No.
‫So what I'm going to do, I'm going to create a DB Reid pool.
‫Right.
‫And this is the user for DB Reed is called DB Reed.
‫And the password is here's the thing goes about passwords for database users.
‫You have to create the longest possible, most incomprehensible password ever and store it in some sort
‫of a key vault.
‫Right where you read that.
‫That's the best practice.
‫Don't store it in the code.
‫Obviously, don't send it.
‫Just I guess you can store it in a text file somewhere as a config that is not pushed to the source
‫or source code, but you can use that as well.
‫But I am going to use DB redivided because.
‫Because why not?
‫All right.
‫Because if I implemented every single best practice that this video is going to become like three hours.
‫Right.
‫You get an idea.
‫I'm going to talk through things that is best practice, but I'm not going to implement everything because
‫it's just too much.
‫Right.
‫So I'm going to create three of those DB Reed pool.
‫Right.
‫And DB create bool.
‫I got to create the old use lowercase.
‫And.
‫Do we read this guy's deep delete pool, DB daily delete, they've been delete, right, so three pools,
‫guys, three.
‫Different polls in the Connect method weren't going to connect.
‫I'm going to to actually connect all the polls together, right.
‫Which is the DBI, cripple, DBI, delete pool, be report.
‫And this is awesome, guys, because those pools will start maybe it depends on the number of connections,
‫the minimum number of connections that you start with.
‫And this is configurable.
‫And I talked about that in the polling video.
‫And you can start like, you know, let's say you have so many leads.
‫Right.
‫So you can start with obviously more people read and write in general.
‫It depends on the U.K. So you can start with that.
‫A lot of reads polling since I got 10 and the delay time, obviously, no one delay it so you can start
‫with zero.
‫And if someone attempt to delete, we're going to open up a new data DB DB connection and then obviously
‫we're going to keep it until it dies.
‫Keep a lifetime.
‫All right.
‫So this stuff, obviously, let's go to each route and start using this stuff.
‫So the reproduce function uses the pool.
‫But we don't want to just use the general rule.
‫We're going to use the DB read pool to select text from doodles.
‫Another I think we don't need to do is as this is bad.
‫We should again, guys, I'm just going to talk about the bad practices here and what you can do to
‫to elevate them, but I'm not necessarily going to implement them in this video.
‫So this is bad because this is an unbounded query.
‫Some some people say, what did you say here?
‫Inbounded.
‫But because of my accent, maybe getting Gatun, it's called inbounded.
‫That means there is no bound for it.
‫That means if the if you have like three million rows here, you get to return all the three million.
‫That's bad.
‫Always limit the stuff is a limited zero 10 or do some paging or something like that.
‫Right.
‫Don't do that.
‫Don't do paging Clydeside.
‫That's bad idea, but it's a tutorial so.
‫So yeah.
‫Debride pool déby create to do.
‫We're going to use the create pool and we're going to create this user going to only have create permission.
‫So it's going to be able to insert but it's not going to able to delete as that make sense.
‫Right.
‫So even if you snuck out into the insert, if someone managed to do a sequel injection here, they won't
‫be able to drop my database.
‫So they won't be able to delete my stuff.
‫They might be able to insert some stuff.
‫Right.
‫The truth is there is the riskiest here, right, if you think about it.
‫So that's why obviously I don't have any authentication whatsoever on my app.
‫Right.
‫That's another layer of protection that you can add.
‫Add them.
‫I guess this is a clean side front end, right, with JWT or just normal authentication where nobody
‫can actually delete unless they own that stuff.
‫Right.
‫And you can add multiple.
‫So I'm just currently deleting this particular item and I'm using this particular syntax which avoids
‫avoid SQL injection as well.
‫That's another way to avoid second injection, but that the function the creator of this library managed
‫is doing the sequel injection for us, that is doing the sanity sanitation for it instead of doing like
‫this one.
‫Right.
‫That's that's just way better.
‫I think that's it if I started this, let's see if we're going to Edwards.
‫Obviously, we're going to get some.
‫Edwards now says, OK, what the hell's Debbie create?
‫I don't I don't have it right.
‫So now we go to admin and we start creating all these beautiful rolls.
‫We're going to create a new user or roll call.
‫Debbie Reed.
‫Right.
‫Was the deliberate why did we call it they buried Debbie Reid?
‫Deep, deep, deep, create, so deep read, write, read, only access to that, to that, to do table
‫right and privileges, they can obviously log in.
‫Are they super user?
‫Hell no.
‫Create rules.
‫Hell no.
‫Hell no update catalog.
‫Hell no.
‫Inherit rights from the patent trolls.
‫OK, I don't know why it doesn't have a patent so it doesn't matter.
‫And security.
‫Right.
‫Well that's it.
‫That's all we need.
‫The trick is they can lay a log in or not.
‫And there is there is a reason where you you sometimes don't need this rule to the login because you
‫might have just a roll with sort of permissions that you can assign to different tables, but it doesn't
‫have an actual user behind it.
‫So we save that stuff and we're going to hug and create and create DB, create, create user.
‫A user would only create or insert permission, and the password is DBI create and what they can do,
‫can they only login save that we can do boom db delete a user with delete permissions and delete the
‫day name with permission privileges.
‫They can log in.
‫Awesome save.
‫Now if I go to the to Tudou database, my Tudou database and I go to schema, I go all the way to my
‫to do a table.
‫That's my beautiful table.
‫Right click properties.
‫Right.
‫There are schools that allow you to do the same thing I'm about to do but.
‫It depends really on you what what do you prefer and now will go to privileges, obviously this this
‫the owner of this stable is Postgres and that's a good thing, right?
‫You want the owner to be someone who has, like, very great permission so that the measly different
‫other users that consume the stable only have so much permission on the stable.
‫Right.
‫So they can they cannot drop your whole table or something like that.
‫So I'm going to add a roll, call them PJI create and this permission is only insert because it's a
‫great right.
‫Can they select.
‫Yeah, I didn't really select.
‫So let's try without selecting.
‫Maybe because I don't know what the internal library does.
‫If the answer does select then we might run into an error.
‫But let's, let's give it the minimum permissions here.
‫Insert only rice or a and then I'm going to create another one.
‫You call it delete and this guys only can delete.
‫I don't have I don't have an update route, so you might want if you have if your app has an update
‫out, you might do the same thing as here as well.
‫All right.
‫So this is.
‫Good, good.
‫Now we're going to create a debride, which only, oh, my God, this has to be only select, right?
‫I guarantee must be selected.
‫Well, I selected it.
‫Didn't I?
‫Debbie Reed, Debbie deletes.
‫Looks like we're good, so we have to create.
‫We have created and thought, why does it say, A, I don't know why it's called a right to be read,
‫only read.
‫And not only did, but we save this puppy and now if I start my app, let's see if we get any errors,
‫no beautiful errors.
‫So now let's go to my app and let's see if it works normally.
‫If I look, it just we know reading works down because I just read my existing RO Rose.
‫Right, because what what do you have, Rose?
‫We have this is the F thing I get if I insert another thing.
‫But the old failed.
‫OK, let's take a look.
‫Let's take a look at it.
‫We failed to insert as I told you guys, I wasn't sure why, why it would fail.
‫So let's let's take a look.
‫So.
‫Create to do is what failed, right, see, I did use the create pool, I am inserting into that and
‫maybe we just put it here breakpoint and see what exactly the problem.
‫So another thing, if you notice, I didn't return to the error to the user because one of the best
‫practices, you don't need to tell the user what what is going on in your database.
‫Right.
‫So you need to create your own errors.
‫So if you return it, that's kind of bad, because sometimes E will contain the source code and contain
‫line numbers.
‫And and hackers might use this to give insight into your application, where your code is, where your
‫stuff is and might run into a problem.
‫So so that's why I'm certain of just falls untrue.
‫But obviously that's also confusing because you need to tell the user like no.
‫Right, we failed, but we don't know what's going on.
‫So you need to specific error exceptions and and stuff like that.
‫Oh yeah.
‫Why do we have here.
‫Hmm.
‫Come on.
‫It says, permission to know for sequence.
‫Oh, that's beautiful, that's beautiful, guys, that is so beautiful.
‫OK, let me explain what's happening.
‫OK, I completely forgot about that.
‫So.
‫So this is actually powerful stuff, so.
‫If I have to two columns here, I'd which is which is a primary key, which is cereal behind the scenes.
‫This actually creates a sequence and a sequence guys as a database construct that basically when you
‫select it, it gives you the a number and you selected again, it gives you another number as just a
‫sequence of numbers.
‫Here's the thing I gave my DBI create user access to my to do so I can insert barve, but the the act
‫of inserting a new row in my to do table triggered that sequence.
‫Right, right.
‫And I need to get a new sequence of what this device does.
‫It reads from the to do ID sequence in order to get the next sequence.
‫But guess what, the user do not have permission on reading that sequence.
‫So we need DB create to get access to the sequence.
‫Not everybody else.
‫Just a read should be enough.
‫Right.
‫And, and hopefully that should be enough with the select in this case.
‫That's a good practice.
‫That's a good, that's a good.
‫I'm happy that we failed here so we can learn the process that's going to run this beautiful the by
‫by the way guys will be available in the description below and I'm going to share that with you.
‫I need to actually write.
‫Let's try this up again.
‫Boom, boom.
‫Oh, another permission denied again, I thought I give you the permission.
‫So is is select is not enough permission enough or sequence to DOS ID sequence, but I gave you access.
‫That's interesting.
‫What what what exactly do you need, apparently select not enough guys.
‫All right, let's read it again.
‫Despite me giving you the security, I give you a read.
‫With grant permission?
‫No, no, no, definitely not maybe usage, that's a different I don't know what usage means here.
‫There's a difference between selecting usage.
‫Let's give it usage.
‫Maybe we'll read about that later.
‫Refresh.
‫And then boom, boom.
‫Okay, looks like we did it, guys, so the trick was not select persay, its usage, the idiot reading
‫a sequence, the consequences different than using the sequence to generate a sequence, apparently.
‫Right.
‫So that's the difference here with with with this sequence.
‫We need to give them two permissions, DB sequence and DB grant and select.
‫Well the I mean, use it sorry usage.
‫And that's awesome.
‫How about delete does delete work.
‫Did it it.
‫Oh oh oh.
‫I don't think delete is working.
‫Let's take a look.
‫Why is delete not working.
‫Because if I click.
‫Let's put the what the heck?
‫All right, David, did it pull boom, why?
‫Permission, dad, permission to not for table tattoos, all right, guys, so I think I know what's
‫going on, but let's first fix the bug in my client's side.
‫Why, when I click, delete and I fail and I return false, nothing happened and my client doesn't tell
‫me anything.
‫So I'm going to go to my index page and see delete where's my delete method here.
‫And it says, OK, if if not succeed, I should have failed.
‫Right.
‫But I'm not even getting that.
‫So I'm going to do a little bit debug on the client side here to see why, why that's a problem.
‫So let's just do some little bit of client side coding here.
‫It's been a while, it's been a while, beautifulest demo code.
‫Let's do that boom and then refresh.
‫And then it's just before I clicked on it, did I?
‫Yes, I did.
‫Now this will fail.
‫Go ahead and fail, false return false.
‫We got back here, the success, this is true.
‫What are you talking about?
‫How is this true?
‫That's wrong.
‫That is wrong.
‫How is a result true?
‫We clearly right.
‫Let's do it again.
‫Let's do it this side here, I'm going to move my breakpoint somewhere else and let's just remove this.
‫So I did remove these, OK, so we return false lesser dude to delete to do all of that.
‫Guys, we're not using this stuff that's just dumb and.
‫Right.
‫So the bug here is this should be like that, right.
‫Although sex is equal, this we're not actually setting that thing, that's just bad.
‫OK, now if I let's try without debugging, because I have confidence this will work, if I do the up,
‫that's it failed.
‫That's exactly what I want.
‫OK, now it failed.
‫And here's why it failed.
‫Why did we give the delete permission, guys?
‫The DB create DB delete.
‫We only give it delete but.
‫If you look closely to what what that delete user is doing.
‫It is not only the leading, it is actually it needs to identify what what are the rules in order to
‫delete it.
‫So technically it needs to find the ID and the database and then delete that.
‫The act of fining is a select, so you need to give it a select.
‫OK, so that's that's what was missing here.
‫So we go properties, security, DB delete, give it a select which is not a big deal.
‫And just like that refresh now we have refresh here.
‫Boom deleted add to do there to do another Dinu boom boom boom boom.
‫That's awesome man.
‫Now we have a beautiful application with a specific database user for each resprout.
‫How about that guys.
‫Help you enjoy this video.
‫Give it a like if you like it share with your friends.
‫I'm going to see in the next one.
‫You guys stay awesome.
‫Goodbye.
‫Check out the other contenders gentled.
‫By the way guys, we talk about back in mostly in the shower.
‫That's my specialty right now.
‫The next one guy's awesome by.