[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Proposal for 2.0: SODA 2.0

Chris Travers wrote:

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......
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! 


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. 


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).
Hm. I've never heard least-privilege described that way, but I like it. I think that's a very sane, rationalized, *implementable* and *useful* way to approach things. 


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.


I think I already indicated my support of this position :-)


Declarative anything is a win.


You have no idea how relieved I am to see that sentence!


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. 


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.
I've flip-flopped on this issue a few times, but have held steady for most apps over the last 10 years - and it generally depends on how much trust humans need to put in the correctness of the data. In a financial app, I'd say humans completely trust the app to be completely correct - which means that correctness is to be preferred over (almost) all other things, such as functionality. It's usually possible to achieve both, but not always. 


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? 

--
-Adam Thompson
 <..hidden..>
 (204) 291-7950