Re: Proposal for 2.0: SODA 2.0

[Chris Travers <..hidden..>]

On Mon, Mar 8, 2010 at 11:21 AM, Adam Thompson <..hidden..> wrote:
>> Many of the data integrity constraints can be embedded as CHECK or referential constraints in the database schema; this alone can minimize the *possibility* of someone changing data in the wrong way.  For those transactions that have to be multi-step, e.g. GL transactions, it makes perfect sense to wrap the INSERTs in a stored procedure.  Also, DEFER constraints can mitigate this problem (rules are only checked at COMMIT time, not at INSERT/UPDATE/DELETE time).<<

I would love to use DEFER more.  Unfortunately, from the Pg 8.4 manual:
"Only foreign key constraints currently accept this clause. All other
constraint types are not deferrable."

In fact only foreign key constraints which are "NO ACTION" can be deferred......

Also, I would wonder what the performance of a deferred check
constraint that was something like sum(amount) = 0 on the journal line
table would be and whether it would be worth it....

>> I think there are already too many stored procedures; using SPs as a means of enforcing security doesn't have mainstream traction outside traditional Sybase (and MS SQL) shops.  I feel that practice encourages developers to make the DB into a black box: the "you're not good enough to see MY tables" mindset.  Which (IMHO) leads quickly to a bad case of Not-Invented-Here syndrome.<<

We don't usually use them in 1.3 to enforce security.  We do in some
areas, for example, where superuser access or special permissions
might be required, or where other permission must be denied.  Some of
this can be done with triggers as well, and where we can, I think we
should move towards more triggers instead of more security definer
functions.  Of course triggers in PG have to run stored procs so this
would increase rather than decrease the number of such procedures....

>> Leave the DB structure as open as possible, enforcing security with the most direct tools the DB gives us for that task, *removing* an extra access layer where needed.  VIEWs are the mechanism for projecting complex relationships in an understandable way.<<

This gets to one of the basic ideas I have regarding secure
application design, as a strategy towards better use of the least
privilege principle.  It's what I call "push security back."  Where
possible, security should be enforced on the point furthest back.  As
much of the application as possible should be outside the
application's own security perimeter (i.e. should not be trusted).

This is one area where I disagree with Aurynn.  If we use stored procs
to enforce security as our primary method, then the code in those
sprocs is going to be fully trusted.  Any use of EXECUTE then poses a
possibility of SQL injection, in the database, likely as the db
superuser.  While there are tools to prevent this, I think we get more
depth of defence out of reducing the rights of a given sproc to those
of the user in addition.  The upside is that ad-hoc reporting queries
work as well.

>> The other upside is that declarative security can be auto-discovered and auto-documented.  Procedural security cannot.  Maintenance down the road is somewhat easier declaratively, too, for the same reasons of discoverability.<<

Declarative anything is a win.  Yes, we should use it wherever
possible.  What it doesn't really allow us to do at present is
something like:

Allow record to be deleted if approved is not true and user has role
x.  These are the few cases where sprocs have to enforce security
privileges, whether by being called or on the trigger level.

Don't get me wrong:  I would love to get rid of procedural security on
that case.  I just don't have a tool to do it right now.

>> I've been programming, managing, using and teaching RDBMSes for nigh 20 years now: using stored procedures as a security mechanism is often (but not always, by any means!) associated with programmers or DBAs looking for job security, not about transparency or doing things the right way.  You might say I've grown a strong bias against the technique as I've observed it and managed it over the years.<<

The only security fully enforced by this currently is the fact that
nearly all db access goes through a few app calls, which can be easily
audited for SQL injection issues.  Otherwise, the best security comes
from implementing it as far back as possible.  Usually (but not
always) this is on the table level.

If a table change is going to break addons or third party bits, it's
not much more likely to do that if the tables are inaccessible to the
end user.  Any third party add-on which makes proper use of the db (RI
constraints) will break when this happens and there is no way around
that.  In fact, it SHOULD break when this happens because it is better
to inform the user loudly that it can't work than to do its best and
end up with inconsistent data.

This doesn't mean necessarily that if we need to decompose tables much
further, that we shouldn't recompose them in views as a logical
interface (at which point the view becomes the point where permissions
should be enforced) but we shouldn't expect that not to break addons.

>> That's not to say SPs have no place - they are an *excellent* hammer... when you're dealing with nails, not screws.<<

Agreed.  This proposal is for a hammer, not a crescent wrench.....

BTW, I used to want all access to the app to go through relational
interfaces.  However, as time has gone on, I think ensuring that all
core functionality is available through stored procedures is a good
thing.  The result is cleaner code, code that is easier to read, and
better control of SQL injection issues.

 Best Wishes,
Chris Travers