Re: Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general