Automatic Database Filtering

[00:00:00]
>> Kyle Cook: So really what we need to write is a function that will convert between these two formats. So we can come into our builder right here, we have our can function, that's totally done, we have our pick permitted fields, that's done. We're now going to create a function called toDrizzleWhere. To Drizzle where, and what this function is going to do is it's going to convert our conditions into a Drizzle syntax.

[00:00:24]
Now, depending on what database provider or ORM you're using, your function will look very different than mine, but this is just an example of what Drizzle would look like. Again, this is going to be a generic function using the exact same generics we've used before. There we go. And let's define the properties for this. Obviously the very first one is our resource. And the next one is going to be our action, so we can come in here.

[00:00:52]
Resources, resource, action. Now we don't need anything else inside this function because all we're doing is we're just checking very simply our resource and our action and then turning that into a Drizzle query by getting all of our permission code. So let's get our conditions. That's going to be coming from our permissions for the specific resource that we're checking. I want to filter these permissions.

[00:01:14]
And I specifically want to get it where the action is equal to our current action. This just gives me all the permissions that match the resource and action we passed in. Then what I want to do after that is I want to map through these, and I specifically want to get just my conditions, so we can get the conditions from them, and then we'll do a simple little filter on that. If I can spell properly.

[00:01:36]
And we only want to get them where our condition is not equal to null. There we go. So this is going to give us an array of all of our different conditions where we actually have conditions for our permission. So if we scroll up a little ways to give you a good example of what this would look like, we check our author real quick, you can see they have two permissions for Project read. So this will return to us an array, and the first value in our array would be this object right here, and the second value in our array would be this object right here.

[00:02:03]
If we were to look at document read, you can see we would have three things that would be here. We'd have this object as our first value, this object as our second value, and this object as our third value. So essentially we just have an array of all the different conditions that we have access to, and then we can convert that array into a Drizzle query because we know that each allow statement essentially is an OR, so this or this or this, and every condition inside that object is an AND, so we can combine together a bunch of ands and ors to essentially give us a SQL query for our database.

[00:02:35]
So let's go all the way back down to where we have this. We have our conditioned data. Next thing I want to do is get the table that I'm going to be executing this data upon. This is where we're going to have to do a little bit of TypeScript hackery to get this to work, but we're going to be using the PgTableWithColumns type, and we're just going to pass in the any type here just to make it easier to work with our particular TypeScript.

[00:02:55]
So just as any table inside of our database, it doesn't matter what it is, and we're going to set this equal to if our resource is equal to project. Then we're going to set that to our project table. That's our table from our database, otherwise we're going to set it to our document table. Obviously in a larger scale application, you would need some type of lookup table for this. For our simple application, we just have two types, so we're just going to be using a simple conditional check like this.

[00:03:27]
Next we can do if our conditions.length is equal to 0, that means we don't have any restrictions at all on this particular thing, so we're just going to return as is. Now, technically this would mean that they don't have access to anything because this means there's no permissions that match their thing, so we should never get to the point where we're giving ourselves a database query if they don't have permission, because if we take a look at our services layer, give me a second to pull that up.

[00:03:54]
You'll know that anywhere that we do our database query, we first check that they have permission first, so they have to have at least one permission before we ever try to filter our database query. So in the case where they don't have any permissions, we're just going to ignore that case completely, we should never get to this point. You could throw an error or do whatever else you want in this particular situation.

[00:04:13]
Now what we can do is, as I said, each condition on its own is an OR query, so we can wrap these in an OR query using Drizzle, and we can take all of our conditions, and we just want to map through these and convert each condition into an AND query. So we have our condition just like that. And what I want to do is we'll return an AND query because each object or each key inside this object is anded together into one single thing, and we can just get all those by using Object.entries on our condition.

[00:04:45]
We then want to map through those with the key value pair. And now here we can return whatever data we want. So this key, if I spell that properly, the key is like the name, so status, the creator ID, the ID, and the value is like the draft or the published or it's going to be the ID of the user, something along those lines. So first of all, if our value is equal to null. There we go. That means that essentially we're trying to check against null so we can use the isNull check inside of Drizzle and just pass along our table key.

[00:05:18]
What this little bit of code is going to do is it's going to say, OK, check the column in our database associated with that key, so status, document ID, whatever it is, check to see is that particular value null. Otherwise, if we're not doing a null check, we just want to check equality so we can just return equality, where we're going to be checking our table key, again, the column in our table that we're trying to check, and I want to check it against the value for our condition, and that's because most of our conditions if we scroll up and look at them, are going to be a condition where we have a key associated with a value.

[00:05:50]
That's what that equal statement does, check the status column to see if it is equal to the value of published, and then it's going to add that together by checking the project ID column and checking to see if it's equal to our project ID. That's essentially what that's doing, and of course it's oring each one of these together because we're getting all those possible conditions. Now we scroll down, the only error we have is a TypeScript error because we're using any.

[00:06:13]
That's perfectly OK. We can now use this toDrizzleWhere and replace all the instances in our code where we had that manual user where clause. So this is back inside of our services file again. Instead of this user where clause, we can completely get rid of this. And instead use our permissions.toDrizzleWhere, pass it in our document and pass it in our read, and this is going to give us back SQL syntax that looks just like this, but it's automatically handled by our permissions for us.

[00:06:39]
So this entire section, I can completely remove it because it's handled for me automatically. This whole user section up here is no longer needed anymore, and now my project documents are going to be properly filtered for me, and I can actually clean up a lot of my imports up here. So let's just do that while we're at it. And now let's actually make sure this works. So if we go to our document, let's log in as a user that's quite restricted, our viewer, for example, you can see that they only have access to published and archived documents inside of this page.

[00:07:05]
Now let's go ahead and say, you know what, we want to change this. We want to change it so that the viewer has access to more data. So let's find our viewer permissions. Here we go, and let's change something about their document. Let's just allow them to read all documents. So we can say document and we can say read. They now have permission to read all of the documents in the entire database. If we give this page a refresher, actually, I believe the reason why this is not working is because I didn't actually add this case into our system.

[00:07:34]
I'm never actually checking to see if they have a condition where they can view everything on top of other stuff. So here I'm filtering out those null conditions. I need to actually change this code slightly. So here's our conditions, including our null conditions, and what we need to do is if conditions.some, oops, if any of those conditions are null, then we just want to return undefined because that'll not filter our database at all.

[00:08:05]
Then what we can do is we can make sure here we filter out all the null ones, even though we should never get to this point, but TypeScript is not smart enough to know, so we'll just say not equal to null. There we go. Now let's actually test this. We come back here, you can see by doing that, by adding that little edge case, we are now able to view all the documents because we added that one permission.

[00:08:27]
If I were to change my permissions up here for our viewer, let's find that again, we'll get rid of that read all permission wherever it is, because obviously they can't read everything, but let's say that we want them to read only draft documents and archived documents. It should now hide our published documents, and if we go back to our page, you can see our published documents are automatically hidden.

[00:08:47]
So this is the power of creating this syntax that essentially converts from our JSON to our SQL, is that anytime we update our permissions, it also updates all of our SQL queries for us automatically. There's a lot of TypeScript that goes into it, but once it's in place, it's quite nice to be able to use. Now let's change this back so we have the exact correct permissions, and that's pretty much everything we need to create the advanced version of our attribute-based access control.