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,

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to