-- Example OpenRADIUS schema for Postgres. Run as a superuser or 
-- as the unix user that has full access to the database.
--
-- 2004/08/08 - Created based on schema.mysql, EvB

-- Create owner of the database and a limited access user for the RADIUS server

create user "openradius" nocreatedb nocreateuser password 'openradius';
create user "radiusd"    nocreatedb nocreateuser password 'radiusd';

-- Create database and switch to it as the database owner

create database "openradius" with encoding = 'latin1';
\c openradius openradius


-- Data table containing clients, users, groups, realms, hints, huntgroups,
-- whatnot; everything that adds attributes, whether for use in subsequent
-- queries, for checking or for use in the reply.

create table data (
    id serial primary key,
    space varchar(8) not null,
    name varchar(64) not null,

    attribute varchar(64) not null,
    value varchar(255),

    groupname varchar(64),
    comment varchar(255)
);

create index data_space_name on data (space, name);


-- Logging table

create table log (
    log_id serial primary key,
    log_when timestamp,
    log_who varchar(64),
    log_what varchar(255)
);


-- Accounting table
--
-- 1. To detect duplicates in Stop records (the only ones you need for metered
-- billing) while recording them, lock the accounting table and check that the
-- query
--
-- select acct_id 
--   from accounting 
--  where acct_nas = ? 
--    and acct_status_type = ? 
--    and acct_sessionid = ? 
--    and acct_timestamp + <dupinterval> >= now()
--
-- does not return any records before inserting. The theory behind this is that
-- although Acct-Session-Id may collide among NASes and a single NAS may reuse
-- the same values quickly, a single NAS' values should be unique over the
-- period during which duplicates can be expected.
--
-- This period is at most max_retrans_count * timeout, which is typically
-- below one minute. Any simplistic NAS' values for Acct-Session-Id should be
-- unique within such a short timespan. Even a simple counter that's reset at
-- reboot will do as session id that way.
--
-- So, we can safely conclude a record is a duplicate -- erring on the safe
-- side, i.e. in favour of dropping the record -- if the same NAS generated the
-- same Acct-Session-Id in the last minute or two. False negatives are only
-- possible if a NAS uses different Session-Ids for the same session, which is
-- forbidden by RFC2866.
--
-- 2. To match Stop records to Start records (not very useful in most cases, as
-- only few broken NASes send information in the Start record that is not
-- repeated in the Stop record, and for 'sessions in progress' it's probably
-- better to use a separate session table; see below), I'd advise to lock the
-- table and use the following statement; do the (standard) insert only if the
-- update affected no rows.
--
-- update accounting 
--    set acct_status_type = 'Stop',
--        acct_session_time = ?,
--        acct_input_octets = ?,
--        acct_output_octets = ?,
--        acct_input_packets = ?,
--        acct_output_packets = ?,
--  where acct_nas = ?
--    and acct_sessionid = ?
--    and acct_timestamp + ? + 600 >= now()  -- acct_session_time as bind var
--    and acct_timestamp + ? <= now() + 600  -- ditto
--    and acct_status_type = 'Start'
--    and user_name = ?			     -- optional
--    and nas_port = ?			     -- optional
--
-- The idea is to look back in time, 10 minutes on either side around now() -
-- session time, for a start record from the same NAS with the same session id.
-- A few extra safety checks prevent overeager matching for NASes that are 
-- broken enough to reuse the same session id for a start record within 10
-- minutes. Be sure to only use information for this that is known in both the
-- Start- and Stop records; some NASes may not send eg. Framed-IP-Address in
-- start records, so don't use that.

create table accounting (
    acct_id serial8 primary key,

    acct_nas varchar(32),		    -- NAS-IP-Address or NAS-Identifier
    acct_session_id varchar(32),
    acct_timestamp timestamp,

    user_name varchar(64),
    nas_ip_address varchar(16),
    nas_port varchar(16),
    service_type varchar(16),
    framed_protocol varchar(16),
    framed_ip_address varchar(16),
    framed_ip_netmask varchar(16),
    login_ip_host varchar(16),
    login_service varchar(16),
    login_tcp_port integer,
    class varchar(16),
    called_station_id varchar(64),
    calling_station_id varchar(64),
    nas_identifier varchar(64),
    nas_port_type varchar(16),
    port_limit integer,

    acct_status_type varchar(16),
    acct_input_octets integer,
    acct_output_octets integer,
    acct_session_time integer,
    acct_input_packets integer,
    acct_output_packets integer,
    acct_terminate_cause varchar(16),
    acct_multi_session_id varchar(64),
    acct_link_count integer
);

