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

Hello, I'm Joey Adams, and I'm interested in applying for Google
Summer of Code to work on PostgreSQL. I'm a former GSoC student (I
worked on CCAN last year), and a strong C programmer, though I am
still new to working with large, established communities. I apologize
if this is the wrong place to send GSoC student introductions.

My proposal is bold, though I believe it can be trimmed down and
refined into something very useful, yet simple enough to implement in
3 months by a newcomer to the PostgreSQL code base.

I propose adding application-level access control to PostgreSQL via a
jails concept. In a nutshell, a jail is created as part of the
database definition (typically exposing a free variable for the
current user). When a jail is activated for a session, the only
accesses allowed are those indicated in the jail itself. A jail
cannot be exited without closing the session. If used properly, jails
make it possible to safely execute untrusted SQL code (though one may
not want to, citing the principle of least privilege).

For example, suppose we have the following database definition for a
trivial discussion board system:

CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT,
password TEXT,
email TEXT,
email_public BOOLEAN
);

CREATE TABLE posts (
id SERIAL PRIMARY KEY,
title TEXT,
"user" INT REFERENCES users(id),
content TEXT,
published BOOLEAN
);

CREATE TABLE tags (
post INT REFERENCES posts(id),
tag TEXT
);

Suppose our discussion board is written in PHP. It authenticates the
user via cookies, after which it has an ID for the current user. One
way to arbitrate access is to code it into the PHP using queries like
this:

pg_query_params('SELECT users.email WHERE id=$1 AND (email_public=TRUE
OR id=$2)', $_GET['user'], $current_user);

Simple enough. However, this access control check has to be done for
every query, and it can get rather complex as more tables are
involved.

Views are a way to alleviate this complexity, but:

* Views only apply to SELECTs
* Queries still have to specify the current user (unless one uses a
global variable (which requires a bit of a hack, if I'm not mistaken))

My proposal would make it possible to have a statement in the database
definition much like this:

CREATE JAIL jail (
SELECT id, name FROM users;
SELECT email FROM users
WHERE id=current_user
OR email_public=TRUE;

SELECT * FROM posts
WHERE "user"=current_user
OR published=TRUE;
UPDATE posts
WHERE "user"=current_user
SET title, content, published;
INSERT INTO posts
WHERE "user"=current_user;
DELETE FROM posts
WHERE "user"=current_user;

SELECT * FROM tags, posts
WHERE tags.post=posts.id;
INSERT INTO tags
WHERE post IN (
SELECT id FROM posts
WHERE "user"=current_user);
DELETE FROM posts
WHERE post IN (
SELECT id FROM posts
WHERE "user"=current_user);
);

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.

Example of a PHP script invoking a jail:

<?php
pg_connect('user=foo password=spiderman') or die('Database error occurred');

/* authentication-fu */
// $current_user now contains the id of the logged-in user.

pg_query_params('USE JAIL jail WITH current_user=$1', array($current_user));

/* Remaining queries in this session will only be able to SELECT,
UPDATE, INSERT, and DELETE on users, posts, and tags, following the
rules set forth by the jail. */
?>

I came up with this idea while working on a small
software-as-a-service application using PostgreSQL. I needed to
implement access control in the PHP frontend. I ended up creating a
database abstraction class to protect the rest of the frontend code
from SQL messiness. Every database query I needed, I wrote/extended a
PHP function for it. My database access class for my "small"
application exploded to over 1000 lines (granted, a lot of it was
comments).

Although having accessor functions can make the code easier to read
and maintain, it can result in a lot of redundancy. Why not just use
the database abstraction library PostgreSQL gives you: SQL?

Jails seem to me like a revolutionary addition to SQL, and
implementing them "fully" could be quite involved. However, my guess
is that by restricting what may be done in a jail simply to SELECT,
UPDATE, INSERT and DELETE, an initial jail implementation capable of
what I posted above would be relatively easy. Work would mainly
consist of adding code to PostgreSQL's parser and the rewrite engine.

Comments?