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
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
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
Thanks for taking the time to think this over,
Sent via pgsql-hackers mailing list (firstname.lastname@example.org)
To make changes to your subscription: