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.
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.
