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 <[email protected]> (204) 291-7950 ------------------------------------------------------------------------------ Download Intel® Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev _______________________________________________ Ledger-smb-devel mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
