Michael: Hello and welcome to PostgresFM, a weekly show about
all things PostgreSQL.
I am Michael, founder of pgMustard.
This is Nikolay, founder of Postgres.AI.
Hey Nikolay, how are you doing?
Nikolay: Hi Michael, I'm doing great.
How are you?
Michael: I'm good also.
So this week, well I was in charge of choosing, but I've actually
picked something that you suggested.
I was looking through all the listener suggestions, all of the
ideas we've had in the past.
And this was one of yours, right?
Nikolay: I have no idea.
I already forgot.
It was my idea?
Okay.
Michael: So you suggested a while back that we talk about super
user and especially super user in the new normal, the new context
of cloud providers or managed services and whether we, well,
the fact that we normally don't have superuser access anymore
in those cloud environments, whether we should, that kind of
thing.
So a little bit of a refresher on what a superuser is, what it
can do and, and maybe why we don't have it or why we should have
it.
Nikolay: Right.
Right.
Yeah.
Good questions.
Good.
What is a superuser, right?
First of all, it's just bypassing all privilege, privilege checks,
right?
This is the idea.
Michael: Yeah, I looked it up in the documentation.
Superuser bypasses all permission checks except the right to
log in.
It's a dangerous privilege, should not be used carelessly, it's
best to do most of your work as a role that is not a superuser
and then talks about how to create one.
Nikolay: So you can create a superuser with no login flag, right?
Or...
Michael: I guess so.
Yeah.
Nikolay: Interesting.
Actually, I have a white spot in my knowledge here, maybe.
Honestly.
But yeah, superuser is what people sometimes use, not thinking
about permissions at all.
Even launching their services and web applications using super
user database.
It's very, very bad practice.
Did you do that ever?
Michael: Yeah, it's the default, right?
It's the default, well, you don't know better.
Nikolay: Actually, yes, and it means defaults here again are
not perfect because they don't encourage you to create a non-superuser
database or user that you will be using.
Also, I can remember some companies, small and big ones, doesn't
matter, which give a single superuser or not.
So it's different kinds of scenes, different scenes.
One scene is, let's use one single superuser for all people who have
DBA access, SRE access, like admin access, and share it.
Or let's give everyone a super user, but named one.
Maybe the first thing, like, it's different, like, so different
scenes, like, use super user by default when you work with
database, checking something.
And a different scene is like, let's share an account, let's share
database role.
Both are not good things, right?
But at least if you separate roles and create multiple superusers,
it's already slightly better.
Not slightly, actually.
It's significantly better because you can see who is doing what,
at least, right?
Distinguish people.
But in general, yeah, Postgres, how it's organized, it provokes
you to use superuser for everything by default, and you need
to make efforts to go out of it.
Most people at least realize this and at least stop using superuser
for application work.
This is number one thing to do.
Okay, you are going to use Superuser for yourself because you
have all the rights, you are maybe the owner of everything, right?
You own this database, so okay, you have Superuser.
It's a separate question, should you always log in as Superuser,
right?
Maybe you should log in as a normal user, a regular one with limited
permissions and only if needed, use superuser access.
But at least all application code must not use superuser.
We use superuser, right?
This is obvious.
Michael: Well, and why?
Like, it's the danger, right?
Nikolay: Well, security.
My favorite topic, security, right?
I'm joking.
Michael: Well, security is a really good reason, but I think
also, like, the danger of being able to, like, drop things, being
able to destroy data, it's not just a security issue, right?
Somebody could steal everything, but they could also just destroy
everything and not steal anything.
Nikolay: Right.
Well, it's also a kind of type of security.
Well, it might be reliability or something, but it's still insecure
to give everyone...
It's not about somebody outside of the company stealing data, but
even inside the company somebody made a mistake.
It's also about, it means that the work is not secure, right?
But I wish auditors dug into this hole deeper.
Like many companies already reported, I mean Postgres companies
reported, they have SOC2, and some companies go to IPO, and they
have a lot of auditing activities from external auditors, right?
And I know companies who are very well-known, they have a bunch
of questions and some of these questions sometimes are related to
Postgres, but when you look at them, being Postgres experts, they
look funny, usually.
So yeah, I think it would be good to create some standard or
something.
Actually, there is some standard, right?
Crunchy Data they shared a big PDF a few years ago, which is
aimed to make Postgres setup more secure and it was for, I think,
for army or something like that development.
Michael: Yeah, the US military I think collaborated with them on
it.
Nikolay: Right, this is a good thing. And even better that they
shared it and this became public so you can use it to grab some
things, and obviously a lot of things are related to permissions
and what kind of database user you use, right?
But if your company right now already got SOC 2 or is doing this
or IPO or something, I would not rely on external auditors to
say, okay, we are good here.
They suck there.
Their questions don't cover this topic almost.
Or at least, what I saw so far over the last 5 years.
I didn't see good questions, honestly.
Like, some of them were kind of good, but this kind of topic,
like, do you use super user?
Do you distinguish users, I mean, humans?
And so on and so forth.
Like, what is your model for these privileges and so on?
Quite weak.
I'm not an expert, as usual, I say.
I'm not an expert in security at all.
This is not, like, this is maybe one of the least favorite topics
in databases.
But it's a super important topic, right?
So, yeah.
Okay, so when should we use superuser?
Michael: That was where I was thinking we were gonna go.
So You mentioned a while back, I think maybe on Twitter, maybe
just to me, I can't remember, that there's a bunch of times when
you're using it, when you're maybe with a client that's using
a managed service provider, that it's frustrating to you that
you don't have superuser access.
So I was interested, like, when are those times, like, What are
you trying to do that you can't do without it or that's difficult
to do without it?
Nikolay: Yeah, there are things that only a superuser can do,
obviously.
For example, COPY FROM PROGRAM is a dangerous thing to do because
you can basically execute any shellcode under the Postgres OS user,
Linux user, right?
And some things like I don't remember exactly But there are certain
types of things where you need superuser, definitely.
Michael: So I found a list.
I was looking at all the different cloud providers and whether
they do or don't provide it.
And there's a really good list in the Supabase docs of what
is unsupported in their highest privileged role.
So they don't, let me just read it quickly.
Supabase provides a default post control to all instances deployed.
Superuser access is not given as it allows destructive operations
to be performed on the database.
And so those unsupported operations are.
Nikolay: Destructive, Okay.
Michael: Yeah, it was an interesting choice of words.
CREATE ROLE WITH REPLICATION, CREATE SUBSCRIPTION, CREATE EVENT
TRIGGER, COPY FROM PROGRAM, as you mentioned, and of course,
ALTER USER WITH SUPERUSER, so you can't make other users superusers.
Nikolay: Well, these are destructive actions.
If they are destructive, let's remove them from PostgreSQL.
This is judgment, like, let me judge, right?
I'm the owner of this database, or who is the owner of the database?
Let me drop my position, it's very simple.
Two things.
First thing, for managed providers, right?
Managed PostgreSQL providers.
If you don't run in a container, question why not?
Right?
Run in a container.
Or actually, or in a separate VM.
You probably run it in a separate VM or in a container at least,
right?
So, it's...
Michael: So, you mean like is it isolated?
So, if somebody breaks out and, or yeah, if somebody else is
being destructive, it doesn't affect you, if, you know, a different
customer.
That, yeah.
Nikolay: A Firecracker microVM or something, a lot of things,
but at least container.
So it's already isolated.
If it's isolated, second point, give me superuser because I'm
the owner of this database, that's it.
And they usually say, most of them say, it's for your safety,
right?
But it's bullshit and a lie.
It's a lie.
Because I know for sure that inside a big provider, very big provider,
teams, this topic pops up from time to time.
And technical people usually say, let's do it.
There are no big reasons to say, like, we protect these users.
Like, when AWS gives you an EC2 instance, it's a virtual machine
with Linux, They give you root, right?
Michael: Oh, I don't know.
Nikolay: They give you root, of course.
Well, I have root.
Then people say, okay, but for Postgres, we have a lot of automation
and you can break it.
Well, if I break it, I break it.
So if I execute copy from program as I did with Crunchy Bridge
and I move pg_wal directory, well this is destructive action.
I copy from program to table and I just MV PgWal2 PgWal2 and
I've got a panic, I mean database got panic.
I've got a big joy observing that I can destroy myself, I mean
my database because this is how I think, okay, I own it at least
like, okay Not directly, but I own it You know, there is a philosophical
There is a philosophical very good statement You can truly own
only what you can destroy.
Ooh.
Right?
Michael: I've not heard it, but it makes sense.
Nikolay: It makes sense in anything, right?
For example, if you cannot destroy your own company, if you're
a startup guy, founder, you do not own it at all.
Maybe investors own it, right?
And you should realize it.
Who can destroy it?
This is like ownership without the ability to destroy it.
So here we come to destructive actions, but let me judge it.
Let me judge it and let me feel it, right?
So, I don't accept any reasons like that, we protect you, you
can destroy it, our support will be fed up with questions, I
destroyed something.
Well, if you destroy something, you recover from backups, that's
it.
But they also say, okay, if we have a lot of automation, and
if we allow you to copy from programs, superuser, for example,
then you will be able as a user to see our automation pieces,
right?
Reverse engineer it.
This is already a real reason.
This is the real reason.
They don't tell you as a first reason, but this is the number
1 reason, honestly.
They don't want you to see the automation from inside.
Crunchy Bridge is great here.
They don't care.
I'm not sure if their product is open source.
There are doubts on it, because they stopped publishing images,
as I know, and so on.
But here, they give you superuser.
You can go copy from program and explore, you know, like directory
layout and so on everything, find which programs are there, probably
try to execute them.
Like it's your world, you really own it.
They give you this, they charge you extra.
You charge me extra and you protect me from myself?
I don't know.
There will be time when people start realizing it, and I hope
auditors will also realize it.
Who owns this database?
Michael: Yeah.
Of all the ones I checked, Crunchybridge were the only ones that
supplied full superuser.
A lot of the other ones create kind of a pseudo role just below
that with like super base.
I think couldn't find a list of things that were removed from
others.
But Crunchy Bridge does definitely deserve credit for that.
And especially if you consider they were the ones that are publishing
the security guide.
I feel like that's a really good argument for it's possible it's
just maybe people don't want to do it I would say they've got
another possible advantage or at least it may be like some other
reasons I I am not as convinced as you as the support reasons
not a good 1 because even to investigate was it the user that
messed up or was it us that messed up is actually quite difficult
sometimes in support.
I don't know if you've ever had that.
Nikolay: Do you use RDS or Cloud SQL or something?
Do you know how calls with support work?
Do you see how the calls are usually organized?
Michael: I haven't ever used their support.
Nikolay: I mean, any of managed services support.
If you just try something, they won't walk you step by step what
Happened.
They will offer you to recover from backup, blah, blah, blah,
like that.
Of course, there are logs, right?
Michael: So...
And possibly ironically, I have actually seen people have great
experience with Crunchy Data support.
This is not sponsored by Crunchy Data, I promise, but there are
providers out there giving really good support.
And I could imagine an argument for, especially if you're a provider
that provides a free tier, for example.
The ones that charge more than it would cost you to host your
own, I can see the argument for maybe they should provide super
user access.
If it's a free service...
Oh, go on.
Nikolay: So yesterday I had 1 million rows in the table, but
today it's only like minus 10 rows.
Where are those 10 rows, right?
Should I go to support?
Because maybe it's a bug, maybe it's a bug of their automation,
maybe it's a bug of Postgres itself, rows disappeared.
Michael: If you've ever run a product, you're going to get some
customers that come with support questions that it turns out
it's nothing to do with your product.
It happens to all of us, right?
Nikolay: Or maybe I know I executed a delete.
Or maybe I know my application could have executed a delete.
This is some kind of problem.
It's the same question.
The same type of question.
That's why I say then they say we're protective.
It's bullshit.
Michael: On that note, maybe to move on slightly, I did actually
notice in the Crunchy docs that for the superuser role, they
have pgAudit on by default to log what it's doing.
Nikolay: Yeah. This is what I suspected.
Michael: That's interesting, right?
Nikolay: Yeah.
Well, yes.
If you enable it for all users, so your logs will be flooded
with a lot of data, and like it will become a performance
bottleneck very quickly.
So it makes sense to enable it for superusers and capture
everything that's happening.
Michael: And of course, I suspect you could change that if you've
got superuser access.
Nikolay: You can change that.
Michael: But I think the idea is to help people help themselves
and also help you support them if it may be alarming if it looks
like they are using it as their application user or
Nikolay: something like that.
People are not stupid in general.
There are stupid people, but there is only a minority of them.
And if you say we protect you, it means like you don't trust
your own customers.
It's bullshit.
That's why I say it's bullshit.
So this is a good thing to have good defaults.
For example, OK, superusers and PGAudit is enabled.
If someone disables it, this record that got disabled goes to
logs, so we have footprints, right, of this action.
But in general, people can...
At least somehow customers I deal with, I see opposite.
I sometimes think, oh, I need to explain to you this, but they're
quite smart.
They're like, okay, we already got this.
So they can understand what's happening.
My point is that they don't give this to you to protect them,
not you.
They want to be protected.
They want to share what they got.
And this is like a business decision, it's not a technical decision.
So they don't want to share automation and how exactly they adjusted
Postgres and so on and so forth.
But I hope people will start realizing this and cases like Country
Bridge will be more common and people who are truly open-source
believers and lovers, they will shift to a more open approach and
a more trustful approach, like trust your customers, they can decide
if it's destructive or not, right?
And just keep everything open, share your automation, and give
ownership and access to your customers.
In this case, the premium is usually quite significant over the infrastructure
costs.
It would be reasonable to pay, right?
Now there is an imbalance in this world, as I see.
And I hope with time we also realize it and start asking
questions like who is the actual owner of this database?
Michael: Yeah, interesting, good point.
But what do you think about free, like, I completely take your
point on providers that are charging a premium over what it would
cost you to run the service.
But what about the ones that are offering you a free tier, like
Neon or Supabase, or even, there are some newer ones as well
Nikolay: Well, how is it different
Michael: Well, I actually don't know; maybe it's a premium
feature, maybe like, if it ends up in more support
personally, even if you don't agree that it
should end in more support, I think it would overall
Nikolay: I don't think it will be a big part of the whole picture.
won't be a big part I mean this kind of questions like I destroyed
my database and I don't of course if it got destroyed and you
didn't do anything, this is a good question.
But if you have a pgAudit setup, you can close and support
easily can point to small how-to, how to understand what's happening,
and that's it.
And support usually doesn't look inside your database, right?
This is your area.
Like RDS support, for example, you need to have a high level
of support and convincing.
They usually check only the VM and the underlying things and
the infrastructure things, right?
What's happening inside your database?
Who deleted rows or who moved, for example, a pg_wal?
I still say this is the same level of things.
It's your area.
Michael: Yeah, true.
I mean, there's, I think even...
Nikolay: I know many people won't agree with me, actually.
Michael: I actually don't know if they would anymore.
I think there's an increasing education around data processing,
especially with all that the privacy laws in the EU and in California,
around whose data is it and who's processing the data, who owns
the data.
Nikolay: Right.
Yeah, in true spirit of some of those laws, like if this is my
data, I should own it and be able to destroy everything, not
only at logical level, like delete.
Maybe I want to destroy pg_wal right now myself, you know, I
don't know, like this is true ownership.
And see how it works inside, like I need to feel it, this is
true ownership.
In my opinion, inspect it.
Michael: I can see the argument a little bit for things like
replication or you know some of the let's say some of the cloud
providers they said they have for example really easy single
checkbox high availability or something and then you go and
destroy replication or you mess something up that means that's
not working anymore.
Nikolay: Usually such providers don't use Postgres replication.
This checkbox usually is not based on Postgres replication.
It's usually underlying block storage device replication, synchronous
usually.
So, this is what I know about RDS and Cloud SQL.
So, usually this is lower level.
Michael: Cool.
Makes sense.
Maybe you can't even mess that up.
Great.
Nikolay: So anyway, I know we live in the world when people say
like...
Honestly, I also think most of developers look at what RDS did
or CloudSQL did or Microsoft guys did, and they just copy this
approach, not thinking deeper.
Crunchy is, as you mentioned, they are security experts, obviously,
and they are brave.
So kudos, actually.
I already told this a couple of times on Twitter.
This is great.
But others just copy what others, like, say, smaller or new providers,
they just copy decisions from bigger providers and copy their
arguments like this is to protect users, this is to protect customers,
this is for your own safety, let me decide what is destructive.
Michael: Yeah, well and I know quite a few managed service providers
listen and people that work at them it would be great to hear
from you if there's something we've missed or if there's a If
there's a way of explaining this that would be would be better
let us know.
Nikolay: Yeah, actually I can criticize this easily because I
don't develop a managed service, managed Postgres service, right?
Yeah.
Because in this case I would need to be more careful because
I would have my situation.
But honestly, several times I saw this, like how to implement,
how to protect, what we should protect, if you provide supervision,
what kind of dangers exist.
And the list is obviously, this is copied from programs, foreign
data wrappers probably, and so on, like, dangerous parts.
So if you think about it, you can implement a good model, and
I think, of course, maybe Postgres could provide some additional
tools to restrict certain areas, but if it's my database, I should
decide what to enable, what to disable, and at which point for
whom, right?
Michael: Yeah I don't know if Postgres can do anything about
this because ultimately these are features that we need, like
someone needs to be able to do them.
Well, maybe if no one needs to be able to do them, it shouldn't
be in Postgres at all.
But if someone wants to be able to do them, it seems silly to
me not like to disable it because we want to be able to host
it in clouds?
Nikolay: Yeah, well, I want to disable it sometimes.
For example, in Database Lab, we have a job bot, right?
And we don't want to job bot to like we want as much freedom
as possible for end users to execute any SQL.
But if any SQL is copied from a program, it can be dangerous
because all foreign data are hyper because maybe users who are
end users, they are still inside the same team, but maybe admins
doesn't want them to execute it.
And we just want to protect here.
Some users have full access, admin decides, right?
But some users who work only at this level, this SQL experimentation
level, they are restricted.
And at some point we removed all possibilities of copy of program
from the developers and so on and so on.
So they cannot do harm even if they are inside the same team.
But the admin decides which permissions to provide, right?
So in the end of the day, you own this database and you decide
what to do.
When I say you, I mean admins, right?
Because inside, bigger customer there might be some additional
users, right?
And the good tool can provide the ability to control those permissions.
Michael: Yeah, and that's what we're saying, right?
Like, we're not saying RDS should provide superuser to every
single person who has access to the RDS dashboard, but to like
an admin.
That's what we're asking for, right?
1 person or a small group of users.
Nikolay: Right, right.
Well, of course there is a chain reaction.
If, for example, RDS, for example, Okay, we provide superusers,
but then what to do with access to WAL files, backups, physical
backups, physical replication connection, and so on and so on.
There is a chain reaction here, right?
And they restrict you here, partially because it's kind of vendor
locking, obviously.
So they also restrict a number of things, like for example, recovery
target LSN, so you cannot perform 0 downtime upgrades with our
recipe, which involves recovery target LSN.
You can only do with slot advancement, we call it Instacart approach,
right, this article, Instacart approach, which the opinions is
more risky.
Right, so recovery target LSN, you need to do it, like you need
to provide it.
So, I mean, if you provide superuser, this is a Pandora's box
in terms of decisions what to provide to your users.
Michael: Well, it's a Pandora's box, but it's making all of those
decisions at once and saying we let you do anything.
Nikolay: Yeah, that's cool.
You are the owner, you decide what to do.
We provide you automation, we charge for it.
That's it.
Now we live in a different world, we provide you some automation,
we hide a lot of capabilities Postgres has from you, we restrict
you and charge for it.
It's not fair.
But this is the most popular approach right now.
We had discussions about managed services, right?
Michael: Yeah, second episode actually.
Maybe it's a little bit of a loop.
Nikolay: Right, right.
I feel the loop indeed, but a different angle completely.
We had the previous episode also echoing the first one.
Interesting.
So we know the next topic should echo the third one.
Let's do it.
Okay.
Anyway, okay.
That's it.
Michael: Podcast wraparound.
Nikolay: Yeah.
Right.
So maybe this discussion was not too technical, right?
But you can go read the documentation if you want technical.
Michael: Yeah, and there were good talks on roles and security
and just covering the basics.
There's loads of documentation on this kind of thing.
We can include some links.
Nikolay: Right, yeah.
We had some philosophical discussion.
Do you own your database?
RDS users, do you own your databases?
Who owns it?
Maybe people don't care about it until some auditors decide that
it's not truly ownership.
But probably, how AWS is organized, probably they will never
raise this question.
Everyone is happy.
So I don't know.
Michael: And at least there's choice out there now.
There's at least one provider that does, and you can self-host.
You can even use the cloud and manage it yourself.
Nikolay: I also say I want to own my bloat, right?
Because AWS doesn't allow you to take your physical level.
Michael: Logical replication.
Nikolay: Yeah, logical, you lose the bloat your data files have.
And you cannot stop thinking about bloat because it's Postgres.
Bloat is the center of architecture.
So you need to understand how this works if you want good performance.
So, yeah, interesting.
So you work at an abstraction only, some abstraction level.
And you cannot copy files.
Okay, maybe enough.
Michael: Thanks so much, Nikolay.
Thanks, everyone.
And see you next week.
Nikolay: Thank you.