Haas: Surviving Without A Superuser - Part One

[Posted December 9, 2021 by corbet]

PostgreSQL developer Robert Haas has begun a blog series on what would be needed to allow database administrators to safely delegate superuser powers.

Consider, for example, the case of a service provider who would like to support a database with multiple customers as tenants. The customers will naturally want to feel as if they have the powers of a true superuser, with the ability to do things like create new roles, drop old ones, change permissions on objects that they don't own, and generally enjoy the freedom to bypass permission checks at the SQL level which superusers enjoy. The service provider, who is the true superuser, also wants this, but does not want the customers to be able to do the really scary things that a superuser can do, like changing archive_command to rm -rf / or deleting the entire contents of pg_proc so that the system crashes and the database in which the operation was performed is permanently ruined.

Haas: Surviving Without A Superuser - Part One

Posted Dec 10, 2021 11:49 UTC (Fri) by martin.langhoff (guest, #61417) [Link] (11 responses)

It is always good to learn something new. However, the era of app/service level tenancy, with a huge contact (attack!) surface is (or should be) over.

In 2021 we have so many good tools for container/OS/VM level segregation, it's just not worth it.

Haas: Surviving Without A Superuser - Part One

Posted Dec 10, 2021 13:52 UTC (Fri) by dskoll (subscriber, #1630) [Link] (1 responses)

Yes, I agree. It seems like a whole lot of work followed by a whole lot of vulnerability-patching to achieve something that can already be done fairly easily with techniques that have been scrutinized for vulnerabilities already.

Haas: Surviving Without A Superuser - Part One

Posted Dec 10, 2021 20:36 UTC (Fri) by Vipketsh (guest, #134480) [Link]

To me it looks like just another entry in the trend of putting containers at an ever higher level.

There may be good reasons to do this on a database level. The same as, say, why you would want to run a container instead of a full VM.

Haas: Surviving Without A Superuser - Part One

Posted Dec 11, 2021 4:04 UTC (Sat) by bartoc (guest, #124262) [Link] (2 responses)

seems like it would have benefits even with containerization or whatever. In particular a large app with many components needs to be able to share data between them but would also like to confine / separate each one as much as possible, as a defense-in-depth measure.

Haas: Surviving Without A Superuser - Part One

Posted Dec 12, 2021 1:24 UTC (Sun) by bjartur (guest, #67801) [Link] (1 responses)

Are these activities not all administrative? Shouldn’t containers drop these privileges once setup is complete?

Haas: Surviving Without A Superuser - Part One

Posted Dec 12, 2021 23:38 UTC (Sun) by bartoc (guest, #124262) [Link]

I don't necessarily think so, it could be useful to have each user of your web app or whatever have their own database user that are used for database operations relating to them.

Haas: Surviving Without A Superuser - Part One

Posted Dec 12, 2021 13:01 UTC (Sun) by kleptog (subscriber, #1183) [Link] (3 responses)

Depends on your threat model. If we're talking about a server which is shared between different departments/apps in the same company then any "vulnerabilities" are completely irrelevant. The other department/app isn't a threat, you just want to prevent undesired interaction under normal situations.

Containerising databases isn't really ideal, because there is so much benefit to large buffer caches and actual hardware. Containers are ideally stateless, and databases are basically only state.

Here we've dealt with the problem by creating a trusted service which the components connect to to create/manage databases and users. Standard roles/rights in Postgres prevent the applications accidentally stomping on each other. The idea of tenants is interesting, but eventually only a small part of the puzzle.

Haas: Surviving Without A Superuser - Part One

Posted Dec 12, 2021 14:38 UTC (Sun) by Wol (subscriber, #4433) [Link]

> The other department/app isn't a threat, you just want to prevent undesired interaction under normal situations.

Until the regulators get involved ...

Cheers,
Wol

Haas: Surviving Without A Superuser - Part One

Posted Dec 12, 2021 16:55 UTC (Sun) by NYKevin (subscriber, #129325) [Link] (1 responses)

> The other department/app isn't a threat, you just want to prevent undesired interaction under normal situations.

Well, you say that...

The reality is that it depends. In some cases, perhaps even most cases, this simplification is perfectly sensible. But if you're going to worry about nation-state adversaries, then you have to make some far more pessimistic assumptions. For example:

* Our adversaries will attempt to install persistent threats and move laterally through our network and other systems.
* Our adversaries will try to get us to hire sleeper agents. They may already have succeeded at this.
* Our adversaries might serve a secret warrant/NSL on one of our employees, demanding their cooperation with some purported investigation, without our knowledge.
* Our adversaries could potentially know almost all security-relevant properties of our systems, either through regulation or espionage.
* Our adversaries can pass laws, which (within their jurisdiction) we have to obey. Such laws are subject to geopolitical considerations, which is why this is not an instant win condition for our adversaries.

As a result, a model in which some subsystems are treated as untrusted may be a necessary component of defense-in-depth, if your adversaries really do have nation-state capabilities.

Haas: Surviving Without A Superuser - Part One

Posted Dec 12, 2021 22:09 UTC (Sun) by kleptog (subscriber, #1183) [Link]

> But if you're going to worry about nation-state adversaries, then you have to make some far more pessimistic assumptions

That is why I said it depended on your threat model. If you care about nation state adversaries then anything cloud related is off the table. If it's not in a private bunker with an armed guard out the front then you may as well give up.

There is always a trade-off between the costs of implementing security and the benefits. In the large number of cases the costs of protecting against a nation state actor are far more than the value of the data. At that point you just have to accept the risk and get on with doing actual work and protecting against the threats you can afford to.

Haas: Surviving Without A Superuser - Part One

Posted Dec 12, 2021 14:16 UTC (Sun) by epa (subscriber, #39769) [Link]

That's true until you want to share data between different users. There are plenty of use cases where different users should have separate privilege, but be able to grant limited access to their data to other users, and this to be managed by the RDBMS so it's fully consistent and transaction-safe. Different groups within the same company can share the same server and have separate databases on it. This is preferable to some much more complex and fragile replication setup (or to passing around data outside the RDBMS, which usually results in ad hoc bodges that aren't transaction-safe). I agree, I don't think I would want it for a cloud provider where different users trust each other not at all.

Haas: Surviving Without A Superuser - Part One

Posted Dec 14, 2021 8:35 UTC (Tue) by taladar (subscriber, #68407) [Link]

I could see this being useful in situations where the real superuser takes care of complex high availability clustering and backup requirements while the user with some superuser privileges essentially just takes care of the data and users allowed to access it.

Haas: Surviving Without A Superuser - Part One

Posted Dec 16, 2021 7:27 UTC (Thu) by Cyberax (✭ supporter ✭, #52523) [Link] (2 responses)

It's a good time to plug my blog post about multi-tenant isolation in Postgres using security labels: https://blog.alex.net/2021/08/13/multitenancy-in-postgresql/

This is a somewhat overlooked scenario, when you want to add one more security barrier in case you code gets compromised.

Haas: Surviving Without A Superuser - Part One

Posted Dec 16, 2021 10:53 UTC (Thu) by nye (subscriber, #51576) [Link] (1 responses)

> We can’t do database-per-tenant or schema-per-tenant partitioning as this will blow up the complexity of all the routine operations like database upgrade

What sorts of things are you thinking of here? Things like updating the software version? Or more like data version migrations (whether schema updates or pure data)?

Haas: Surviving Without A Superuser - Part One

Posted Dec 16, 2021 19:14 UTC (Thu) by Cyberax (✭ supporter ✭, #52523) [Link]

Both. With potentially tens of thousands of tenants migrating each schema becomes problematic. We also occasionally need cross-tenant interactions and the tables are also partitioned.

It's all fixable of course, just kinda clumsy with multiple schemas.