On Mon, Mar 8, 2010 at 4:08 PM, Adam Thompson <[email protected]> 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 ------------------------------------------------------------------------------ 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
