doc/examples/postgresql.sql

   1 /*
   2  * $Id$
   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           VARCHAR(32) NOT NULL,
  19         AcctUniqueId            VARCHAR(32) NOT NULL,
  20         UserName                VARCHAR(253),
  21         Realm                   VARCHAR(64),
  22         NASIPAddress            INET NOT NULL,
  23         NASPortId               VARCHAR(15),
  24         NASPortType             VARCHAR(32),
  25         AcctStartTime           TIMESTAMP with time zone,
  26         AcctStopTime            TIMESTAMP with time zone,
  27         AcctSessionTime         BIGINT,
  28         AcctAuthentic           VARCHAR(32),
  29         ConnectInfo_start       VARCHAR(50),
  30         ConnectInfo_stop        VARCHAR(50),
  31         AcctInputOctets         BIGINT,
  32         AcctOutputOctets        BIGINT,
  33         CalledStationId         VARCHAR(50),
  34         CallingStationId        VARCHAR(50),
  35         AcctTerminateCause      VARCHAR(32),
  36         ServiceType             VARCHAR(32),
  37         FramedProtocol          VARCHAR(32),
  38         FramedIPAddress         INET,
  39         AcctStartDelay          BIGINT,
  40         AcctStopDelay           BIGINT
  41 );
  42 -- This index may be usefull..
  43 -- CREATE UNIQUE INDEX radacct_whoson on radacct (AcctStartTime, nasipaddress);
  44
  45 -- For use by onoff-, update-, stop- and simul_* queries
  46 CREATE INDEX radacct_active_user_idx ON radacct (userName) WHERE AcctStopTime IS NULL;
  47 -- and for common statistic queries:
  48 CREATE INDEX radacct_start_user_idx ON radacct (acctStartTime, UserName);
  49 -- and, optionally
  50 -- CREATE INDEX radacct_stop_user_idx ON radacct (acctStopTime, UserName);
  51
  52 /*
  53  * There was WAAAY too many indexes previously. This combo index
  54  * should take care of the most common searches.
  55  * I have commented out all the old indexes, but left them in case
  56  * someone wants them. I don't recomend anywone use them all at once
  57  * as they will slow down your DB too much.
  58  *  - pnixon 2003-07-13
  59  */
  60
  61 /*
  62  * create index radacct_UserName on radacct (UserName);
  63  * create index radacct_AcctSessionId on radacct (AcctSessionId);
  64  * create index radacct_AcctUniqueId on radacct (AcctUniqueId);
  65  * create index radacct_FramedIPAddress on radacct (FramedIPAddress);
  66  * create index radacct_NASIPAddress on radacct (NASIPAddress);
  67  * create index radacct_AcctStartTime on radacct (AcctStartTime);
  68  * create index radacct_AcctStopTime on radacct (AcctStopTime);
  69 */
  70
  71
  72
  73 /*
  74  * Table structure for table 'radcheck'
  75  */
  76 CREATE TABLE radcheck (
  77         id              SERIAL PRIMARY KEY,
  78         UserName        VARCHAR(64) NOT NULL DEFAULT '',
  79         Attribute       VARCHAR(64) NOT NULL DEFAULT '',
  80         op              VARCHAR(2) NOT NULL DEFAULT '==',
  81         Value           VARCHAR(253) NOT NULL DEFAULT ''
  82 );
  83 create index radcheck_UserName on radcheck (UserName,Attribute);
  84 /*
  85  * Use this index if you use case insensitive queries
  86  */
  87 -- create index radcheck_UserName_lower on radcheck (lower(UserName),Attribute);
  88
  89 /*
  90  * Table structure for table 'radgroupcheck'
  91  */
  92 CREATE TABLE radgroupcheck (
  93         id              SERIAL PRIMARY KEY,
  94         GroupName       VARCHAR(64) NOT NULL DEFAULT '',
  95         Attribute       VARCHAR(64) NOT NULL DEFAULT '',
  96         op              VARCHAR(2) NOT NULL DEFAULT '==',
  97         Value           VARCHAR(253) NOT NULL DEFAULT ''
  98 );
  99 create index radgroupcheck_GroupName on radgroupcheck (GroupName,Attribute);
 100
 101 /*
 102  * Table structure for table 'radgroupreply'
 103  */
 104 CREATE TABLE radgroupreply (
 105         id              SERIAL PRIMARY KEY,
 106         GroupName       VARCHAR(64) NOT NULL DEFAULT '',
 107         Attribute       VARCHAR(64) NOT NULL DEFAULT '',
 108         op              VARCHAR(2) NOT NULL DEFAULT '=',
 109         Value           VARCHAR(253) NOT NULL DEFAULT ''
 110 );
 111 create index radgroupreply_GroupName on radgroupreply (GroupName,Attribute);
 112
 113 /*
 114  * Table structure for table 'radreply'
 115  */
 116 CREATE TABLE radreply (
 117         id              SERIAL PRIMARY KEY,
 118         UserName        VARCHAR(64) NOT NULL DEFAULT '',
 119         Attribute       VARCHAR(64) NOT NULL DEFAULT '',
 120         op              VARCHAR(2) NOT NULL DEFAULT '=',
 121         Value           VARCHAR(253) NOT NULL DEFAULT ''
 122 );
 123 create index radreply_UserName on radreply (UserName,Attribute);
 124 /*
 125  * Use this index if you use case insensitive queries
 126  */
 127 -- create index radreply_UserName_lower on radreply (lower(UserName),Attribute);
 128
 129 /*
 130  * Table structure for table 'usergroup'
 131  */
 132 CREATE TABLE usergroup (
 133         UserName        VARCHAR(64) NOT NULL DEFAULT '',
 134         GroupName       VARCHAR(64) NOT NULL DEFAULT '',
 135         priority        INTEGER NOT NULL DEFAULT 0
 136 );
 137 create index usergroup_UserName on usergroup (UserName);
 138 /*
 139  * Use this index if you use case insensitive queries
 140  */
 141 -- create index usergroup_UserName_lower on usergroup (lower(UserName));
 142
 143 /*
 144  * Table structure for table 'realmgroup'
 145  * Commented out because currently not used
 146  */
 147 --CREATE TABLE realmgroup (
 148 --      id              SERIAL PRIMARY KEY,
 149 --      RealmName       VARCHAR(30) DEFAULT '' NOT NULL,
 150 --      GroupName       VARCHAR(30)
 151 --);
 152 --create index realmgroup_RealmName on realmgroup (RealmName);
 153
 154 /*
 155  * Table structure for table 'realms'
 156  * This is not yet used by FreeRADIUS
 157  */
 158 --CREATE TABLE realms (
 159 --      id              SERIAL PRIMARY KEY,
 160 --      realmname       VARCHAR(64),
 161 --      nas             VARCHAR(128),
 162 --      authport        int4,
 163 --      options         VARCHAR(128) DEFAULT ''
 164 --);
 165
 166 /*
 167  * Table structure for table 'nas'
 168  */
 169 CREATE TABLE nas (
 170         id              SERIAL PRIMARY KEY,
 171         nasname         VARCHAR(128) NOT NULL,
 172         shortname       VARCHAR(32) NOT NULL,
 173         type            VARCHAR(30) NOT NULL DEFAULT 'other',
 174         ports           int4,
 175         secret          VARCHAR(60) NOT NULL,
 176         community       VARCHAR(50),
 177         description     VARCHAR(200)
 178 );
 179 create index nas_nasname on nas (nasname);
 180
 181 --
 182 -- Table structure for table 'radpostauth'
 183 --
 184
 185 CREATE TABLE radpostauth (
 186         id              BIGSERIAL PRIMARY KEY,
 187         username        VARCHAR(253) NOT NULL,
 188         pass            VARCHAR(128),
 189         reply           VARCHAR(32),
 190         authdate        TIMESTAMP with time zone NOT NULL default 'now'
 191 ) ;
 192
 193 CREATE TABLE radippool (
 194         id serial NOT NULL,
 195         pool_name text NOT NULL,
 196         ip_address inet,
 197         nas_ip_address text NOT NULL,
 198         nas_port integer NOT NULL,
 199         calling_station_id text DEFAULT ''::text NOT NULL,
 200         expiry_time timestamp(0) without time zone NOT NULL,
 201         username text DEFAULT ''::text,
 202         calledstationid character varying(64),
 203         poolkey character varying(120)
 204 );
 205
 206 --
 207 -- Table structure for table 'dictionary'
 208 -- This is not currently used by FreeRADIUS
 209 --
 210 -- CREATE TABLE dictionary (
 211 --     id              SERIAL PRIMARY KEY,
 212 --     Type            VARCHAR(30),
 213 --     Attribute       VARCHAR(64),
 214 --     Value           VARCHAR(64),
 215 --     Format          VARCHAR(20),
 216 --     Vendor          VARCHAR(32)
 217 -- );
 218
 219 /*
 220  * Note: (pnixon: 2003-12-10) The following function should not be required
 221  * if you use the PG specific queries in raddb/postgresql.conf
 222  *
 223  * Common utility function for date calculations. This is used in our
 224  * alternative account stop query to calculate the start of a session.
 225  *
 226  * This function is Copyright 2001 by Mark Steele (msteele@inet-interactif.com)
 227  *
 228  * Please note that this requires the plpgsql to be available in your
 229  * radius database. If it is not available you can register it with
 230  * postgres by running this command:
 231  *
 232  *   createlang plpgsql <databasename>
 233  */
 234 CREATE FUNCTION DATE_SUB(date,int4,text) RETURNS DATE AS '
 235 DECLARE
 236         var1 date;
 237         var2 text;
 238 BEGIN
 239         var2 = $2 || '' '' || $3;
 240         SELECT INTO var1
 241                 to_date($1 - var2::interval, ''YYYY-MM-DD'');
 242 RETURN var1;
 243 END;' LANGUAGE 'plpgsql';