Re: Database security

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

YES!!

2007/2/18, Ilya A. Volynets-Evenbakh <ilya@total-knowledge.com>:

OK. I didn't know there are "setuid" procedures. This really changes
whole picture. If that's possible, we should go your way.

I think, as far as access checker, it should be something like
bool is_authorized(object_type, object_id, access_type)

Reason: we have more access types, then just read/write, and
they are all defined in more or less generic way, according to
the spec.

Alexey Parshin wrote:
> 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

--
Ilya A. Volynets-Evenbakh
Total Knowledge. CTO
http://www.total-knowledge.com

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