Add support for statement_timeout

[Jon Dufresne]

Some database servers have support for the max time a SQL statement can execute before it is aborted. In PostgreSQL this is the statement_timeout variable. Other database servers may have similar features available.

I'm suggesting that Django expose a database setting that allows connections to set this variable. This will allow applications to easily set limits on this resource. A bug in an application may unexpectedly produce a long running query. It is often more desirable to kill the request/query than to allow the request to continue consuming resources. A bug of this nature could lead to a DOM vulnerability in an application. Setting statement_timeout could help reduce the harm of such a bug.

PR to follow.

[Jon Dufresne]

​https://github.com/django/django/pull/4641

[Aymeric Augustin]

It's usually preferable to define such settings in the database server configuration to avoid the overhead of an extra SQL query on each connection. Did you consider this, and if so, how do you plan to address it? (SET TIME ZONE works similarly and has been the subject of long discussions about not making an extra SQL query when not needed.)

I see that your patch only deals with PostgreSQL. In order to maintain feature-parity between database backends, this option should be added to every backend where it can be implemented.

However, if you're focusing on PostgreSQL, perhaps a more useful generalization would be to support setting arbitrary connection options. I would prefer adding a generic mechanism for setting connection options rather than a special case for one of them that you happen to find useful.

[Jon Dufresne]

It's usually preferable to define such settings in the database server configuration to avoid the overhead of an extra SQL query on each connection. Did you consider this, and if so, how do you plan to address it?

I see your point. Motivation for this change came directly from PostgrSQL's documentation which reads: ​http://www.postgresql.org/docs/current/static/runtime-config-client.html

Setting statement_timeout in postgresql.conf is not recommended because it would affect all sessions.

So I was taking PostgreSQL's advice by avoiding globally setting this and instead only set it for the application that needs it.

I could add to the documentation to recommend setting this as the server level for dedicated database servers.

I see that your patch only deals with PostgreSQL. In order to maintain feature-parity between database backends, this option should be added to every backend where it can be implemented.

Sure I'll look into this. I kind of wanted to float the idea first.

However, if you're focusing on PostgreSQL, perhaps a more useful generalization would be to support setting arbitrary connection options. I would prefer adding a generic mechanism for setting connection options rather than a special case for one of them that you happen to find useful.

That is an interesting idea. I can take look at what this would take.

[Anssi Kääriäinen]

On PostgreSQL you can alter the user directly:

ALTER USER django_db_user SET statement_timeout = 10;

There is also connection_created signal which should allow running arbitrary SQL for new connections. So, I don't think there is need for this feature.

[Aymeric Augustin]

For the record, there's also the PGOPTIONS environment variable.

[ris]

Keep in mind not everybody has admin access to their postgres installation (shared hosting?).