Can you slow down queries for a specific user in a postgres database

Question

I am assuming it's possible to slow down the query access of a specific user in a postgres database. How would you do It? Is there a way for the user to see that such a setting has been placed on them, and what might the user do to overcome it, if anything at all.

Basically I'm interested in throttling resources on a per user basis to prevent bad actors from abusing the system.

Answer 1

To my knowledge, vanilla Postgres does not natively offer these features within the engine. Per the Priorities PostgreSQL wiki page:

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 the last sentence alludes to though, there are ways to simulate this behavior by using connection settings (e.g. work_mem and maintenance_work_mem for memory) or OS level tricks with the help of extensions (e.g. prioritize for CPU prioritization).

If you've got a budget, there are customized Postgres engines that claim to provide this functionality as well, one of which is offered by EnterpriseDB. I haven't used it nor do I have any idea how well it performs, but it's another option if you're looking for more alternatives.