SET variable - Permission issues - Mailing list pgsql-hackers

Hello all,
While working on an application I have been developing for about two 
years now, I have come across a base-limitation in PostgreSQL for three 
separate problems. I was talking with other members of the community 
recently and they agreed that the issue has some merit so I thought I 
would bring it up to the list.

First a bit about my environment to better understand the issue. My 
application allows users to connect directly to the database and issue 
commands as they see fit with their own database credentials, which 
operate under various permission restrictions. While I understand this 
is an unusual way of using the database, this is simply the design of my 
application. With that being said, I believe this issue is also 
important to address for other scenarios as well (like an organization 
that offers shared hosting of a PostgreSQL instance).

The actual issue is that many important variables within the PostgreSQL 
configuration file can be overridden by any (non superuser) account 
using SET.

The first instance of this I ran into was with statement_timeout, which 
I was hoping to use to limit the amount of time a query can run for 
before the query is cancelled. This was crucial to the architecture of 
my application to assure that infinite queries did not bog down the 
system and deny service to other users running their own queries. The 
problem is that regardless of what is set in the configuration, users of 
any privilege level can override the value in the postgresql.conf with 
the SET command.

This was frustrating, but not the end of the world. I solved it by using 
a perl script that monitors the running queries and kills any process 
that runs over the allotted time. This isn't perfect though, since a 
user could issue enough commands to prevent the perl script from being 
able to function correctly.

The second variable that I have recently come to realize exists causes a 
much more serious problem. This would be the work_mem setting. This 
variable allows any user to override the system default (again using 
SET) and issue some commands that may intentionally eat up huge chunks 
of RAM.

This one seems a lot more serious as I have no way of monitoring what 
this value is set to. Also, if the attacker acts fast enough (and uses 
the statement_timeout issue as well), they can completely DoS the server.

Finally, the third variable that has caused my application grief is the 
SEED value used by the RANDOM function that can also be set by any 
user.I realize I should be using a better pseudorandom number generator 
for anything important, but since I did not know that PostgreSQL's 
random is beyond a simple case of low-entropy and is actually completely 
deterministic for all my users (because they can each SET their own SEED 
values without restrictions), I now need to rework quite a bit of code 
to resolve this.

These are the three variables which have been problematic so far in my 
application, but I am sure there are others I simply have not noticed 
(or are harmless in my application, but may cause problems in another 
scenario). As far as a solution to the problem, some discussion was made 
about adding more variables for system maximums (like for 
statement_timeout and work_mem), but this does not cover all cases (like 
SEED) and adds unnecessary limitations to the system. Some back-end 
users may actually need to alter these variables beyond what is set as 
the maximum but cannot do so without altering the configuration file.

The other option that was discussed was to add privileges for variables 
(i.e. adding GRANT and REVOKE privileges for the SET command). This is 
obviously a bit more effort but I feel it would be the best option as it 
will add a lot more flexibility into the security of PostgreSQL.

If anybody has any other solutions for how to temporarily resolve these 
problems I would definitely love to hear it but I think it is a good
idea to discuss this problem and find a solution that everyone is 
comfortable with.

Thanks for taking the time to think this over,
-Josh