Skip to content
PodSubs
ShowsPeopleGuestsJobsNewsEvents
More
BlogPodCred
Menu
ShowsPeopleGuestsJobsNewsEventsBlogPodCredNetworksSign in
Join nowSign in
  • Shows
  • People
  • Jobs
  • News
  • More
    • Guests
    • Events
    • Blog
    • PodCred
    • Networks
    Join nowSign in

Postgres FM

Postgres FM artwork

User management

Oct 3, 202539 minAudio
ShareXLinkedInFacebookWhatsAppEmail

Nik and Michael discuss user management in Postgres - how roles work, making administration easier, setting passwords, and avoiding them being logged. Here are some links to things they mentioned: Roles https://www.postgresql.org/docs/current/user-manag.html Privileges https://www.postgresql.org/docs/current/ddl-priv.html ALTER DEFAULT PRIVILEGES https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html GRANT https://www.postgresql.org/docs/current/sql-grant.html REASSIGN OWNED https://www.postgresql.org/docs/current/sql-reassign-owned.html ALTER ROLE (including SET) https://www.postgresql.org/docs/current/sql-alterrole.html CREATE ROLE https://www.postgresql.org/docs/current/sql-createrole.html Have I Been Pwned https://haveibeenpwned.com Pwned Passwords API https://haveibeenpwned.com/API/v3#PwnedPasswords Crunchy Data PostgreSQL Security Technical Implementation Guide (STIG) https://www.crunchydata.com/blog/announcing-the-crunchy-data-postgresql-stig Our episode on auditing https://postgres.fm/episodes/auditing ~~~ What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc! ~~~ Postgres FM is produced by: Michael Christofides, founder of pgMustard Nikolay Samokhvalov, founder of Postgres.ai With credit to: Jessie Draws for the elephant artwork

Audio and video play straight from the show's own hosting. PodSubs stores and serves no media.

Transcript

From the show. Tap a time to play from there.

Michael: Hello and welcome to PostgresFM, a weekly show about all things PostgreSQL. I am Michael, founder of pgMustard, and as usual, I'm joined by Nik, founder of Postgres.AI. Hey Nik, how's it going? Nikolay: Hi Michael, everything is alright. My favorite topic today. Michael: I was actually gonna bring up an old joke and say it's the most boring one yet but your idea now Nikolay: yeah it's my idea like it's like cold plunges sometimes you need to do it to feel then like once I finish this recording I will feel much better

Michael: yeah well and also you mentioned some things to me, no spoilers, but I think you've got some ways of making this interesting, so I'm looking forward to those. Nikolay: Who knows, let's see. I wanted to talk about some kind of simple things. Of course I'm pretty sure there are many people who know the topic much better than me, but I just wanted to raise some questions we currently have in Postgres and so on. Michael: Yeah, me too. So, user management, where do you want to start? I think it might even be interesting to think about the concept

of Roles in Postgres. I've come from a background of different databases and Postgres was a little bit different. When I came across it, I didn't really understand it until reading the docs exactly how things work. Nikolay: First thing if you run self-managed Postgres, first thing you confuse is operational system user and database user, right? Yeah. Second thing you confuse Users versus Roles because in other database systems they are distinguished in Postgres they are kind of the same thing.

Michael: Third thing, groups as well. Nikolay: Oh yes, exactly. Yeah. There are no groups basically in Postgres, although they're like directly, there are no separate concepts. But let's answer all these. Like, operational system User is operational system User, and Postgres User is Postgres User. Sometimes there is a coincidence and they both are named Postgres, for example, in Ubuntu. For example in Ubuntu. And to make things more complex, if you just, if you locally

connect using psql, and you don't specify uppercase U option, In this case, your current OS User will be used. So it's okay. But we need to distinguish. These things are very different, right? Just, yeah, coincidence makes things... Like I know people who created it, they thought, like, let's make things simple.

Yeah. But confusion which comes off it, if I, for example, needed to decide right now, I would probably not do this, you know? Like, just to avoid confusion, because the cost of confusion is much bigger than convenience you have. Michael: Yeah I think it's 1 of those I think it's 1 of those things that depends who you're trying to optimize for Are we trying to optimize for a developer at university learning computer science for the first time just trying to get set up

