WEBVTT 00:00.000 --> 00:14.760 Okay, last talk of the day, I think it will be quite intense and interesting. 00:14.760 --> 00:24.760 So please welcome Jimi Angelacos and he will tell us why RLS sucks and if we can use it. 00:24.760 --> 00:25.760 Thank you very much. 00:25.760 --> 00:33.080 Thank you, thank you for being here. 00:33.080 --> 00:36.320 This is all about knowledge sharing, right? 00:36.320 --> 00:39.840 I'm not out to impress anyone. 00:39.840 --> 00:43.760 I was trying, well let me get into that in a moment. 00:43.760 --> 00:47.000 Let me talk about myself and my favorite subject. 00:47.000 --> 00:51.880 So I'm a systems and database architect, I'm based in Edinburgh in Scotland. 00:52.440 --> 00:56.880 I've been using open-source and trying to contribute for the past 95 years. 00:56.880 --> 01:03.880 I've been using Postgres exclusively for the past 16 years and now I'm a contributor to the project. 01:03.880 --> 01:05.880 My book is coming out. 01:05.880 --> 01:08.880 It's going to print very shortly. 01:08.880 --> 01:10.880 Postgres scale mistakes from Manning. 01:10.880 --> 01:18.880 I also have another book that I co-wrote with other very interesting Postgres people. 01:18.880 --> 01:25.880 And I've also written a Postgres extension called PG status for visualization of internal statistics. 01:25.880 --> 01:28.880 And time series analysis of those statistics. 01:28.880 --> 01:32.880 Now let's get into what this talk is about. 01:32.880 --> 01:38.880 First of all, we're going to see what role level security is. 01:38.880 --> 01:45.880 Then we're going to talk about when to use it, how it works, and how to use it. 01:45.880 --> 01:48.880 Makes sense. 01:48.880 --> 01:56.880 We're also going to examine what's wrong with it and what we can do about that. 01:56.880 --> 02:03.880 And also some other things to try that are interesting with role level security. 02:03.880 --> 02:07.880 So the motivation coming back to what I said before, 02:07.880 --> 02:21.880 motivation is I had a customer that wanted their applications users to not be able to see each other's data. 02:21.880 --> 02:31.880 But what's interesting about this customers that they were used to the fact that their application was usually badly coded. 02:31.880 --> 02:34.880 And they were like, I don't trust my application. 02:34.880 --> 02:39.880 It would leak data from one customer to the other. 02:39.880 --> 02:43.880 And so they were looking for a solution for that. 02:43.880 --> 02:48.880 Because their application used to have things like in the past, don't try it now. 02:48.880 --> 02:52.880 Slash user slash 1234 slash data. 02:52.880 --> 02:58.880 And if you guessed the URL of the other user, the idea of the other user, you were able to see their data. 02:58.880 --> 03:00.880 Nice. 03:00.880 --> 03:07.880 So something that can protect you against that is role level security. 03:07.880 --> 03:17.880 So role level security gives you a finer-grained control over which rows are visible to which 03:17.880 --> 03:18.880 users. 03:18.880 --> 03:24.880 And we're talking about database users now. 03:24.880 --> 03:38.880 So it provides additional security beyond the table or column level privileges that we're used to with the grant statements in SQL. 03:38.880 --> 03:42.880 So it's a type of access control list. 03:42.880 --> 03:49.880 And it saves you the effort of doing your security filtering in the application layer, right? 03:49.880 --> 03:52.880 Because if you have it in the database, it's already there. 03:52.880 --> 03:54.880 The data's already protected. 03:54.880 --> 03:57.880 You don't need to write code to do that. 03:57.880 --> 04:04.880 So when would you use role level security? 04:04.880 --> 04:11.880 Some scenarios are you could have confidential data. 04:11.880 --> 04:15.880 And you want to restrict access to sensitive records. 04:15.880 --> 04:22.880 For example, let's say your doctor, you don't want your other staff to be able to see the medical records. 04:22.880 --> 04:24.880 Only you should be able to see them. 04:24.880 --> 04:26.880 Just an example. 04:26.880 --> 04:33.880 Or it could be departmental separation or separation of roles in your organization. 04:33.880 --> 04:40.880 For example, only the HR department should be able to see HR related content in someone and so on. 04:40.880 --> 04:45.880 Only finance should be able to see finance and so on. 04:45.880 --> 04:48.880 Another use case is multi-tenancy. 04:48.880 --> 04:55.880 So if you have systems that are used by multiple tenants, let's say by multiple customers, 04:55.880 --> 05:03.880 you want them to be able to see only their own data in the database. 05:03.880 --> 05:08.880 So it does give you finer grain visibility control. 05:08.880 --> 05:15.880 You select which roles out of the same table a specific user can see. 05:15.880 --> 05:21.880 So you don't have to have one table for each customer and have 10,000 tables with their data. 05:21.880 --> 05:26.880 You can have a table that says transactions. 05:26.880 --> 05:33.880 So how does RLS work? 05:33.880 --> 05:40.880 Similarly, what you see as the result of RLS is that from the user's perspective, 05:40.880 --> 05:45.880 the rows that are not supposed to see don't exist for them. 05:45.880 --> 05:52.880 So if let's say there's a thousand rows in the table and you're only supposed to be able to see 100 rows out of that table, 05:52.880 --> 05:57.880 if you do a select count, the number you will get back is 100. 05:57.880 --> 05:59.880 The other rows do not exist for you. 05:59.880 --> 06:02.880 You don't even know how many they are. 06:03.880 --> 06:18.880 So you can tell that RLS is built into the internals of Postgres and it's like a barrier that doesn't allow you to see the things you're not supposed to see. 06:18.880 --> 06:22.880 So how does the barrier function? 06:22.880 --> 06:31.880 Key concepts are the policies where you define the conditions for reading and modifying rows in that table. 06:31.880 --> 06:42.880 And also there's the security barrier that needs to be applied to things like views, 06:42.880 --> 06:56.880 so that people are not allowed to bypass the row level security by doing clever tricks with the query optimizer, let's say. 06:57.880 --> 07:07.880 So, please move to the next slide. 07:07.880 --> 07:10.880 So how does RLS work? 07:10.880 --> 07:14.880 It is exactly literally an access control list. 07:14.880 --> 07:17.880 It's not like an access control list. 07:17.880 --> 07:25.880 So internally, what you're doing is you are effectively adding where to your original query. 07:25.880 --> 07:32.880 So it's like show me the transactions where user equals that. 07:32.880 --> 07:38.880 So the user is only able to see those transactions. 07:38.880 --> 07:44.880 And the policies that you create can be permissive or restrictive. 07:44.880 --> 07:45.880 What is the difference? 07:45.880 --> 07:50.880 So permissive policies, policy A and policy B are the default. 07:50.880 --> 08:02.880 And it means that if any of the permissive policies match, right, if I, for example, policy A is that I'm in the finance department, 08:02.880 --> 08:08.880 I don't need policy B to be able to read the data because it's OR. 08:08.880 --> 08:15.880 Get it? So if one of the policies that are permissive are satisfied for me, the user, I can read the data. 08:15.880 --> 08:19.880 Restrictive policies require an end. 08:19.880 --> 08:26.880 So if let's say there's a policy C that forbids something, but policy D allows it, 08:26.880 --> 08:31.880 because they're restrictive both have to allow it. 08:31.880 --> 08:41.880 So as the result of policy C not being satisfied and being restrictive, it forbids access to the data. 08:41.880 --> 08:45.880 Clear? Cool. 08:46.880 --> 08:53.880 All of this is visible in the Postgres catalog inside PG policy. 08:53.880 --> 09:04.880 And PG policy has columns like policy RLID, which table, it's the idea of the table that the policy refers to. 09:04.880 --> 09:14.880 Policy Command does the policy apply to select inserts, updates, deletes, or all of the above. 09:15.880 --> 09:22.880 Is it permissive to or restrictive false? 09:22.880 --> 09:27.880 And this is where the access list, access control list thing comes in. 09:27.880 --> 09:31.880 You get an array of roles that the policy applies to. 09:31.880 --> 09:37.880 So it tells you that the policy applies for these users. 09:37.880 --> 09:43.880 And then you get the specifics of the rules. You get the using clause. 09:43.880 --> 09:45.880 We'll see what that is. 09:45.880 --> 09:51.880 And the with check clause that define the policy. 09:51.880 --> 09:55.880 So let's see how you define the policy. 09:55.880 --> 10:04.880 First of all, what you have to do is enable role level security by default it's turned off. 10:04.880 --> 10:10.880 And why is it turned off by default? Because by default it denies everything. 10:10.880 --> 10:15.880 So you wouldn't be able to read any rows from the table. 10:15.880 --> 10:21.880 So now, by enabling role level security without the policy, I've blocked everyone out. 10:21.880 --> 10:26.880 So I need to create the policy that allows access to the rows of that table. 10:26.880 --> 10:35.880 So I create the policy, cost policy, on the table customer, for all SQL commands, 10:35.880 --> 10:39.880 to public, which means that it applies to everyone. 10:39.880 --> 10:42.880 The policy applies to every single user in the system. 10:42.880 --> 10:48.880 Using asterisk, we will see why not. 10:48.880 --> 10:56.880 Using customer user, which is the column in the table, equals current user. 10:56.880 --> 11:11.880 So I want current user to only be able to see the rows that have the same name as him or her or it. 11:11.880 --> 11:19.880 So whatever the current user is, this will get applied to my query. 11:19.880 --> 11:28.880 So it's like I'm adding the statement, let's say my queries select star from table. 11:28.880 --> 11:32.880 So excuse me. 11:32.880 --> 11:36.880 So select star from customer. 11:36.880 --> 11:46.880 What I wrote is effectively turning the query into select star from customer, where customer user equals current user. 11:46.880 --> 11:48.880 So that's what the using clause does. 11:48.880 --> 11:54.880 It adds a condition to the original query. 11:54.880 --> 11:59.880 But I did have a clickbait title. 11:59.880 --> 12:04.880 It does suck, but our list sucks too. 12:04.880 --> 12:05.880 Why? 12:05.880 --> 12:10.880 Because there are a few things that are wrong with the current implementation of row level security. 12:10.880 --> 12:16.880 Not in the sense that they didn't think things through back when the implemented it. 12:16.880 --> 12:20.880 But right now it doesn't work for a lot of people. 12:20.880 --> 12:28.880 Because it assumes that your application works in a certain specific way. 12:28.880 --> 12:40.880 And you may have noticed that people generally don't have their data separated by which database user it belongs to. 12:40.880 --> 12:47.880 Also, most of the time you don't want Postgres to manage your applications users. 12:47.880 --> 12:57.880 Another problem with the row system that RLS depends upon is that it has global scope, so it applies for all databases that you have on the server. 12:57.880 --> 13:03.880 So you have no fine-grained control over which users can see what in which database. 13:03.880 --> 13:10.880 Additionally, the RLS system doesn't give you any sort of reasonable way to store user attributes or preferences. 13:10.880 --> 13:15.880 So you have to put those in your application anyway. 13:15.880 --> 13:30.880 So in most cases, your application is probably going to connect to the database with the connection string with the fixed user that is just one user. 13:30.880 --> 13:32.880 And that is a real problem. 13:32.880 --> 13:44.880 Because when you have something deployed like my customer did, and they suddenly decided that they required row level security, they didn't have the data separated by a user by database user. 13:44.880 --> 13:50.880 They didn't even have a second database user in the database. 13:50.880 --> 13:58.880 So additionally, writing to the database and reading with a single user makes all they think difficult. 13:58.880 --> 14:05.880 Because from the perspective of the Postgres system, it's only one user doing everything. 14:05.880 --> 14:11.880 And changing the application usually requires a significant rewrite. 14:11.880 --> 14:24.880 So that you're able to make the application extract which users logged in and then use that user to pass it to Postgres to login to Postgres to get the data that they need. 14:24.880 --> 14:30.880 So if you already have a developed application, you don't want to do that. 14:30.880 --> 14:36.880 Additionally, aligning application and database users is tedious. 14:36.880 --> 14:44.880 You end up with a spaghetti of grant statements that overlap or add to each other. 14:44.880 --> 14:47.880 And also you have to keep them in sync. 14:47.880 --> 14:53.880 So whatever's in your application needs to be synced to the role system in Postgres. 14:53.880 --> 14:56.880 So that's a job in itself. 14:56.880 --> 14:59.880 So what can we do about that? 14:59.880 --> 15:11.880 Are there ways to enjoy the benefits of row level security without actually using the role systems directly? 15:11.880 --> 15:26.880 So one possible solution is we can set variables in our connection or session or transaction. 15:26.880 --> 15:28.880 And we can use those variables in the policy. 15:28.880 --> 15:32.880 There's nothing preventing us from using them in the policy. 15:32.880 --> 15:52.880 So we can create a policy that says create policy on transaction for all to public using tenant equals current setting which is how you get the value of a variable in the current environment. 15:52.880 --> 15:55.880 And setting app dot tenant. 15:55.880 --> 15:57.880 Why is it prefix by app? 15:57.880 --> 16:01.880 Because you don't want to mix this with actual Postgres configuration. 16:01.880 --> 16:10.880 So you create a prefix like my app that separates those variables from Postgres variables. 16:10.880 --> 16:14.880 So this works. 16:14.880 --> 16:15.880 It actually works. 16:15.880 --> 16:24.880 So you can set app tenant equals mega corp in your connection. 16:24.880 --> 16:32.880 And the policy will retrieve from the environment the current value of app dot tenant. 16:32.880 --> 16:36.880 And find that it is mega corp in the allow access. 16:36.880 --> 16:41.880 So that's a way to do it with a single application user. 16:41.880 --> 16:49.880 With a single database user connecting from the application. 16:49.880 --> 16:58.880 But what if you're feeling paranoid and someone tries to connect as mega corp because they know the name of the company. 16:58.880 --> 17:02.880 So what do you do usually? 17:02.880 --> 17:05.880 You turn everything into a UID. That's the account, yes. 17:05.880 --> 17:10.880 It's not impossible, but it's much harder. 17:10.880 --> 17:15.880 So that's the layer of obscurity to what you're doing here. 17:15.880 --> 17:25.880 So by making app dot tenant the UID, they can't just throw in a lucky guess of a company name and see their data. 17:25.880 --> 17:26.880 Cool. 17:26.880 --> 17:32.880 So what you do is you set app dot tenant to be a string that represents a UID. 17:32.880 --> 17:40.880 And then you cast it in the policy to a UID to make sure that the data type is correct when you're performing the checking. 17:40.880 --> 17:46.880 And that's it. 17:46.880 --> 17:56.880 So something to be aware of is depending on whether you're using connection pooling or not. 17:56.880 --> 18:05.880 You may want to set local this value because set local restricts it to the current transaction. 18:05.880 --> 18:15.880 So if you have like a connection pooling scenario where you fear that this variable may leak to another session, 18:15.880 --> 18:20.880 you can just set it local and have it only there for the duration of the transaction. 18:20.880 --> 18:29.880 So wrap everything that the application doing inside the transaction and then this poof goes away. 18:29.880 --> 18:39.880 When the transaction is completed or rolled back, this doesn't exist anymore, so it can't leak to the next user. 18:39.880 --> 18:44.880 Would you like us to take this step further? 18:44.880 --> 18:48.880 Cool. 18:48.880 --> 18:58.880 Next level, we have the access control list that we implemented and we add roll based access control to it. 18:58.880 --> 19:05.880 So we construct an explicit access control list and an explicit roll based system around it. 19:05.880 --> 19:12.880 So how do we do that? We add an ACL column to the table. 19:12.880 --> 19:16.880 The rule has an ACL. 19:16.880 --> 19:27.880 So we change the table transaction, we add the column ACL that contains an array of UIDs. 19:27.880 --> 19:36.880 And we just say it can't be null, it has to be at least an empty array of UIDs to avoid errors. 19:36.880 --> 19:46.880 So an array of UID in the case as we saw that we are using UIDs as identifiers for the rolls. 19:46.880 --> 19:56.880 So what we do then is we set the rolls that are granted access in the access control list. 19:56.880 --> 20:08.880 So you write a statement like set or set local, app tenant rolls, this array of two D's. 20:08.880 --> 20:14.880 Because I may need both rolls to access this data. 20:14.880 --> 20:22.880 Remember, I may need to be in finance, but I may also need to be a director in order to be able to see this roll. 20:22.880 --> 20:30.880 Okay. So what would the roll level security policy look like for such a thing? 20:30.880 --> 20:40.880 You would type create policy, transfer all policy on transaction, which is our table for all commands to public everyone. 20:40.880 --> 20:48.880 Using ACL, double ampersand, current setting app tenant rolls. 20:48.880 --> 20:52.880 You know what the double ampersand operator does here. 20:52.880 --> 21:04.880 Okay, so it's an array operator and it's make sure that whatever is that these two arrays have shared elements. 21:04.880 --> 21:14.880 So if any of the elements are there, it becomes true and therefore allows access to the roll. 21:14.880 --> 21:16.880 Okay. 21:20.880 --> 21:30.880 And never forget to alter table transaction to enable actually the roll level security that you created the policy for. 21:30.880 --> 21:34.880 So what does this do? 21:34.880 --> 21:44.880 This policy checks if any of the tenant rolls are inside the access control list for that roll. 21:44.880 --> 21:50.880 Right? Remember, this is roll based. Forget about table level security. 21:50.880 --> 21:56.880 So that's why we have an ACL column in every single roll of the table. 21:56.880 --> 22:02.880 The rolls can have attributes that define their privileges. 22:02.880 --> 22:10.880 And like Postgres rolls, they can be thought of as groups. 22:10.880 --> 22:14.880 Remember, Postgres user is a Postgres roll that can log in. 22:14.880 --> 22:20.880 Right? So when we're talking about rolls, we're talking about Postgres users. 22:20.880 --> 22:30.880 And you can also use them as groups because you can accumulate privileges by being assigned multiple rolls. 22:30.880 --> 22:38.880 So rolls can be granted to other rolls and then you have an aggregate of the privileges. 22:38.880 --> 22:42.880 Which is basically paralleling PostgresQL roll system. 22:42.880 --> 22:46.880 It works exactly like this. 22:46.880 --> 22:50.880 Do you want to dive even deeper? 22:50.880 --> 22:54.880 How would you protect this system from your application? 22:54.880 --> 23:00.880 Remember, the application can connect to the database, read and write to the database. 23:00.880 --> 23:03.880 So it can mess this up. 23:03.880 --> 23:08.880 It can set the access control list to empty for everyone, for example. 23:08.880 --> 23:12.880 But you don't want the application to be able to do that, do you? 23:12.880 --> 23:21.880 So if you don't want it to even be aware of how it can change rolls and policies, 23:21.880 --> 23:27.880 you hide the system from the application. 23:27.880 --> 23:30.880 Why? 23:30.880 --> 23:37.880 Because you don't trust your own code or you don't trust the third part of the application that has been bestowed to you. 23:37.880 --> 23:45.880 And you want to add access control to something that didn't have it. 23:45.880 --> 23:52.880 So let's assume that we're working with an app that is written in Django. 23:52.880 --> 24:03.880 What you would do from the database side is you would create a roll for that application, for example, create a roll Django. 24:04.880 --> 24:07.880 You would create your tables as normally. 24:07.880 --> 24:20.880 So for table transaction, you would have your usual example columns here, ID, which is, of course, random UUID. 24:20.880 --> 24:26.880 And amount created that and you add a column called ACL. 24:26.880 --> 24:30.880 So the ACL is in array of UUIDs as we discussed before. 24:30.880 --> 24:37.880 It's not an old default empty array of UUIDs. 24:37.880 --> 24:49.880 So in order to speed up enforcement of the policy, we would like to have an index that supports array operations on the table. 24:49.880 --> 24:56.880 So what you do is you create index on the table transaction using Djinn that supports array operations. 24:56.880 --> 25:03.880 And you just create this index on the ACL column with the modifier array ops. 25:03.880 --> 25:09.880 And that lets you perform the double ampersand operation very quickly from the index. 25:09.880 --> 25:18.880 So you can check instantly to see if someone has access to the roll. 25:18.880 --> 25:23.880 You then parallel Postgres' own system of rolls. 25:23.880 --> 25:25.880 And you create a table called tenant roll. 25:25.880 --> 25:29.880 Let's assume that in the application your tenant is called tenant. 25:29.880 --> 25:32.880 And you start in a table called tenant. 25:32.880 --> 25:38.880 So you create a table tenant roll that has a roll ID, a roll name, and a roll description. 25:38.880 --> 25:43.880 So that humans can understand what the roll is about. 25:43.880 --> 25:48.880 And then you create the table of membership to that roll. 25:48.880 --> 25:54.880 So all you need is tenant ID and roll ID. 25:54.880 --> 26:02.880 And you need them to point to the other tables to the table tenant for tenants and to the table tenant roll for rolls. 26:02.880 --> 26:08.880 And that is sufficient to implement a roll-based access control system. 26:08.880 --> 26:12.880 All you need to know is which roll everyone belongs to. 26:13.880 --> 26:23.880 And then you can determine with the policy from the ACL if that roll or the aggregate of rolls that the user is using allows them to access the data. 26:23.880 --> 26:30.880 Of course you create an index on this table for speeding up things. 26:30.880 --> 26:35.880 So what have we done here? 26:35.880 --> 26:40.880 Effectively we have removed the ability of the database user. 26:40.880 --> 26:42.880 Sorry, this is what we're doing now. 26:42.880 --> 26:48.880 We're removing the ability of the database user Django to see these tables. 26:48.880 --> 26:53.880 So we're revoking every privilege on tenant roll from Django. 26:53.880 --> 27:01.880 So that the application doesn't even know these tables of rolls and memberships exist. 27:01.880 --> 27:17.880 And then we revoke select on the table that we actually want to read from so that the application can't do a select star from the table and find out that there's an ACL. 27:17.880 --> 27:25.880 And then we grant back permissions to select to the application user. 27:25.880 --> 27:28.880 But we don't grant them for the ACL column. 27:28.880 --> 27:32.880 So that's how you make a column invisible to a user. 27:32.880 --> 27:42.880 You remove their ability to select star from the table by revoking all privileges and then you grant back the privileges. 27:42.880 --> 27:51.880 So they can select all the columns that you want them to. 27:51.880 --> 27:53.880 So we create the policy. 27:53.880 --> 27:59.880 Trans our list on transaction using our usual trick ACL. 27:59.880 --> 28:03.880 Double ampersand current setting app tenant rolls equals true. 28:03.880 --> 28:06.880 And that is sufficient to grant access. 28:06.880 --> 28:10.880 And then alter table transaction and enable roll level security. 28:10.880 --> 28:12.880 Okay. 28:12.880 --> 28:20.880 Now this system, the inner, inner workings of the system are hidden from Django or whatever application you have. 28:20.880 --> 28:24.880 How can it regulate access to the data? 28:24.880 --> 28:30.880 You expose the functionality of the system through functions. 28:30.880 --> 28:37.880 So you write a function that's called create tenant roll that allows the application to create rolls. 28:37.880 --> 28:42.880 So roll name, roll description returns you you ID. 28:42.880 --> 28:47.880 Here's the you ID of the new roll that you've just created. 28:48.880 --> 28:59.880 Get tenant rolls is I'm passing it a tenant ID tell me which rolls this tenant has in the system. 28:59.880 --> 29:04.880 Makes sense. 29:04.880 --> 29:07.880 Finally set tenant rolls. 29:07.880 --> 29:15.880 So for this tenant, make sure that they have rolls 1, 2, and 3. 29:15.880 --> 29:19.880 Set tenant rolls sets the rolls for a specific tenant. 29:19.880 --> 29:24.880 And returns Boolean that was able to successfully set the rolls. 29:24.880 --> 29:28.880 So there's one more thing that we need. 29:28.880 --> 29:35.880 For each of the tables, we need a function that adds the roll to that roll. 29:35.880 --> 29:36.880 Right. 29:36.880 --> 29:39.880 So you need an add roll to ACL function. 29:39.880 --> 29:47.880 And you also need a removal from roll ACL type function. 29:47.880 --> 29:50.880 These can be called by the application itself. 29:50.880 --> 29:57.880 For example, by overriding Django's save method. 29:57.880 --> 30:03.880 It's important to note that these functions should be written as security defining. 30:03.880 --> 30:11.880 So that they have the privileges of the user that created them and not the privileges of the user Django. 30:11.880 --> 30:17.880 Otherwise the user Django wouldn't be able to select and set those values. 30:17.880 --> 30:22.880 But they're not able to do it by reading and writing to the database directly. 30:22.880 --> 30:27.880 They're only able to access this system through functions. 30:27.880 --> 30:34.880 And remember when using security defining, you should probably at the bottom of your function definition. 30:34.880 --> 30:38.880 Set search path equals public and then PG temp. 30:38.880 --> 30:48.880 So people aren't able to easily override your function by creating a temp function. 30:48.880 --> 30:57.880 So let's talk about a few things that can catch you out when using our less. 30:57.880 --> 30:59.880 The first and foremost, right. 30:59.880 --> 31:02.880 Policies can add overhead to queries. 31:02.880 --> 31:04.880 So everything becomes slower. 31:04.880 --> 31:06.880 Why? 31:06.880 --> 31:17.880 You may have complex conditions like using a Boolean that gets calculated from a join of three or four other tables. 31:17.880 --> 31:24.880 Right. That would slow down access because for every single row that you would bring back from that table, 31:24.880 --> 31:35.880 it would have to perform that select of three tables and join to see if it satisfies the condition. 31:35.880 --> 31:38.880 So complex conditions can be a problem. 31:38.880 --> 31:45.880 So try to keep your policies simple and explicit when using our less. 31:45.880 --> 31:52.880 Another thing to remember is that the super user bypasses all RLS checks. 31:52.880 --> 31:54.880 It doesn't apply to them. 31:54.880 --> 32:04.880 So the Postgres user or whatever super user you have can read whatever they want from database without the RLS blank to them. 32:04.880 --> 32:11.880 Table owners also can bypass row level security because it's their table after all. 32:11.880 --> 32:15.880 But not if you force row level security. 32:15.880 --> 32:23.880 So if you alter table force row level security, then you make the policy apply to the table owner as well. 32:23.880 --> 32:29.880 That's what force means. 32:29.880 --> 32:40.880 You should also try to set a restrictive delete policy because it's easy to assume that, 32:40.880 --> 32:45.880 you know, deleting is writing to the table. It's the same as updating, right? 32:45.880 --> 32:56.880 No, it's not. So if you have an update policy and you assume that people are not able to delete rows because they can't modify rows in the table, then you are wrong. 32:56.880 --> 33:02.880 If they can read the rows and there's no delete policy, they can also delete them. 33:02.880 --> 33:09.880 So make sure that you make that restrictive so that it is exclusive with other permissions. 33:09.880 --> 33:18.880 And also make sure that you reset your variables so that they don't leak from session to session. 33:18.880 --> 33:22.880 And other people get the privileges that they shouldn't have. 33:22.880 --> 33:36.880 Which is, it's worth mentioning that PG Bouncer's statement mode will not work with set and set local because everything, it's like auto commit, right? 33:36.880 --> 33:47.880 Everything is in, it's own isolated transaction, so you can't really set a statement in the same transaction as the command you're running. 33:47.880 --> 33:51.880 So it won't work. 33:51.880 --> 34:04.880 Finally, if you have views, as we mentioned, you want to make sure that nobody can take advantage of your view and bypass row level security, 34:04.880 --> 34:14.880 by tricking the query optimizer. Let's say by creating a function that they define as cost zero point zero zero zero zero. 34:14.880 --> 34:18.880 So the optimizer will select it always, right? 34:18.880 --> 34:24.880 And such a function can leak data. 34:24.880 --> 34:34.880 Because for example, you can put in it. It may not be able to read the data, but you may cheat and put in a notice. 34:34.880 --> 34:41.880 And it can leak the data through the notice statement, right, which is not a select. 34:41.880 --> 34:53.880 So if you define the view with bracket security barrier, that means that this is not allowed. 34:53.880 --> 34:58.880 So that's about it. 34:58.880 --> 35:07.880 Here's some discount codes for the books that I mentioned, and I will be very happy to take your questions. 35:07.880 --> 35:19.880 You know the front? 35:19.880 --> 35:22.880 You. 35:22.880 --> 35:25.880 I will repeat your question. 35:25.880 --> 35:32.880 I was wondering, is the wait just in your solution to begin the set of variables in this session? 35:32.880 --> 35:38.880 Is there a wait to do that in just one trip to the server together, the select? 35:38.880 --> 35:48.880 Right, so the question is if you can set the variable in one trip to the server without, you mean without issuing two statements. 35:48.880 --> 35:57.880 First the set and then you just wrap everything in a transaction, and it does both things at the same time. 35:57.880 --> 36:01.880 It's better than nothing. 36:01.880 --> 36:04.880 I don't think you can set the connection. 36:04.880 --> 36:13.880 I don't think it's safe or sane to set the variable in your connection string, but you could do that. 36:13.880 --> 36:16.880 You could probably. 36:16.880 --> 36:26.880 But the standard way people do it is usually they open the session as they set the variable and then they use the variable through the policy or whatever. 36:26.880 --> 36:30.880 In the second question, might not have an answer. 36:30.880 --> 36:35.880 Is there a wait to secure a field level rather than low level? 36:35.880 --> 36:40.880 I mean, even finer than just. 36:40.880 --> 36:44.880 There's already a column level security. 36:44.880 --> 36:50.880 You can grant access to specific columns. 36:50.880 --> 36:55.880 Also depending on a variable set in the session? 36:55.880 --> 36:58.880 No, this is role-based, so it would have to do with Postgres roles. 36:58.880 --> 37:08.880 But you can use the same trick to implement a similar system for specific columns if you want to dive to the third level. 37:08.880 --> 37:12.880 It's a lot of work. 37:12.880 --> 37:23.880 Up there. 37:23.880 --> 37:26.880 A simple question. 37:26.880 --> 37:28.880 How does the optimizer cope? 37:28.880 --> 37:35.880 If you've got a table with a million rows, 12 of which the user can actually see, and you join on that table, 37:35.880 --> 37:39.880 does the optimizer know about that or does it pick the? 37:39.880 --> 37:44.880 No, but if there's a policy, you mean for the ACL, right? 37:44.880 --> 37:47.880 Yeah, that's why we created the index. 37:47.880 --> 37:53.880 So the optimizer knows to use the index for the ACL. 37:53.880 --> 37:54.880 Okay. 37:54.880 --> 37:59.880 So if there's only 12 protected rows on the table, and you have an index on the ACL, 37:59.880 --> 38:02.880 then it knows to select from that index. 38:02.880 --> 38:09.880 So does the optimizer kind of know that it's a more complex query involved with an index and some values? 38:09.880 --> 38:15.880 Does it just unroll it and treat it like a query with all that stuff in it? 38:15.880 --> 38:22.880 Sorry, what I'm saying is if you've got an index and certain values in the index, 38:22.880 --> 38:29.880 the optimizer just goes, okay, you've said select from where blah blah blah. 38:29.880 --> 38:34.880 And it sort of internally adds and index is blah blah blah. 38:34.880 --> 38:37.880 So it just, well, thank you. 38:37.880 --> 38:52.880 I'm glad you were able to get your question. 38:52.880 --> 38:58.880 But any other question? 38:58.880 --> 39:06.880 Yeah, all these tables that you created for the rows or these ACL columns you created can be indexed. 39:06.880 --> 39:10.880 And the optimizer does use the indexes. 39:10.880 --> 39:19.880 So it basically modifies the query to apply the policies. 39:19.880 --> 39:20.880 Yes. 39:20.880 --> 39:30.880 Can you have a row row level security for tables that are linked to each other through foreign key relationships? 39:30.880 --> 39:39.880 Like you have the UI design run table, you want a little security based on that and then you connect to other tables or you have to copy that. 39:39.880 --> 39:40.880 Basically you need a joiner. 39:40.880 --> 39:42.880 Can you do that? 39:42.880 --> 39:46.880 Yeah, the joiner's work if you have access to the rows. 39:46.880 --> 39:50.880 If you don't have access to the rows, it's the same thing as the rows not existing for you. 39:50.880 --> 39:52.880 So the joiner won't work, right? 39:52.880 --> 39:53.880 But join's work. 39:53.880 --> 39:54.880 Yeah. 39:54.880 --> 40:02.880 So if you have access to transactions and you want to get, let's say, the payment details for the transaction. 40:02.880 --> 40:07.880 As long as you can see the transaction ID you're fine. 40:07.880 --> 40:13.880 And if you wanted to protect the payment details as well, you would set an ACL on them as well. 40:13.880 --> 40:14.880 On the other table. 40:14.880 --> 40:15.880 Separate ACL. 40:15.880 --> 40:16.880 Yeah. 40:16.880 --> 40:20.880 But the joiner already protects you because you can't see those rows that you can't see. 40:20.880 --> 40:23.880 So you can't join them with anything. 40:23.880 --> 40:24.880 Yeah. 40:24.880 --> 40:29.880 But the application could select anything it likes from the secondary. 40:29.880 --> 40:30.880 Yes. 40:30.880 --> 40:39.880 So the basic use case here was avoid inadvertent leakage of data through application bugs. 40:39.880 --> 40:40.880 Right? 40:40.880 --> 40:46.880 So it's not intentionally crippling the application so that it's not able to select its own data. 40:46.880 --> 41:04.880 We're just making sure that the developers that wrote it that we don't trust haven't exposed any bugs that let another customer tweak their URL or something to see data that they're not supposed to. 41:04.880 --> 41:08.880 And the other question. 41:08.880 --> 41:09.880 Okay. 41:09.880 --> 41:10.880 Thank you very much. 41:10.880 --> 41:11.880 Thank you. 41:11.880 --> 41:16.880 Thank you.