Postgres FM - User management

Episode Date: October 3, 2025

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.htmlALTER DEFAULT PRIVILEGES https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.htmlGRANT https://www.postgresql.org/docs/current/sql-grant.htmlREASSIGN OWNED https://www.postgresql.org/docs/current/sql-reassign-owned.htmlALTER ROLE (including SET) https://www.postgresql.org/docs/current/sql-alterrole.html CREATE ROLE https://www.postgresql.org/docs/current/sql-createrole.htmlHave I Been Pwned https://haveibeenpwned.comPwned Passwords API https://haveibeenpwned.com/API/v3#PwnedPasswordsCrunchy Data PostgreSQL Security Technical Implementation Guide (STIG) https://www.crunchydata.com/blog/announcing-the-crunchy-data-postgresql-stigOur 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 pgMustardNikolay Samokhvalov, founder of Postgres.aiWith credit to:Jessie Draws for the elephant artwork

Hello, and welcome to Postgres FM, a weekly show about all things PostgreSQL. I am Michael, founder of PG-Must, and as usual, I'm joined by Nick, founder of Postgres AI. Hey, Nick, how's it going? Hi, Michael, everything is all right. My favorite topic today. I was actually going to bring up an old joke and say it's the most boring one yet. But your idea now? Yeah, it's my idea.

It's like cold plungers. Sometimes you need to do it to feel then. Once you finish with recording, I will feel much better. 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 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 i just wanted to raise some questions we currently have in posgues and so on yeah me too so user management where do you want to start you like

I think it might even be interesting to think about the concept of roles in Postgres because it's not, I've come from 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. 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 PostGrist, they are kind of the same thing. Third thing, groups as well. Oh, yes, exactly. Yeah. Yeah. There are no groups, basically, in PostGurzegos, although they're, like, directly, there are no separate concept. But let's answer all these. Like, a personal system user is a personal system user, and PostGis user is PostGis user.

Sometimes there is coincidence and they both are named Postgres, 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 just yeah coincidence makes things

like I know like people who created it they thought like let's make things simple yeah but confusion which comes of 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 convenience you have. Yeah, I think it's one of those, I think it's one 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, like there are several questions that all, if you, if you optimized for that and that alone, you're, you're, you're score highly in certain things but sometimes you would make some design decisions if you are optimizing for that user that wouldn't be good for people trying to set it 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

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 yeah good points and anyway I just learned like taught myself to use to distinguish yeah when in my language I just try to mention explicitly OS user, DB users of people who are listening and 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, 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, right? So this is another way you can, but still,

like if you say user probably is the visitor yeah and second confusion and i think of third confusion is similar here right so users are enrolls it's the same in posgues and and groups also like basically the same so it's just one single database object basically right and and you can you can create a role or user michael and then granted to user user or role nick right so i will get all permissions you have and in this case this will make you like virtually uh database group michael right so this like it's very flexible but also be confusing especially for those who came from other database management systems 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, Nick and Michael could both be, both be DBA in the DBA's group, but also in the, in like, have other memberships to other groups and makes it easier to manage permissions that way, right? Yeah, good. So, yeah, these are kind of basic things. And I wanted to discuss, like, like, I never heard anyone saying Posgis 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 postgues, like sysadmins and then later

SRIs, they always complain, always. And when they start complaining, I don't listen. I just say, have you learned about default privileges? Cool, yeah. Yeah, this is the first thing you need to remember. there are default privileges and you say you can you can say you can alter them and say like these default privileges that should be always assigned right 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 the table and who will take care of handling permissions privileges right so 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 use it's impossible to avoid these mistakes impossible like last week as 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 it's not 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's interesting it's always going to happen always

so here my advice don't blame yourself like it's common yeah that's it and read the read the error messages did you get a good message back no oh yeah interesting lack of permissions that's it like you cannot okay yeah you cannot read from some table but it won't say it won't tell you that it's because of lack of permissions for skin so it's 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 right yeah so you've you've kind of

start you've gone down the what roles have permissions to read and write to different tables for example of views or schemers but we also have the challenge of ownership that's also tied in with users and is that worth touching on a little bit yeah owner too owner owned by you remember the sequences can be owned by table but it's a very completely different thing but every object can have owner in terms of role right yeah so there are two ownership things but this is all there's some specific thing for sequences it's very very specific for sequences yeah so objects can belong to and here I like when people start complaining I ask same thing I ask have you learned about alter

like passing all ownership from one user to another like grant blah blah on the buy 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 is it something like reassigned owned by yeah something like that yeah 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. 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, business admins has a use name or use ID. I don't remember exactly. It tracks workload separately for each user, which is good.

Yeah, true. So it's useful for troubleshooting, but also I think it helps protect you from yourself sometimes. If you know you're 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 maybe you're 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. In terms of security, right? Well, I was, yeah, so security I think is part of, it, but I think also just protection from yourself, like, from 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. 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 PIGS statements and also by database. So just like another kinds of aggregation pgis assessments offers why not using them right so like i 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 time that database time is spent every second my favorite seconds per second per each user right so and this helps you understand like oh if you if your application and parts of workloads are split between multiple users you can track and see which user generates more load at any given moment, right? Or maybe more I.O. in terms of hits and reads.

