1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
|
-- ==============================================================
-- GNUmed database schema change script
--
-- License: GPL v2 or later
-- Author: karsten.hilbert@gmx.net
--
-- ==============================================================
\set ON_ERROR_STOP 1
set check_function_bodies to on;
--set default_transaction_read_only to off;
-- --------------------------------------------------------------
-- audit.audit_fields
-- actually, there's an INSERT trigger on child tables already
-- which forces .modified_when to now()
alter table audit.audit_fields
drop constraint if exists
audit_audit_fields_sane_modified_when cascade;
alter table audit.audit_fields
add constraint audit_audit_fields_sane_modified_when check
((modified_when <= clock_timestamp()) IS TRUE)
;
-- add a foreign key which is, however, NOT ENFORCED
-- this should cue in pg_dump about the needed order
-- of dumping dem.staff vs other tables ...
--
-- actually, this needs to be put into all child tables
-- and link against dem.staff.db_user (but not be enforced)
--
--alter table audit.audit_fields
-- drop constraint if exists fk_audit_audit_fields_dem_staff_db_user cascade
--;
--
--alter table audit.audit_fields
-- add constraint fk_audit_audit_fields_dem_staff_db_user
-- foreign key (modified_by)
-- references dem.staff(db_user)
-- on update restrict
-- on delete restrict
-- not valid
--;
create or replace function gm.account_is_dbowner_or_staff(_account name)
returns boolean
language plpgsql
as '
DECLARE
_is_owner boolean;
BEGIN
-- is _account member of current db group ?
-- PERFORM 1 FROM pg_auth_members
-- WHERE
-- roleid = (SELECT oid FROM pg_roles WHERE rolname = current_database())
-- AND
-- member = (SELECT oid FROM pg_roles WHERE rolname = _account)
-- ;
-- IF FOUND THEN
-- -- should catch people on staff, gm-dbo, and postgres
-- RETURN TRUE;
-- END IF;
-- postgres
IF _account = ''postgres'' THEN
RETURN TRUE;
END IF;
-- on staff list
PERFORM 1 FROM dem.staff WHERE db_user = _account;
IF FOUND THEN
RETURN TRUE;
END IF;
-- owner
SELECT pg_catalog.pg_get_userbyid(datdba) = _account INTO STRICT _is_owner FROM pg_catalog.pg_database WHERE datname = current_database();
IF _is_owner IS TRUE THEN
RETURN TRUE;
END IF;
RETURN FALSE;
END;';
-- --------------------------------------------------------------
-- audit.audit_trail
alter table audit.audit_trail
drop constraint if exists
audit_audit_trail_sane_orig_when cascade;
alter table audit.audit_trail
add constraint audit_audit_trail_sane_orig_when check
((orig_when <= clock_timestamp()) IS TRUE)
;
alter table audit.audit_trail
drop constraint if exists
audit_audit_trail_sane_audit_when cascade;
alter table audit.audit_trail
add constraint audit_audit_trail_sane_audit_when check
((audit_when <= clock_timestamp()) IS TRUE)
;
alter table audit.audit_trail
drop constraint if exists
audit_audit_trail_orig_before_audit_when cascade;
alter table audit.audit_trail
add constraint audit_audit_trail_orig_before_audit_when check
((orig_when <= audit_when) IS TRUE)
;
-- --------------------------------------------------------------
select gm.log_script_insertion('v21-audit-audit_constraints.sql', '21.0');
|
