Chetan Gaonker | 7f4bf74 | 2016-05-04 15:56:08 -0700 | [diff] [blame] | 1 | /* |
| 2 | * $Id: 5ab9d29306cbef460fe310aafd5d2046267cbbdc $ |
| 3 | * |
| 4 | * Postgresql schema for FreeRADIUS |
| 5 | * |
| 6 | * All field lengths need checking as some are still suboptimal. -pnixon 2003-07-13 |
| 7 | * |
| 8 | */ |
| 9 | |
| 10 | /* |
| 11 | * Table structure for table 'radacct' |
| 12 | * |
| 13 | * Note: Column type bigserial does not exist prior to Postgres 7.2 |
| 14 | * If you run an older version you need to change this to serial |
| 15 | */ |
| 16 | CREATE TABLE radacct ( |
| 17 | RadAcctId bigserial PRIMARY KEY, |
| 18 | AcctSessionId text NOT NULL, |
| 19 | AcctUniqueId text NOT NULL UNIQUE, |
| 20 | UserName text, |
| 21 | GroupName text, |
| 22 | Realm text, |
| 23 | NASIPAddress inet NOT NULL, |
| 24 | NASPortId text, |
| 25 | NASPortType text, |
| 26 | AcctStartTime timestamp with time zone, |
| 27 | AcctUpdateTime timestamp with time zone, |
| 28 | AcctStopTime timestamp with time zone, |
| 29 | AcctInterval bigint, |
| 30 | AcctSessionTime bigint, |
| 31 | AcctAuthentic text, |
| 32 | ConnectInfo_start text, |
| 33 | ConnectInfo_stop text, |
| 34 | AcctInputOctets bigint, |
| 35 | AcctOutputOctets bigint, |
| 36 | CalledStationId text, |
| 37 | CallingStationId text, |
| 38 | AcctTerminateCause text, |
| 39 | ServiceType text, |
| 40 | FramedProtocol text, |
| 41 | FramedIPAddress inet |
| 42 | ); |
| 43 | -- This index may be useful.. |
| 44 | -- CREATE UNIQUE INDEX radacct_whoson on radacct (AcctStartTime, nasipaddress); |
| 45 | |
| 46 | -- For use by update-, stop- and simul_* queries |
| 47 | CREATE INDEX radacct_active_user_idx ON radacct (AcctSessionId, UserName, NASIPAddress) WHERE AcctStopTime IS NULL; |
| 48 | |
| 49 | -- For use by onoff- |
| 50 | create INDEX radacct_bulk_close ON radacct (NASIPAddress, AcctStartTime) WHERE AcctStopTime IS NULL; |
| 51 | |
| 52 | -- and for common statistic queries: |
| 53 | CREATE INDEX radacct_start_user_idx ON radacct (AcctStartTime, UserName); |
| 54 | |
| 55 | -- and, optionally |
| 56 | -- CREATE INDEX radacct_stop_user_idx ON radacct (acctStopTime, UserName); |
| 57 | |
| 58 | /* |
| 59 | * There was WAAAY too many indexes previously. This combo index |
| 60 | * should take care of the most common searches. |
| 61 | * I have commented out all the old indexes, but left them in case |
| 62 | * someone wants them. I don't recomend anywone use them all at once |
| 63 | * as they will slow down your DB too much. |
| 64 | * - pnixon 2003-07-13 |
| 65 | */ |
| 66 | |
| 67 | /* |
| 68 | * create index radacct_UserName on radacct (UserName); |
| 69 | * create index radacct_AcctSessionId on radacct (AcctSessionId); |
| 70 | * create index radacct_AcctUniqueId on radacct (AcctUniqueId); |
| 71 | * create index radacct_FramedIPAddress on radacct (FramedIPAddress); |
| 72 | * create index radacct_NASIPAddress on radacct (NASIPAddress); |
| 73 | * create index radacct_AcctStartTime on radacct (AcctStartTime); |
| 74 | * create index radacct_AcctStopTime on radacct (AcctStopTime); |
| 75 | */ |
| 76 | |
| 77 | |
| 78 | |
| 79 | /* |
| 80 | * Table structure for table 'radcheck' |
| 81 | */ |
| 82 | CREATE TABLE radcheck ( |
| 83 | id serial PRIMARY KEY, |
| 84 | UserName text NOT NULL DEFAULT '', |
| 85 | Attribute text NOT NULL DEFAULT '', |
| 86 | op VARCHAR(2) NOT NULL DEFAULT '==', |
| 87 | Value text NOT NULL DEFAULT '' |
| 88 | ); |
| 89 | create index radcheck_UserName on radcheck (UserName,Attribute); |
| 90 | /* |
| 91 | * Use this index if you use case insensitive queries |
| 92 | */ |
| 93 | -- create index radcheck_UserName_lower on radcheck (lower(UserName),Attribute); |
| 94 | |
| 95 | /* |
| 96 | * Table structure for table 'radgroupcheck' |
| 97 | */ |
| 98 | CREATE TABLE radgroupcheck ( |
| 99 | id serial PRIMARY KEY, |
| 100 | GroupName text NOT NULL DEFAULT '', |
| 101 | Attribute text NOT NULL DEFAULT '', |
| 102 | op VARCHAR(2) NOT NULL DEFAULT '==', |
| 103 | Value text NOT NULL DEFAULT '' |
| 104 | ); |
| 105 | create index radgroupcheck_GroupName on radgroupcheck (GroupName,Attribute); |
| 106 | |
| 107 | /* |
| 108 | * Table structure for table 'radgroupreply' |
| 109 | */ |
| 110 | CREATE TABLE radgroupreply ( |
| 111 | id serial PRIMARY KEY, |
| 112 | GroupName text NOT NULL DEFAULT '', |
| 113 | Attribute text NOT NULL DEFAULT '', |
| 114 | op VARCHAR(2) NOT NULL DEFAULT '=', |
| 115 | Value text NOT NULL DEFAULT '' |
| 116 | ); |
| 117 | create index radgroupreply_GroupName on radgroupreply (GroupName,Attribute); |
| 118 | |
| 119 | /* |
| 120 | * Table structure for table 'radreply' |
| 121 | */ |
| 122 | CREATE TABLE radreply ( |
| 123 | id serial PRIMARY KEY, |
| 124 | UserName text NOT NULL DEFAULT '', |
| 125 | Attribute text NOT NULL DEFAULT '', |
| 126 | op VARCHAR(2) NOT NULL DEFAULT '=', |
| 127 | Value text NOT NULL DEFAULT '' |
| 128 | ); |
| 129 | create index radreply_UserName on radreply (UserName,Attribute); |
| 130 | /* |
| 131 | * Use this index if you use case insensitive queries |
| 132 | */ |
| 133 | -- create index radreply_UserName_lower on radreply (lower(UserName),Attribute); |
| 134 | |
| 135 | /* |
| 136 | * Table structure for table 'radusergroup' |
| 137 | */ |
| 138 | CREATE TABLE radusergroup ( |
| 139 | id serial PRIMARY KEY, |
| 140 | UserName text NOT NULL DEFAULT '', |
| 141 | GroupName text NOT NULL DEFAULT '', |
| 142 | priority integer NOT NULL DEFAULT 0 |
| 143 | ); |
| 144 | create index radusergroup_UserName on radusergroup (UserName); |
| 145 | /* |
| 146 | * Use this index if you use case insensitive queries |
| 147 | */ |
| 148 | -- create index radusergroup_UserName_lower on radusergroup (lower(UserName)); |
| 149 | |
| 150 | -- |
| 151 | -- Table structure for table 'radpostauth' |
| 152 | -- |
| 153 | |
| 154 | CREATE TABLE radpostauth ( |
| 155 | id bigserial PRIMARY KEY, |
| 156 | username text NOT NULL, |
| 157 | pass text, |
| 158 | reply text, |
| 159 | CalledStationId text, |
| 160 | CallingStationId text, |
| 161 | authdate timestamp with time zone NOT NULL default now() |
| 162 | ); |
| 163 | |
| 164 | /* |
| 165 | * Table structure for table 'nas' |
| 166 | */ |
| 167 | CREATE TABLE nas ( |
| 168 | id serial PRIMARY KEY, |
| 169 | nasname text NOT NULL, |
| 170 | shortname text NOT NULL, |
| 171 | type text NOT NULL DEFAULT 'other', |
| 172 | ports integer, |
| 173 | secret text NOT NULL, |
| 174 | server text, |
| 175 | community text, |
| 176 | description text |
| 177 | ); |
| 178 | create index nas_nasname on nas (nasname); |