Yeah. Wafferpool 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? Yeah, that's a good point actually. And since you mentioned temporary files, that is one 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. This I wanted to touch.

Yes. Okay. Yeah. Yeah. I don't know if you remember. I once confessed that I put some big system down. No, I don't remember. Yeah, yeah, there was a story when I, like.

it was a big loss in terms of income for it was e-commerce and yeah i just haven't noticed that the users had user level settings and it was not workman workman is a good idea i think but it was uh logging settings uh oh oh i think i'm looking mid-duration statement uh so yeah so some something like i don't i don't remember exactly but uh i just thought oh it's 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 PigeBouncer, you can configure it in terms of pool sizes and combination of database and user you can allocate number of connections maximum number of connections and so on and if you start splitting like your quotas you for example have max connections 500 right big sewer almost 200 course for example 360 course and you have like 600 max connections which is very very saying 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 quarters for for each user and at that case we ended up getting rid of it after a year or two of trying to use it because it just amplifies complexity like everywhere you need to think about like oh this is

this but we have users so this is good very sharp fine-tuning method and in case of workman i think you're right it's better to increase workman for specific like reporting roles then globally. But if you go in this direction too far, it's similar to tuning tables, auto vacuum tuning per table. I don't like it because in growing organization, when we need to change things all the time,

complexity of configuration, it bites back very badly after 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 WorkMem is kind of some exclusion, but connections and all the settings, it's better to have common rules for everyone, you know, because per role, it's like exclusions from global rule, right?

And harder to look up, right? Like, easy to forget. I think you make such a good point here. Even WorkMem, 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're only run a very few number of reporting queries,

you could set it just in the session, for example. With caveats again around pullers, but yeah. Yeah, actually PigeBon is one thing, but create role itself, create user and PostGOS itself, has also connection limit option.

And this is the way where you can like, maybe if in some cases it's good. Like we know these users should be very limited. We know they're abusers. Connection limit three. 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 for like importance of separation of roles not only for application parts or workload parts, but also for humans. Especially in the I time we live in like some humans can be sometimes bots, like AI, LLMs also. Sometimes they are actually humans.

And if everyone shares just one 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 already as we don't have access to OS. And so if everyone is connected using one 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. Yeah. plus all the security implications right like you never 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 um there's all sorts of risks to the more the more people that have that let's say it's password like username password just the more threat like yeah it's and if it's one user for all password

rotation becomes like terrible and and i know cases right so and yes and you know there is 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 uh as a good proposal for what posgis lacks so there is uh you know there is create role has expiration you can specify one role i didn't know that Yeah, some valetantile or something like this. So you can specify when I never used it. I just think this is good actually. So you can have temporary access.

But I would rather prefer seeing exploration 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 one month before, for example. 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 think it adds much, but yeah. Yeah, why do you think so? So I think the root issue is password rebutt. use. So, like, I think the main thing you're protecting yourself from is a different service getting hacked and they're not storing passwords hashed, like maybe plain text passwords or maybe a weak hash an algorithm and it gets cracked. And then people try and use the same password and 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 has some, I think there 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. 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 passwords shared among them. And it, it haven't been changed for years yeah is it a good idea or okay okay one user it 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 there are 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, token rotation, expiration, and so there is such policy. But Posgis Lexis. So it should be managed outside of Postgres and enforced outside. It's also a bit of a pain to do. Like, it's, in terms of like applicating, well, back to zero downtime, right? Like we have to, if we want to rotate it, 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 expiration, but maybe some workflow when, for example, two passwords temporarily allowed for some time and then you're going to like, like, can still use the same role because you have a lot of notes, application notes, and some other things which are constantly working with this database, right? So 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. I completely agree Or there is Maybe I'm missing something 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 Poned But he also runs a service I know this website actually

Yeah 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 from it creates one more dump every time and it always say yes basically answers 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 one of your users sets a password you can send you can hash it send it off to his service and say is this one in the leak they database and all of the simple passwords are like 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 that's why people before auto-generated passwords lately like just just not rely on the users anymore

at all, just always auto-generate, that's it. Do you mean, like, using a password manager, or do you mean, like... 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-generated. 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, right? so there is like you need some layer here above but posgues could have some some of these things it recently got another thing which we discussed last week right the the way to use uh oof how to

pronounce it oh yeah oh i say oath but i actually don't know if i've heard anyone say it yeah yeah so so yeah that's interesting but there are but more like fundamental things that could be also implement just this opportunity here okay anyway we agree we both agree that simple passwords are bad and postguists could check them when role is created or or password changed using alter role right yeah maybe and we could implement like everybody on their own could implement their own kind of like interface and function functional I think it's possible with event triggers maybe right I don't know. Actually, I don't know.

