1# Core schema
2# Copyright (C) 2009, AllWorldIT
3# Copyright (C) 2008, LinuxRulz
4#
5# This program is free software; you can redistribute it and/or modify
6# it under the terms of the GNU General Public License as published by
7# the Free Software Foundation; either version 2 of the License, or
8# (at your option) any later version.
9#
10# This program is distributed in the hope that it will be useful,
11# but WITHOUT ANY WARRANTY; without even the implied warranty of
12# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
13# GNU General Public License for more details.
14#
15# You should have received a copy of the GNU General Public License along
16# with this program; if not, write to the Free Software Foundation, Inc.,
17# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
18
19@PRELOAD@
20
21
22/*
23Priorities...
24	0      - System policy priority (fallthrough)
25	1-50   - System policies
26	50-100 - Custom policies
27*/
28
29/* Policies */
30CREATE TABLE policies (
31	ID			@SERIAL_TYPE@,
32
33	Name			VARCHAR(255) NOT NULL,
34
35	Priority		SMALLINT NOT NULL,
36
37	Description		TEXT,
38
39	Disabled		SMALLINT NOT NULL DEFAULT '0'
40
41) @CREATE_TABLE_SUFFIX@;
42
43INSERT INTO policies (Name,Priority,Description) VALUES ('Default',0,'Default System Policy');
44INSERT INTO policies (Name,Priority,Description) VALUES ('Default Outbound',10,'Default Outbound System Policy');
45INSERT INTO policies (Name,Priority,Description) VALUES ('Default Inbound',10,'Default Inbound System Policy');
46INSERT INTO policies (Name,Priority,Description) VALUES ('Default Internal',20,'Default Internal System Policy');
47INSERT INTO policies (Name,Priority,Description) VALUES ('Test',50,'Test policy');
48
49
50/* Member list for policies */
51CREATE TABLE policy_members (
52	ID			@SERIAL_TYPE@,
53
54	PolicyID		@SERIAL_REF_TYPE@,
55
56	/*
57		Format of key:
58		NULL = any
59		a.b.c.d/e = IP address with optional /e
60		@domain = domain specification,
61		%xyz = xyz group,
62		abc@domain = abc user specification
63
64		all options support negation using !<key>
65	*/
66	Source			TEXT,
67	Destination		TEXT,
68
69	Comment			VARCHAR(1024),
70
71	Disabled		SMALLINT NOT NULL DEFAULT '0',
72
73	FOREIGN KEY (PolicyID) REFERENCES policies(ID)
74) @CREATE_TABLE_SUFFIX@;
75
76
77/* Default System Policy */
78INSERT INTO policy_members (PolicyID,Source,Destination) VALUES
79	(1,NULL,NULL);
80/* Default Outbound System Policy */
81INSERT INTO policy_members (PolicyID,Source,Destination) VALUES
82	(2,'%internal_ips,%internal_domains','!%internal_domains');
83/* Default Inbound System Policy */
84INSERT INTO policy_members (PolicyID,Source,Destination) VALUES
85	(3,'!%internal_ips,!%internal_domains','%internal_domains');
86/* Default Internal System Policy */
87INSERT INTO policy_members (PolicyID,Source,Destination) VALUES
88	(4,'%internal_ips,%internal_domains','%internal_domains');
89/* Test Policy */
90INSERT INTO policy_members (PolicyID,Source,Destination) VALUES
91	(5,'@example.net',NULL);
92
93
94
95/* Groups usable in ACL */
96CREATE TABLE policy_groups (
97	ID			@SERIAL_TYPE@,
98
99	Name			VARCHAR(255) NOT NULL,
100
101
102	Disabled		SMALLINT NOT NULL DEFAULT '0',
103
104	Comment			VARCHAR(1024),
105
106
107	UNIQUE (Name)
108)  @CREATE_TABLE_SUFFIX@;
109
110INSERT INTO policy_groups (Name) VALUES ('internal_ips');
111INSERT INTO policy_groups (Name) VALUES ('internal_domains');
112
113
114
115/* Group members */
116CREATE TABLE policy_group_members (
117	ID			@SERIAL_TYPE@,
118
119	PolicyGroupID		@SERIAL_REF_TYPE@,
120
121	/* Format of member: a.b.c.d/e = ip,  @domain = domain, %xyz = xyz group, abc@domain = abc user */
122	Member			VARCHAR(255) NOT NULL,
123
124
125	Disabled		SMALLINT NOT NULL DEFAULT '0',
126	Comment			VARCHAR(1024),
127
128
129	FOREIGN KEY (PolicyGroupID) REFERENCES policy_groups(ID)
130)  @CREATE_TABLE_SUFFIX@;
131
132INSERT INTO policy_group_members (PolicyGroupID,Member) VALUES (1,'10.0.0.0/8');
133INSERT INTO policy_group_members (PolicyGroupID,Member) VALUES (2,'@example.org');
134INSERT INTO policy_group_members (PolicyGroupID,Member) VALUES (2,'@example.com');
135
136
137
138/* Message session tracking */
139CREATE TABLE session_tracking (
140	Instance		VARCHAR(255),
141	QueueID			VARCHAR(255),
142
143	Timestamp		BIGINT NOT NULL,
144
145	ClientAddress		VARCHAR(64),
146	ClientName		VARCHAR(255),
147	ClientReverseName	VARCHAR(255),
148
149	Protocol		VARCHAR(255),
150
151	EncryptionProtocol	VARCHAR(255),
152	EncryptionCipher	VARCHAR(255),
153	EncryptionKeySize	VARCHAR(255),
154
155	SASLMethod		VARCHAR(255),
156	SASLSender		VARCHAR(255),
157	SASLUsername		VARCHAR(255),
158
159	Helo			VARCHAR(255),
160
161	Sender			VARCHAR(255),
162
163	Size			@BIG_INTEGER_UNSIGNED@,
164
165	RecipientData		TEXT,  /* Policy state information */
166
167	UNIQUE (Instance)
168)  @CREATE_TABLE_SUFFIX@;
169CREATE INDEX session_tracking_idx1 ON session_tracking (QueueID,ClientAddress,Sender);
170CREATE INDEX session_tracking_idx2 ON session_tracking (Timestamp);
171
172
173
174