on a local machine and get up and running easily? I think for example in the Stack Overflow survey they do each year, There are several questions that if you optimised for that and that alone you'd score highly in certain things. But sometimes you would make some design decisions if you were optimising for that user that wouldn't be good for people trying to set up a company in a more secure manner with lots of different

users that have different needs and maybe behind a firewall like that in a way that's very very different to that individual user trying to get set up for a little hobby project or just to learn about databases. So I think it's really hard to make a system that's optimal for both and I'm not sure I think probably we're at the stage where Postgres should be optimizing more for the secure case, but I could understand if it chose to go more along the lines of make

it easy to get started. Nikolay: Yeah, good points. Anyway, I just taught myself to distinguish. When in my language, I just try to mention explicitly OS users, DB users, or people who are listening understand very quickly what I'm trying to say. And this I think is a good mitigation of this to avoid confusion. Just OS user, DB user, that's it. Although if you say role, people also maybe won't think about

OS user because we don't use role there at all. So this is another way you can, but still like if you say user, probably it's the user. Yeah, and the second confusion and I think third confusion is similar here. So users and roles, it's the same in Postgres and groups are basically the same. It's just 1 single database object. You can create a role or user Michael and then grant it to user

role Nik, right? So I will get all permissions you have. And in this case, this will make you virtually database group Michael, right? So It's very flexible, but also be confusing, especially for those who came from other database management systems. Michael: For sure. I pulled out a good line from the docs which says any role can act as a user, as a group, or both.

Which I thought was a nice succinct way of saying the groups can still act as users individually but they can also be groups of permissions and you yeah... Nik and Michael could both be both in the DBAs group but also like have other memberships to other groups and makes it easier to manage permissions that way, right? Nikolay: Yeah, good. So yeah, these are kind of basic things. And I wanted to discuss like, like, I never heard anyone saying, Postgres is having so beautiful privileges system.

Somehow it's like, I've heard opposite a lot of times, a lot of times, especially from guys who manage operational system and they need to manage Postgres, like sysadmins and then later SREs, they always complain. When they start complaining, I don't listen. I just say, have you learned about default privileges? Michael: Ah, cool. Nikolay: Yeah. This is the first thing you need to remember. There are default privileges, and you can alter them and say,

these default privileges that should be always assigned. This makes things easier, Much easier. Because if you have a lot of database objects, like you think, oh, how, like I'm going to create new role, user, group, how am I going to, like, or tomorrow developers will create new table and who will take care of handling permissions, privileges. Right, so default privileges help, right?

And still, like still, common mistakes are forgetting some interesting privileges like the connection to database or usage for schema and it's impossible to avoid these mistakes. Impossible. Like last week I remember we spent 15 minutes trying to understand why it's not working. Our monitoring was not working. And then we realized, okay, it's not monitoring, but something was wrong. And we realized that it was not a usual setup.

And we just forgot about usage permission to some schema. That's it. It's always going to happen. So here my advice, don't blame yourself. It's common, yeah, That's it. Michael: And read the error messages? Did you get a good message back? Nikolay: No. Michael: Oh, interesting. Nikolay: Lack of permissions, that's it. You cannot do something. You cannot read from some table, but it won't tell you that it's

because of lack of permissions for schema. So it's tricky sometimes you need to like brainstorm like ideas and then you find it. Yeah it was a view I think yeah. So these are kind of common roadblocks you can experience. Michael: Yeah so you've kind of stuck you've gone down the what roles have permissions to read and write to different tables, for example, of views or schemas. But we also have the challenge of ownership that's also tied

in with users. Is that worth touching on a little bit? Nikolay: Yeah, owner to owner, owned by. You remember the sequences can be owned by table, but it's a very completely different thing. And But every object can have owner in terms of role, right? So there are 2 ownership things. But this is just some specific thing for sequences. It's very specific for sequences. Yeah, so objects can belong to.

