1#@!#!123s

��JFIF�� 1#@!#!123s
: / usr / share / doc / cluebringer-v2.1.x / database /
Filename : core.tsql
back
# Core schema
# Copyright (C) 2009-2011, AllWorldIT
# Copyright (C) 2008, LinuxRulz
# 
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
# 
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
# 
# You should have received a copy of the GNU General Public License along
# with this program; if not, write to the Free Software Foundation, Inc.,
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.

@PRELOAD@


/* 
Priorities...
	0      - System policy priority (fallthrough)
	1-50   - System policies
	50-100 - Custom policies
*/

/* Policies */
CREATE TABLE @PREFIX@policies (
	ID			@SERIAL_TYPE@,

	Name			VARCHAR(255) NOT NULL,

	Priority		SMALLINT NOT NULL,

	Description		TEXT,

	Disabled		SMALLINT NOT NULL DEFAULT '0'

) @CREATE_TABLE_SUFFIX@;

INSERT INTO @PREFIX@policies (Name,Priority,Description) VALUES ('Default',0,'Default System Policy');
INSERT INTO @PREFIX@policies (Name,Priority,Description) VALUES ('Default Outbound',10,'Default Outbound System Policy');
INSERT INTO @PREFIX@policies (Name,Priority,Description) VALUES ('Default Inbound',10,'Default Inbound System Policy');
INSERT INTO @PREFIX@policies (Name,Priority,Description) VALUES ('Default Internal',20,'Default Internal System Policy');
INSERT INTO @PREFIX@policies (Name,Priority,Description) VALUES ('Test',50,'Test policy');


/* Member list for policies */
CREATE TABLE @PREFIX@policy_members (
	ID			@SERIAL_TYPE@,

	PolicyID		@SERIAL_REF_TYPE@,

	/* 
		Format of key: 
		NULL = any
		a.b.c.d/e = IP address with optional /e
		@domain = domain specification, 
		%xyz = xyz group, 
		abc@domain = abc user specification

		all options support negation using !<key>
	*/
	Source			TEXT,
	Destination		TEXT,

	Comment			VARCHAR(1024),

	Disabled		SMALLINT NOT NULL DEFAULT '0',

	FOREIGN KEY (PolicyID) REFERENCES @PREFIX@policies(ID)
) @CREATE_TABLE_SUFFIX@;


/* Default System Policy */
INSERT INTO @PREFIX@policy_members (PolicyID,Source,Destination) VALUES
	(1,NULL,NULL);
/* Default Outbound System Policy */
INSERT INTO @PREFIX@policy_members (PolicyID,Source,Destination) VALUES
	(2,'%internal_ips,%internal_domains','!%internal_domains');
/* Default Inbound System Policy */
INSERT INTO @PREFIX@policy_members (PolicyID,Source,Destination) VALUES
	(3,'!%internal_ips,!%internal_domains','%internal_domains');
/* Default Internal System Policy */
INSERT INTO @PREFIX@policy_members (PolicyID,Source,Destination) VALUES
	(4,'%internal_ips,%internal_domains','%internal_domains');
/* Test Policy */
INSERT INTO @PREFIX@policy_members (PolicyID,Source,Destination) VALUES
	(5,'@example.net',NULL);



/* Groups usable in ACL */
CREATE TABLE @PREFIX@policy_groups (
	ID			@SERIAL_TYPE@,

	Name			VARCHAR(255) NOT NULL,


	Disabled		SMALLINT NOT NULL DEFAULT '0',

	Comment			VARCHAR(1024),


	UNIQUE (Name)
)  @CREATE_TABLE_SUFFIX@;

INSERT INTO @PREFIX@policy_groups (Name) VALUES ('internal_ips');
INSERT INTO @PREFIX@policy_groups (Name) VALUES ('internal_domains');



/* Group members */
CREATE TABLE @PREFIX@policy_group_members (
	ID			@SERIAL_TYPE@,

	PolicyGroupID		@SERIAL_REF_TYPE@,

	/* Format of member: a.b.c.d/e = ip,  @domain = domain, %xyz = xyz group, abc@domain = abc user */
	Member			VARCHAR(255) NOT NULL,
	

	Disabled		SMALLINT NOT NULL DEFAULT '0',
	Comment			VARCHAR(1024),


	FOREIGN KEY (PolicyGroupID) REFERENCES @PREFIX@policy_groups(ID)
)  @CREATE_TABLE_SUFFIX@;

INSERT INTO @PREFIX@policy_group_members (PolicyGroupID,Member) VALUES (1,'10.0.0.0/8');
INSERT INTO @PREFIX@policy_group_members (PolicyGroupID,Member) VALUES (2,'@example.org');
INSERT INTO @PREFIX@policy_group_members (PolicyGroupID,Member) VALUES (2,'@example.com');



/* Message session tracking */
CREATE TABLE @PREFIX@session_tracking (
	Instance		VARCHAR(255),
	QueueID			VARCHAR(255),

	UnixTimestamp		BIGINT NOT NULL,

	ClientAddress		VARCHAR(64),
	ClientName		VARCHAR(255),
	ClientReverseName	VARCHAR(255),

	Protocol		VARCHAR(255),

	EncryptionProtocol	VARCHAR(255),
	EncryptionCipher	VARCHAR(255),
	EncryptionKeySize	VARCHAR(255),

	SASLMethod		VARCHAR(255),
	SASLSender		VARCHAR(255),
	SASLUsername		VARCHAR(255),

	Helo			VARCHAR(255),

	Sender			VARCHAR(255),

	Size			@BIG_INTEGER_UNSIGNED@,

	RecipientData		TEXT,  /* Policy state information */

	UNIQUE (Instance)
)  @CREATE_TABLE_SUFFIX@;
CREATE INDEX @PREFIX@session_tracking_idx1 ON @PREFIX@session_tracking (QueueID,ClientAddress,Sender);
CREATE INDEX @PREFIX@session_tracking_idx2 ON @PREFIX@session_tracking (UnixTimestamp);