On 3/21/10 9:36 PM, Joseph Adams wrote:
> Inside of the jail definition is a series of pseudo-statements that
> indicate the space of queries the user can perform. Simply creating a
> jail does not make it go into effect. A jail is activated using
> another query, and it remains in effect for the remainder of the
> session. It cannot be deactivated through the protocol, as doing so
> would constitute a privilege escalation.
This is an interesting approach and I don't think that most of the
people commenting on this list have quite grasped it.
I see two major difficulties to solve with this approach: (1)
developing a way of phrasing the query stubs which would allow common
things like dynamic where clauses, order by, and limit, and (2) whether
it's practical for the author of any real application to define all of
those queries beforehand.
For (1), you might want to look at Meredith's libDejector, which takes a
similar approach for SQL-injection protection:
http://www.thesmartpolitenerd.com/code/dejector.html
I don't think that the idea of turning on the jail mode via a
session-level switch works, given the realities of connection pooling.
Also, I do not believe that we currently have any USERSET variable which
can be turned on but not off, so that would require adding a whole new mode.
BTW, if you wanted something less ambitious, we have a longstanding
request to implement "local superuser", that is, the ability to give one
role the ability to edit other roles in one database only.
-- -- Josh Berkus PostgreSQL Experts Inc.
http://www.pgexperts.com