1------------------------------------------------------------------------------
2-- Database upgrade to 2.3 version
3--
4-- Use:
5--	psql --no-psqlrc --single-transaction -f upgrade.sql database-name
6--
7-- Please, make a backup of your existing database first!
8-- Use a tool such as nohup or script in order to log output and check
9-- error messages:
10--	- Lines with "NOTICE:" are not important.
11--	- You should pay attention to lines with "ERROR:"
12------------------------------------------------------------------------------
13
14-- Template for utmp and wtmp tables
15CREATE TABLE global.tmp (
16    idcor	INT,			-- user
17    token	TEXT NOT NULL,		-- auth token in session cookie
18    start	TIMESTAMP (0) WITHOUT TIME ZONE
19                        DEFAULT CURRENT_TIMESTAMP
20			NOT NULL,	-- login time
21    ip		INET			-- IP address at login time
22) ;
23
24-- Currently logged-in users
25CREATE TABLE global.utmp (
26    casticket	TEXT,			-- CAS service ticket
27    lastaccess	TIMESTAMP (0) WITHOUT TIME ZONE
28                        DEFAULT CURRENT_TIMESTAMP
29			NOT NULL,	-- last access to a page
30
31    FOREIGN KEY (idcor) REFERENCES global.nmuser (idcor),
32    PRIMARY KEY (idcor, token)
33) INHERITS (global.tmp) ;
34
35-- All current and previous users. Table limited to 'wtmpexpire' days
36CREATE TABLE global.wtmp (
37    stop	TIMESTAMP (0) WITHOUT TIME ZONE
38			NOT NULL,	-- logout or last access if expiration
39    stopreason	TEXT NOT NULL,		-- 'logout', 'expired'
40
41    FOREIGN KEY (idcor) REFERENCES global.nmuser (idcor),
42    PRIMARY KEY (idcor, token)
43) INHERITS (global.tmp) ;
44
45-- Failed login attempts
46CREATE TABLE global.authfail (
47    origin	TEXT,			-- login name or IP address
48    otype	TEXT,			-- type of origin ('ip' or 'login')
49    nfail	INTEGER,		-- failed attempts count
50    lastfail	TIMESTAMP (0)		-- date of last failed
51		    WITHOUT TIME ZONE
52		    DEFAULT CURRENT_TIMESTAMP,
53    blockexpire	TIMESTAMP (0) WITHOUT TIME ZONE,
54
55    PRIMARY KEY (origin, otype)
56) ;
57
58
59DELETE FROM global.config where key = 'ldapattrpasswd' ;
60
61INSERT INTO global.config (key, value) VALUES ('authexpire', '36000') ;
62INSERT INTO global.config (key, value) VALUES ('authtoklen', '32') ;
63INSERT INTO global.config (key, value) VALUES ('wtmpexpire', '365') ;
64INSERT INTO global.config (key, value) VALUES ('casurl', 'https://cas.example.com/cas/') ;
65INSERT INTO global.config (key, value) VALUES ('failloginthreshold1', '3') ;
66INSERT INTO global.config (key, value) VALUES ('failloginthreshold2', '10') ;
67INSERT INTO global.config (key, value) VALUES ('faillogindelay1', '120') ;
68INSERT INTO global.config (key, value) VALUES ('faillogindelay2', '300') ;
69INSERT INTO global.config (key, value) VALUES ('failipthreshold1', '10') ;
70INSERT INTO global.config (key, value) VALUES ('failipthreshold2', '30') ;
71INSERT INTO global.config (key, value) VALUES ('failipdelay1', '300') ;
72INSERT INTO global.config (key, value) VALUES ('failipdelay2', '1200') ;
73
74ALTER TABLE dns.rr_cname DROP CONSTRAINT rr_cname_pkey;
75ALTER TABLE dns.rr_cname ADD PRIMARY KEY (idrr);
76
77UPDATE global.config SET value = '23' WHERE key = 'schemaversion' ;
78