On Thu, Apr 21, 2016 at 09:40:18AM -0400, Melvin Davidson wrote: > "and what about user objects added to a database which is > then used as a template for creating another DB ?" > > This existence of objects that are part of the default schema is NOT a > problem. Developers and users should never have access to a template. Just one example of why that assertion does not hold: GNUmed stores medical records. There's no allowance for loosing data. One measure it takes to protect data is to execute (roughly) the following sequence when a database schema upgrade is needed (currently at major release 21 thereof). Say, going from v20 to v21: - create database 'gnumed_v21' template 'gnumed_v20' - from this point on gnumed_v20 is NOT TOUCHED anymore - at this point gnumed_v21 is identical to gnumed_v20 as far as GNUmed is concerned - apply - to gnumed_v21 - those SQL fixups scripts intended to bring v20 up to the very latest minor release of v20 - apply - to gnumed_v21 - the v20.latest->v21 upgrade SQL scripts - apply - to gnumed_v21 - the SQL fixup scripts intended to bring v21 up to the very latest minor release of v21 Whatever goes wrong after having cloned gnumed_v20 into gnumed_v21 doesn't matter to the user because they can _always_ go back to using the gnumed_v20 database until a future upgrade run succeeds at which point they can switch over. Of course, this can also be done via dump v20 / restore into v21 but that's slightly more fragile (more things can go wrong). > The point is to be able to track down rogue objects created > by developers and users That is easy. Compare dumps of the current schema against the official schema. In fact, GNUmed does so. The upgrade does not even start if the template schema does not pass an md5 comparison and it does not consider success unless the upgraded schema passes another (target) md5 comparison. Furthermore, the client refuses to connect to a given database if it cannot verify that database's schema via expected md5 thereof. Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