-- This index is used for real time duplicate stripping. Note that it also 
-- works if multiple RADIUS servers are writing into the same database,
-- which is the advantage of using the database to do duplicate removal.

create unique index accounting_nasstatsesstime on accounting (
    acct_nas, 
    acct_status_type,
    acct_session_id, 
    acct_timestamp
);


-- Session table
--
-- This is useful to track open sessions, for debugging purposes and to limit
-- concurrent access for users, calling stations, area codes, realms, anything.
--
-- It works as follows. Whenever an Access-Accept is sent, we insert a new
-- record in the sessions table and put the new record's ID in a HMAC-protected
-- Class attribute, so we can retrieve the record at accounting time. It also
-- allows us to verify that we only get accounting records for sessions that
-- we actually authenticated, to keep honest downstream providers honest.
--
-- Other than its ID, the newly inserted record contains the values of the 
-- Session-Timeout and Acct-Interim-Interval response attributes, an expiry
-- time (initially now + 120 seconds), a confirmed flag (initially false),
-- a NAS identifier. In addition, you can keep any other information you need
-- in order to count concurrent sessions for a user, for a realm, for a 
-- realm/NAS combination, or whatever criteria you choose.
--
-- When an Accounting-Request comes in, duplicates are filtered, the 
-- authenticator, Message-Authenticator and Class HMAC are checked, and the 
-- record is logged, including the verification status, as normal.
-- 
-- Then, the sessions table is updated, as follows:
--
-- If the Accounting-Request is an Accounting-Start, the expiry value is
-- set to now + 120 seconds + the stored value of Session-Timeout.
-- 
-- If the Accounting-Request is an Accounting-Update, the expiry value is
-- set to now + 120 seconds + twice the stored value of Acct-Interim-Interval.
--
-- If the Accounting-Request is an Accounting-Stop, the expiry value is 
-- set to now.
--
-- If the Accounting-Request is an Accounting-Off or Accounting-On, the expiry 
-- values of all sessions associated with the sending NAS are set to now.
--
-- A session may have a 'check' statement associated with it that can 
-- be used to verify the actual status using an external module. Most likely, 
-- you'll want this string to contain the NAS type and the NAS IP address 
-- in some way, so that your checking module can do the right thing.
--
-- Note: If your NAS doesn't implement Class, but it does implement sending
-- Acct-Session-Id at authentication time, you can use that together with the
-- NAS Identifier to find the session. It's less reliable and less secure 
-- though, as you can't authenticate accounting records and you depend on the
-- NAS to keep Acct-Session-Id unique over the lifetime of each session.

create table sessions (

    id serial primary key,		-- not related to Acct-Session-Id

    -- Basic functions

    nas varchar(32),			-- NAS-IP-Address or NAS-Identifier
    session_timeout integer,
    interim_interval integer,
    confirmed integer,
    expiry timestamp,		

    session_check varchar(255),

    -- Counting criteria, must be present in Access-Request

    user_name varchar(64),
    calling_station_id varchar(64),

    -- For convenience; allows you to get at information in last acct. req.

    last_acct_id bigint
);

create index sessions_expiry on sessions (expiry);
create index sessions_user on sessions (user_name);
create index sessions_calling_station_id on sessions (calling_station_id);


-- Check items table à la FreeRADIUS' radcheck table. Not used by default.
--
--create table radcheck (
--    radcheck_id serial primary key,
--    space varchar(8) not null,
--    name varchar(64) not null,
--
--    attribute varchar(64) not null,
--    op varchar(2),
--    value varchar(255)
--);
--
--create index radcheck_space_name on radcheck (space, name);


-- Access rights for the RADIUS server 

grant select on data to radiusd;

grant insert on log to radiusd;
grant update on log_log_id_seq to radiusd;

grant select, insert, update on accounting to radiusd;

grant select, update on accounting_acct_id_seq to radiusd;

# The select right on the sequence is important, otherwise the 'last_insert_id'
# feature will return quite a cryptic message.

grant select, insert, update, delete on sessions to radiusd;
grant select, update on sessions_session_id_seq to radiusd;


-- Create some test data

insert into data (space, name, attribute, value) 
    values ('clients', '127.0.0.1', 'Secret', 'h1dd3n');

insert into data (space, name, attribute, value)
    values ('users', 'evbergen', 'clear-password', 'welcome1');
insert into data (space, name, attribute, value)
    values ('users', 'evbergen', 'Framed-IP-Address', '172.31.1.1');
insert into data (space, name, attribute, value)
    values ('users', 'evbergen', 'Service-Type', 'Framed');

insert into data (space,name,attribute,value) 
    values ('users', 'yvonne', 'md5-hex-password', 
    	    'dh1L$e1ea87440827ca6e32a74da4cd204eb6');