1 0:0:0,17999999 --> 0:0:2,6599998 Michael: Hello and welcome to PostgresFM, a weekly show about 2 0:0:2,6599998 --> 0:0:3,54 all things PostgreSQL. 3 0:0:3,74 --> 0:0:6,14 I am Michael, founder of pgMustard, and as usual, I'm joined by 4 0:0:6,14 --> 0:0:7,68 Nik, founder of Postgres.AI. 5 0:0:7,68 --> 0:0:8,86 Hey Nik, how's it going? 6 0:0:9,32 --> 0:0:11,599999 Nikolay: Hi Michael, everything is alright. 7 0:0:11,84 --> 0:0:13,299999 My favorite topic today. 8 0:0:14,44 --> 0:0:17,3 Michael: I was actually gonna bring up an old joke and say it's 9 0:0:17,3 --> 0:0:20,14 the most boring one yet but your idea now 10 0:0:20,24 --> 0:0:24,32 Nikolay: yeah it's my idea like it's like cold plunges sometimes 11 0:0:24,32 --> 0:0:28,98 you need to do it to feel then like once I finish this recording 12 0:0:28,98 --> 0:0:30,22 I will feel much better 13 0:0:31,52 --> 0:0:34,58 Michael: yeah well and also you mentioned some things to me, 14 0:0:34,62 --> 0:0:37,18 no spoilers, but I think you've got some ways of making this 15 0:0:37,18 --> 0:0:39,059998 interesting, so I'm looking forward to those. 16 0:0:39,62 --> 0:0:40,64 Nikolay: Who knows, let's see. 17 0:0:40,64 --> 0:0:43,54 I wanted to talk about some kind of simple things. 18 0:0:44,02 --> 0:0:48,12 Of course I'm pretty sure there are many people who know the 19 0:0:48,12 --> 0:0:52,04 topic much better than me, but I just wanted to raise some questions 20 0:0:52,04 --> 0:0:55,02 we currently have in Postgres and so on. 21 0:0:55,84 --> 0:0:56,58 Michael: Yeah, me too. 22 0:0:56,58 --> 0:0:59,84 So, user management, where do you want to start? 23 0:1:0,06 --> 0:1:4,28 I think it might even be interesting to think about the concept 24 0:1:4,28 --> 0:1:5,46 of Roles in Postgres. 25 0:1:6,42 --> 0:1:9,66 I've come from a background of different databases and Postgres 26 0:1:9,66 --> 0:1:11,5 was a little bit different. 27 0:1:11,6 --> 0:1:15,84 When I came across it, I didn't really understand it until reading 28 0:1:15,84 --> 0:1:17,62 the docs exactly how things work. 29 0:1:18,34 --> 0:1:21,3 Nikolay: First thing if you run self-managed Postgres, first 30 0:1:21,3 --> 0:1:25,46 thing you confuse is operational system user and database user, 31 0:1:25,58 --> 0:1:26,08 right? 32 0:1:26,84 --> 0:1:26,92 Yeah. 33 0:1:26,92 --> 0:1:30,14 Second thing you confuse Users versus Roles because in other 34 0:1:30,14 --> 0:1:33,94 database systems they are distinguished in Postgres they are 35 0:1:33,94 --> 0:1:35,28 kind of the same thing. 36 0:1:36,56 --> 0:1:38,7 Michael: Third thing, groups as well. 37 0:1:39,28 --> 0:1:40,34 Nikolay: Oh yes, exactly. 38 0:1:40,92 --> 0:1:41,42 Yeah. 39 0:1:41,68 --> 0:1:44,78 There are no groups basically in Postgres, although they're like 40 0:1:45,16 --> 0:1:48,12 directly, there are no separate concepts. 41 0:1:48,58 --> 0:1:50,42 But let's answer all these. 42 0:1:51,04 --> 0:1:54,34 Like, operational system User is operational system User, and 43 0:1:54,34 --> 0:1:55,9 Postgres User is Postgres User. 44 0:1:57,04 --> 0:2:0,26 Sometimes there is a coincidence and they both are named Postgres, 45 0:2:0,28 --> 0:2:0,71 for example, in Ubuntu. 46 0:2:0,71 --> 0:2:1,46 For example in Ubuntu. 47 0:2:2,9 --> 0:2:7,54 And to make things more complex, if you just, if you locally 48 0:2:7,74 --> 0:2:14,56 connect using psql, and you don't specify uppercase U option, 49 0:2:15,24 --> 0:2:19,44 In this case, your current OS User will be used. 50 0:2:20,9 --> 0:2:22,62 So it's okay. 51 0:2:22,96 --> 0:2:24,06 But we need to distinguish. 52 0:2:25,92 --> 0:2:27,78 These things are very different, right? 53 0:2:29,54 --> 0:2:32,36 Just, yeah, coincidence makes things... 54 0:2:32,64 --> 0:2:37,0 Like I know people who created it, they thought, like, let's 55 0:2:37,0 --> 0:2:38,16 make things simple. 56 0:2:38,86 --> 0:2:39,36 Yeah. 57 0:2:39,78 --> 0:2:45,14 But confusion which comes off it, if I, for example, needed to 58 0:2:45,14 --> 0:2:49,18 decide right now, I would probably not do this, you know? 59 0:2:49,18 --> 0:2:51,86 Like, just to avoid confusion, because the cost of confusion 60 0:2:51,86 --> 0:2:54,84 is much bigger than convenience you have. 61 0:2:55,6 --> 0:2:58,44 Michael: Yeah I think it's 1 of those I think it's 1 of those 62 0:2:58,44 --> 0:3:1,32 things that depends who you're trying to optimize for 63 0:3:1,32 --> 0:3:6,5 Are we trying to optimize for a developer at university learning 64 0:3:6,5 --> 0:3:9,36 computer science for the first time just trying to get set up 65 0:3:9,36 --> 0:3:12,28 on a local machine and get up and running easily? 66 0:3:12,28 --> 0:3:15,52 I think for example in the Stack Overflow survey they do each 67 0:3:15,52 --> 0:3:21,52 year, There are several questions that if you optimised for that 68 0:3:21,56 --> 0:3:25,02 and that alone you'd score highly in certain things. 69 0:3:25,76 --> 0:3:29,78 But sometimes you would make some design decisions if you were 70 0:3:29,78 --> 0:3:34,14 optimising for that user that wouldn't be good for people trying 71 0:3:34,14 --> 0:3:39,4 to set up a company in a more secure manner with lots of different 72 0:3:39,4 --> 0:3:43,6 users that have different needs and maybe behind a firewall like 73 0:3:44,2 --> 0:3:47,8 that in a way that's very very different to that individual user 74 0:3:47,8 --> 0:3:51,18 trying to get set up for a little hobby project or just to learn 75 0:3:51,18 --> 0:3:51,98 about databases. 76 0:3:52,44 --> 0:3:56,24 So I think it's really hard to make a system that's optimal for 77 0:3:56,24 --> 0:4:0,36 both and I'm not sure I think probably we're at the stage where 78 0:4:0,36 --> 0:4:6,38 Postgres should be optimizing more for the secure case, but I 79 0:4:6,38 --> 0:4:10,6 could understand if it chose to go more along the lines of make 80 0:4:10,6 --> 0:4:11,86 it easy to get started. 81 0:4:12,98 --> 0:4:15,04 Nikolay: Yeah, good points. 82 0:4:15,3 --> 0:4:20,44 Anyway, I just taught myself to distinguish. 83 0:4:21,34 --> 0:4:26,56 When in my language, I just try to mention explicitly OS users, 84 0:4:26,58 --> 0:4:30,48 DB users, or people who are listening understand very quickly 85 0:4:30,48 --> 0:4:32,06 what I'm trying to say. 86 0:4:32,56 --> 0:4:36,6 And this I think is a good mitigation of this to avoid confusion. 87 0:4:37,06 --> 0:4:39,12 Just OS user, DB user, that's it. 88 0:4:39,86 --> 0:4:44,82 Although if you say role, people also maybe won't think about 89 0:4:44,82 --> 0:4:48,98 OS user because we don't use role there at all. 90 0:4:50,08 --> 0:4:54,22 So this is another way you can, but still like if you say user, 91 0:4:54,22 --> 0:4:55,36 probably it's the user. 92 0:4:55,84 --> 0:4:58,98 Yeah, and the second confusion and I think third confusion is 93 0:4:58,98 --> 0:4:59,88 similar here. 94 0:4:59,96 --> 0:5:5,04 So users and roles, it's the same in Postgres and groups are 95 0:5:5,38 --> 0:5:6,36 basically the same. 96 0:5:7,12 --> 0:5:9,8 It's just 1 single database object. 97 0:5:12,18 --> 0:5:17,42 You can create a role or user Michael and then grant it to user 98 0:5:17,42 --> 0:5:18,96 role Nik, right? 99 0:5:18,96 --> 0:5:21,38 So I will get all permissions you have. 100 0:5:21,38 --> 0:5:26,82 And in this case, this will make you virtually database group 101 0:5:27,44 --> 0:5:28,54 Michael, right? 102 0:5:28,86 --> 0:5:34,78 So It's very flexible, but also be confusing, especially for 103 0:5:34,78 --> 0:5:38,04 those who came from other database management systems. 104 0:5:38,8 --> 0:5:39,52 Michael: For sure. 105 0:5:39,52 --> 0:5:44,24 I pulled out a good line from the docs which says any role can 106 0:5:44,24 --> 0:5:47,44 act as a user, as a group, or both. 107 0:5:47,72 --> 0:5:51,04 Which I thought was a nice succinct way of saying the groups 108 0:5:51,04 --> 0:5:54,52 can still act as users individually but they can also be groups 109 0:5:54,52 --> 0:5:57,56 of permissions and you yeah... Nik and Michael could both be both 110 0:5:57,56 --> 0:6:2,36 in the DBAs group but also like have other memberships 111 0:6:2,36 --> 0:6:5,76 to other groups and makes it easier to manage permissions that 112 0:6:5,76 --> 0:6:6,52 way, right? 113 0:6:6,74 --> 0:6:7,52 Nikolay: Yeah, good. 114 0:6:8,3 --> 0:6:10,68 So yeah, these are kind of basic things. 115 0:6:10,68 --> 0:6:15,6 And I wanted to discuss like, like, I never heard anyone saying, 116 0:6:16,64 --> 0:6:20,1 Postgres is having so beautiful privileges system. 117 0:6:22,12 --> 0:6:26,04 Somehow it's like, I've heard opposite a lot of times, a lot 118 0:6:26,04 --> 0:6:30,02 of times, especially from guys who manage operational system 119 0:6:30,02 --> 0:6:33,98 and they need to manage Postgres, like sysadmins and then later 120 0:6:34,0 --> 0:6:35,78 SREs, they always complain. 121 0:6:37,66 --> 0:6:40,24 When they start complaining, I don't listen. 122 0:6:40,24 --> 0:6:43,2 I just say, have you learned about default privileges? 123 0:6:44,6 --> 0:6:45,32 Michael: Ah, cool. 124 0:6:45,32 --> 0:6:45,82 Nikolay: Yeah. 125 0:6:46,56 --> 0:6:48,56 This is the first thing you need to remember. 126 0:6:48,56 --> 0:6:53,94 There are default privileges, and you can alter them and say, 127 0:6:54,14 --> 0:6:57,6 these default privileges that should be always assigned. 128 0:6:58,74 --> 0:7:1,16 This makes things easier, Much easier. 129 0:7:1,72 --> 0:7:4,86 Because if you have a lot of database objects, like you think, 130 0:7:4,86 --> 0:7:9,64 oh, how, like I'm going to create new role, user, group, how 131 0:7:9,64 --> 0:7:15,3 am I going to, like, or tomorrow developers will create new table 132 0:7:15,3 --> 0:7:20,64 and who will take care of handling permissions, privileges. 133 0:7:21,28 --> 0:7:25,46 Right, so default privileges help, right? 134 0:7:26,28 --> 0:7:32,52 And still, like still, common mistakes are forgetting some interesting 135 0:7:33,84 --> 0:7:38,6 privileges like the connection to database or usage for schema 136 0:7:38,94 --> 0:7:42,94 and it's impossible to avoid these mistakes. 137 0:7:43,04 --> 0:7:43,54 Impossible. 138 0:7:43,58 --> 0:7:46,76 Like last week I remember we spent 15 minutes trying to understand 139 0:7:46,76 --> 0:7:47,88 why it's not working. 140 0:7:47,88 --> 0:7:49,4 Our monitoring was not working. 141 0:7:49,4 --> 0:7:53,98 And then we realized, okay, it's not monitoring, but something 142 0:7:53,98 --> 0:7:54,52 was wrong. 143 0:7:54,52 --> 0:7:58,02 And we realized that it was not a usual setup. 144 0:7:58,02 --> 0:8:2,86 And we just forgot about usage permission to some schema. 145 0:8:2,86 --> 0:8:3,62 That's it. 146 0:8:5,04 --> 0:8:6,66 It's always going to happen. 147 0:8:8,0 --> 0:8:12,34 So here my advice, don't blame yourself. 148 0:8:14,68 --> 0:8:16,62 It's common, yeah, That's it. 149 0:8:16,86 --> 0:8:18,78 Michael: And read the error messages? 150 0:8:18,78 --> 0:8:20,28 Did you get a good message back? 151 0:8:20,28 --> 0:8:20,78 Nikolay: No. 152 0:8:21,22 --> 0:8:22,56 Michael: Oh, interesting. 153 0:8:22,58 --> 0:8:24,18 Nikolay: Lack of permissions, that's it. 154 0:8:24,24 --> 0:8:25,76 You cannot do something. 155 0:8:25,76 --> 0:8:29,58 You cannot read from some table, but it won't tell you that it's 156 0:8:29,58 --> 0:8:31,42 because of lack of permissions for schema. 157 0:8:32,56 --> 0:8:36,82 So it's tricky sometimes you need to like brainstorm like ideas 158 0:8:36,82 --> 0:8:38,08 and then you find it. 159 0:8:38,4 --> 0:8:40,34 Yeah it was a view I think yeah. 160 0:8:40,58 --> 0:8:45,32 So these are kind of common roadblocks you can experience. 161 0:8:46,94 --> 0:8:51,98 Michael: Yeah so you've kind of stuck you've gone down the what 162 0:8:52,38 --> 0:8:55,76 roles have permissions to read and write to different tables, 163 0:8:55,76 --> 0:8:57,52 for example, of views or schemas. 164 0:8:57,86 --> 0:9:2,64 But we also have the challenge of ownership that's also tied 165 0:9:2,64 --> 0:9:3,78 in with users. 166 0:9:4,4 --> 0:9:7,2 Is that worth touching on a little bit? 167 0:9:7,2 --> 0:9:10,34 Nikolay: Yeah, owner to owner, owned by. 168 0:9:10,38 --> 0:9:13,62 You remember the sequences can be owned by table, but it's a 169 0:9:13,62 --> 0:9:15,16 very completely different thing. 170 0:9:15,74 --> 0:9:20,2 And But every object can have owner in terms of role, right? 171 0:9:21,6 --> 0:9:24,78 So there are 2 ownership things. 172 0:9:24,84 --> 0:9:28,1 But this is just some specific thing for sequences. 173 0:9:28,14 --> 0:9:30,56 It's very specific for sequences. 174 0:9:30,94 --> 0:9:33,78 Yeah, so objects can belong to. 175 0:9:34,06 --> 0:9:38,1 And here, when people start complaining, I ask, same thing, I 176 0:9:38,1 --> 0:9:43,16 ask, have you learned about altering, like passing all ownership 177 0:9:43,4 --> 0:9:47,18 from 1 user to another, like GRANT blah blah blah, OWNED BY 178 0:9:47,18 --> 0:9:48,26 or something like this? 179 0:9:48,26 --> 0:9:51,68 There is such ability, I don't remember syntax, but you can say 180 0:9:51,68 --> 0:9:56,74 I have a lot of stuff, I'm going to quit this company, you know, 181 0:9:57,18 --> 0:9:58,78 and I give it to you, yeah. 182 0:9:58,78 --> 0:10:1,32 Michael: I think it's something like REASSIGN OWNED BY. 183 0:10:1,52 --> 0:10:2,46 Yeah, something like this. 184 0:10:2,46 --> 0:10:3,76 Yeah, something like that, yeah. 185 0:10:4,02 --> 0:10:7,58 Nikolay: Yeah, so it's like, I like these massive operations. 186 0:10:7,72 --> 0:10:13,86 Obviously folks who developed them suffered enough so they developed 187 0:10:13,86 --> 0:10:14,06 it. 188 0:10:14,06 --> 0:10:16,82 And this is great to have massive operations. 189 0:10:16,88 --> 0:10:18,22 It helps you. 190 0:10:19,12 --> 0:10:26,26 Yeah, so I think, actually, I made a mistake in this sequence 191 0:10:26,28 --> 0:10:28,28 of things to mention. 192 0:10:28,58 --> 0:10:34,94 First thing I would mention is that separating roles, like separating 193 0:10:35,58 --> 0:10:40,8 pieces of application, kinds of workload to multiple roles and 194 0:10:40,8 --> 0:10:45,48 groups and so on, and having some approach, it's good, much better 195 0:10:45,48 --> 0:10:48,4 than having just single super user and that's it. 196 0:10:48,94 --> 0:10:49,44 Yeah. 197 0:10:49,54 --> 0:10:50,04 Yeah. 198 0:10:50,38 --> 0:10:53,44 Or just single application user and that's it. 199 0:10:53,5 --> 0:10:57,6 Because in many aspects, so we should separate because this gives 200 0:10:57,6 --> 0:11:1,42 us control and also troubleshooting capabilities because you 201 0:11:1,42 --> 0:11:6,58 know, pg_stat_statements has a username or user ID, I don't remember 202 0:11:6,58 --> 0:11:7,08 exactly. 203 0:11:7,6 --> 0:11:12,18 It tracks workload separately for each user, which is good. 204 0:11:12,18 --> 0:11:12,88 Michael: Yeah, true. 205 0:11:13,78 --> 0:11:16,68 So it's useful for troubleshooting, but also I think it helps 206 0:11:16,68 --> 0:11:19,84 protect you from yourself sometimes. 207 0:11:20,08 --> 0:11:25,12 If you know you only should be reading data, connecting as a 208 0:11:25,12 --> 0:11:29,62 read-only user helps you feel safe and that's true at the application 209 0:11:29,68 --> 0:11:30,78 level as well, right? 210 0:11:32,52 --> 0:11:36,78 If it shouldn't be creating objects, if it should just be doing 211 0:11:36,78 --> 0:11:41,82 normal kind of CRUD operations being a normal application user 212 0:11:42,04 --> 0:11:46,44 makes sense but if you want if you maybe doing migrations or 213 0:11:46,44 --> 0:11:50,42 creating objects as we were just talking about having an application 214 0:11:50,58 --> 0:11:54,84 admin role separately from an application user role makes a lot 215 0:11:54,84 --> 0:11:55,46 of sense 216 0:11:55,52 --> 0:11:57,8 Nikolay: in terms of security right 217 0:11:57,8 --> 0:12:0,8 Michael: well I was yeah so security I think is part of it but 218 0:12:0,8 --> 0:12:4,98 I think also just protection from yourself, like for mistakes 219 0:12:5,0 --> 0:12:5,66 as well. 220 0:12:5,92 --> 0:12:6,22 Right? 221 0:12:6,22 --> 0:12:9,72 If an application user shouldn't be created, like, yeah, probably 222 0:12:9,72 --> 0:12:14,86 mostly security, but I think there's also an element of not allowing 223 0:12:15,24 --> 0:12:18,38 read-only users to drop data, like by mistake. 224 0:12:19,62 --> 0:12:23,26 Nikolay: Yeah, so it's typical divide and conquer approach. 225 0:12:23,32 --> 0:12:28,78 So you divide and you have better control and visibility, like 226 0:12:28,78 --> 0:12:30,86 transparency of what's happening. 227 0:12:30,88 --> 0:12:34,62 And now I'm talking about this and speaking of transparency, 228 0:12:35,0 --> 0:12:39,12 observability-wise, I think it makes sense, actually, to extend 229 0:12:39,96 --> 0:12:44,44 query analysis we discussed so many times and have in monitoring. 230 0:12:44,44 --> 0:12:48,04 I know guys who are developing our monitoring, they are watching 231 0:12:48,04 --> 0:12:49,82 this podcast, listening to it. 232 0:12:50,02 --> 0:12:53,24 So I think it's a good idea actually to have separate graphs 233 0:12:53,76 --> 0:12:57,78 aggregated by user from pg_stat_statements and also by database. 234 0:12:57,78 --> 0:13:2,7 So just like in other kinds of aggregation pg_stat_statements 235 0:13:2,7 --> 0:13:4,94 offers, why not using them, right? 236 0:13:4,94 --> 0:13:7,9 So like I never saw such graphs implemented. 237 0:13:7,96 --> 0:13:10,4 Maybe there are some in some monitoring systems, but I think 238 0:13:10,4 --> 0:13:14,56 it's quite useful and you can understand, for example, how much 239 0:13:15,78 --> 0:13:18,6 database time is spent every second, my favorite seconds per 240 0:13:18,6 --> 0:13:20,44 second per each user, right? 241 0:13:20,66 --> 0:13:25,22 So, and this helps you understand like, oh, if your application 242 0:13:25,52 --> 0:13:29,38 and parts/workloads are split between multiple users, you 243 0:13:29,38 --> 0:13:34,74 can track and see which user generates more load at any given 244 0:13:34,74 --> 0:13:41,02 moment, or maybe more I/O in terms of hits and reads, buffer 245 0:13:41,02 --> 0:13:43,78 pool, or generating a lot of temporary files. 246 0:13:43,78 --> 0:13:47,72 So it's like all the metrics are here, and this is just dimension 247 0:13:47,8 --> 0:13:53,3 you can use to aggregate and see who to blame, which part of 248 0:13:53,3 --> 0:13:55,86 application to blame or workload to blame, right? 249 0:13:56,2 --> 0:13:58,08 Michael: Yeah, that's a good point actually. 250 0:13:58,08 --> 0:14:2,9 And since you mentioned temporary files, that is 1 use case I've 251 0:14:2,9 --> 0:14:7,42 seen from the performance side for roles is you can set, for 252 0:14:7,42 --> 0:14:9,88 example, work_mem at the role level. 253 0:14:9,88 --> 0:14:10,9 So you could give... 254 0:14:11,18 --> 0:14:13,04 Nikolay: This I wanted to touch. 255 0:14:13,04 --> 0:14:13,54 Yes. 256 0:14:14,84 --> 0:14:15,34 Okay. 257 0:14:15,7 --> 0:14:15,78 Yeah. 258 0:14:15,78 --> 0:14:19,68 I don't know if you remember, I once confessed that I put some 259 0:14:19,68 --> 0:14:22,62 big system down. 260 0:14:23,8 --> 0:14:24,94 Michael: No, I don't remember. 261 0:14:25,2 --> 0:14:27,78 Nikolay: Yeah, there was a story when I... 262 0:14:28,58 --> 0:14:30,7 It was a big loss in terms of income. 263 0:14:31,24 --> 0:14:37,96 It was e-commerce, and I just haven't noticed that the users 264 0:14:38,0 --> 0:14:41,68 had user level settings and it was not work_mem. 265 0:14:41,82 --> 0:14:45,34 work_mem is a good idea I think but it was logging settings. 266 0:14:46,76 --> 0:14:49,04 Oh, I think I'm interested. 267 0:14:49,2 --> 0:14:49,98 log_min_duration_statement. 268 0:14:50,58 --> 0:14:56,32 So yeah, so something like I don't remember exactly, but I just 269 0:14:56,32 --> 0:14:57,44 thought, oh, it's great. 270 0:14:57,44 --> 0:14:59,6 Like, let's change some level of verbosity. 271 0:15:0,22 --> 0:15:4,08 And then I didn't recognize that main user which is used for 272 0:15:4,08 --> 0:15:4,58 application. 273 0:15:4,9 --> 0:15:7,24 It was a single main user at that time. 274 0:15:8,04 --> 0:15:12,84 It has specific settings, so colliding to my change, we had observer 275 0:15:12,84 --> 0:15:14,48 effect and everything went down. 276 0:15:15,06 --> 0:15:18,74 So this increases complexity every time you have it. 277 0:15:18,74 --> 0:15:22,12 We also had a case, for example, when we decided to, you know, 278 0:15:22,12 --> 0:15:25,6 in PgBouncer, you can configure it in terms of pool sizes 279 0:15:25,6 --> 0:15:30,58 and combination of database and user, you can allocate a number 280 0:15:30,58 --> 0:15:33,36 of connections, maximum number of connections, and so on. 281 0:15:33,48 --> 0:15:37,8 And if you start splitting your quotas, you, for example, have 282 0:15:37,8 --> 0:15:39,52 max connections 500, right? 283 0:15:39,52 --> 0:15:43,02 Big server, almost 200 cores, for example. 284 0:15:43,26 --> 0:15:47,22 360 cores, and you have like 600 max connections, which is very, 285 0:15:47,22 --> 0:15:50,52 very sane, not far from max connections, cool. 286 0:15:50,58 --> 0:15:52,58 You are not allowing a lot of idle connections. 287 0:15:52,68 --> 0:15:55,94 And then you think, okay, I'm going to split and have boundaries 288 0:15:56,02 --> 0:15:58,22 of quotas for each user. 289 0:15:59,06 --> 0:16:2,64 And at that case, we ended up getting rid of it after a year 290 0:16:2,64 --> 0:16:5,36 or 2 of trying to use it. 291 0:16:5,54 --> 0:16:10,82 Because it just amplifies complexity everywhere. 292 0:16:11,36 --> 0:16:16,78 You need to think about, oh, this is this, but we have users. 293 0:16:17,22 --> 0:16:20,66 So this is good, very sharp, fine tuning method. 294 0:16:21,02 --> 0:16:23,32 And in case of work_mem, I think you're right. 295 0:16:23,32 --> 0:16:27,34 It's better to increase work_mem for specific like reporting roles 296 0:16:28,18 --> 0:16:28,94 than globally. 297 0:16:29,44 --> 0:16:34,12 But If you go in this direction too far, it's similar to tuning 298 0:16:34,12 --> 0:16:36,78 tables, or to vacuum tuning per table. 299 0:16:37,54 --> 0:16:42,72 I don't like it because in a growing organization, when we need 300 0:16:42,72 --> 0:16:47,36 to change things all the time, the complexity of configuration, 301 0:16:47,76 --> 0:16:52,66 it bites back very badly after like a few months everyone already 302 0:16:52,66 --> 0:16:57,34 forgot about this and you have so complex fine-tuned system. 303 0:16:57,62 --> 0:17:2,52 I would first consider like global changes wherever it's possible. 304 0:17:2,78 --> 0:17:7,8 Maybe work_mem is kind of some exclusion but connections and all 305 0:17:7,8 --> 0:17:11,3 the settings, it's better to have common rules for everyone. 306 0:17:12,18 --> 0:17:16,6 Because per role it's like exclusions from global rule. 307 0:17:18,34 --> 0:17:19,86 Michael: And harder to look up, right? 308 0:17:19,86 --> 0:17:21,18 Like, easy to forget. 309 0:17:21,18 --> 0:17:23,16 I think you make such a good point here. 310 0:17:23,2 --> 0:17:27,28 Even work_mem, you might be able to get away with it without 311 0:17:27,52 --> 0:17:28,82 setting it at the role level. 312 0:17:28,82 --> 0:17:32,64 Like, could you set it for each reporting query? 313 0:17:32,64 --> 0:17:35,8 If you only run a very few number of reporting queries, you could 314 0:17:35,8 --> 0:17:38,42 set it just in the session, for example. 315 0:17:39,48 --> 0:17:41,88 With caveats again around poolers, but yeah. 316 0:17:41,98 --> 0:17:46,02 Nikolay: Yeah, actually, it's a balance is 1 thing, But 317 0:17:46,02 --> 0:17:49,74 CREATE ROLE itself, CREATE USER in Postgres itself has also connection 318 0:17:49,74 --> 0:17:50,78 limit option. 319 0:17:51,66 --> 0:17:53,76 And this is the way where you can like... 320 0:17:54,64 --> 0:17:56,4 Maybe in some cases it's good. 321 0:17:56,4 --> 0:17:58,68 We know these users should be very limited. 322 0:17:58,74 --> 0:18:0,28 We know they abuse us. 323 0:18:0,28 --> 0:18:1,42 Connection limit 3. 324 0:18:1,42 --> 0:18:1,5701 That's it. 325 0:18:1,5701 --> 0:18:2,22 So that's it. 326 0:18:3,56 --> 0:18:8,9 But in general, I don't like too many local rules because complexity 327 0:18:8,94 --> 0:18:9,76 grows a lot. 328 0:18:9,76 --> 0:18:14,54 I wanted to emphasize once again importance of separation of 329 0:18:14,54 --> 0:18:19,34 roles not only for application parts or workload parts, but also 330 0:18:19,34 --> 0:18:20,14 for humans. 331 0:18:21,22 --> 0:18:28,58 Especially in AI time we live in, some humans can be sometimes 332 0:18:28,94 --> 0:18:31,62 bots, like AI, LLMs also. 333 0:18:32,86 --> 0:18:36,42 Sometimes they are actually humans and if everyone shares just 334 0:18:36,42 --> 0:18:42,04 1 role, especially if it's a super user, well that's dangerous, 335 0:18:42,1 --> 0:18:42,4 right? 336 0:18:42,4 --> 0:18:47,64 And we cannot understand who is, like even, not only about like 337 0:18:47,64 --> 0:18:52,64 who is like can put us down but for example during incidents 338 0:18:53,2 --> 0:18:55,54 it's harder to trace who is connected. 339 0:18:55,58 --> 0:19:1,0 We can do it using process ID and then go and check at OS level, 340 0:19:1,0 --> 0:19:5,14 but sometimes if they are root there, then we need to trace, 341 0:19:5,14 --> 0:19:5,74 trace, trace. 342 0:19:5,74 --> 0:19:6,6 It's like it's a lot. 343 0:19:6,6 --> 0:19:9,78 Sometimes it's RDS we don't have access to OS, and so if everyone 344 0:19:9,78 --> 0:19:14,2 is connected using 1 role, we cannot understand like who is doing 345 0:19:14,2 --> 0:19:14,7 what. 346 0:19:15,9 --> 0:19:20,46 That's like complicates troubleshooting a lot. 347 0:19:20,46 --> 0:19:23,86 So separation here again is very, very, very good practice. 348 0:19:25,84 --> 0:19:28,58 Michael: Yeah, plus all the security implications, right? 349 0:19:29,76 --> 0:19:34,44 Like You never want to think about what if someone that has super 350 0:19:34,44 --> 0:19:37,92 user access leaves the company all of a sudden. 351 0:19:37,92 --> 0:19:43,84 Like there's all sorts of risks to the more people that have 352 0:19:44,12 --> 0:19:47,36 that, let's say it's password, like username, password, just 353 0:19:47,36 --> 0:19:49,28 the more threat, like 354 0:19:49,28 --> 0:19:49,78 Nikolay: more. 355 0:19:50,74 --> 0:19:51,24 Yeah. 356 0:19:51,34 --> 0:19:57,76 And if it's 1 user for all, password rotation becomes like terrible. 357 0:19:58,66 --> 0:20:0,28 And I know cases, right? 358 0:20:0,28 --> 0:20:1,92 So and yes. 359 0:20:2,04 --> 0:20:5,86 And you know, it's interesting that CREATE ROLE, this is probably 360 0:20:5,86 --> 0:20:9,98 for guys who want to create their first patch or something and 361 0:20:9,98 --> 0:20:13,98 maybe it's a good idea to think is a good proposal for what 362 0:20:13,98 --> 0:20:18,62 Postgres lacks. So there is a... you know, CREATE ROLE has expiration. 363 0:20:19,54 --> 0:20:21,04 You can specify when. Michael: I didn't know that. 364 0:20:22,06 --> 0:20:22,56 Nikolay: Yeah. 365 0:20:22,66 --> 0:20:24,56 Some VALID UNTIL or something like this. 366 0:20:24,56 --> 0:20:26,84 So you can specify when I never use it. 367 0:20:26,84 --> 0:20:28,82 I just think this is good actually. 368 0:20:29,06 --> 0:20:34,22 So you can have temporary access, but I would rather prefer seeing 369 0:20:35,34 --> 0:20:36,98 expiration for password only. 370 0:20:39,78 --> 0:20:41,42 So rotation, yeah. 371 0:20:41,42 --> 0:20:47,12 Rotation will become mandatory and maybe with some warning, which 372 0:20:47,12 --> 0:20:51,38 will start popping up in logs and to client messages like 1 month 373 0:20:51,38 --> 0:20:52,4 before, for example. 374 0:20:53,3 --> 0:20:55,38 Michael: Yeah, I'm not such a big fan. 375 0:20:55,38 --> 0:20:58,46 I haven't really thought it through for databases, but I'm not 376 0:20:58,46 --> 0:21:4,74 a fan of password rotation policies for like individual web apps 377 0:21:4,74 --> 0:21:10,28 that you log into for example I just don't well as much but yeah 378 0:21:10,44 --> 0:21:12,96 Nikolay: yeah that's why do you think so 379 0:21:16,24 --> 0:21:20,28 Michael: So I think the root issue is password reuse. 380 0:21:20,74 --> 0:21:23,6 So I think the main thing you're protecting yourself from is 381 0:21:23,6 --> 0:21:29,18 a different service getting hacked and them not storing passwords 382 0:21:29,24 --> 0:21:29,74 hashed. 383 0:21:29,82 --> 0:21:33,6 Like plain text passwords or maybe a weak hashing algorithm and 384 0:21:33,6 --> 0:21:38,0 it gets cracked and then people try and use the same password 385 0:21:38,0 --> 0:21:40,64 in lots of different services and they happen to get lucky with 386 0:21:40,64 --> 0:21:45,04 your service and if you rotate passwords then they have to do 387 0:21:45,04 --> 0:21:47,92 that within a set period of time, otherwise you have to have 388 0:21:47,92 --> 0:21:48,94 set a new password. 389 0:21:49,7 --> 0:21:53,04 So it's has some, I think it has some protection there, but I'd 390 0:21:53,04 --> 0:21:58,38 much rather focus on just educating people to use unique passwords 391 0:21:58,38 --> 0:22:0,1 so you don't have that vector. 392 0:22:0,46 --> 0:22:3,9 Nikolay: But imagine, okay, imagine there's this, there's an 393 0:22:3,9 --> 0:22:8,1 organization, many, many, many people working, like new people 394 0:22:8,1 --> 0:22:12,02 come, some people leave the company and there is some password 395 0:22:12,1 --> 0:22:17,06 shared among them and it haven't been changed for years. 396 0:22:17,66 --> 0:22:18,16 Yeah. 397 0:22:18,22 --> 0:22:19,38 Is it a good idea? 398 0:22:20,6 --> 0:22:21,6 Or, okay. 399 0:22:21,82 --> 0:22:22,32 Okay. 400 0:22:22,46 --> 0:22:27,88 1 user has every user have their own password, but some users 401 0:22:27,88 --> 0:22:30,94 work at company many years, they created a lot of automation 402 0:22:31,04 --> 0:22:31,56 and so on. 403 0:22:31,56 --> 0:22:34,42 And maybe some of the last service roles, right? 404 0:22:35,14 --> 0:22:36,1 Like for automation. 405 0:22:36,7 --> 0:22:39,96 And some of them also like already propagated to many pieces 406 0:22:39,96 --> 0:22:41,26 of code. 407 0:22:41,66 --> 0:22:45,24 I don't know, like passwords should not be stored in code, right, 408 0:22:45,24 --> 0:22:45,68 of course. 409 0:22:45,68 --> 0:22:49,32 But there are several pieces of automation sharing the same password 410 0:22:49,32 --> 0:22:51,38 and it was not changed for years. 411 0:22:51,68 --> 0:22:52,9 It doesn't feel safe. 412 0:22:52,9 --> 0:22:56,26 And I'm not a security expert. 413 0:22:56,26 --> 0:23:0,9 I just know sometimes there is a policy for password rotation 414 0:23:0,9 --> 0:23:2,52 and token rotation, expiration. 415 0:23:2,64 --> 0:23:3,88 So there is such policy. 416 0:23:4,08 --> 0:23:5,44 But Postgres lacks this. 417 0:23:6,02 --> 0:23:10,54 So it should be managed outside of Postgres and enforced outside. 418 0:23:11,24 --> 0:23:16,52 Michael: It's also a bit of a pain to do, like, in terms of like, 419 0:23:17,24 --> 0:23:18,54 back to 0 downtime, right? 420 0:23:18,54 --> 0:23:21,64 Like, we have to, if we want to rotate, 421 0:23:21,9 --> 0:23:22,4 Nikolay: yeah. 422 0:23:22,54 --> 0:23:28,08 Yes, I think like usually you will end up having new role but 423 0:23:28,08 --> 0:23:31,36 then it won't work everywhere because of ownership as well. 424 0:23:31,4 --> 0:23:34,02 So exactly like thinking through, okay. 425 0:23:35,02 --> 0:23:38,32 Maybe not only the expiration, but maybe some workflow when, 426 0:23:38,32 --> 0:23:42,04 for example, 2 passwords are temporarily allowed for some time, 427 0:23:42,04 --> 0:23:48,4 and then you can still use the same role because you have a lot 428 0:23:48,4 --> 0:23:52,86 of nodes, application nodes and some other things which constantly 429 0:23:52,9 --> 0:23:57,24 working with this database right so you need you need some overlapping 430 0:23:57,98 --> 0:24:2,52 for both passwords for example or another thing actually maybe 431 0:24:2,52 --> 0:24:8,16 we will agree both here It would be good to say I don't, like, 432 0:24:8,48 --> 0:24:10,16 simple passwords are not allowed. 433 0:24:11,18 --> 0:24:11,68 Yes. 434 0:24:12,12 --> 0:24:12,62 Yes. 435 0:24:13,52 --> 0:24:14,62 Michael: I completely agree. 436 0:24:14,62 --> 0:24:15,12 Nikolay: Right? 437 0:24:15,24 --> 0:24:18,04 Or there is, maybe I'm missing something because I'm old. 438 0:24:18,04 --> 0:24:21,58 Michael: No, the only thing I know of in this area is quite a 439 0:24:21,58 --> 0:24:24,88 cool service by a guy, an Australian guy called Troy Hunt. 440 0:24:24,88 --> 0:24:26,6 I might have mentioned it in the past. 441 0:24:26,8 --> 0:24:31,2 He runs a website called Have I Been Pwned, but he also runs 442 0:24:31,2 --> 0:24:31,9 a service. 443 0:24:32,52 --> 0:24:34,18 Nikolay: I know this website, actually. 444 0:24:34,54 --> 0:24:35,78 Michael: Yeah, it's great. 445 0:24:36,42 --> 0:24:40,6 So that website is you can put your email address in, and the 446 0:24:40,6 --> 0:24:44,64 website will tell you, are you in any dumps of email address, 447 0:24:44,64 --> 0:24:45,64 password, combos, 448 0:24:45,86 --> 0:24:46,28 font. 449 0:24:46,28 --> 0:24:49,78 Nikolay: It creates 1 more dump every time and it always say yes. 450 0:24:50,74 --> 0:24:52,24 Michael: Basically the answer is always yes. 451 0:24:52,24 --> 0:24:54,38 Yeah but you can see which services for example. 452 0:24:55,46 --> 0:24:55,9 Yeah. 453 0:24:55,9 --> 0:25:1,36 Well and the another service he runs is basically all of those 454 0:25:1,36 --> 0:25:6,24 passwords so you so if somebody if like 1 of your users sets 455 0:25:6,24 --> 0:25:10,24 a password, you can hash it, send it off to his service, and 456 0:25:10,24 --> 0:25:12,86 say, is this 1 in the leaked database? 457 0:25:12,9 --> 0:25:15,12 And all of the simple passwords are. 458 0:25:15,48 --> 0:25:18,66 Every single simple password you can ever imagine is in there. 459 0:25:18,66 --> 0:25:20,02 Plus any that have been leaked. 460 0:25:20,02 --> 0:25:24,1 So you can not only stop people setting simple passwords, but 461 0:25:24,1 --> 0:25:27,02 you can also stop them setting ones that have already been leaked. 462 0:25:27,1 --> 0:25:29,72 So it's, well, at least publicly known and leaked. 463 0:25:30,28 --> 0:25:33,72 So that's a cool service, but obviously it's a web service. 464 0:25:33,94 --> 0:25:37,3 Nikolay: That's why people prefer auto-generated passwords lately, 465 0:25:37,3 --> 0:25:43,2 like just not rely on the users anymore at all, just always auto-generate, 466 0:25:43,5 --> 0:25:44,34 that's it. 467 0:25:45,04 --> 0:25:47,56 Michael: Do you mean like using a password manager or do you 468 0:25:47,56 --> 0:25:48,34 mean like? 469 0:25:48,68 --> 0:25:52,86 Nikolay: In general, instead of like you build some system and 470 0:25:53,42 --> 0:25:57,04 instead of saying like you will generate it, you will provide 471 0:25:57,04 --> 0:25:59,34 a password, no, just auto generate it. 472 0:25:59,34 --> 0:26:2,98 But again, There is no, for example, feature to ask user to change 473 0:26:2,98 --> 0:26:3,48 it. 474 0:26:4,2 --> 0:26:7,9 There are some things, usually some systems have, but Database 475 0:26:7,9 --> 0:26:8,72 system doesn't. 476 0:26:8,76 --> 0:26:12,32 So there is like, you need some layer here above. 477 0:26:12,34 --> 0:26:15,08 But Postgres could have some of these things. 478 0:26:15,42 --> 0:26:19,02 It recently got another thing, which we discussed last week, 479 0:26:20,14 --> 0:26:24,72 the way to use OAuth, how to pronounce it. 480 0:26:24,8 --> 0:26:25,52 Michael: Oh, yeah. 481 0:26:25,68 --> 0:26:28,44 I say OAuth, but I actually don't know if I've heard anyone say 482 0:26:28,44 --> 0:26:28,94 it. 483 0:26:29,06 --> 0:26:31,22 Nikolay: Yeah, yeah, So yeah, that's interesting. 484 0:26:31,4 --> 0:26:34,92 But there are more like fundamental things that could be also 485 0:26:34,92 --> 0:26:35,42 implemented. 486 0:26:36,34 --> 0:26:38,08 Just there's opportunity here. 487 0:26:38,72 --> 0:26:42,9 Okay, anyway, we both agree that simple passwords are bad and 488 0:26:42,9 --> 0:26:47,46 Postgres could check them when role is created or password changed 489 0:26:47,56 --> 0:26:48,78 using ALTER ROLE. 490 0:26:49,64 --> 0:26:50,14 Right? 491 0:26:51,04 --> 0:26:51,54 Michael: Yeah. 492 0:26:51,74 --> 0:26:52,24 Nikolay: Maybe. 493 0:26:52,5 --> 0:26:53,3 I don't know. 494 0:26:53,4 --> 0:26:56,42 Michael: And we could implement like everybody on their own could 495 0:26:56,42 --> 0:26:59,12 implement their own kind of like interface and function. 496 0:26:59,54 --> 0:27:0,04 Nikolay: Function. 497 0:27:0,66 --> 0:27:3,08 I think it's possible with event triggers maybe, right? 498 0:27:3,08 --> 0:27:3,96 I don't know. 499 0:27:4,74 --> 0:27:7,8 Michael: I was actually thinking just a little simple web app, 500 0:27:7,8 --> 0:27:8,5 but yeah. 501 0:27:8,66 --> 0:27:12,04 Nikolay: Well, on top of Postgres, of course, but inside Postgres, 502 0:27:12,04 --> 0:27:15,56 I think if it was a feature, it would be good to have it implemented 503 0:27:15,66 --> 0:27:18,06 in a way that could be customized. 504 0:27:18,7 --> 0:27:21,94 You provide a function which just returns true or false. 505 0:27:22,66 --> 0:27:23,32 That's it. 506 0:27:23,32 --> 0:27:24,3 It will be good. 507 0:27:24,84 --> 0:27:28,04 And then you have some policy to be defined. 508 0:27:28,38 --> 0:27:31,9 Speaking of passwords and leaking passwords. 509 0:27:31,96 --> 0:27:36,88 Of course, we know like plaintext passwords is bad, right? 510 0:27:37,06 --> 0:27:41,74 Encrypted, they should be encrypted and stored in hash. 511 0:27:42,56 --> 0:27:48,42 And many, many years, MD5 was the choice, right? 512 0:27:49,02 --> 0:27:54,52 And in pg_hba.conf you configure like no trust, no plain text, 513 0:27:54,52 --> 0:27:55,86 just MD5, right? 514 0:27:55,94 --> 0:28:0,52 But since Postgres 10 we have SCRAM, which is like salted. 515 0:28:1,38 --> 0:28:4,34 Well, MD5 actually was also salted with username. 516 0:28:4,68 --> 0:28:5,78 You know this, right? 517 0:28:6,54 --> 0:28:9,66 So we always could create, when you say CREATE USER, you could 518 0:28:9,66 --> 0:28:14,7 specify not the plain text password in the DDL command, but you 519 0:28:14,7 --> 0:28:19,24 could concatenate password, actual password you want to have 520 0:28:19,24 --> 0:28:24,56 and username, and then calculate MD5 of it, and then have prefix 521 0:28:24,6 --> 0:28:27,44 MD5 and provide all this instead of password. 522 0:28:27,5 --> 0:28:28,82 And this will work properly. 523 0:28:29,1 --> 0:28:35,6 So this protects you from password leaking to like just leaking 524 0:28:35,6 --> 0:28:40,24 password right because for example we always say log statement 525 0:28:40,24 --> 0:28:46,04 should be DDL so we see what's happening right but if it's DDL 526 0:28:46,16 --> 0:28:49,86 then every CREATE USER or ALTER USER changing password, it leaks 527 0:28:49,96 --> 0:28:50,78 that value. 528 0:28:52,5 --> 0:28:52,8 And it 529 0:28:52,8 --> 0:28:54,9 Michael: means anybody with access to the logs. 530 0:28:55,32 --> 0:28:55,82 Nikolay: Yeah. 531 0:28:56,46 --> 0:28:59,74 In big organization, we have a log collecting system and so on. 532 0:29:0,02 --> 0:29:4,3 Usually, there are some mechanisms to remove PII and so on, but 533 0:29:4,3 --> 0:29:8,96 this is not just PII, it's very, very, very dangerous PII. 534 0:29:10,24 --> 0:29:16,36 And yeah, so actually, so plaintext is absolutely no algorithm 535 0:29:16,4 --> 0:29:19,62 to store passwords, because also it will go to dumps. 536 0:29:19,86 --> 0:29:24,22 Well, regular dumps don't dump users and roles, right? 537 0:29:24,62 --> 0:29:26,4 But pg_dumpall does. 538 0:29:26,52 --> 0:29:27,76 pg_dumpall does. 539 0:29:28,52 --> 0:29:32,22 I'm not sure if it works on RDS, though, and others, but in self-managed, 540 0:29:32,22 --> 0:29:36,54 definitely it works, and it will dump everything, including this. 541 0:29:37,04 --> 0:29:44,04 Okay, so this is a good idea to have this hash instead of password 542 0:29:44,06 --> 0:29:49,12 to provide it, And even if it goes to logs, it's already like 543 0:29:49,34 --> 0:29:52,74 not the raw value, which is much better, right? 544 0:29:53,6 --> 0:29:58,4 And SCRAM adds even better protection because still MD5 you can 545 0:29:58,78 --> 0:30:3,02 basically find collisions with regular hardware these days, right? 546 0:30:3,62 --> 0:30:7,4 So SCRAM is much better and much more strongly encrypted and 547 0:30:7,4 --> 0:30:8,08 so on. 548 0:30:8,42 --> 0:30:16,2 But still, I see people use, even if SCRAM is configured, everything, 549 0:30:16,58 --> 0:30:19,68 Creating user is a problem because it goes to logs. 550 0:30:20,34 --> 0:30:23,9 Especially if it's MD5, people still use plain text to create 551 0:30:23,9 --> 0:30:27,28 user and if log statement is DDL, it will go with plain text 552 0:30:27,28 --> 0:30:27,94 to logs. 553 0:30:28,68 --> 0:30:31,82 And to avoid that, How can we avoid that? 554 0:30:32,08 --> 0:30:36,66 Well, changing log statement is not a good idea because you will 555 0:30:36,66 --> 0:30:37,44 miss the fact. 556 0:30:37,44 --> 0:30:42,04 I would prefer seeing that this user was created but do not see 557 0:30:42,04 --> 0:30:42,54 password. 558 0:30:43,74 --> 0:30:47,0 And I actually expected pgaudit would do this, but no. 559 0:30:47,0 --> 0:30:47,52 It also... 560 0:30:47,52 --> 0:30:48,36 Michael: Interesting. 561 0:30:50,18 --> 0:30:52,54 Nikolay: I know pgaudit was developed by Crunchy. 562 0:30:52,54 --> 0:30:56,18 Crunchy in terms of security is great by the way, the document, 563 0:30:56,32 --> 0:30:59,68 maybe we mentioned, I don't remember this document created... 564 0:31:0,04 --> 0:31:1,5 Michael: We did a whole episode on auditing. 565 0:31:2,2 --> 0:31:6,5 Nikolay: So there's a public document developed, I think, during 566 0:31:6,5 --> 0:31:10,78 the contract with US military. 567 0:31:11,48 --> 0:31:12,58 So it was great. 568 0:31:12,8 --> 0:31:16,14 Huge list of checks you should do to improve security. 569 0:31:17,04 --> 0:31:18,74 And I checked Crunchy Bridge. 570 0:31:18,74 --> 0:31:21,86 I was thinking, OK, how do they do it? 571 0:31:22,82 --> 0:31:24,36 Well, they log it twice. 572 0:31:24,62 --> 0:31:27,94 And if you create a role using plaintext password, it's logged 573 0:31:27,94 --> 0:31:29,48 twice as plaintext. 574 0:31:30,36 --> 0:31:33,14 So that's disappointing, actually. 575 0:31:33,7 --> 0:31:36,3 And pgaudit will log it as plain text as well. 576 0:31:36,3 --> 0:31:36,6 Why? 577 0:31:36,6 --> 0:31:40,14 Because it logs the statement as context. 578 0:31:40,9 --> 0:31:41,86 This is a problem. 579 0:31:42,56 --> 0:31:46,8 pgaudit says the role was created, but then it also logs context. 580 0:31:46,88 --> 0:31:52,66 And context exposes password if it was not hash value but plain 581 0:31:52,66 --> 0:31:53,16 text. 582 0:31:53,7 --> 0:31:57,44 And I just recalled, we did it a couple of weeks ago, we were 583 0:31:57,44 --> 0:31:59,74 curious and it was needed for some work. 584 0:31:59,86 --> 0:32:6,16 And I recall many, many, many years ago, I created basic PL/pgSQL 585 0:32:6,42 --> 0:32:9,56 scripts, which will auto-generate password. 586 0:32:10,58 --> 0:32:12,84 You just specify length, and it will auto-generate. 587 0:32:13,44 --> 0:32:15,64 It will be printed, but not logged. 588 0:32:16,5 --> 0:32:17,78 So it's quite easy. 589 0:32:20,84 --> 0:32:25,16 You just do anonymous DO block, and that's it. 590 0:32:25,16 --> 0:32:27,54 Or you can wrap it inside function, right? 591 0:32:28,26 --> 0:32:33,58 In case of context, the function name will be mentioned, right? 592 0:32:33,66 --> 0:32:35,26 But not the body of it. 593 0:32:35,42 --> 0:32:36,6 Michael: Yeah, I'm missing something. 594 0:32:36,6 --> 0:32:39,84 How come it avoids being logged? 595 0:32:41,0 --> 0:32:44,32 Nikolay: So if you, for example, wrap it inside the PL/pgSQL 596 0:32:44,62 --> 0:32:47,54 function, its name will be logged, but not the body. 597 0:32:48,34 --> 0:32:49,18 That's it. 598 0:32:49,86 --> 0:32:50,36 Oh! 599 0:32:50,74 --> 0:32:51,54 That's it. 600 0:32:51,74 --> 0:32:56,92 So you just create the PL/pgSQL function, CREATE or ALTER USER, 601 0:32:57,26 --> 0:32:58,42 and work through it. 602 0:32:58,94 --> 0:33:3,82 And even if you will do plain text, like for simplicity, convenience, 603 0:33:3,86 --> 0:33:5,24 it won't go to logs. 604 0:33:6,46 --> 0:33:7,46 Well, it will. 605 0:33:7,6 --> 0:33:10,02 Because again, like again, combining idea. 606 0:33:10,52 --> 0:33:14,28 In my case, the password is auto generated and just printed out. 607 0:33:14,48 --> 0:33:16,26 So you can start using it. 608 0:33:17,22 --> 0:33:20,78 Michael: What about other, like, let's say it takes, for some 609 0:33:20,78 --> 0:33:23,48 reason, you hit some lock or something, and it takes more than 610 0:33:23,48 --> 0:33:27,64 50 milliseconds or whatever you've set 611 0:33:27,64 --> 0:33:28,88 log_statement to. 612 0:33:30,42 --> 0:33:34,44 If it's in a function, it won't get logged there either, or it 613 0:33:34,44 --> 0:33:34,94 might. 614 0:33:35,2 --> 0:33:35,74 Would it? 615 0:33:35,74 --> 0:33:37,62 No, it wouldn't, because it wouldn't be in the query. 616 0:33:37,72 --> 0:33:38,22 Nice. 617 0:33:38,48 --> 0:33:38,94 Nikolay: Yeah, yeah. 618 0:33:38,94 --> 0:33:40,96 So function name will be logged. 619 0:33:41,52 --> 0:33:42,1858 Password is auto-generated inside. 620 0:33:42,1858 --> 0:33:43,2 It's printed to you, but it's not logged. 621 0:33:45,68 --> 0:33:46,18 Great. 622 0:33:46,92 --> 0:33:48,84 Michael: Yeah, not logged in anywhere. 623 0:33:48,9 --> 0:33:49,4 Great. 624 0:33:49,44 --> 0:33:54,32 Nikolay: Yeah, but it's like you this is, it should be like kind 625 0:33:54,32 --> 0:33:56,14 of code style or something. 626 0:33:56,68 --> 0:33:58,48 And you need to enforce it yourself. 627 0:33:58,58 --> 0:33:59,96 But this won't enforce it. 628 0:33:59,96 --> 0:34:4,36 This is what I think there's a gap here, we could fill just improving 629 0:34:4,36 --> 0:34:9,88 Postgres itself, just to have some protection that good practices 630 0:34:9,94 --> 0:34:11,6 are followed, right? 631 0:34:11,6 --> 0:34:16,04 Right now it's just on shoulders of who manages Postgres. 632 0:34:16,86 --> 0:34:19,92 Michael: I actually can't think of any good reason to log the 633 0:34:19,92 --> 0:34:22,66 actual password in plain text ever. 634 0:34:23,4 --> 0:34:23,9 Exactly. 635 0:34:25,44 --> 0:34:26,9 Like, yeah, we might be better. 636 0:34:26,94 --> 0:34:28,06 That could just be 637 0:34:28,32 --> 0:34:29,18 Nikolay: simple change. 638 0:34:29,6 --> 0:34:30,26 Yeah, yeah. 639 0:34:30,62 --> 0:34:31,42 Just mask it. 640 0:34:31,42 --> 0:34:31,92 Yeah. 641 0:34:32,52 --> 0:34:32,78 Why? 642 0:34:32,78 --> 0:34:33,28 Yeah. 643 0:34:34,56 --> 0:34:35,36 Doesn't matter. 644 0:34:35,4302 --> 0:34:39,14 Is it a hash or just password itself, just mask it. 645 0:34:39,14 --> 0:34:40,36 Michael: Star, star, star, star. 646 0:34:40,36 --> 0:34:41,26 It doesn't matter. 647 0:34:41,28 --> 0:34:43,04 Nikolay: Yeah, exactly. 648 0:34:43,82 --> 0:34:49,16 1 more thing I think is kind of interesting, I learned recently. 649 0:34:49,28 --> 0:34:53,14 I haven't checked myself, but I rely on what my team tells me. 650 0:34:54,02 --> 0:34:56,98 We develop components for self-driving vehicles. 651 0:34:58,48 --> 0:35:2,92 And 1 of them is to deal with index health, which we discussed 652 0:35:2,92 --> 0:35:3,66 many times. 653 0:35:4,16 --> 0:35:10,32 So we needed to be able to recreate indexes and we started with 654 0:35:10,32 --> 0:35:14,54 dblink, which was not good because password is always there. 655 0:35:14,54 --> 0:35:16,78 When you connect, you need to specify a password. 656 0:35:17,54 --> 0:35:20,66 I recall the old trick I learned like many years ago, but then 657 0:35:20,66 --> 0:35:24,56 I recently saw it's actually described in documentation of dblink_connect 658 0:35:25,02 --> 0:35:25,52 function. 659 0:35:25,76 --> 0:35:31,2 You can use PostgreSQL FDW server and on top of it, you can use 660 0:35:31,2 --> 0:35:31,7 dblink. 661 0:35:32,02 --> 0:35:37,06 In this case, dblink_connect will not need to have a password, 662 0:35:37,66 --> 0:35:41,1 because password is defined in the FDW, right? 663 0:35:42,18 --> 0:35:43,76 So in user mapping, actually. 664 0:35:44,96 --> 0:35:49,02 In a foreign server you define just like IP address and give 665 0:35:49,02 --> 0:35:51,98 you the name, but then you define user mapping, there you need 666 0:35:51,98 --> 0:35:52,94 to provide password. 667 0:35:53,22 --> 0:35:58,0 So the thing is that if you create a dump, this thing will be 668 0:35:58,0 --> 0:36:0,36 dumped, like user mapping will be dumped, right? 669 0:36:0,36 --> 0:36:1,94 And password is there, right? 670 0:36:2,08 --> 0:36:6,38 So also like kind of like things that should be maybe understood 671 0:36:6,38 --> 0:36:8,04 better and maybe adjusted. 672 0:36:9,06 --> 0:36:12,02 So, yeah, it's, it's, it's already better. 673 0:36:12,1 --> 0:36:15,74 I mean, you like, your code doesn't need to specify password. 674 0:36:15,74 --> 0:36:17,06 It's just 1 definition. 675 0:36:17,12 --> 0:36:17,56 That's it. 676 0:36:17,56 --> 0:36:23,52 But if you work with dumps too much, this is a place where password 677 0:36:23,52 --> 0:36:24,36 can be leaked. 678 0:36:25,44 --> 0:36:27,42 And you probably don't expect it also. 679 0:36:27,52 --> 0:36:31,02 I think people don't expect password to be leaked in dumps. 680 0:36:31,98 --> 0:36:36,1 So yeah, these are interesting things about passwords and user 681 0:36:36,1 --> 0:36:36,6 management. 682 0:36:37,08 --> 0:36:39,56 Right now we should make efforts to be protected. 683 0:36:39,84 --> 0:36:40,82 This is the key. 684 0:36:40,96 --> 0:36:43,26 And also like let me complain a little bit. 685 0:36:43,26 --> 0:36:48,48 The problem is if we, if like People often call us to check things. 686 0:36:48,84 --> 0:36:54,6 We come, and it's actually not easy to understand kind of like 687 0:36:54,6 --> 0:36:57,4 what's happening with, for example, user management and passwords. 688 0:36:57,4 --> 0:37:2,98 We can see the list of roles and so on, but to understand the 689 0:37:2,98 --> 0:37:6,88 risks and the problems, we need to talk to people right now. 690 0:37:7,1 --> 0:37:12,04 So the automated solutions to perform security audit, they are 691 0:37:12,04 --> 0:37:13,76 usually quite weak, unfortunately. 692 0:37:14,54 --> 0:37:16,02 Because It's not easy. 693 0:37:16,02 --> 0:37:18,74 It's not easy to understand that for example passwords is going 694 0:37:18,74 --> 0:37:21,32 to be leaked next week because of something. 695 0:37:21,9 --> 0:37:22,4 Yeah 696 0:37:22,66 --> 0:37:26,08 Michael: Yeah on kind of password protection front. 697 0:37:26,3 --> 0:37:30,76 I actually I don't think we mentioned it last week But I spotted 698 0:37:30,76 --> 0:37:35,58 in the release notes that MD5 is actually being deprecated as 699 0:37:35,58 --> 0:37:36,5 part of 18. 700 0:37:36,74 --> 0:37:37,4 Yeah, yeah. 701 0:37:37,54 --> 0:37:42,98 So not removed yet, but you get deprecation warnings now in Postgres 702 0:37:42,98 --> 0:37:43,74 18 if you try 703 0:37:43,74 --> 0:37:44,44 Nikolay: and set it. 704 0:37:44,44 --> 0:37:45,34 It took 8 years. 705 0:37:45,34 --> 0:37:52,36 SCRAM was added in Postgres 10, so it's 8 or 9 years, depending 706 0:37:52,36 --> 0:37:53,44 on how you count. 707 0:37:55,32 --> 0:37:56,98 Michael: But progress, good news. 708 0:37:57,6 --> 0:37:58,52 Nikolay: Good, yeah, yeah. 709 0:37:58,52 --> 0:38:2,08 So everyone should be using SCRAM and think about things we just 710 0:38:2,08 --> 0:38:4,04 discussed, and it will be much better. 711 0:38:4,9 --> 0:38:10,58 But right now, there are many dangers in terms of security, unfortunately. 712 0:38:11,5 --> 0:38:14,86 And I think it's also job for managed platform to improve things, 713 0:38:14,86 --> 0:38:15,36 right? 714 0:38:16,02 --> 0:38:17,24 And I think they do. 715 0:38:17,46 --> 0:38:20,26 I saw many things, and they recognize this problem. 716 0:38:22,66 --> 0:38:25,52 Michael: I think they just take a lot of this out of, they handle 717 0:38:25,52 --> 0:38:26,2 that stuff. 718 0:38:26,2 --> 0:38:30,98 If you've got a good provider that's hot on security stuff, then 719 0:38:31,26 --> 0:38:34,24 you probably at least got the absolute basics. 720 0:38:34,44 --> 0:38:36,78 Nikolay: Certificates and TLS. 721 0:38:37,36 --> 0:38:38,18 This is also good. 722 0:38:38,18 --> 0:38:38,96 Michael: Yeah, good point. 723 0:38:40,44 --> 0:38:43,14 Slightly outside the scope of user management, but definitely 724 0:38:43,14 --> 0:38:44,8 security 101, right? 725 0:38:44,8 --> 0:38:46,08 Nikolay: Adjacent topic, yeah. 726 0:38:46,08 --> 0:38:48,0 Michael: Yeah, yeah, another time. 727 0:38:49,34 --> 0:38:52,12 Nikolay: Right, so yeah, these are things I wanted to touch a 728 0:38:52,12 --> 0:38:52,66 little bit. 729 0:38:52,66 --> 0:38:53,3 What else? 730 0:38:53,48 --> 0:38:54,26 That's it. 731 0:38:54,52 --> 0:38:55,44 Michael: I think that's good. 732 0:38:55,44 --> 0:38:55,94 Yeah. 733 0:38:56,04 --> 0:38:57,1 Thanks so much, Nikolay. 734 0:38:57,1 --> 0:38:58,12 Catch you next week. 735 0:38:58,44 --> 0:38:58,87 Nikolay: Thank you.