And here, when people start complaining, I ask, same thing, I ask, have you learned about altering, like passing all ownership from 1 user to another, like GRANT blah blah blah, OWNED BY or something like this? There is such ability, I don't remember syntax, but you can say I have a lot of stuff, I'm going to quit this company, you know, and I give it to you, yeah. Michael: I think it's something like REASSIGN OWNED BY. Yeah, something like this. Yeah, something like that, yeah. Nikolay: Yeah, so it's like, I like these massive operations.

Obviously folks who developed them suffered enough so they developed it. And this is great to have massive operations. It helps you. Yeah, so I think, actually, I made a mistake in this sequence of things to mention. First thing I would mention is that separating roles, like separating pieces of application, kinds of workload to multiple roles and

groups and so on, and having some approach, it's good, much better than having just single super user and that's it. Yeah. Yeah. Or just single application user and that's it. Because in many aspects, so we should separate because this gives us control and also troubleshooting capabilities because you know, pg_stat_statements has a username or user ID, I don't remember exactly. It tracks workload separately for each user, which is good.

Michael: Yeah, true. So it's useful for troubleshooting, but also I think it helps protect you from yourself sometimes. If you know you only should be reading data, connecting as a read-only user helps you feel safe and that's true at the application level as well, right? If it shouldn't be creating objects, if it should just be doing normal kind of CRUD operations being a normal application user makes sense but if you want if you maybe doing migrations or

creating objects as we were just talking about having an application admin role separately from an application user role makes a lot of sense Nikolay: in terms of security right Michael: well I was yeah so security I think is part of it but I think also just protection from yourself, like for mistakes as well. Right? If an application user shouldn't be created, like, yeah, probably mostly security, but I think there's also an element of not allowing read-only users to drop data, like by mistake.

Nikolay: Yeah, so it's typical divide and conquer approach. So you divide and you have better control and visibility, like transparency of what's happening. And now I'm talking about this and speaking of transparency, observability-wise, I think it makes sense, actually, to extend query analysis we discussed so many times and have in monitoring. I know guys who are developing our monitoring, they are watching this podcast, listening to it.

So I think it's a good idea actually to have separate graphs aggregated by user from pg_stat_statements and also by database. So just like in other kinds of aggregation pg_stat_statements offers, why not using them, right? So like I never saw such graphs implemented. Maybe there are some in some monitoring systems, but I think it's quite useful and you can understand, for example, how much database time is spent every second, my favorite seconds per second per each user, right?

So, and this helps you understand like, oh, if your application and parts/workloads are split between multiple users, you can track and see which user generates more load at any given moment, or maybe more I/O in terms of hits and reads, buffer pool, or generating a lot of temporary files. So it's like all the metrics are here, and this is just dimension you can use to aggregate and see who to blame, which part of

application to blame or workload to blame, right? Michael: Yeah, that's a good point actually. And since you mentioned temporary files, that is 1 use case I've seen from the performance side for roles is you can set, for example, work_mem at the role level. So you could give... Nikolay: This I wanted to touch. Yes. Okay. Yeah. I don't know if you remember, I once confessed that I put some big system down.

Michael: No, I don't remember. Nikolay: Yeah, there was a story when I... It was a big loss in terms of income. It was e-commerce, and I just haven't noticed that the users had user level settings and it was not work_mem. work_mem is a good idea I think but it was logging settings. Oh, I think I'm interested. log_min_duration_statement. So yeah, so something like I don't remember exactly, but I just

thought, oh, it's great. Like, let's change some level of verbosity. And then I didn't recognize that main user which is used for application. It was a single main user at that time. It has specific settings, so colliding to my change, we had observer effect and everything went down. So this increases complexity every time you have it. We also had a case, for example, when we decided to, you know, in PgBouncer, you can configure it in terms of pool sizes and combination of database and user, you can allocate a number

of connections, maximum number of connections, and so on. And if you start splitting your quotas, you, for example, have max connections 500, right? Big server, almost 200 cores, for example. 360 cores, and you have like 600 max connections, which is very, very sane, not far from max connections, cool. You are not allowing a lot of idle connections. And then you think, okay, I'm going to split and have boundaries of quotas for each user. And at that case, we ended up getting rid of it after a year

