Strict control over the statement_timeout variable in PostgreSQL

Question

Does anybody know how to limit a users ability to set variables? Specifically statement_timeout?

Regardless of if I alter the user to have this variable set to a minute, or if I have it set to a minute in the postgresql.conf file, a user can always just type SET statement_timeount TO 0; to disable the timeout completely for that session.

Does anybody know a way to stop this? I know some variables can only be changed by a superuser but I cannot figure out if there is a way to force this to be one of those controlled variables. Alternatively, is there a way to revoke SET from their role?

In my application, this variable is used to limit the ability of random users (user registration is open to the public) from using up all the CPU time with (near) infinite queries. If they can disable it then it means that I must find a new methodology for limiting resources to users. If there is no method for securing this variable, is there other ways of achieving this same goal that you may suggest?

Edit 2011-03-02 The reason the database is open to the public and arbitrary SQL is allowed is because this project is for a game played directly in the database. Every player is a database user. Data is locked down behind views, rules and triggers, CREATE is revoked from public and the player role to prevent most alterations to the schema and SELECT on pg_proc is removed to secure game-sensitive function code.

This is not some mission critical system I have opened up to the world. It is a weird proof of concept that puts an abnormal amount of trust in the database in an attempt to maintain the entire CIA security triangle within it.

Thanks for your help, Abstrct

You allow random users to connect to your DB and run commands? — Milen A. Radev
– Milen A. Radev, Commented Mar 1, 2011 at 20:20
People call you crazy, but PostgreSQL offers user permissions and even row-level permissions for exactly the reason of not trusting clients. That said, disabling statement_timeout doesn't really do anything. Clients can still make as many requests as they want, unless you have some system to limit perf. — Paul Draper
– Paul Draper, Commented May 22, 2021 at 15:40

Magnus Hagander · Accepted Answer · 2011-03-01 20:18:23Z

1

There is no way to override this. If you allow the user to run arbitrary SQL commands, changing the statement_timeout is just the top of the iceberg anyway... If you don't trust your users, you shouldn't let them run arbitrary SQL - or accept that they can run, well, arbitrary SQL. And have some sort of external monitor that cancels the queries.

answered Mar 1, 2011 at 20:18

Magnus Hagander

25.5k6 gold badges60 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Abstrct Over a year ago

Drats. Not what I was hoping to hear. The entire point of my project is to let untrusted users run arbitrary code so this part will not change. Unless somebody else knows a better method I will look into writing something for process monitoring. Thanks for your response.

Kuberchaun Over a year ago

I think this is a very valid use case sad PG doesn't allow this fine grained type of access control. And for the record I don't trust any user on the system from a admin point of view. I give them access to only what they need to do so having what Abstrct asks would be really nice. How about GRANT set_statement_timeout TO SCARY_USER100;

Abstrct Over a year ago

Thanks StarShip3000, it is nice to know not everyone on the internet thinks I am completely crazy. I didn't think GRANT SET would be too much to ask for but I suppose this just has not been a focus for the PostgreSQL project.

Abstrct Over a year ago

Sorry to spam comments but, Magnus, when you mention "changing the statement_timeout is just the top of the iceberg anyway", would it be possible to elaborate on that? I have put a lot of time and energy into securing what I can in the database but I always appreciate a new point of view as you might know of something I haven't thought to harden.

Magnus Hagander Over a year ago

Well, for one thing, if you can run arbitrary SQL, you can run something that generates huge temporary datafiles, for example. Which will run you out of disk space. There is currently no way to limit that either. I guess if you set your statement_timeout really low, you can prevent most DOS-style problems (if you could lock it), but you will also make the database a lot less useful...

|

Hubbitus · Accepted Answer · 2020-01-09 13:04:21Z

Basically you can't do this in plain postgres. Meantime for accomplish your goal you may use some type of proxies and rewrite/forbidd some queries.

There several solutions for that, f.e.:

db-query-proxy - article how it born (in Russian).
BGBouncer + pgbouncer-rr-patch

Last contains very useful examples and it is very simple do on Python:

import re
def rewrite_query(username, query):
    q1="SELECT storename, SUM\(total\) FROM sales JOIN store USING \(storeid\) GROUP BY storename ORDER BY storename"
    q2="SELECT prodname, SUM\(total\) FROM sales JOIN product USING \(productid\) GROUP BY prodname ORDER BY prodname"
    if re.match(q1, query):
        new_query = "SELECT storename, SUM(total) FROM store_sales GROUP BY storename ORDER BY storename;"
    elif re.match(q2, query):
        new_query = "SELECT prodname, SUM(total) FROM product_sales GROUP BY prodname ORDER BY prodname;"
    else:
        new_query = query
    return new_query

Collectives™ on Stack Overflow

Strict control over the statement_timeout variable in PostgreSQL

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related