How to limit work_mem on PostgreSQL

Question

As an analyst I love having the ability to turn up the work_mem on occasion when a big sort or index build is in the offing.

As a DBA, the idea of random users being able to request an arbitrarily large amount of system resources at run-time is a little scary. I confirmed that a basically permission-less fresh-created user, could request as much work_mem as they wished. It would presumably take only a few greedy users at once to starve out other users, which is scary.

Is there any way to limit the work_mem a user can request?

Answer 1

According to the PostgreSQL Wiki this sort of thing isn't possible.

PostgreSQL has no facilities to limit what resources a particular user, query, or database consumes, or correspondingly to set priorities such that one user/query/database gets more resources than others. It's necessary to use operating system facilities to achieve what limited prioritization is possible.

As suggested in the text above, you could try using operating system facilities to achieve limits such as that. For example, ulimit can be used to create process-specific resource limits.