or 2 of trying to use it. Because it just amplifies complexity everywhere. You need to think about, oh, this is this, but we have users. So this is good, very sharp, fine tuning method. And in case of work_mem, I think you're right. It's better to increase work_mem for specific like reporting roles than globally. But If you go in this direction too far, it's similar to tuning

tables, or to vacuum tuning per table. I don't like it because in a growing organization, when we need to change things all the time, the complexity of configuration, it bites back very badly after like a few months everyone already forgot about this and you have so complex fine-tuned system. I would first consider like global changes wherever it's possible. Maybe work_mem is kind of some exclusion but connections and all

the settings, it's better to have common rules for everyone. Because per role it's like exclusions from global rule. Michael: And harder to look up, right? Like, easy to forget. I think you make such a good point here. Even work_mem, you might be able to get away with it without setting it at the role level. Like, could you set it for each reporting query? If you only run a very few number of reporting queries, you could set it just in the session, for example.

With caveats again around poolers, but yeah. Nikolay: Yeah, actually, it's a balance is 1 thing, But CREATE ROLE itself, CREATE USER in Postgres itself has also connection limit option. And this is the way where you can like... Maybe in some cases it's good. We know these users should be very limited. We know they abuse us. Connection limit 3. That's it. So that's it. But in general, I don't like too many local rules because complexity grows a lot.

I wanted to emphasize once again importance of separation of roles not only for application parts or workload parts, but also for humans. Especially in AI time we live in, some humans can be sometimes bots, like AI, LLMs also. Sometimes they are actually humans and if everyone shares just 1 role, especially if it's a super user, well that's dangerous,

right? And we cannot understand who is, like even, not only about like who is like can put us down but for example during incidents it's harder to trace who is connected. We can do it using process ID and then go and check at OS level, but sometimes if they are root there, then we need to trace, trace, trace. It's like it's a lot. Sometimes it's RDS we don't have access to OS, and so if everyone is connected using 1 role, we cannot understand like who is doing

what. That's like complicates troubleshooting a lot. So separation here again is very, very, very good practice. Michael: Yeah, plus all the security implications, right? Like You never want to think about what if someone that has super user access leaves the company all of a sudden. Like there's all sorts of risks to the more people that have that, let's say it's password, like username, password, just

the more threat, like Nikolay: more. Yeah. And if it's 1 user for all, password rotation becomes like terrible. And I know cases, right? So and yes. And you know, it's interesting that CREATE ROLE, this is probably for guys who want to create their first patch or something and maybe it's a good idea to think is a good proposal for what Postgres lacks. So there is a... you know, CREATE ROLE has expiration.

You can specify when. Michael: I didn't know that. Nikolay: Yeah. Some VALID UNTIL or something like this. So you can specify when I never use it. I just think this is good actually. So you can have temporary access, but I would rather prefer seeing expiration for password only. So rotation, yeah. Rotation will become mandatory and maybe with some warning, which will start popping up in logs and to client messages like 1 month

before, for example. Michael: Yeah, I'm not such a big fan. I haven't really thought it through for databases, but I'm not a fan of password rotation policies for like individual web apps that you log into for example I just don't well as much but yeah Nikolay: yeah that's why do you think so Michael: So I think the root issue is password reuse. So I think the main thing you're protecting yourself from is

a different service getting hacked and them not storing passwords hashed. Like plain text passwords or maybe a weak hashing algorithm and it gets cracked and then people try and use the same password in lots of different services and they happen to get lucky with your service and if you rotate passwords then they have to do that within a set period of time, otherwise you have to have set a new password. So it's has some, I think it has some protection there, but I'd much rather focus on just educating people to use unique passwords

so you don't have that vector. Nikolay: But imagine, okay, imagine there's this, there's an organization, many, many, many people working, like new people come, some people leave the company and there is some password shared among them and it haven't been changed for years. Yeah. Is it a good idea? Or, okay. Okay. 1 user has every user have their own password, but some users work at company many years, they created a lot of automation

and so on. And maybe some of the last service roles, right? Like for automation. And some of them also like already propagated to many pieces of code. I don't know, like passwords should not be stored in code, right, of course. But there are several pieces of automation sharing the same password and it was not changed for years. It doesn't feel safe. And I'm not a security expert. I just know sometimes there is a policy for password rotation and token rotation, expiration.

