Re: Proposal for 2.0: SODA 2.0

[Chris Travers <..hidden..>]

On Mon, Mar 8, 2010 at 4:08 PM, Adam Thompson <..hidden..> wrote:

> Oh.  That bites.  And I see CHECK has even worse limitations.  So much
> for that idea.
>
>> 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....
>
> That's a valid point; there are lots of examples like that.  Some SQL
> dialects (can't remember where I saw this) allow you to do lovely things
> like SUM(CURRENT TRANSACTION x) which allows GL table checks like the
> one above without large performance hits.  *Definitely* non-standard,
> though!

That would be nice.
>
>> 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....
>
> I don't have a particularly large problem with (preferably) anonymous
> SPs tied to triggers, because there is usually no other, more "concrete"
> way to declare that concept.  Conversely, having triggers proliferate
> all over the place is bad form and indicates your ER model is
> considerably more complex than it should be.


Agreed on both points, though I don't think SP trigger procs can be
anonymous in Pg.

>
>> Declarative anything is a win.
>
> You have no idea how relieved I am to see that sentence!

Good, then we're on the same page :-)
>
>> 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 think the example you provided is a valid case; my personal preference
> would be to do it in a trigger (assuming the necessary syntax is
> possible in Pg8.4) but I can see that using triggers to replace
> declarative security where the declarative syntax is insufficiently
> expressive could be a nightmare, ultimately.  I don't want to
> re-implement a security subsystem _even more_ than I don't want
> security-by-stored-procedure.

The way I envision it is "declarative security with narrow procedural
exceptions."

One of the major reasons for this is that hasn't been mentioned is
that declarative security (and data constraints, etc) basically
provide a mathematical model for who can access what.  This is static.
 It isn't susceptible to procedural errors.  It provides a place were
you can look and say "this can happen" and "this cannot happen."

The procedural exceptions to this are allow us to do something
different.  We can say "even though this cannot normally happen, users
can do this if they do it through this procedure."  That provides a
narrow opening to the declarative rules.

For example, I don't have to check the user's role inside
draft_delete(int).  Either the user has permission to delete drafts
(by running the function) or the user doesn't.

>> 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.
>
> Doesn't this contradict what you've been saying?
>
> Are there many places where core *read* functionality cannot be
> implemented as, say, a view instead of an SP?  (Write functionality is a
> different kettle of fish.)  Or are you talking about write-access only here?

Ok, we use stored procedures basically as two ways in LedgerSMB:

1)  Named Queries.    In 2.0 if I want to run a trial balance, I will
run a stored procedure to get that data.  That is basically nothing
more than a named, prepared SQL statement (prepare/execute from the
psql prompt is no different except that such doesn't persist across
sessions).  There's no reason a third party app couldn't hit the
tables directly to do the same thing.  The idea here is that it makes
the main db operations from LedgerSMB available to third party apps
without breaking our security model and allows us all to share code
even if working from different languages.

I suppose it might be possible to implement a trial balance view, but
I can't imagine it being any easier to use than a stored procedure
that third party developer can utilize, and performance might be
pretty bad.  The same would go for other periodic reports (income
statements, balance sheet, etc).

The other major benefit this offers is that most Perl files only
contain Perl code and  SQL files only contain SQL and PLPGSQL code.
This is not much different then separating CSS/Javascript/HTML into
separate files.

2)  Controlled Security Exceptions.  For example:  Nobody is allowed
to delete from journal_line as a general rule.  However individuals
with proper permissions may use a specific stored procedure to delete
unapproved transactions (only) from these tables.

I guess my opinion is that if we get to the point where we are
seriously trying to heavily normalize the db well beyond 3nf then we
need to provide views.  The views wouldn't replace the named query
approach but would rather support it by providing a friendly
relational interface to other developers.  The named query approach
works best with a logical schema that everyone can understand (whether
or not it represents physical storage is another question and we
aren't necessarily addressing that at this point).

> I'd like to see the project go one step further and declare that no
> read-access functions should run as SECURITY DEFINER, only write-access
> functions.

How about:  "Only use SECURITY DEFINER when you absolutely have to?"

(IOW, push security back....)

> I can't think of any real-world example I've ever run into where
> column-level permission granularity couldn't solve this.  (Well, except
> for really badly-designed databases that needed, effectively, row-level
> security!)

Row level security can be accomplished through VIEWs....   I can see
why some folks might eventually want contact management that only
gives some folks access to their own customers, for example.  VIEWS
are nice for that and allow declarative security to be preserved.

> And there shouldn't be any functions that both read and write (I think).

I like to try to return useful return info on write operations, so
very often times the write is followed by a read (often of the data
just written).  That usually means that the app can be aware of any
default values (including serial number assignments, etc).  The use of
RETURNING is where this will probably be headed.  Not sure if that
counts as a read.....

Best Wishes,
Chris Travers