Re: Proposal: access control jails (and introduction as aspiring GSoC student)

I apologize for my silence, as I've been busy reading up more on the
internals of PostgreSQL.

From what I can tell, a big problem with my jails idea (as well as the
variables Robert described) is that there really isn't a way to store
context in the backend specifically for the end client (e.g. a PHP
script) due to connection pooling. Also, I almost feel that storing
such context would be a disadvantage, as it would harm some of the
referential transparency that pooling and caching take advantage of,
now and in the future. However, I'm not going to give up :)

Perhaps we could have some sort of LET statement that allows the
client to pass data to the server, then have libpq automatically wrap
queries with the LET statement (when necessary). Here's what it would
look like to the PHP scripter:

// New libpq function
pg_set('current_user', 'bob');

$result = pg_query_params(
'SELECT answer FROM secrets WHERE user=current_user AND question=$1',
array('Birth place'));

What this really does is something like:

$result = pg_query_params(
'LET current_user=$1 DO $2 $3',
array(
'bob',
'SELECT answer FROM secrets WHERE user=current_user AND question=$1',
'Birth place')
));

Here, the hypothetical LET statement executes a query string, binding
current_user to our desired value. The client library would wrap all
future queries in this fashion.

Granted, it would be silly to pass the value itself to the server over
and over, so a serious implementation would probably pass a context
ID, and these variable assignments would live in the backend instead.
Moreover, LET is a terrible keyword choice here, considering most
PostgreSQL users won't need to use it explicitly thanks to additional
libpq support.

Alternatively (this might require changing the client/server
protocol), a context ID could be passed back and forth, thus providing
a way to tell clients apart.

Implementing this idea requires adding to the backend and to libpq.
The backend would need at least two new statements. One would set a
variable of a session context, creating one if necessary and returning
its ID. Another would execute a string as a parameter and bind both
immediate arguments and session context to it. libpq would need a
function to set a variable, and it would need to wrap queries it sends
out with LET statements if necessary.

Note that these variables can't be used in pre-defined functions
unless they are somehow declared in advance. One idea would be to
first add global variable support, then make session-local contexts be
able to temporarily reassign those variables. Another would be to
provide an explicit declaration statement.

Would this make a good proposal for GSoC?: Implement the backend part
of my proposal, and create a proof-of-concept wrapper demonstrating
it. This way, I add the new statements, but don't mess around with
existing functionality too much.