Database security

["Alexey Parshin" <alexeyp@gmail.com>]

Gentlemen,

Since we are implementing a database that may be used in commercial app, the security of the database is really important. 
1) One day or another, someone would try to hack into the database directly, around our application. We should be prepared to it.
2) The database should enforce security to guarantee it, not just suggest it.
3) The nature of our permissions doesn't allow to use database permissions to handle access of different users to parts of the tables - we need stored procs to achieve it.

So, my idea was to allow users to select from any table and to allow any modification to be done only through stored procs.
It's currently implemented in SVN as the following:
1) Every table is granted access as:
GRANT SELECT ON <table name> TO PUBLIC;
2) Every stored procedure is defined as SECURITY DEFINER. Such stored procs are executed with the permissions of their creator (superuser, in our case)

In order to maintain the security, we need user ID defined within the session. Postgres doesn't have any support for session variables (other then engine parameters), or I couldn't find it. 
Instead, we can use temporary tables to implement the equivalent to session variables. In order to prevent regular user from dropping temporary table with session parameters, we revoke the right to create a temp table from PUBLIC. 
I provided (in SVN) file login.sql that creates a session_info table with user_id. It isn't a real login procedure yet - it just tests the concept. After we have user id in such temp table, and can guarantee users can't (re)create or change it - we can use user id in the security checks.

A typical security check should happen at the start of every object modification an be something like:

if can_modify("topic",topic_id) = false then
   RAISE EXCEPTION('You can't modify this topic');
end if

The actual prototype of 'can_modify' should be discussed. It could be a set of 'can_update', 'can_delete', etc, or it could be can_modify(object type,object id,access level);

-- 
Alexey Parshin,
http://www.sptk.net