So there is such policy. But Postgres lacks this. So it should be managed outside of Postgres and enforced outside. Michael: It's also a bit of a pain to do, like, in terms of like, back to 0 downtime, right? Like, we have to, if we want to rotate, Nikolay: yeah. Yes, I think like usually you will end up having new role but then it won't work everywhere because of ownership as well. So exactly like thinking through, okay.

Maybe not only the expiration, but maybe some workflow when, for example, 2 passwords are temporarily allowed for some time, and then you can still use the same role because you have a lot of nodes, application nodes and some other things which constantly working with this database right so you need you need some overlapping for both passwords for example or another thing actually maybe we will agree both here It would be good to say I don't, like,

simple passwords are not allowed. Yes. Yes. Michael: I completely agree. Nikolay: Right? Or there is, maybe I'm missing something because I'm old. Michael: No, the only thing I know of in this area is quite a cool service by a guy, an Australian guy called Troy Hunt. I might have mentioned it in the past. He runs a website called Have I Been Pwned, but he also runs a service. Nikolay: I know this website, actually. Michael: Yeah, it's great. So that website is you can put your email address in, and the

website will tell you, are you in any dumps of email address, password, combos, font. Nikolay: It creates 1 more dump every time and it always say yes. Michael: Basically the answer is always yes. Yeah but you can see which services for example. Yeah. Well and the another service he runs is basically all of those passwords so you so if somebody if like 1 of your users sets a password, you can hash it, send it off to his service, and say, is this 1 in the leaked database?

And all of the simple passwords are. Every single simple password you can ever imagine is in there. Plus any that have been leaked. So you can not only stop people setting simple passwords, but you can also stop them setting ones that have already been leaked. So it's, well, at least publicly known and leaked. So that's a cool service, but obviously it's a web service. Nikolay: That's why people prefer auto-generated passwords lately, like just not rely on the users anymore at all, just always auto-generate,

that's it. Michael: Do you mean like using a password manager or do you mean like? Nikolay: In general, instead of like you build some system and instead of saying like you will generate it, you will provide a password, no, just auto generate it. But again, There is no, for example, feature to ask user to change it. There are some things, usually some systems have, but Database system doesn't. So there is like, you need some layer here above. But Postgres could have some of these things.

It recently got another thing, which we discussed last week, the way to use OAuth, how to pronounce it. Michael: Oh, yeah. I say OAuth, but I actually don't know if I've heard anyone say it. Nikolay: Yeah, yeah, So yeah, that's interesting. But there are more like fundamental things that could be also implemented. Just there's opportunity here. Okay, anyway, we both agree that simple passwords are bad and Postgres could check them when role is created or password changed

using ALTER ROLE. Right? Michael: Yeah. Nikolay: Maybe. I don't know. Michael: And we could implement like everybody on their own could implement their own kind of like interface and function. Nikolay: Function. I think it's possible with event triggers maybe, right? I don't know. Michael: I was actually thinking just a little simple web app, but yeah. Nikolay: Well, on top of Postgres, of course, but inside Postgres, I think if it was a feature, it would be good to have it implemented in a way that could be customized.

You provide a function which just returns true or false. That's it. It will be good. And then you have some policy to be defined. Speaking of passwords and leaking passwords. Of course, we know like plaintext passwords is bad, right? Encrypted, they should be encrypted and stored in hash. And many, many years, MD5 was the choice, right?

And in pg_hba.conf you configure like no trust, no plain text, just MD5, right? But since Postgres 10 we have SCRAM, which is like salted. Well, MD5 actually was also salted with username. You know this, right? So we always could create, when you say CREATE USER, you could specify not the plain text password in the DDL command, but you could concatenate password, actual password you want to have

and username, and then calculate MD5 of it, and then have prefix MD5 and provide all this instead of password. And this will work properly. So this protects you from password leaking to like just leaking password right because for example we always say log statement should be DDL so we see what's happening right but if it's DDL then every CREATE USER or ALTER USER changing password, it leaks

