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.

Some kind things our listeners have said