On Sat, Dec 24, 2011 at 11:34 AM, Darren Duncan <darren@xxxxxxxxxxxxxxxx> wrote: > > > You can do it correctly while reusing all of your code; you just have > different arguments at connect time and otherwise your code uses the > connection handle in the same way afterwards. Its fine to have flags in the > app so the app just tries acceptable things, but privileges in the database > are the only way to actually be safe and resilient against accidental > changes. -- Darren Duncan Agreed. Roles in the db are important (we use them extensively in LedgerSMB). However here is a case where it doesn't work so well: Suppose I want to run read-write test cases against a production database to look for things that are wrong but we want to be mathematically sure that the test cases do not commit data to the database. So I don't entirely know the best way to do this in other languages, but here is what we did in Perl: 1) Our application normally uses DBI (and DBD::Pg). 2) We have a special module (LedgerSMB::DBTest) which basically exposes the portions of the DBI/DBD::Pg interface we are using, but lies to the higher levels about committing. Basically it returns true, but does nothing database-wise, providing the appearance of a consistent set of changes but in fact the changes are still to be rolled back. 3) In our test case scripts, we switch out the DBI database handles with instances of LedgerSMB::DBTest. This works well because it is fails safely. If we omit something from the DBTest module, our code will error. If we have test cases that depend on some transactions committing and others rolling back, we have to order the test cases appropriately or the test cases fail. In other words, the worst we can get are test case failures, not spurious commits, and the changes necessary to make this happen are in the test case files themselves. This makes it easy to verify. Best Wishes, Chris Travers -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