that value. And it Michael: means anybody with access to the logs. Nikolay: Yeah. In big organization, we have a log collecting system and so on. Usually, there are some mechanisms to remove PII and so on, but this is not just PII, it's very, very, very dangerous PII. And yeah, so actually, so plaintext is absolutely no algorithm to store passwords, because also it will go to dumps. Well, regular dumps don't dump users and roles, right?

But pg_dumpall does. pg_dumpall does. I'm not sure if it works on RDS, though, and others, but in self-managed, definitely it works, and it will dump everything, including this. Okay, so this is a good idea to have this hash instead of password to provide it, And even if it goes to logs, it's already like not the raw value, which is much better, right? And SCRAM adds even better protection because still MD5 you can

basically find collisions with regular hardware these days, right? So SCRAM is much better and much more strongly encrypted and so on. But still, I see people use, even if SCRAM is configured, everything, Creating user is a problem because it goes to logs. Especially if it's MD5, people still use plain text to create user and if log statement is DDL, it will go with plain text to logs. And to avoid that, How can we avoid that?

Well, changing log statement is not a good idea because you will miss the fact. I would prefer seeing that this user was created but do not see password. And I actually expected pgaudit would do this, but no. It also... Michael: Interesting. Nikolay: I know pgaudit was developed by Crunchy. Crunchy in terms of security is great by the way, the document, maybe we mentioned, I don't remember this document created... Michael: We did a whole episode on auditing.

Nikolay: So there's a public document developed, I think, during the contract with US military. So it was great. Huge list of checks you should do to improve security. And I checked Crunchy Bridge. I was thinking, OK, how do they do it? Well, they log it twice. And if you create a role using plaintext password, it's logged twice as plaintext. So that's disappointing, actually.

And pgaudit will log it as plain text as well. Why? Because it logs the statement as context. This is a problem. pgaudit says the role was created, but then it also logs context. And context exposes password if it was not hash value but plain text. And I just recalled, we did it a couple of weeks ago, we were curious and it was needed for some work. And I recall many, many, many years ago, I created basic PL/pgSQL

scripts, which will auto-generate password. You just specify length, and it will auto-generate. It will be printed, but not logged. So it's quite easy. You just do anonymous DO block, and that's it. Or you can wrap it inside function, right? In case of context, the function name will be mentioned, right? But not the body of it. Michael: Yeah, I'm missing something.

How come it avoids being logged? Nikolay: So if you, for example, wrap it inside the PL/pgSQL function, its name will be logged, but not the body. That's it. Oh! That's it. So you just create the PL/pgSQL function, CREATE or ALTER USER, and work through it. And even if you will do plain text, like for simplicity, convenience, it won't go to logs. Well, it will.

Because again, like again, combining idea. In my case, the password is auto generated and just printed out. So you can start using it. Michael: What about other, like, let's say it takes, for some reason, you hit some lock or something, and it takes more than 50 milliseconds or whatever you've set log_statement to. If it's in a function, it won't get logged there either, or it might. Would it? No, it wouldn't, because it wouldn't be in the query.

Nice. Nikolay: Yeah, yeah. So function name will be logged. Password is auto-generated inside. It's printed to you, but it's not logged. Great. Michael: Yeah, not logged in anywhere. Great. Nikolay: Yeah, but it's like you this is, it should be like kind of code style or something. And you need to enforce it yourself. But this won't enforce it. This is what I think there's a gap here, we could fill just improving Postgres itself, just to have some protection that good practices

are followed, right? Right now it's just on shoulders of who manages Postgres. Michael: I actually can't think of any good reason to log the actual password in plain text ever. Exactly. Like, yeah, we might be better. That could just be Nikolay: simple change. Yeah, yeah. Just mask it. Yeah. Why? Yeah. Doesn't matter. Is it a hash or just password itself, just mask it. Michael: Star, star, star, star.

It doesn't matter. Nikolay: Yeah, exactly. 1 more thing I think is kind of interesting, I learned recently. I haven't checked myself, but I rely on what my team tells me. We develop components for self-driving vehicles. And 1 of them is to deal with index health, which we discussed many times. So we needed to be able to recreate indexes and we started with dblink, which was not good because password is always there.