I was actually thinking just a little simple web app, but yeah. 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 it could be customized. You provide a function, which just returns true or false. That's it. It would 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 PGHB, Confugure, like, no trust, no plaintext, just MD5, right? But since PostG10, we have Scram, which is like, salted. Well, MD5 actually was

also sold it 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 comment but you could concatenate password your 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 yes so this protects you from password, 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 means anybody with access to the logs. Yeah. In a big organization, we have 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 like it's very very very dangerous PII right and yeah so actually so plain text is absolutely no like as an algorithm to store passwords because also it will go to dumps well regular dump don't dump users and rows right but PJ dump all does which is dump all does I'm not sure if it works on RDS or others, but in self-manage, definitely it works and it will dump everything, including this. Okay. So this is good idea to have like this like 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, you can basically

you find collisions with irregular hardware these days, right? So Scrum is much better and much more stronger encrypted and so on. But still, like, when I see people use, like, even if Scrum is configured everything. Creating user is a problem because it goes to logs, especially if it's MD5, people still use plain text and 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 which this user was created but do not see password. Yeah. And I actually expected PG audit would do this, but no. It also looks. Oh, interesting. Yeah.

And I know like PGO audit was developed by Crunchy. Crunchy in terms of security is great. By the way, there is a document. Maybe we mentioned, I don't remember. this document created We did a whole episode on auditing so it came up then. So there was a public document developed I think

during their like contract with US military right so it was great huge list of checks you should do to improve security. And I checked a crunchy bridge I was thinking okay like how do they do it? Well they're logged twice

and if you create a role using plain text password it's log twice as plain text. So that's disappointing actually, and PG audit will log it as plain text as well. Why? Because it logs the statement as like

context. This is a problem. PG audit 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 like a couple of weeks ago. We were curious.

and it was needed for some work. And I recalled many, many, many years ago, I created basic, like, PLPG-Skilled scripts, which will auto-generate password. You just specify length and it will auto-generate. It will be printed but not locked. Yeah, so it's quite easy. Like, 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. Yeah, I'm missing something. How come it avoids being logged? So if you, for example, wrap it inside PLPrecrow scale function, its name will be logged, but not the body.

Oh. That's it. So you just create PLPG scale 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 print it out so you can start using it what about other like let's say it takes for some reason you hit some lock or something

it takes more than 50 milliseconds or whatever you've set statement time not like log statement to if it's in a function it won't get logged there either it might would it no it wouldn't because it wouldn't be in the query nice yeah yeah so function name will will be locked password is already inside

it's printed to you but it's not great yeah not logged in anywhere great yeah but it's it's like you this is um it should be like kind of code style or something and you need to enforce it yourself. Database won't enforce it. This is what I think.

There's a gap here we could feel just improving PostGus itself, just to have some protection that good practices are followed. Right? Right now, it's just on shoulders of who manages PostGGGGGUS. I actually can't think of any good reason to log the actual password in plain text ever. Exactly. Like what, yeah, we might be. but that could just be

simple change, yeah, yeah, yeah, yeah. Just mask it, yeah. Why? It doesn't matter, is it a hash or just password itself, just mask it? Star, star, star, star. No, it doesn't matter. Yeah.

Exactly. One more thing, I think, is like, kind of interesting. I learned recently. I haven't checked myself, but I rely on what my team tells me. You know, we develop components for self-driving.

Vosgues right yeah and one of them is to deal with index health which we discussed many times yeah so we needed to be able to recreate indexes and we started with DB link which was not good because password is always there when you connect you need to specify password I recall the old trick I learned like many years ago but then I recently saw it's actually described in documentation of DB link connect function you can use you can use PostgreavesfdW server, and on top of it, you can use DBLink.

In this case, DBLink Connect will not need to have password because password is defined in the DW, 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 which should be maybe understood better and maybe adjusted so huh yeah it's it's already better i mean you like your code doesn't need to specify password it's just one 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, like, people often call us to check things, we come. And it's actually not easy to understand, like, 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 of 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. 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. So not removed yet, but you get deprecation warnings now if you're in Postgres 18, if you try and... It took eight years. Scamb was added in Postgres 10, so it's 10 or eight or nine years, if we're depending on how you count so yeah progress good good news good yeah yeah so everyone should

be using scram and think about things we just discussed and it will be much better but but right now 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 they do i saw many things and they recognize this problem. Yeah, I think they just take a lot of this out of, like they handle that stuff. If you've got a good provider that's hot

on security stuff, then you're probably, at least got the absolute basics. Certificates and TLS. Yeah, good point. Yeah, also important. Slightly outside the scope of user management, but definitely security 101, right?

Adjacent topic, yeah. Yeah, yeah. Another time. Right. So yeah, these are things I wanted to touch a little bit. What else? That's it? I think that's good. Yeah. Thanks so much, Nikola. Catch you next week. Thank you. Bye-bye.