changeset 1173:5239306ee55e pgaudit

Improbe audit trail configuration. Audit only relevant columns, thus not internal stuff like tree_modified. Audit INSERT also, because we will need it to track initial values.
author Tom Gottfried <tom@intevation.de>
date Fri, 11 Nov 2016 16:50:00 +0100
parents 74e5b9630064
children 2e59a51d914f
files db_schema/setup-db.sh
diffstat 1 files changed, 64 insertions(+), 4 deletions(-) [+]
line wrap: on
line diff
--- a/db_schema/setup-db.sh	Wed Nov 09 20:01:00 2016 +0100
+++ b/db_schema/setup-db.sh	Fri Nov 11 16:50:00 2016 +0100
@@ -86,16 +86,76 @@
 psql $DB_CONNECT_STRING -d $DB_NAME --command \
      "ALTER DATABASE $DB_NAME SET pgaudit.role TO '$AUDITOR_ROLE';
       ALTER DATABASE $DB_NAME SET pgaudit.log_parameter TO on;
-      GRANT UPDATE, DELETE ON
+      GRANT DELETE ON
           land.probe,
-          land.kommentar_p,
           land.ortszuordnung,
           land.zusatz_wert,
           land.messung,
-          land.kommentar_m,
           land.messwert,
           land.status_protokoll
-          TO $AUDITOR_ROLE;"
+          TO $AUDITOR_ROLE;
+      GRANT INSERT, UPDATE, DELETE ON
+          land.kommentar_p,
+          land.kommentar_m
+          TO $AUDITOR_ROLE;
+      GRANT INSERT, UPDATE (
+              id_alt,
+              test,
+              mst_id,
+              labor_mst_id,
+              hauptproben_nr,
+              datenbasis_id,
+              ba_id,
+              probenart_id,
+              media_desk,
+              media,
+              umw_id,
+              probeentnahme_beginn,
+              probeentnahme_ende,
+              mittelungsdauer,
+              erzeuger_id,
+              probe_nehmer_id,
+              mpl_id,
+              mpr_id,
+              solldatum_beginn,
+              solldatum_ende
+          ) ON land.probe TO $AUDITOR_ROLE;
+      GRANT INSERT, UPDATE (
+              ort_id,
+              ortszuordnung_typ,
+              ortszusatztext
+          ) ON land.ortszuordnung TO $AUDITOR_ROLE;
+      GRANT INSERT, UPDATE (
+              pzs_id,
+              messwert_pzs,
+              messfehler,
+              nwg_zu_messwert
+          ) ON land.zusatz_wert TO $AUDITOR_ROLE;
+      GRANT INSERT, UPDATE (
+              id_alt,
+              nebenproben_nr,
+              mmt_id,
+              messdauer,
+              messzeitpunkt,
+              fertig,
+              geplant
+          ) ON land.messung TO $AUDITOR_ROLE;
+      GRANT INSERT, UPDATE (
+              messgroesse_id,
+              messwert_nwg,
+              messwert,
+              messfehler,
+              nwg_zu_messwert,
+              meh_id,
+              grenzwertueberschreitung
+          ) ON land.messwert TO $AUDITOR_ROLE;
+      GRANT INSERT, UPDATE (
+              mst_id,
+              datum,
+              text,
+              messungs_id,
+              status_kombi
+          ) ON land.status_protokoll TO $AUDITOR_ROLE;"
 
 if [ "$NO_DATA" != "true" ]; then
     echo import stammdaten
This site is hosted by Intevation GmbH (Datenschutzerklärung und Impressum | Privacy Policy and Imprint)