When you connect, you need to specify a password. I recall the old trick I learned like many years ago, but then I recently saw it's actually described in documentation of dblink_connect function. You can use PostgreSQL FDW server and on top of it, you can use dblink. In this case, dblink_connect will not need to have a password, because password is defined in the FDW, right? So in user mapping, actually.

In a foreign server you define just like IP address and give you the name, but then you define user mapping, there you need to provide password. So the thing is that if you create a dump, this thing will be dumped, like user mapping will be dumped, right? And password is there, right? So also like kind of like things that should be maybe understood better and maybe adjusted. So, yeah, it's, it's, it's already better. I mean, you like, your code doesn't need to specify password.

It's just 1 definition. That's it. But if you work with dumps too much, this is a place where password can be leaked. And you probably don't expect it also. I think people don't expect password to be leaked in dumps. So yeah, these are interesting things about passwords and user management. Right now we should make efforts to be protected. This is the key. And also like let me complain a little bit. The problem is if we, if like People often call us to check things.

We come, and it's actually not easy to understand kind of like what's happening with, for example, user management and passwords. We can see the list of roles and so on, but to understand the risks and the problems, we need to talk to people right now. So the automated solutions to perform security audit, they are usually quite weak, unfortunately. Because It's not easy. It's not easy to understand that for example passwords is going to be leaked next week because of something.

Yeah Michael: Yeah on kind of password protection front. I actually I don't think we mentioned it last week But I spotted in the release notes that MD5 is actually being deprecated as part of 18. Yeah, yeah. So not removed yet, but you get deprecation warnings now in Postgres 18 if you try Nikolay: and set it. It took 8 years. SCRAM was added in Postgres 10, so it's 8 or 9 years, depending

on how you count. Michael: But progress, good news. Nikolay: Good, yeah, yeah. So everyone should be using SCRAM and think about things we just discussed, and it will be much better. But right now, there are many dangers in terms of security, unfortunately. And I think it's also job for managed platform to improve things, right? And I think they do. I saw many things, and they recognize this problem.

Michael: I think they just take a lot of this out of, they handle that stuff. If you've got a good provider that's hot on security stuff, then you probably at least got the absolute basics. Nikolay: Certificates and TLS. This is also good. Michael: Yeah, good point. Slightly outside the scope of user management, but definitely security 101, right? Nikolay: Adjacent topic, yeah. Michael: Yeah, yeah, another time. Nikolay: Right, so yeah, these are things I wanted to touch a little bit.

What else? That's it. Michael: I think that's good. Yeah. Thanks so much, Nikolay. Catch you next week. Nikolay: Thank you.

Open the transcript file

Hosts and Guests

Hosts

  • Michael Christofides Host Feed-stated
  • Nikolay Samokhvalov Host Feed-stated

Credit the people on this episode

Be the first to rate this episode

More episodes

All episodes
  1. Postgres 19

    Nov 19, 202653 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://youtu.be/VcDU-NRE3hI Michael, Nik, and Kirk discuss Postgres 19: why the release came in November and not in...

    Credit the people on this episode

  2. The hidden costs of CREATE TEMP TABLE

    Nov 13, 202639 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gAdhpYewLZ8 Nik and Kirk discuss the hidden costs of CREATE TEMP TABLE: dead tuples and...

    Credit the people on this episode

  3. Extensions on demand

    Nov 6, 20261 hr 9 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=7oeAAqU00zk Nik and Kirk are joined by Álvaro Hernández to discuss how Postgres in...

    Credit the people on this episode

  4. UUIDv4 vs UUIDv7

    Oct 30, 202648 minAudio

    Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gf8e3JkQ_8M Nik is joined by Kirk Wolak to discuss what a random UUIDv4 primary key really...

    Credit the people on this episode

  5. Dump/restore done. Now what?

    Oct 23, 202651 minAudio

    Nik is joined by Kirk Wolak, Software Architect at KiraSoft, to discuss what a database still needs after pg_restore finishes and before it takes production traffic - what pg_dump and pg_restore leave behind, why...

    Credit the people on this episode