/etc/freeradius/sql/postgresql/schema.sql is in freeradius-postgresql 2.2.8+dfsg-0.1build2.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
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 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 | /*
* $Id$
*
* Postgresql schema for FreeRADIUS
*
* All field lengths need checking as some are still suboptimal. -pnixon 2003-07-13
*
*/
/*
* Table structure for table 'radacct'
*
* Note: Column type BIGSERIAL does not exist prior to Postgres 7.2
* If you run an older version you need to change this to SERIAL
*/
CREATE TABLE radacct (
RadAcctId BIGSERIAL PRIMARY KEY,
AcctSessionId VARCHAR(64) NOT NULL,
AcctUniqueId VARCHAR(32) NOT NULL UNIQUE,
UserName VARCHAR(253),
GroupName VARCHAR(253),
Realm VARCHAR(64),
NASIPAddress INET NOT NULL,
NASPortId VARCHAR(15),
NASPortType VARCHAR(32),
AcctStartTime TIMESTAMP with time zone,
AcctStopTime TIMESTAMP with time zone,
AcctSessionTime BIGINT,
AcctAuthentic VARCHAR(32),
ConnectInfo_start VARCHAR(50),
ConnectInfo_stop VARCHAR(50),
AcctInputOctets BIGINT,
AcctOutputOctets BIGINT,
CalledStationId VARCHAR(50),
CallingStationId VARCHAR(50),
AcctTerminateCause VARCHAR(32),
ServiceType VARCHAR(32),
XAscendSessionSvrKey VARCHAR(10),
FramedProtocol VARCHAR(32),
FramedIPAddress INET,
AcctStartDelay INTEGER,
AcctStopDelay INTEGER
);
-- This index may be usefull..
-- CREATE UNIQUE INDEX radacct_whoson on radacct (AcctStartTime, nasipaddress);
-- For use by onoff-, update-, stop- and simul_* queries
CREATE INDEX radacct_active_user_idx ON radacct (UserName, NASIPAddress, AcctSessionId) WHERE AcctStopTime IS NULL;
-- and for common statistic queries:
CREATE INDEX radacct_start_user_idx ON radacct (AcctStartTime, UserName);
-- and, optionally
-- CREATE INDEX radacct_stop_user_idx ON radacct (acctStopTime, UserName);
/*
* There was WAAAY too many indexes previously. This combo index
* should take care of the most common searches.
* I have commented out all the old indexes, but left them in case
* someone wants them. I don't recomend anywone use them all at once
* as they will slow down your DB too much.
* - pnixon 2003-07-13
*/
/*
* create index radacct_UserName on radacct (UserName);
* create index radacct_AcctSessionId on radacct (AcctSessionId);
* create index radacct_AcctUniqueId on radacct (AcctUniqueId);
* create index radacct_FramedIPAddress on radacct (FramedIPAddress);
* create index radacct_NASIPAddress on radacct (NASIPAddress);
* create index radacct_AcctStartTime on radacct (AcctStartTime);
* create index radacct_AcctStopTime on radacct (AcctStopTime);
*/
/*
* Table structure for table 'radcheck'
*/
CREATE TABLE radcheck (
id SERIAL PRIMARY KEY,
UserName VARCHAR(64) NOT NULL DEFAULT '',
Attribute VARCHAR(64) NOT NULL DEFAULT '',
op CHAR(2) NOT NULL DEFAULT '==',
Value VARCHAR(253) NOT NULL DEFAULT ''
);
create index radcheck_UserName on radcheck (UserName,Attribute);
/*
* Use this index if you use case insensitive queries
*/
-- create index radcheck_UserName_lower on radcheck (lower(UserName),Attribute);
/*
* Table structure for table 'radgroupcheck'
*/
CREATE TABLE radgroupcheck (
id SERIAL PRIMARY KEY,
GroupName VARCHAR(64) NOT NULL DEFAULT '',
Attribute VARCHAR(64) NOT NULL DEFAULT '',
op CHAR(2) NOT NULL DEFAULT '==',
Value VARCHAR(253) NOT NULL DEFAULT ''
);
create index radgroupcheck_GroupName on radgroupcheck (GroupName,Attribute);
/*
* Table structure for table 'radgroupreply'
*/
CREATE TABLE radgroupreply (
id SERIAL PRIMARY KEY,
GroupName VARCHAR(64) NOT NULL DEFAULT '',
Attribute VARCHAR(64) NOT NULL DEFAULT '',
op CHAR(2) NOT NULL DEFAULT '=',
Value VARCHAR(253) NOT NULL DEFAULT ''
);
create index radgroupreply_GroupName on radgroupreply (GroupName,Attribute);
/*
* Table structure for table 'radreply'
*/
CREATE TABLE radreply (
id SERIAL PRIMARY KEY,
UserName VARCHAR(64) NOT NULL DEFAULT '',
Attribute VARCHAR(64) NOT NULL DEFAULT '',
op CHAR(2) NOT NULL DEFAULT '=',
Value VARCHAR(253) NOT NULL DEFAULT ''
);
create index radreply_UserName on radreply (UserName,Attribute);
/*
* Use this index if you use case insensitive queries
*/
-- create index radreply_UserName_lower on radreply (lower(UserName),Attribute);
/*
* Table structure for table 'radusergroup'
*/
CREATE TABLE radusergroup (
UserName VARCHAR(64) NOT NULL DEFAULT '',
GroupName VARCHAR(64) NOT NULL DEFAULT '',
priority INTEGER NOT NULL DEFAULT 0
);
create index radusergroup_UserName on radusergroup (UserName);
/*
* Use this index if you use case insensitive queries
*/
-- create index radusergroup_UserName_lower on radusergroup (lower(UserName));
/*
* Table structure for table 'realmgroup'
* Commented out because currently not used
*/
--CREATE TABLE realmgroup (
-- id SERIAL PRIMARY KEY,
-- RealmName VARCHAR(30) DEFAULT '' NOT NULL,
-- GroupName VARCHAR(30)
--);
--create index realmgroup_RealmName on realmgroup (RealmName);
/*
* Table structure for table 'realms'
* This is not yet used by FreeRADIUS
*/
--CREATE TABLE realms (
-- id SERIAL PRIMARY KEY,
-- realmname VARCHAR(64),
-- nas VARCHAR(128),
-- authport int4,
-- options VARCHAR(128) DEFAULT ''
--);
--
-- Table structure for table 'radpostauth'
--
CREATE TABLE radpostauth (
id BIGSERIAL PRIMARY KEY,
username VARCHAR(253) NOT NULL,
pass VARCHAR(128),
reply VARCHAR(32),
CalledStationId VARCHAR(50),
CallingStationId VARCHAR(50),
authdate TIMESTAMP with time zone NOT NULL default 'now()'
);
|