1 00:00:00,060 --> 00:00:03,680 Nikolay: Hello, hello, this is PostgresFM, podcast about PostgreSQL. 2 00:00:04,920 --> 00:00:06,540 Do I pronounce it right, Michael? 3 00:00:06,660 --> 00:00:07,580 Michael: I think so. 4 00:00:07,840 --> 00:00:08,140 Nikolay: Yeah. 5 00:00:08,140 --> 00:00:09,100 And hi, Michael. 6 00:00:09,340 --> 00:00:10,420 Michael from pgMustard. 7 00:00:11,600 --> 00:00:12,640 Michael: Hello, Nikolay. 8 00:00:12,640 --> 00:00:15,340 Nikolay: Yeah, and I'm Nikolay, Postgres.AI. 9 00:00:15,340 --> 00:00:16,520 And let's... 10 00:00:16,860 --> 00:00:18,920 We don't have guests today, unfortunately. 11 00:00:19,540 --> 00:00:22,720 We have good guests coming soon, but not this time. 12 00:00:24,240 --> 00:00:28,440 My idea was, you know how much I don't like to deal with security. 13 00:00:28,660 --> 00:00:30,560 I just need to do it. 14 00:00:31,260 --> 00:00:34,500 Let's talk about policies, create policy. 15 00:00:35,500 --> 00:00:37,520 Why is it create policy, by the way? 16 00:00:37,540 --> 00:00:41,140 It's not create RLS policy, it's just create policy. 17 00:00:42,260 --> 00:00:45,280 Policy is a very general word, right? 18 00:00:45,280 --> 00:00:49,700 It can be not only row level security, maybe some other policies. 19 00:00:50,660 --> 00:00:53,540 Do you think the decision was made to... 20 00:00:54,140 --> 00:00:55,540 I think this is not standard. 21 00:00:55,840 --> 00:00:59,920 I'm not sure because I just checked with some AI and it says 22 00:01:00,060 --> 00:01:02,820 SQL standard doesn't have RLS. 23 00:01:03,620 --> 00:01:06,160 Michael: I looked up where did the name come from because it 24 00:01:06,160 --> 00:01:09,060 wasn't the title of the page in the Postgres documentation. 25 00:01:10,440 --> 00:01:13,100 And the time it's used is in the alter table statement. 26 00:01:13,100 --> 00:01:16,320 So this is a table level setting that you first enable. 27 00:01:16,560 --> 00:01:20,100 And only once you've enabled it on the table, then you set policies. 28 00:01:20,660 --> 00:01:25,520 So it's alter table, and then enable row level security, and 29 00:01:25,520 --> 00:01:29,700 then once that's enabled, you need to create at least 1 policy 30 00:01:29,700 --> 00:01:32,860 so that people, or non-superusers, can read anything. 31 00:01:33,260 --> 00:01:37,120 Nikolay: Right, but still, it confused me from the very beginning. 32 00:01:37,120 --> 00:01:41,180 I think it was implemented originally very long ago, right, before 33 00:01:41,320 --> 00:01:44,020 Postgres 10 or after, I don't remember. 34 00:01:44,020 --> 00:01:45,560 Michael: I didn't actually check this time. 35 00:01:45,600 --> 00:01:48,540 Nikolay: Yeah, but I think it's quite old feature relatively. 36 00:01:50,140 --> 00:01:55,760 It definitely wasn't in Postgres 9.0, but possibly it's already 37 00:01:55,760 --> 00:01:56,620 present in... 38 00:01:57,040 --> 00:01:58,280 Michael: I found it in 9.5. 39 00:01:58,780 --> 00:02:00,040 Nikolay: 9.5, yeah. 40 00:02:00,240 --> 00:02:02,960 It means 2015 or something, right? 41 00:02:03,900 --> 00:02:09,060 So let's talk about the problem it's solving or tries to solve. 42 00:02:09,400 --> 00:02:15,400 Problem is in the era of SaaS, software as a service, We have 43 00:02:15,400 --> 00:02:21,340 a lot of cases when 1 system running in cloud operates with many, 44 00:02:21,340 --> 00:02:30,860 many different users which ideally should not put at risk each 45 00:02:30,860 --> 00:02:31,860 other or something. 46 00:02:32,120 --> 00:02:33,300 There should be some segregation. 47 00:02:33,540 --> 00:02:37,600 For example, if we have, say, like Twitter style, right? 48 00:02:37,600 --> 00:02:39,520 We have users, we have... 49 00:02:39,900 --> 00:02:41,200 No, Twitter is not good. 50 00:02:41,200 --> 00:02:44,020 Let's think about something more business-oriented. 51 00:02:44,460 --> 00:02:45,300 It will be more... 52 00:02:45,300 --> 00:02:48,840 Michael: Or e-commerce, being able to see somebody else's orders 53 00:02:48,840 --> 00:02:51,700 or being able to see their address or something like that. 54 00:02:51,880 --> 00:02:55,960 Nikolay: Let's find some B2B example because this is where it 55 00:02:55,960 --> 00:02:59,960 feels much more sound, so to speak. 56 00:02:59,960 --> 00:03:03,980 So, for example, let's take Slack and we try to implement Slack, 57 00:03:04,000 --> 00:03:04,500 right? 58 00:03:05,280 --> 00:03:10,380 So we have workspaces, many of them, and channels inside and 59 00:03:10,380 --> 00:03:14,060 people communicate, but each workspace belongs to some customer, 60 00:03:14,060 --> 00:03:16,340 some organization which uses our service. 61 00:03:16,480 --> 00:03:17,700 And it's SaaS, right? 62 00:03:17,800 --> 00:03:18,660 It's in cloud. 63 00:03:19,180 --> 00:03:23,480 And is it a good idea to keep all messages, for example, in a 64 00:03:23,480 --> 00:03:27,240 single, maybe partition, but we forget about performance for 65 00:03:27,240 --> 00:03:32,840 a while, in a single table called like messages for all customers? 66 00:03:33,680 --> 00:03:35,900 Maybe it's a good idea, maybe not, right? 67 00:03:35,900 --> 00:03:41,620 But from security point of view, it's terrible idea because if 68 00:03:41,880 --> 00:03:48,120 selecting our messages, we have some bug or some kind of hacker 69 00:03:48,120 --> 00:03:52,860 attack, we can select other organization messages, right? 70 00:03:53,560 --> 00:03:54,520 And that's bad. 71 00:03:55,080 --> 00:04:00,660 So, or maybe just developer forgot to put some condition, again, 72 00:04:00,660 --> 00:04:03,140 a bug or something like unintentional. 73 00:04:04,280 --> 00:04:09,100 And suddenly 1 organization deals with messages from other organizations. 74 00:04:09,720 --> 00:04:11,880 It sounds super scary, right? 75 00:04:13,660 --> 00:04:17,140 For platform development, I mean for the SaaS service developers, 76 00:04:17,360 --> 00:04:18,220 it's bad. 77 00:04:18,960 --> 00:04:23,940 So my point is, some SaaS systems still have this situation. 78 00:04:24,620 --> 00:04:27,320 Old ones definitely have this situation unresolved. 79 00:04:28,680 --> 00:04:28,780 And 80 00:04:28,780 --> 00:04:29,280 Michael: most. 81 00:04:30,040 --> 00:04:30,360 I think 82 00:04:30,360 --> 00:04:30,600 Nikolay: it 83 00:04:30,600 --> 00:04:32,180 Michael: might be most, yeah. 84 00:04:32,780 --> 00:04:35,900 Nikolay: Yeah, ultimate, so it's called multi-tenant system, 85 00:04:35,900 --> 00:04:36,400 right? 86 00:04:37,280 --> 00:04:42,040 Ultimate way is like you create a database or maybe it's like 87 00:04:42,100 --> 00:04:46,820 whole cluster for each, at least big customer and that's it, 88 00:04:46,820 --> 00:04:47,320 right? 89 00:04:47,400 --> 00:04:51,160 And full segregation, full separation of data, and that's great, 90 00:04:51,160 --> 00:04:52,700 but it's super expensive. 91 00:04:53,300 --> 00:04:58,080 Even if you put it in the same database, in the same schema, 92 00:04:58,080 --> 00:05:01,200 but in different tables, it's also quite expensive. 93 00:05:01,560 --> 00:05:04,900 Although we know Postgres can live with millions of tables. 94 00:05:05,540 --> 00:05:10,160 I can send you a new Presentation we just recently discussed 95 00:05:10,160 --> 00:05:12,720 it wasn't probably Presented in Brazil. 96 00:05:12,720 --> 00:05:15,280 I don't remember again like hundred million tables. 97 00:05:15,280 --> 00:05:15,940 Is it possible? 98 00:05:15,940 --> 00:05:20,040 Well, it's possible, but it's difficult. 99 00:05:20,660 --> 00:05:24,360 So the idea of RLS comes from this problem. 100 00:05:24,480 --> 00:05:29,360 We want to have guaranteed protection that 1 customer sees only 101 00:05:29,640 --> 00:05:32,680 their data, not other customers' data. 102 00:05:32,680 --> 00:05:37,860 And it can be achieved by defining special rule that additional 103 00:05:39,020 --> 00:05:43,140 condition will be always present in selects right or updates 104 00:05:43,140 --> 00:05:44,560 deletes inserts 105 00:05:44,600 --> 00:05:47,580 Michael: yeah and I think I think would you say it's fair that 106 00:05:47,680 --> 00:05:51,100 most, when I said most don't have this, I mean most don't have 107 00:05:51,100 --> 00:05:52,360 it at the database level. 108 00:05:52,360 --> 00:05:55,580 I think most people put a lot of effort into making sure this 109 00:05:55,580 --> 00:05:59,820 couldn't happen at the application level, but most don't also 110 00:05:59,860 --> 00:06:03,420 add an additional level, like layer of defense at the database 111 00:06:03,420 --> 00:06:03,640 level. 112 00:06:03,640 --> 00:06:06,780 And this is a database level, so any application sitting on top 113 00:06:06,780 --> 00:06:10,140 of it, or any client sitting on top of it, gets that by default. 114 00:06:10,420 --> 00:06:13,220 Nikolay: Yeah, that's why I always say we cannot trust the application 115 00:06:13,480 --> 00:06:17,120 layer, because what if next year your organization decides to 116 00:06:17,120 --> 00:06:20,140 try a new framework or something and you have 2 applications 117 00:06:20,320 --> 00:06:23,040 and they behave differently or you need to deal with implementing 118 00:06:23,040 --> 00:06:25,880 the same rules in 2 systems and basically copy-pasting. 119 00:06:26,880 --> 00:06:30,040 Sometimes in different languages it's super hard to maintain. 120 00:06:30,880 --> 00:06:35,440 What if you have some specific interfaces connected to database 121 00:06:35,440 --> 00:06:39,880 and people just work with this data directly, right? 122 00:06:40,840 --> 00:06:43,460 They bypass your application logic completely. 123 00:06:45,480 --> 00:06:49,240 This is good to have inside the database, closer to data, and 124 00:06:49,340 --> 00:06:51,380 applied in the same manner to all. 125 00:06:51,580 --> 00:06:55,140 I wanted to say this problem can be solved using views. 126 00:06:57,260 --> 00:07:01,720 So you can, for example, have a view which will deal only with 127 00:07:01,720 --> 00:07:07,660 data dynamically for a particular customer only, not seeing anything 128 00:07:07,660 --> 00:07:08,160 else. 129 00:07:08,680 --> 00:07:13,880 You just put something to the rule definition, which will involve 130 00:07:14,140 --> 00:07:16,120 some current setting again. 131 00:07:16,880 --> 00:07:20,000 And in the current setting you have your customer ID, for example, 132 00:07:20,000 --> 00:07:25,700 and you say, okay, this view is selecting rows from messages 133 00:07:25,800 --> 00:07:30,780 table, where, and we have the check that customer, like project 134 00:07:30,780 --> 00:07:34,020 ID, organization ID, doesn't matter, it belongs to this customer 135 00:07:34,020 --> 00:07:34,960 ID, that's it. 136 00:07:34,960 --> 00:07:35,420 Always. 137 00:07:35,420 --> 00:07:36,800 It can be joined maybe. 138 00:07:37,280 --> 00:07:41,780 And in this case, this view can be even writable, so inserts, 139 00:07:41,780 --> 00:07:43,220 deletes, updates could work. 140 00:07:43,900 --> 00:07:46,680 And this check is automatically verified. 141 00:07:47,060 --> 00:07:49,280 Although maybe it's not verified at insert time. 142 00:07:49,280 --> 00:07:50,280 I don't remember exactly. 143 00:07:50,280 --> 00:07:52,100 I think there is some caveat here. 144 00:07:54,520 --> 00:07:58,140 But if we think about only selects at least, it's definitely 145 00:07:58,140 --> 00:07:58,640 working. 146 00:08:00,060 --> 00:08:03,680 If you make your application work only with this view, not with 147 00:08:03,680 --> 00:08:09,060 the original underlying table, in this case you can be 100% sure 148 00:08:09,060 --> 00:08:13,960 that they will see only data they can see, not anything else. 149 00:08:15,740 --> 00:08:19,240 This additional filter will be always—planner will be always 150 00:08:19,240 --> 00:08:20,060 using it. 151 00:08:20,580 --> 00:08:23,800 But this is not RLS, it's an alternative approach. 152 00:08:23,860 --> 00:08:25,380 And I think inserts will be a problem. 153 00:08:25,380 --> 00:08:29,540 I don't remember 100%, but I remember I was, I got bitten by 154 00:08:29,540 --> 00:08:34,200 this problem a few times in my experience, and we needed to implement 155 00:08:34,200 --> 00:08:37,440 triggers to double-check that inserts are protected and the same 156 00:08:37,440 --> 00:08:39,780 rule is applied the third time. 157 00:08:40,020 --> 00:08:42,940 So maybe this is like downside of this approach. 158 00:08:43,940 --> 00:08:51,160 But create policy statement is more like natural way to protect 159 00:08:51,180 --> 00:08:52,740 data for these purposes, right? 160 00:08:52,740 --> 00:08:59,580 So we define a policy and we alter a table saying that row level 161 00:08:59,620 --> 00:09:01,420 security is enabled for this table. 162 00:09:01,420 --> 00:09:02,700 By default, it's not, right? 163 00:09:02,800 --> 00:09:04,580 Michael: Well, yeah, in Postgres, yeah. 164 00:09:04,840 --> 00:09:08,040 Nikolay: Yeah, and then this policy is supposed to protect us 165 00:09:08,080 --> 00:09:12,940 in terms of multi-tenant situations, so customers deal only with 166 00:09:12,940 --> 00:09:15,640 the data they can deal with, they don't see each other. 167 00:09:15,940 --> 00:09:19,060 Do you think it's a good protection for multi-tenant situation 168 00:09:19,940 --> 00:09:21,900 when we keep everything in 1 table? 169 00:09:22,200 --> 00:09:24,400 Michael: I have mixed feelings about role of security. 170 00:09:24,520 --> 00:09:28,340 I think this is a security question in general. 171 00:09:28,820 --> 00:09:31,820 How much are the downsides worth it? 172 00:09:31,820 --> 00:09:34,840 How much is the extra complexity worth it for the additional 173 00:09:34,840 --> 00:09:41,100 security and yeah I think in environments where security is of 174 00:09:41,100 --> 00:09:45,100 paramount importance it makes sense to add security at every 175 00:09:45,100 --> 00:09:47,100 level including this 176 00:09:47,520 --> 00:09:50,200 Nikolay: 1 this 1 I mean you mean database level 177 00:09:50,360 --> 00:09:53,600 Michael: yeah I don't see Postgres very level security in use 178 00:09:53,600 --> 00:09:57,800 that much so most people seem to be making the trade-off of not 179 00:09:57,800 --> 00:10:02,520 using it in favor well not in favor of I suspect most people 180 00:10:02,520 --> 00:10:06,100 using row level Security also have other measures in place. 181 00:10:06,660 --> 00:10:10,020 But I've seen a growing number of performance related questions 182 00:10:10,020 --> 00:10:12,180 around Rail-Level Security in various communities. 183 00:10:12,720 --> 00:10:18,620 And I think it's due to the popularity of PostgREST and the 184 00:10:18,680 --> 00:10:20,840 automatic creating. 185 00:10:20,860 --> 00:10:26,000 Also, my theory is because people are creating these RESTful 186 00:10:26,000 --> 00:10:31,780 APIs directly interfacing with the database, it becomes super 187 00:10:31,780 --> 00:10:34,900 important to have security at the database level because people 188 00:10:34,900 --> 00:10:36,940 can change the parameters of the URL. 189 00:10:37,200 --> 00:10:41,080 And otherwise, they could just view other people's data immediately. 190 00:10:41,580 --> 00:10:44,620 So I think it makes sense that it's growing in popularity. 191 00:10:44,620 --> 00:10:47,320 But I don't think it's been growing in popularity as an additional 192 00:10:47,360 --> 00:10:47,640 layer. 193 00:10:47,640 --> 00:10:50,640 I think it's been growing in popularity as the only layer. 194 00:10:50,660 --> 00:10:51,660 But I could be wrong. 195 00:10:51,660 --> 00:10:54,900 Nikolay: Yeah, that's interesting, because exactly this alternative 196 00:10:54,920 --> 00:10:58,220 approach, which I explained using views, it's coming. 197 00:10:59,060 --> 00:11:04,080 Actually, my bachelor thesis was about updatable views, and master 198 00:11:04,080 --> 00:11:06,740 thesis was about updatable XML views. 199 00:11:06,900 --> 00:11:09,220 So it was like 20 years plus ago. 200 00:11:09,520 --> 00:11:15,220 But Postgres particularly provokes you to use views over tables 201 00:11:15,220 --> 00:11:15,880 and views. 202 00:11:15,880 --> 00:11:21,200 You just create a view in, say, by default, schema v1, and this 203 00:11:21,200 --> 00:11:24,440 is your v1 version 1 API. 204 00:11:25,160 --> 00:11:31,520 By creating views and controlling who can see what, you can already 205 00:11:31,520 --> 00:11:33,300 limit access. 206 00:11:34,460 --> 00:11:38,000 You can hide some columns, just not taking them to views, and 207 00:11:38,000 --> 00:11:38,700 so on. 208 00:11:39,340 --> 00:11:43,380 And that's great, but maybe you are right, because I did see 209 00:11:43,380 --> 00:11:47,940 some popularity of low-level security growing from that side. 210 00:11:48,160 --> 00:11:52,260 Because people need more and more and more logic on database 211 00:11:52,260 --> 00:11:56,180 side, which is related to data, because there is no middleware. 212 00:11:56,460 --> 00:12:00,180 It's only this high-skill application called Postgres. 213 00:12:00,940 --> 00:12:04,400 And all the data-related logic is supposed to be in database, 214 00:12:04,600 --> 00:12:06,660 including everything related to permissions. 215 00:12:07,720 --> 00:12:11,880 And all UI logic is on FAT client, right? 216 00:12:11,880 --> 00:12:13,500 It's like React or something. 217 00:12:14,240 --> 00:12:16,820 And, Yeah, I see your point. 218 00:12:16,820 --> 00:12:17,320 Exactly. 219 00:12:17,320 --> 00:12:23,160 If you have middleware, Ruby, Python, Java, anything, then people 220 00:12:23,160 --> 00:12:26,120 tend to implement all the security checks right there, right? 221 00:12:26,120 --> 00:12:29,160 And maintain them in a unified way and so on. 222 00:12:29,160 --> 00:12:29,940 Yeah, I agree. 223 00:12:30,100 --> 00:12:32,980 There's no application, middleware application here. 224 00:12:32,980 --> 00:12:35,860 So it goes to database or where else, right? 225 00:12:35,860 --> 00:12:37,480 Because it cannot go to UI. 226 00:12:37,540 --> 00:12:40,320 But interesting, again, views are there as well, but it's not 227 00:12:40,320 --> 00:12:40,660 enough. 228 00:12:40,660 --> 00:12:42,100 It doesn't feel enough. 229 00:12:42,440 --> 00:12:46,420 And sometimes we need to deal with data from multiple clients. 230 00:12:46,840 --> 00:12:48,080 Sometimes, it depends, right? 231 00:12:48,080 --> 00:12:50,820 Because, I mean, admin mode or something, right? 232 00:12:51,340 --> 00:12:56,000 Yeah, in this case, ROLL security provides this bypass RLS flag 233 00:12:56,000 --> 00:12:59,540 for when you create a role or user, you can specify this flag, 234 00:12:59,540 --> 00:13:03,420 it's kind of super user, not super user, but it bypasses these 235 00:13:03,420 --> 00:13:04,780 security checks, right? 236 00:13:05,140 --> 00:13:05,980 Yeah, I agree. 237 00:13:05,980 --> 00:13:10,460 And then, but my point stands as well, like old SaaS systems, 238 00:13:10,520 --> 00:13:16,400 they tend to not using RLS, but there are many other fresh, like 239 00:13:16,860 --> 00:13:22,200 last few years, a lot of SaaS systems are created, have been 240 00:13:22,200 --> 00:13:27,800 created, and still created, and also with AI, it's also very 241 00:13:27,800 --> 00:13:31,800 much standard situation sometimes think about charge BT as well 242 00:13:31,960 --> 00:13:35,100 It's also like kind of many messages from these chats. 243 00:13:35,280 --> 00:13:38,320 It can be also single table and it requires some security as 244 00:13:38,320 --> 00:13:38,980 well, right? 245 00:13:39,240 --> 00:13:43,260 And my point is that new SaaS systems, they tend to have it even 246 00:13:43,260 --> 00:13:48,160 without Postgres or Hasura or something like that. 247 00:13:48,900 --> 00:13:55,340 Even if they use regular Django or Java or anything, they sometimes, 248 00:13:55,380 --> 00:13:59,300 I just observed it, they sometimes implement RLS because they 249 00:13:59,300 --> 00:14:03,620 think, okay, we need better protection for our SaaS system. 250 00:14:05,080 --> 00:14:05,740 Multi-tenant system. 251 00:14:05,740 --> 00:14:07,480 Michael: Cool, I didn't realize that, that's great. 252 00:14:07,480 --> 00:14:10,240 Nikolay: Yeah, I just see it, and then they grow and start hitting 253 00:14:10,240 --> 00:14:11,260 performance issues. 254 00:14:13,320 --> 00:14:17,080 And this is natural, And it looks like the first, let's talk 255 00:14:17,080 --> 00:14:18,740 about performance, if you don't mind. 256 00:14:19,120 --> 00:14:21,680 Michael: No, I think the most interesting part of this 257 00:14:21,680 --> 00:14:22,360 Nikolay: is performance. 258 00:14:23,260 --> 00:14:28,180 And the first thing they bump into sometimes, not everyone, is 259 00:14:28,180 --> 00:14:33,580 that having some condition present in the RLS definition, this 260 00:14:33,800 --> 00:14:37,220 filtering, doesn't mean that the planner will see it. 261 00:14:37,360 --> 00:14:39,980 You can have an index on a column. 262 00:14:40,200 --> 00:14:41,760 For example, it's a simple condition. 263 00:14:42,040 --> 00:14:43,520 Some column equals some constant. 264 00:14:43,520 --> 00:14:47,200 Forget about current setting or some stable functions for a while. 265 00:14:47,420 --> 00:14:48,180 Some constant. 266 00:14:48,700 --> 00:14:52,920 And we have an index on this column, but we don't have this condition 267 00:14:53,700 --> 00:14:55,420 used in the where clause. 268 00:14:56,520 --> 00:14:59,600 This will lead to sequential scan, right? 269 00:14:59,600 --> 00:15:03,460 Because planner doesn't see what row level security is going 270 00:15:03,460 --> 00:15:06,400 to, which filter is going to apply, right? 271 00:15:07,100 --> 00:15:08,060 And this is surprise. 272 00:15:09,060 --> 00:15:12,260 Michael: Yeah, sequential scan, but like with a filter so each 273 00:15:12,260 --> 00:15:14,440 1 will be checked against the policy 274 00:15:16,260 --> 00:15:17,340 Nikolay: Just use it 275 00:15:18,380 --> 00:15:20,820 Michael: You might even yes, even if you have an index on that. 276 00:15:20,820 --> 00:15:24,100 So yeah, that's super unintuitive, I think, for people. 277 00:15:24,520 --> 00:15:27,300 And it tripped me up when I was reading about this. 278 00:15:27,620 --> 00:15:33,900 I thought, I wondered if it could use an index on a simple policy, 279 00:15:34,040 --> 00:15:37,620 on a column, like a user ID or something like that. 280 00:15:37,900 --> 00:15:41,680 And I even saw guides, in fact, there's a really good guide on 281 00:15:41,680 --> 00:15:45,900 row level security by the team at Supabase who heavily encourage 282 00:15:46,360 --> 00:15:51,340 the use of row level security as part of their offering because 283 00:15:51,340 --> 00:15:52,320 they're using Postgres. 284 00:15:53,080 --> 00:15:56,760 But yeah, they say you can think of policies as adding a where 285 00:15:56,760 --> 00:15:58,020 clause to every query. 286 00:15:58,520 --> 00:16:01,500 But that tripped me up When it came to performance because it's 287 00:16:01,500 --> 00:16:03,900 not like a where clause from a performance perspective. 288 00:16:03,900 --> 00:16:07,460 It's like a where clause from like a user experience perspective 289 00:16:07,540 --> 00:16:11,680 like from a from what you see at the end of the Data coming back 290 00:16:11,680 --> 00:16:14,820 might might look the same as if you'd feel today using a where 291 00:16:14,820 --> 00:16:18,040 clause But performance like the planner doesn't have access to 292 00:16:18,040 --> 00:16:21,660 that to choose to do a different type of scan at the beginning. 293 00:16:21,820 --> 00:16:26,400 So yeah, super important distinction and 1 of their tips on the 294 00:16:26,400 --> 00:16:29,600 performance side is therefore to always include all the filters 295 00:16:29,600 --> 00:16:31,900 you want anyway in the queries. 296 00:16:32,320 --> 00:16:34,800 Nikolay: Yeah Yeah, that's interesting. 297 00:16:35,240 --> 00:16:38,360 But it's easy to solve, I think. 298 00:16:38,420 --> 00:16:41,680 Just add it there, into the where class, and that's it. 299 00:16:42,340 --> 00:16:46,500 Let's talk about, step back maybe a little bit about not performance, 300 00:16:46,840 --> 00:16:50,240 because I will forget about this, but I wanted to share some 301 00:16:50,500 --> 00:16:51,820 feature stuff. 302 00:16:51,820 --> 00:16:56,840 First of all, I saw the case that if we have foreign keys and 303 00:16:56,840 --> 00:17:01,160 so on, referential integrity checks are not covered, right? 304 00:17:01,560 --> 00:17:06,220 This is like, like, the integrity is more important than role 305 00:17:06,220 --> 00:17:07,540 level security, right? 306 00:17:07,740 --> 00:17:10,640 Did you see any interesting cases related to this? 307 00:17:11,320 --> 00:17:14,660 Michael: Well, I only read about, I thought it was a really fascinating 308 00:17:15,560 --> 00:17:20,140 concept that by enforcing referential integrity you could be 309 00:17:20,140 --> 00:17:22,460 leaking security information like if you... 310 00:17:22,460 --> 00:17:24,400 Nikolay: Or inserting something which is not... 311 00:17:24,400 --> 00:17:27,100 Michael: Yeah exactly if you're 1 user trying to insert something 312 00:17:27,380 --> 00:17:33,120 and you find out that it that it fails to be inserted then you 313 00:17:33,120 --> 00:17:35,760 know that thing exists but from some other user. 314 00:17:35,760 --> 00:17:38,760 Nikolay: Insert is a bad example, because foreign key doesn't 315 00:17:38,760 --> 00:17:39,340 help with insert. 316 00:17:39,340 --> 00:17:41,680 It helps with, for example, deletes, right? 317 00:17:41,680 --> 00:17:42,840 Like, SK deletes. 318 00:17:43,480 --> 00:17:47,140 So you, for example, can delete what you cannot see or something 319 00:17:47,140 --> 00:17:47,680 like that. 320 00:17:47,680 --> 00:17:48,900 Michael: Yeah, Yeah, sorry, good point. 321 00:17:48,900 --> 00:17:51,800 Maybe it's not relevant then, but I did read in the documentation 322 00:17:52,660 --> 00:17:58,780 that you need to be careful with leaking information by checking... 323 00:18:00,060 --> 00:18:01,320 Nikolay: Ah, I see how. 324 00:18:01,320 --> 00:18:05,140 For example, if you try to delete something without cascade and 325 00:18:05,140 --> 00:18:09,380 it says it cannot be deleted because there is a row in different 326 00:18:09,380 --> 00:18:14,240 table which we reference to, it reveals the presence of this 327 00:18:14,240 --> 00:18:17,060 row, but maybe you are not supposed to see it. 328 00:18:18,760 --> 00:18:20,780 Yeah, you like, it's not your role. 329 00:18:21,100 --> 00:18:23,180 It's somebody else's role. 330 00:18:23,680 --> 00:18:24,920 Something like this, interesting. 331 00:18:24,920 --> 00:18:27,980 Yeah, and then like from error, you can conclude that there is 332 00:18:27,980 --> 00:18:30,040 some data there, right? 333 00:18:30,100 --> 00:18:32,620 Maybe you can understand the values. 334 00:18:32,980 --> 00:18:33,980 Yeah, it's interesting. 335 00:18:34,120 --> 00:18:36,880 But I never saw such situations in production. 336 00:18:37,540 --> 00:18:41,860 Another thing I had experience with is that pg_dump. 337 00:18:42,340 --> 00:18:46,460 Yeah, you mentioned also before we started recording that it's 338 00:18:46,460 --> 00:18:47,380 bad for backups. 339 00:18:48,580 --> 00:18:52,620 Michael: No, it's not bad, but you just have to test your backups. 340 00:18:53,200 --> 00:18:55,580 Nikolay: But as I said, the dumps are not backups. 341 00:18:56,280 --> 00:19:00,600 I'm still on this idea that by default backup is physical backup. 342 00:19:00,600 --> 00:19:02,580 If we say logical backups, okay. 343 00:19:03,200 --> 00:19:06,560 It's good, it's bad for, I mean not bad, exactly. 344 00:19:06,620 --> 00:19:11,960 It can be tricky and surprising for logical backups. 345 00:19:11,960 --> 00:19:16,480 For example, you dump your table but you see only rows you can 346 00:19:16,480 --> 00:19:16,980 see. 347 00:19:17,520 --> 00:19:18,020 Surprise. 348 00:19:18,240 --> 00:19:19,860 Michael: Yeah, well, exactly. 349 00:19:19,860 --> 00:19:23,320 I was just imagining using a service like Supabase and wanting 350 00:19:23,320 --> 00:19:29,340 to keep a semi-regular dump of the data somewhere else just for 351 00:19:29,340 --> 00:19:30,920 my own peace of mind. 352 00:19:31,320 --> 00:19:34,900 Nikolay: No errors, just lack of a lot of data. 353 00:19:38,100 --> 00:19:41,680 Michael: It was more that if you're not super careful about which 354 00:19:41,680 --> 00:19:44,800 user you're using, like making sure that's from a super user 355 00:19:44,800 --> 00:19:49,540 or some role that can see all the data, it'd be very easy to 356 00:19:49,540 --> 00:19:53,940 think I've got like I've backed this up and then if you actually 357 00:19:53,940 --> 00:19:57,020 ever need it you're in real trouble you've lost a lot of data. 358 00:19:57,180 --> 00:19:59,560 Nikolay: Yeah you can have it unnoticed. 359 00:20:00,540 --> 00:20:04,120 Michael: Yeah a credit to the Postgres documentation They called 360 00:20:04,120 --> 00:20:06,100 that out as something to be aware of. 361 00:20:06,500 --> 00:20:11,240 Nikolay: In the same area of dumps, a pg_dump doesn't have skip 362 00:20:11,240 --> 00:20:14,820 policies, or pg_restore doesn't have skip policies flag, as I 363 00:20:14,820 --> 00:20:15,320 remember. 364 00:20:16,060 --> 00:20:20,360 And this was a problem because some users of our Database Lab 365 00:20:20,360 --> 00:20:23,100 Engine, DBLab Engine, they... 366 00:20:23,640 --> 00:20:26,920 This is how actually I noticed that more and more people start 367 00:20:26,920 --> 00:20:28,940 using row level security. 368 00:20:29,480 --> 00:20:32,900 I just like They start asking questions, okay, we have some low-level 369 00:20:32,900 --> 00:20:36,860 security here, but we restore it under, like in this non-production 370 00:20:37,080 --> 00:20:39,740 environment for dev test activities. 371 00:20:40,440 --> 00:20:43,940 And we don't need row level security at all here, right? 372 00:20:44,160 --> 00:20:45,040 How to skip it? 373 00:20:45,040 --> 00:20:48,300 We just want to check various stuff. 374 00:20:49,940 --> 00:20:53,940 And pg_restore has a lot of flags, to skip this, to skip that, 375 00:20:53,940 --> 00:20:54,740 many things. 376 00:20:55,400 --> 00:20:59,420 But as for policies, skip policies, it's lacking. 377 00:20:59,700 --> 00:21:05,440 And We needed to deal with feature supports, hyphen l, small 378 00:21:05,440 --> 00:21:10,440 l, lowercase l and uppercase L, to have a list of objects present 379 00:21:10,440 --> 00:21:15,860 in the dump, and then just have filter added, like it's a dictionary 380 00:21:15,920 --> 00:21:19,780 of everything that was dumped, and we just removed policies and 381 00:21:19,780 --> 00:21:21,220 then restored without them. 382 00:21:21,220 --> 00:21:23,800 And we automated this in the Database Lab Engine. 383 00:21:23,860 --> 00:21:27,520 So basically, the idea is, if I had a little bit more time, I 384 00:21:27,520 --> 00:21:30,060 would probably code it and pg_restore could have it. 385 00:21:30,060 --> 00:21:33,360 It feels like some good addition for future Postgres versions. 386 00:21:34,060 --> 00:21:38,080 Michael: I understand the attraction, but I'm also kind of against 387 00:21:38,140 --> 00:21:38,480 it. 388 00:21:38,480 --> 00:21:41,480 Like, partly because we're about to discuss performance issues, 389 00:21:41,480 --> 00:21:41,980 right? 390 00:21:42,260 --> 00:21:44,020 I'd want to catch those earlier. 391 00:21:44,040 --> 00:21:45,260 I'd want to know. 392 00:21:45,360 --> 00:21:49,600 I'd probably want to still have row level security on in my dev 393 00:21:49,600 --> 00:21:50,100 environment. 394 00:21:52,040 --> 00:21:53,040 Nikolay: Good point, actually. 395 00:21:53,220 --> 00:21:53,720 Yeah. 396 00:21:54,000 --> 00:21:57,360 Removing policies, this is what we didn't think about. 397 00:21:57,360 --> 00:22:01,740 We just received idea, let's remove it, and we implemented it. 398 00:22:01,740 --> 00:22:03,900 Now I think, oh, indeed, you're right. 399 00:22:04,360 --> 00:22:04,980 You can... 400 00:22:05,900 --> 00:22:07,120 You don't see the whole... 401 00:22:07,200 --> 00:22:11,380 Not plan, but all the problems that policies can bring and we 402 00:22:11,380 --> 00:22:16,740 will discuss in a minute Maybe 1 of the biggest right about stable 403 00:22:16,740 --> 00:22:21,260 function and so on Yeah, so it's a good point But it means also 404 00:22:21,260 --> 00:22:25,760 that if you want to have policies, you want to reconstruct your 405 00:22:25,760 --> 00:22:26,260 users. 406 00:22:26,480 --> 00:22:28,860 Well, this is a good idea as well. 407 00:22:28,860 --> 00:22:31,800 So I have 2 do ideas, 2 to do ideas. 408 00:22:32,520 --> 00:22:35,060 Anyway, skip policies is valid flag for pg_dump. 409 00:22:35,280 --> 00:22:37,580 In some cases, it's up to the user, right? 410 00:22:37,580 --> 00:22:39,360 It should be possible to decide. 411 00:22:39,620 --> 00:22:43,600 Because it supports the removal of skipping of many other things. 412 00:22:44,020 --> 00:22:44,520 True. 413 00:22:44,760 --> 00:22:47,040 Like permissions, for example, ACL, right? 414 00:22:47,040 --> 00:22:48,980 It's booming, it's skipped. 415 00:22:49,940 --> 00:22:52,620 But ownership, but RLS now. 416 00:22:52,900 --> 00:22:56,780 But I have to do for Database Lab Engine as well, like we should find 417 00:22:56,780 --> 00:23:02,840 a path to simplify restoration of users and to preserve all policies 418 00:23:02,980 --> 00:23:04,460 so we see performance. 419 00:23:04,620 --> 00:23:08,300 I mean, this is 1 of the purposes of the Database Lab Engine, to experiment 420 00:23:08,420 --> 00:23:09,620 with plans, right? 421 00:23:09,620 --> 00:23:13,280 And see how indexes help or not help, and so on. 422 00:23:13,280 --> 00:23:13,780 Yeah. 423 00:23:13,940 --> 00:23:14,440 Good. 424 00:23:15,040 --> 00:23:16,900 Let's talk about performance, back to performance. 425 00:23:17,320 --> 00:23:23,720 So we discussed that you must put this filter from policies. 426 00:23:23,860 --> 00:23:29,200 You must duplicate filtering in the where clause to achieve good 427 00:23:29,200 --> 00:23:29,700 performance. 428 00:23:30,400 --> 00:23:35,320 Another thing is that it's natural to use function current_setting, 429 00:23:35,320 --> 00:23:36,790 for example, or something like this. 430 00:23:36,790 --> 00:23:42,180 Some function which current_setting is just getting the value 431 00:23:42,180 --> 00:23:44,160 of a variable. 432 00:23:44,240 --> 00:23:50,680 It can be standard GUC, grand unified configuration, or GUC variables, 433 00:23:50,740 --> 00:23:50,940 right? 434 00:23:50,940 --> 00:23:52,060 Let's call them variables. 435 00:23:52,480 --> 00:23:54,440 Or it can be user defined variable. 436 00:23:54,600 --> 00:23:58,940 It has to have namespace or blah, blah, dot blah, blah, right? 437 00:23:59,180 --> 00:24:02,640 And you can use in set or set_config function. 438 00:24:03,040 --> 00:24:04,660 You can set it to something. 439 00:24:05,420 --> 00:24:09,380 And then you can use it, you can access it either using show, 440 00:24:09,520 --> 00:24:13,680 but you cannot use show inside SQL query. 441 00:24:13,940 --> 00:24:18,500 To access it inside SQL query, you can use function current_setting. 442 00:24:19,300 --> 00:24:24,520 And naturally for Postgres, Postgres, and I think for Hasura 443 00:24:24,520 --> 00:24:28,020 as well, many things are coming inside these functions already 444 00:24:28,020 --> 00:24:33,500 preset by this thin layer which provides API. 445 00:24:33,840 --> 00:24:35,780 For example, some headers are there. 446 00:24:36,420 --> 00:24:41,820 And in headers, and also like processing of JWT tokens, basically, 447 00:24:42,620 --> 00:24:46,220 authentication, authorization is done through this mechanism. 448 00:24:46,240 --> 00:24:50,980 And it's natural for you to include it into queries and say, 449 00:24:50,980 --> 00:24:53,900 okay, current_setting and blah, blah, blah, and we see which 450 00:24:53,900 --> 00:24:57,540 user is working right now. 451 00:25:00,020 --> 00:25:03,740 And Again, it's natural to put the current setting into definition 452 00:25:04,700 --> 00:25:06,780 of your level security policies. 453 00:25:07,360 --> 00:25:13,220 And you say, okay, I don't like customer ID equals what you have 454 00:25:13,340 --> 00:25:17,360 in some headers or something coming from JWT token, anywhere. 455 00:25:17,580 --> 00:25:21,340 And you just use current setting to identify your user and Apply 456 00:25:21,340 --> 00:25:24,560 the security rule to filter out on the rows. 457 00:25:24,640 --> 00:25:29,860 This user can see only right So you have current setting used 458 00:25:29,860 --> 00:25:32,860 in row level security And here we come to 2 problems. 459 00:25:32,960 --> 00:25:36,520 First problem is 2 separate problems, actually, but they are 460 00:25:36,520 --> 00:25:37,020 connected. 461 00:25:38,300 --> 00:25:41,920 First big problem is that if you have select count dealing with 462 00:25:41,920 --> 00:25:46,160 many, many rows, like a million, 10 million, or it may be another 463 00:25:46,160 --> 00:25:46,660 aggregate. 464 00:25:47,020 --> 00:25:49,080 We know Postgres has slow count, right? 465 00:25:49,240 --> 00:25:53,620 But if you have row level security, slow count is order of magnitude 466 00:25:53,620 --> 00:25:59,340 slower, even worse, because row level security check is applied 467 00:25:59,340 --> 00:26:00,220 to each row. 468 00:26:00,940 --> 00:26:01,800 This is terrible. 469 00:26:02,080 --> 00:26:07,760 We already have very slow aggregates, which makes you think even 470 00:26:07,760 --> 00:26:13,200 more about something like materialized views or continuous aggregates 471 00:26:13,520 --> 00:26:18,740 in TimescaleDB or some other stuff, like other kind of pre-calculation, 472 00:26:20,660 --> 00:26:22,260 like denormalization with pre-calculation. 473 00:26:22,440 --> 00:26:24,100 I don't know, like something like this. 474 00:26:24,400 --> 00:26:30,360 So not providing actual values in real time, because It's a huge 475 00:26:30,360 --> 00:26:34,360 penalty, additional check, even if it's just a simple check with 476 00:26:34,360 --> 00:26:35,820 constants and that's it. 477 00:26:36,220 --> 00:26:38,040 This is the number 1 problem. 478 00:26:38,800 --> 00:26:40,900 It feels terrible and I don't have a solution. 479 00:26:40,900 --> 00:26:43,980 We don't have good solutions, actually, honestly, to slow count 480 00:26:43,980 --> 00:26:46,080 in Postgres because it's a row store. 481 00:26:46,520 --> 00:26:51,880 It's really expensive to calculate the sum of 100 million values. 482 00:26:52,860 --> 00:26:56,260 But now we all additionally need to check every row. 483 00:26:57,180 --> 00:26:58,180 Can we deal with it? 484 00:26:58,180 --> 00:26:59,120 Can we deal with it? 485 00:26:59,120 --> 00:27:00,460 Oh, it's not our row. 486 00:27:00,580 --> 00:27:02,060 Wow, it's super slow. 487 00:27:02,440 --> 00:27:07,180 But additionally, another layer of penalty comes if you use a 488 00:27:07,180 --> 00:27:11,660 function which is not immutable, which is stable, for example, 489 00:27:11,660 --> 00:27:13,280 or volatile, which is worse. 490 00:27:13,680 --> 00:27:15,600 But current setting is stable function. 491 00:27:16,780 --> 00:27:21,720 It means that Postgres also, even with its current setting and 492 00:27:21,720 --> 00:27:27,160 just some variable which is just set and that's it, it will call 493 00:27:27,160 --> 00:27:28,440 it for each row. 494 00:27:28,700 --> 00:27:29,880 Boom, boom, boom. 495 00:27:29,900 --> 00:27:30,400 Right? 496 00:27:31,260 --> 00:27:33,160 And this is insanely slow already. 497 00:27:33,340 --> 00:27:34,300 Insanely slow. 498 00:27:36,200 --> 00:27:40,120 So people can think Postgres has slow count. 499 00:27:40,920 --> 00:27:43,940 People with RLS can think, oh, it's very slow count. 500 00:27:43,940 --> 00:27:48,940 People with RLS and current setting inside RLS think, okay, it's 501 00:27:48,940 --> 00:27:49,840 terribly slow. 502 00:27:49,840 --> 00:27:51,420 Absolutely terribly slow, right? 503 00:27:52,120 --> 00:27:56,320 Yeah, but for this last problem with current_setting, there is 504 00:27:56,320 --> 00:28:00,520 a few ways to avoid this behavior. 505 00:28:02,020 --> 00:28:07,540 I like the way just to put it inside parentheses and write SELECT 506 00:28:07,540 --> 00:28:08,040 word. 507 00:28:08,400 --> 00:28:14,340 So Postgres Planner moves current_setting call into, it's called 508 00:28:14,340 --> 00:28:17,420 init plan, right? 509 00:28:18,180 --> 00:28:22,120 Another node in the plan, and it will be called once, and then 510 00:28:22,120 --> 00:28:22,840 just used. 511 00:28:22,840 --> 00:28:23,580 That's great. 512 00:28:23,680 --> 00:28:25,020 So this is easy solution. 513 00:28:25,600 --> 00:28:27,600 Just surround by parentheses and... 514 00:28:28,740 --> 00:28:30,920 Just surrounded by parentheses won't solve it. 515 00:28:30,920 --> 00:28:32,300 You need the word SELECT. 516 00:28:32,360 --> 00:28:35,380 So it becomes sub SELECT that goes to a new plan. 517 00:28:35,380 --> 00:28:36,960 This is also interesting behavior. 518 00:28:37,340 --> 00:28:39,560 Also quite surprising to some folks. 519 00:28:40,080 --> 00:28:42,320 But anyway, there is some solution here. 520 00:28:42,840 --> 00:28:48,900 However, problem like we need to check every row in real time, 521 00:28:49,940 --> 00:28:51,300 it cannot go away. 522 00:28:51,820 --> 00:28:56,180 This is actually the number 1, maybe the only 1 reason I think 523 00:28:56,180 --> 00:28:59,940 RLS, row level security is painful. 524 00:29:00,740 --> 00:29:02,720 Like how to solve it? 525 00:29:03,920 --> 00:29:09,240 Michael: I was reading the, there's a really good gist put together 526 00:29:09,240 --> 00:29:13,660 by I think 1 of the team at Supabase and or at least they reference 527 00:29:13,660 --> 00:29:20,560 it in their docs. And they do mention a kind of a trick where 528 00:29:20,560 --> 00:29:26,840 if as long as the function isn't leaking any information, you 529 00:29:26,840 --> 00:29:28,760 could bypass RLS. 530 00:29:28,980 --> 00:29:32,320 So you can, I think it's called security definer? 531 00:29:32,800 --> 00:29:36,760 You can set it up in a way that the function can then bypass 532 00:29:36,900 --> 00:29:37,940 row level security. 533 00:29:39,720 --> 00:29:40,380 But yeah, 534 00:29:40,380 --> 00:29:43,240 Nikolay: I- But it feels like we're turning off. 535 00:29:43,860 --> 00:29:44,260 Why? 536 00:29:44,260 --> 00:29:49,720 I can say, OK, let's run all massive aggregate calls, like counts, 537 00:29:49,960 --> 00:29:51,240 sums, anything. 538 00:29:51,540 --> 00:29:55,820 Let's just run them using a User which bypasses RLS. 539 00:29:56,920 --> 00:29:57,680 That's it. 540 00:29:58,660 --> 00:30:02,020 If you want to present some big count somewhere, just do this. 541 00:30:02,020 --> 00:30:07,080 But if it's global it makes sense, I would say okay here we need 542 00:30:07,080 --> 00:30:10,680 to count everything bypass RLS, we would do it anyway. 543 00:30:10,680 --> 00:30:16,120 But if it's inside single organization it feels like breaking 544 00:30:16,120 --> 00:30:18,620 a hole in our wall we just built. 545 00:30:19,120 --> 00:30:23,440 Michael: I don't understand it enough to understand why that 546 00:30:23,440 --> 00:30:25,840 might be okay and I think you would have to be careful about 547 00:30:25,840 --> 00:30:29,220 not leaking information but because of that first tip where we're 548 00:30:29,220 --> 00:30:33,040 still providing all the where clauses we need to only be accessing 549 00:30:33,040 --> 00:30:35,640 that organization at the query level. 550 00:30:36,100 --> 00:30:39,220 We've still got 1 level of security there. 551 00:30:39,960 --> 00:30:44,300 So I can see why it's attractive, but that's the only solution 552 00:30:44,300 --> 00:30:49,660 I saw to avoid doing it on every, to avoid that check. 553 00:30:49,660 --> 00:30:51,000 Nikolay: It's logical problem. 554 00:30:51,220 --> 00:30:57,600 If we must definitely check, and we do it after the player already 555 00:30:57,600 --> 00:31:01,380 did everything, and basically after the last stage of execution, 556 00:31:01,400 --> 00:31:01,900 right? 557 00:31:03,480 --> 00:31:08,600 It's going to be super expensive if you have a lot of rows before 558 00:31:08,600 --> 00:31:10,120 producing the final result. 559 00:31:10,920 --> 00:31:16,120 But I feel kind of like Postgres could say, okay, we have it 560 00:31:16,520 --> 00:31:19,540 in the workloads, we already applied this rule, let's skip it. 561 00:31:19,540 --> 00:31:22,500 This could be some optimization, but it needs to be done inside 562 00:31:22,500 --> 00:31:24,300 Postgres somehow. 563 00:31:24,720 --> 00:31:26,520 I'm not sure if it was discussed. 564 00:31:26,520 --> 00:31:30,280 Michael: Well, like all planner things, it would work, even if 565 00:31:30,280 --> 00:31:33,680 it was implemented for simple conditions, it wouldn't work for 566 00:31:33,680 --> 00:31:34,620 some more complex. 567 00:31:35,220 --> 00:31:39,160 There would be a never-ending list of quick, because these policies 568 00:31:39,160 --> 00:31:39,960 are so flexible. 569 00:31:39,960 --> 00:31:41,320 That's 1 of their strengths, right? 570 00:31:41,320 --> 00:31:43,980 You can set pretty much anything as a policy. 571 00:31:43,980 --> 00:31:46,320 You can do it at the operation level. 572 00:31:46,320 --> 00:31:49,900 You can do it at a role level, you do so many different levels, 573 00:31:49,900 --> 00:31:52,940 and then you can pretty much, I think you can use any SQL query 574 00:31:53,320 --> 00:31:55,760 to set up exactly the policy you want. 575 00:31:55,760 --> 00:31:56,260 Nikolay: Yeah. 576 00:31:56,580 --> 00:31:59,380 Michael: Then you've got a whole new planning issue, like the 577 00:31:59,380 --> 00:32:03,020 planners are already pretty complicated, So I get why they haven't. 578 00:32:04,060 --> 00:32:04,720 Nikolay: I agree. 579 00:32:06,040 --> 00:32:09,560 For example, you know, in the check constraints, you cannot refer 580 00:32:09,560 --> 00:32:10,460 to other tables. 581 00:32:14,380 --> 00:32:15,300 They are simple. 582 00:32:16,400 --> 00:32:21,380 But here in these policies, like rules, we can do whatever we 583 00:32:21,380 --> 00:32:21,880 want. 584 00:32:21,980 --> 00:32:25,180 This means it's super, like what I just proposed, probably it's 585 00:32:25,180 --> 00:32:26,820 impossible to implement, right? 586 00:32:27,060 --> 00:32:30,760 If it was just this table, no reference to other table, Maybe 587 00:32:30,760 --> 00:32:34,640 current setting can be used and the seat maybe there would be 588 00:32:34,640 --> 00:32:38,560 a way to Connect to the planner and see okay. 589 00:32:38,560 --> 00:32:42,080 It's already applied this rule that we can skip it Skip it in 590 00:32:42,080 --> 00:32:47,160 a safe manner 100% safe man reliable manner, but if we can refer 591 00:32:47,160 --> 00:32:50,340 to any table write anything there it's impossible 592 00:32:52,200 --> 00:32:53,720 Michael: or much more 593 00:32:53,720 --> 00:32:58,820 Nikolay: so many like predicates like yeah so I agree with you 594 00:32:58,820 --> 00:32:59,320 here 595 00:32:59,760 --> 00:33:02,300 Michael: there's for people that want that I will share I'll 596 00:33:02,300 --> 00:33:05,460 share a link to that gist there's also a really good talk by 597 00:33:05,900 --> 00:33:09,820 Paul Copplestone, CEO of Supabase from the last POSETTE conference 598 00:33:09,820 --> 00:33:13,580 I can share as well does a beginner's guide to RLS but also covers 599 00:33:13,580 --> 00:33:17,100 some quite in-depth manages to in about 20 minutes cover some 600 00:33:17,100 --> 00:33:19,920 quite in-depth performance tips I think most of which we've covered 601 00:33:19,920 --> 00:33:22,800 now, but it's visual and you can see him going through them, 602 00:33:22,800 --> 00:33:23,420 which is nice. 603 00:33:23,420 --> 00:33:23,800 Nikolay: Yeah. 604 00:33:23,800 --> 00:33:30,260 Well, I'm quite sure, like Supabase and other systems which 605 00:33:30,300 --> 00:33:36,700 work with Postgres or Hasura or something, these guys are at 606 00:33:36,700 --> 00:33:40,240 the frontier of RLS usage, right? 607 00:33:40,240 --> 00:33:45,740 And for sure a lot of experience is in the discussions there, 608 00:33:46,980 --> 00:33:47,820 which is great. 609 00:33:48,800 --> 00:33:55,020 But I just see, still I feel like we already had bad Aggregates 610 00:33:55,440 --> 00:33:59,620 you you made them worse Starting starting using RLS. 611 00:33:59,620 --> 00:34:02,260 So yeah So By the 612 00:34:02,260 --> 00:34:03,873 Michael: way, I had a question for you. 613 00:34:03,873 --> 00:34:07,940 Did you know how many times we've had this topic requested for 614 00:34:07,940 --> 00:34:08,540 the podcast? 615 00:34:08,940 --> 00:34:10,020 Nikolay: I have no idea. 616 00:34:11,040 --> 00:34:11,780 Michael: 4 times. 617 00:34:12,180 --> 00:34:14,600 4 different people have requested something. 618 00:34:15,780 --> 00:34:18,740 That's by far the most we've ever had for a single topic. 619 00:34:19,540 --> 00:34:22,900 Nikolay: Okay, in this case we should have a lot of feedback, 620 00:34:23,500 --> 00:34:24,140 I guess. 621 00:34:24,140 --> 00:34:24,640 Well, 622 00:34:25,080 --> 00:34:25,580 Michael: yeah. 623 00:34:25,940 --> 00:34:29,800 There are also a couple of people asking about, like, when asked 624 00:34:29,800 --> 00:34:32,680 for more information, like what exactly they wanted us to talk 625 00:34:32,680 --> 00:34:32,960 about. 626 00:34:32,960 --> 00:34:36,040 They asked about considerations around performance, like how 627 00:34:36,040 --> 00:34:39,280 to then diagnose performance issues, but I don't think there's 628 00:34:39,280 --> 00:34:40,760 anything unique about it, right? 629 00:34:40,760 --> 00:34:42,780 Like mostly it's still... 630 00:34:43,260 --> 00:34:44,520 Whoa, it's tricky, 631 00:34:44,640 --> 00:34:45,920 Nikolay: it's tricky, yeah. 632 00:34:46,020 --> 00:34:51,060 My, like, general rule is just to compare with and without RLS 633 00:34:52,640 --> 00:34:55,940 For example, you can use user spaces. 634 00:34:55,960 --> 00:34:56,460 Yeah. 635 00:34:56,640 --> 00:35:00,560 Yeah, because it can be tricky I of course you like filter and 636 00:35:00,560 --> 00:35:04,600 so on but I feel like in the plans or maybe I'm missing something 637 00:35:04,600 --> 00:35:09,320 but I feel in the plans where like we can start guessing how 638 00:35:09,320 --> 00:35:11,820 much time do we spend on an RLS check. 639 00:35:12,640 --> 00:35:16,320 Michael: I well I think I think personally if you're if you've 640 00:35:16,320 --> 00:35:19,940 already gone with RLS if you've already decided that and you're 641 00:35:19,940 --> 00:35:24,640 well into your project, I think the RLS checks don't matter that 642 00:35:24,640 --> 00:35:24,960 much. 643 00:35:24,960 --> 00:35:26,020 It's more indexing. 644 00:35:26,760 --> 00:35:31,280 I still see more people with indexing issues than RLS performance 645 00:35:31,320 --> 00:35:31,820 issues. 646 00:35:32,540 --> 00:35:36,480 They look like RLS issues because people might think that... 647 00:35:36,600 --> 00:35:37,280 I agree, 648 00:35:37,280 --> 00:35:37,800 Nikolay: I agree. 649 00:35:37,800 --> 00:35:42,760 But I'm talking about this massive count, for example, like, 650 00:35:42,800 --> 00:35:46,720 you have a count on them and how much of that time, like, how 651 00:35:46,720 --> 00:35:49,840 much, how big is the overhead from our list? 652 00:35:49,840 --> 00:35:52,960 It's quite tricky without like comparing 2 cases. 653 00:35:54,020 --> 00:35:58,680 I wish the EXPLAIN ANALYZE show this. 654 00:35:58,680 --> 00:36:05,180 By the way, We just have BUFFERS by default committed, right? 655 00:36:05,540 --> 00:36:07,840 We forgot to celebrate a little bit. 656 00:36:07,840 --> 00:36:09,960 Maybe it should be a separate episode about this. 657 00:36:09,960 --> 00:36:10,240 I don't 658 00:36:10,240 --> 00:36:10,380 Michael: know. 659 00:36:10,380 --> 00:36:14,960 I think I want to wait until Postgres 18 to celebrate that, or 660 00:36:14,960 --> 00:36:17,640 at least far into the future to release candidates. 661 00:36:17,640 --> 00:36:19,040 Nikolay: I cannot believe this. 662 00:36:19,540 --> 00:36:20,720 It's super great, right? 663 00:36:21,400 --> 00:36:23,720 Michael: Yeah, a couple of things people to thank on that front. 664 00:36:23,720 --> 00:36:28,680 I think David Rowley did a really good job and also Guillaume 665 00:36:28,680 --> 00:36:32,060 Lellouche did a lot of the heavy lifting as well. 666 00:36:32,180 --> 00:36:34,800 So those 2 in particular, there was a really good conversation 667 00:36:34,840 --> 00:36:37,380 on the mailing list that I think you kicked off actually. 668 00:36:38,400 --> 00:36:41,240 Nikolay: I didn't expect at this time it will go through. 669 00:36:42,720 --> 00:36:45,580 I was discussing things near this topic, right? 670 00:36:46,060 --> 00:36:50,540 But I'm happy to see it and I guess I will need to find another 671 00:36:50,540 --> 00:36:52,760 topic to be upset with. 672 00:36:53,400 --> 00:36:57,940 Because it was more than 2 years I was talking about how I'm 673 00:36:57,940 --> 00:37:00,040 upset that BUFFERS are not default. 674 00:37:00,720 --> 00:37:02,340 And it's going to be over. 675 00:37:02,980 --> 00:37:08,860 I feel happy and sad at the same time Another thing was UUID 676 00:37:08,860 --> 00:37:15,140 version 7 finally Right, and you know what like my perception 677 00:37:15,360 --> 00:37:20,560 was we were waiting until full finalization of RFC, right? 678 00:37:21,180 --> 00:37:22,480 Michael: Oh, I forgot to say too. 679 00:37:22,660 --> 00:37:23,980 Nikolay: Yeah, because everyone... 680 00:37:24,140 --> 00:37:25,147 Michael: Is it not being finalized? 681 00:37:25,147 --> 00:37:28,640 Nikolay: It was developed before Postgres 17 released and then 682 00:37:29,040 --> 00:37:33,080 the decision was made not to take it into 17 because RFC is not 683 00:37:33,080 --> 00:37:33,980 fully finalized. 684 00:37:34,200 --> 00:37:38,300 At the same time, everyone already started releasing UUID support 685 00:37:38,300 --> 00:37:43,380 like Node.js, Go, like Google decided to release it, everyone 686 00:37:43,380 --> 00:37:45,460 already, but Postgres decided to stay conservative. 687 00:37:45,700 --> 00:37:51,140 The thing is that, unless I'm fully mistaken, I see RFC is not 688 00:37:51,140 --> 00:37:52,720 fully finalized yet. 689 00:37:53,400 --> 00:37:53,560 Michael: Amazing. 690 00:37:53,560 --> 00:37:54,060 Still. 691 00:37:54,840 --> 00:37:57,340 So maybe it will be before 18's released? 692 00:37:57,340 --> 00:38:01,160 Nikolay: Andrey told me not to shout about this. 693 00:38:02,220 --> 00:38:03,000 Michael: Probably sensible. 694 00:38:03,280 --> 00:38:07,940 Andrey and I have similar philosophies then, wait until it's 695 00:38:07,940 --> 00:38:09,580 actually released before celebrating. 696 00:38:09,580 --> 00:38:12,520 Nikolay: Yeah I bet we can talk about these things here because 697 00:38:12,520 --> 00:38:14,820 hackers don't listen to us right so. 698 00:38:14,840 --> 00:38:16,360 Michael: I think a couple days. 699 00:38:16,940 --> 00:38:20,060 Nikolay: They won't go and say let's revert it I hope. 700 00:38:20,240 --> 00:38:25,680 Anyway congrats to Andrey because this is who coded it, This 701 00:38:25,680 --> 00:38:29,600 is him who coded this during our Postgres TV live coding, hacking 702 00:38:29,600 --> 00:38:32,000 sessions with Kirk and Andrey. 703 00:38:32,140 --> 00:38:36,880 So yeah, this week was, to commit, surprised me. 704 00:38:37,360 --> 00:38:41,600 Both things I'm like very passionate about, like buffers by default 705 00:38:41,600 --> 00:38:47,080 in Explain, Analyze, and UID version 7, I think, again, like 706 00:38:47,080 --> 00:38:50,640 people who listen to us, don't wait until the release of those 707 00:38:51,140 --> 00:38:56,100 functions, UUIDv7, start using it right now because it's just, 708 00:38:56,100 --> 00:38:57,680 basically, it's synthetic sugar. 709 00:38:57,800 --> 00:39:02,960 Not fully, but you can start using UUID data type and insert 710 00:39:02,960 --> 00:39:07,660 UUID version 7 generated on application side, or using some SQL 711 00:39:07,660 --> 00:39:09,220 function, PGSQL function. 712 00:39:09,220 --> 00:39:10,780 I have examples in my how-tos. 713 00:39:11,640 --> 00:39:15,940 And when this feature becomes native, switch to native. 714 00:39:16,400 --> 00:39:21,820 But it's hard to overestimate the importance of this support 715 00:39:21,820 --> 00:39:25,440 because you know I can convince a couple of guys to start using 716 00:39:25,440 --> 00:39:26,180 right now. 717 00:39:26,180 --> 00:39:31,440 Some guys can guess but 90% will follow default path right? 718 00:39:31,920 --> 00:39:32,920 Michael: Same with buffers. 719 00:39:34,020 --> 00:39:34,520 Nikolay: Exactly. 720 00:39:35,140 --> 00:39:37,280 Michael: People can start using it now, there's no reason they 721 00:39:37,280 --> 00:39:40,440 can't use it now, it's just more will once it's there. 722 00:39:40,440 --> 00:39:43,780 Nikolay: We will finally analyze plans with buffers always and 723 00:39:43,780 --> 00:39:50,160 let's like to wrap it up, when you see overhead from RLS, it's 724 00:39:50,160 --> 00:39:52,160 not about buffers, usually. 725 00:39:52,540 --> 00:39:55,060 Unless it's a lack of index. 726 00:39:55,640 --> 00:39:59,320 But if this is like current setting, we talked about current 727 00:39:59,320 --> 00:40:01,600 setting on each row, it's called. 728 00:40:02,620 --> 00:40:06,540 Buffers are like tiny, but timing is awful. 729 00:40:07,880 --> 00:40:11,300 And yeah, this is 1 of the rare cases where buffers... 730 00:40:11,760 --> 00:40:13,260 Well, buffers don't lie, right? 731 00:40:13,260 --> 00:40:14,160 They don't lie. 732 00:40:14,220 --> 00:40:15,720 They show there is no I/O 733 00:40:15,720 --> 00:40:16,220 Here. 734 00:40:16,400 --> 00:40:17,980 And it's already useful information. 735 00:40:18,560 --> 00:40:19,060 But... 736 00:40:19,340 --> 00:40:20,420 Michael: But you need both. 737 00:40:21,020 --> 00:40:22,100 Nikolay: You need both, right. 738 00:40:22,100 --> 00:40:26,840 And you know, sometimes I say, most of the time we optimize targeting 739 00:40:26,840 --> 00:40:27,340 buffers. 740 00:40:27,400 --> 00:40:32,140 In this case, we don't because it's just not about I/O 741 00:40:32,140 --> 00:40:32,780 at all. 742 00:40:32,960 --> 00:40:34,340 But seeing that I/O 743 00:40:34,340 --> 00:40:36,720 is low, it already gives us idea. 744 00:40:37,420 --> 00:40:39,040 Maybe it's RLS, right. 745 00:40:40,160 --> 00:40:41,020 Michael: Nice 1, Nikolay. 746 00:40:41,680 --> 00:40:44,320 Well, thanks again and catch you soon. 747 00:40:44,760 --> 00:40:46,640 Nikolay: See you later, bye bye, thank you.