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