The PostgreSQL community debates ALTER SYSTEM
lwn.net
lwn.net
Anyone who has had to work in security knows about the endless deluge of low effort script kiddie “security reports” of non issues they have to deal with by security “researchers” fishing for a bounty. And you have to read all of them, because there’s no telling when one might be legitimate. Or you bring in some third party auditor for compliance or some other reason and they are just as clueless and snap up something like this to add to their list of “recommendations” that you have to respond to so they can feel like they are more justified collecting the paycheck.
So I think implementing the feature - but being extremely loud about what it is and isn’t - is the right move here.
Otherwise, the often wiser thing to do is to make the danger of doing something as obvious and blatant as possible. It's like the debate around having a root account or not: IMHO it's better to have a superuser that everybody perceives as an obvious danger, that's easily recognizable and never really ok to use for mundane tasks. I've seen people misunderstanding Windows ACLs way too often because of this.
It's a shame there are ways to bypass it, but at least it communicates the intent: you aren't supposed to modify this system without modifying the actual config
I think if the the config is unchangeable, all ways to change the config (e.g. don't reload config on demon restart etc.) should be considered. Or the config piped into PG on startup, not reading any files. But this takes time to do right.
This had to be in on short notice (trys to move this to another release where squashed) b/c vested interests.
I think the top comment on the LWN article gets it right. Don't think of it as a SECURITY feature, think of it as a SAFETY feature. Imagine some subset of buttons on a physical control panel than can be locked behind a flimsy plastic door. It's not going to keep anyone determined out, but it will keep people from using them on accident.
All of the people driving this work for companies that sell postgres hosting. This is why they are driving this, to make their own offering cheaper to operate, customers can't use "ALTER SYSTEM" and destroy the managed DB creating problems. Their intent is not for Kubernetes users, this might be a side effect, but for their own companies. If your developers use ALTER SYSTEM and then deploy to production without changing prod conf, I'd say talk to the developers.
It's irrelevant on how it is meant to be, it's 100% relevant on how it is perceived.
The feature is driven by "We're power users and we know what to do and what this is" whereas the resistance is "Yes, but there are a million users, they are not power users, they will shoot themselves in the foot, and then PG will gain a bad rep for security".
Assume one guy in my team does ALTER SYSTEM on a cloud managed DB. Next month he leaves, later someone redeploys the DB, nothing works and nobody knows why. Declarative config and IaC has become standard operations to solve real world problems.
Besides that, if I wanted to operate the system internals of a DB myself I wouldn’t be using a cloud-hosted version in the first place. I pay them precisely to manage all these details so I don’t have to think about it. If I don’t want that there are a lot cheaper alternatives.
Even if that's true (and I'm not sure it is, there seemed to be plenty of people in support of the feature), that doesn't mean it only benefits them. Plenty of features are championed by one group but benefit many others, so perhaps evaluating whether it's good based on who asked for it isn't a useful strategy.
> The feature is driven by "We're power users and we know what to do and what this is"
That's not how I perceived it. There are, as was noted in the article, multiple ways to do this, and even ways to enforce it currently. To me this seems very clearly a case of users, and people responsible for providing it to users, wanting a feature to make the quality of life of those users better (anyone that does an ALTER SYSTEM in the environments they are referring to is going off-track and going to cause themselves a problem) and attempting to work with upstream to provide a solution rather than using kludges (such as making the files immutable, but possibly not getting a useful error from postgresql when the command fails) or implementing local patches, so it's not necessarily exactly the same as the mainline postgresql.
> the resistance is "Yes, but there are a million users, they are not power users, they will shoot themselves in the foot, and then PG will gain a bad rep for security".
No, the resistance did not seem to be that the users will shoot themselves in the foot, from what I read. It was that they were afraid security researches would see this as a security mechanism, but since it's not necessarily hard to bypass they would get called out for poor security, affecting the reputation of the project. I'm not sure how you expect millions of users to shoot themselves in the foot by having to enable a flag that prevents them from doing this and then attempting to do it. It's not default behavior, it's specifically opt-in behavior. And if it's clearly not a security feature, but a safety feature to help people from doing things they shouldn't in environment that it will cause problems in, then hopefully security researchers won't waste their time with erroneous CVEs.
Isn't this what we want - that people have their own various motivations for contributing to an open source project? A healthy process is one that allows for contributions, variously motivated, to be discussed and accepted or not.
That's a real use case. The right way to address it is an easy to place speed bump. It isn't some complex solution that achieves a random security ideal which has little to do with the use case. And would make configuration harder.
That's exactly why this is being described as a safety feature, not a security feature.
A developer debugging problems on their own computer will naturally prefer to try to fix things themselves rather than depend on an external admin whose attention is hard to get. And you have every tool necessary to try to do so.
I'll merely say that if it is possible, then I'd expect this detail to have been in the article. Because the existence of an acceptable solution for the use case would have been an important part of the discussion.
As evidence for my point of view, the article early on points out that Tom Lane thought that there was a way to do it. And even described how to do it. But it turned out that he was wrong.
Now I'd be shocked if either of us knew PostgreSQL nearly as well as https://en.wikipedia.org/wiki/Tom_Lane_(computer_scientist). The fact that he thought there was a way to do it makes it reasonable that you'd think the same. The fact that he couldn't come up with a way to do it without changing PostgreSQL suggests that, like he was initially, you're wrong.
But as I say, my evidence is circumstantial. And I'd be open to learning what it is in case I ever again wind up setting up PostgreSQL on an old version for a containerized system. Though in a new system I'd prefer to use the mechanism created specifically for this purpose.
So the proper solution to this whole thing would be for the OS to provide such a facility: "permission X is denied to Y because Z". This seems like a useful facility in general, come to think of it. But it would have taken more time and effort, and would require buy-in from more parties, some of whom might be very hostile to this notion (e.g. I don't think it would be an easy thing on Linux). No wonder that this isn't an option that is even contemplated as realistic.
And so instead we got yet another easy-to-make crutch in the tower of crutches and duck tape that is modern software.
I guess a technical understanding of Postgresql leads to different thoughts than your average working dev/devops/ops person.
Which, TBF characterizes a lot of tech discussions.
That's precisely my point. They aren't "fixing" anything. They're creating blind workarounds that simply let them continue developing.
> And you have every tool necessary to try to do so.
So containers are an entirely inappropriate mechanism to containerize things? Thus we have to patch software that runs in the container to be container aware to avoid problems /created/ by developers "fixing" things?
Very natural indeed. What benefit that brings just happens to be entirely beyond me.
This is why shit infra that barely makes sense and barely works exists
Happens all the time in less disciplined workplaces.
No, it tries to fix the problem of Postgres managed hosting companies, where users can use ALTER SYSTEM but can't change *.conf - all of the people driving this, work for companies that offer managed hosting. The "Developer tries..." is just a fake argument, b/c "We, the Postgres hosting companies want this" would probably not work. So it's the "For the developer ..."
In 25 years I never used ALTER SYSTEM in Postgres for my local development, I always made changes to *.conf - if for the idiotic reason that whatever you Google, you get an answer to change some entry in *.conf not an answer to ALTER SYSTEM
From the article:
> Even then, the discussion was not quite done; Momjian questioned merging this change so late in the PostgreSQL development cycle. ""My point is that we are designing the user API in the last weeks of the commitfest, which usually ends badly for us"". Fennema-Nio pointed out that the API was essentially unchanged from its initial, September form, and that months had been spent discussing alternatives. Haas said that such a small patch would not improve by being held up for another release cycle: ""I think it has to be right to get this done while we're all thinking about it and the issue is fresh in everybody's mind.""
What? The goal isn't to make the configuration unchangeable, it's to ensure that all configuration changes go through the configuration management system. You need to have a reload option that reloads the configuration from disk.
This is not something that's just useful for cloud companies btw, it's useful in any scenario where you're doing configuration/change management.
The PG community is a lot of things, but I think “dominated by commercial interests” is not one of them.
Disclosure: I work at EDB :)
Citrus is part of Microsoft/Azure and EnterpriseDB offers hosting orchestrated on VMs and another option on Kubernetes.
I think most of the people in the discussion were some variety of those companies.
I also think that is a feature that is rarely used, that probably shouldn't be on by default.
What is the risk of misconfiguration? Someone might accidentally disable alter system? Seems like that isn't exactly a risk
Something different than (below)?
https://www.postgresql.org/docs/current/app-createdb.html
https://www.postgresql.org/docs/current/app-createuser.html
(others https://www.postgresql.org/docs/current/reference-client.htm...)
I never use these myself, but it seems there are ways to get there.
You can use the same tool, however it needs to connect to 1 DB, do the DB creation and role setup, then reconnect to a new DB.
If you have a single app or DB in a server, it can make sense to usually schema management but it's more complicated if multiple apps have their own DBs sharing the same server.
The last few places I worked had scripts that ran before schema management as a superuser then used the application framework schema management (migrations) thereafter.
The variance in the script prevents using the typical migration frameworks built into the app, and it’s the wrong responsibility - the app migrations have a contract with whatever database is being connected to about which users and schemas exist.
I’m still not sure a declarative mechanism is desirable though because the database “setup” is still subject to change after initial provisioning: new schemas/roles/etc will be required and we need an imperative API to execute those changes transactionally with precision.
This comes from a postgres developer?
Postgres is the only software I know that has a hardcode that forbids running as root:
"root" execution of the PostgreSQL server is not permitted.
The server must be started under an unprivileged user ID to prevent possible system security compromise.
You: "But I want to run postgres isolated in a single user VM or container or unshare where only root exists, which has much stronger security guarantees that UNIX user separation!"
Postgres: "Forget about it, those things don't exist, this is the 70s."
I hope somebody patches that out.
Creating files in a VSCode dev container creates them as root and I really don't like that.
My understanding of Docker has always been that you're not supposed to rely on it for security—it's less a kevlar vest and more a build-your-own-platemail tinker toy set. If you know what you're doing you can create a quite secure environment with it, but if you don't you're not that much better off than running stuff directly on an EC2 instance.
Some practices that help secure your image are running a stripped-down image (fewer binaries to call out to if an attacker achieves RCE) and being judicious in volumes and network settings. But yeah, I'd definitely also prefer to see the container running rootless.
It's all about reducing what an attacker can do if they get to RCE, and root still plays a role in that.
But after disabling alter system, you can use permissions to avoid local overrides, so this is probably worth adding in the docs.
1. This config option approach was extremely easy to implement
2. Because making this auto.conf read-only would break many existing tools around Postgres that write to auto.conf
What you really want is to prevent postgres from writing to that file.
That’s more complicated than just making it write only for everyone. Adding an option to stop postgres from doing what you don’t want it to do makes sense to me.
Checkpointing/crash recovery/shared memory also seem to be optimized for daemon restarts.
But! I will admit Kubernetes is handy for stuff like specifying scaling properties... but I mostly feel like conflating the two things ("configuring services" and "configuring cloud providers") was an oops.
I'm not sure it matters for the majority of companies but it has a measurable impact for the gigantic ones.
Afaik containization was rooted in the ability to run multi tenant servers without semi trusted apps overstepping their bounds. See Google Borg and Facebook Tupperware (iirc they started as chroot automation and expanded)
Some stuff like Java have ran in a runtime level container long before OS containers so it seems a bit redundant there, but still facilities having conflicting versions of the runtime installed.
This not a security patch but more of a safeguard against hapless idiots manhandling themselves without resistance in an unsupported case.
It is a safety feature that can help avoid people getting confused when working in a containerized environment. It is not a security feature at all. Nor is it intended to be. It is meant so that people like me can set up a configuration that will work better for coworkers who know less about databases than I do.
Just to give one example of an obvious problem with your idea, saying "WITH FORCE" will suggest to some that this configuration setting is somehow forcibly maintained. This is likely to generate bug reports based on that misunderstanding. "I said WITH FORCE and it is easy to get around!" And, worse yet, questions about, "Why do we have a security feature that doesn't provide any security?"