I implemented what I would call 'automatic profile switching' for an AAA system that uses my aaarails project with their internal NMS.
Customers which exceed a certain data credit should get a profile assigned that limits their bandwidth until they re-charge.

I use the standard postgres schema for freeradius, so this solution might be useful to you if you happen to run a similar setup and are in need for an idea.

TD;DR: Create two (one normal, one for limited services) profiles per user. Change authorize_reply_query in freeradius to select the right profile based on the state of a user. Change the state via trigger-functions in the database that react to the accounting inserts/updates.


This is how I did it:
Add a data_credit field per 'user' (radchecks table) and some columns to hold data for bandwidth calculations:

  ALTER TABLE radchecks ADD COLUMN data_credit BIGINT;
  ALTER TABLE radchecks ADD COLUMN in_octets BIGINT  DEFAULT 0;
  ALTER TABLE radchecks ADD COLUMN in_octets_last BIGINT  DEFAULT 0;
  ...

Create a radchecks_id per accounting entry and populate it on inserts

ALTER TABLE radaccts ADD COLUMN radchecks_id INTEGER;

CREATE OR REPLACE FUNCTION radius_find_radchecks_from_whatever()
  RETURNS trigger AS
$BODY$
  ...do your magic...
  NEW.radchecks_id = the_id_you_extracted_based_on_the_accounting_data;
  RETURN NEW;    
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE TRIGGER trigger_insert_radaccts
  BEFORE INSERT
  ON radaccts
  FOR EACH ROW
  EXECUTE PROCEDURE radius_find_radchecks_from_whatever();

Fire a trigger on insert or update to the accounting table (radaccts). This triggers a function that calculates the used bandwidth and subtracts it from the data_credit.

CREATE OR REPLACE FUNCTION radaccts_counters() RETURNS TRIGGER AS
$$
  DECLARE
    ...
  BEGIN
    ...
    new_in  = NEW.acctinputoctets;
    ...
         
    if (new_in IS NOT NULL) then
        ...
        
        UPDATE radchecks SET in_octets = (in_octets+diff_in), 
                             in_octets_last = new_in
                         WHERE id = NEW.radchecks_id;
       
    END IF;

    ...
    UPDATE radchecks SET credit_counter = (credit_counter - diff_in - diff_out) 
                     WHERE id = NEW.radchecks_id;
    ...
    RETURN NEW;
  END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER trigger_insert_radaccts_counters AFTER INSERT OR UPDATE ON radaccts
   FOR EACH ROW EXECUTE PROCEDURE radaccts_counters();

Carry two profiles for the user: the normal one and one 'shadow profile' for limited service. (I did that by adding a column 'priority' to radreplies)
Modify the freeradius query for profile attributes so that depending on the value of data_credit either the normal or the shadow profile gets selected.
So you would set your 'authorize_reply_query' to something like this:

SELECT radreplies.id, radreplies.UserName, radreplies.cAttribute, 
       radreplies.cValue, radreplies.Op
FROM radreplies
LEFT JOIN radchecks ON radreplies.Username=radchecks.Username
WHERE radchecks.Username = '${User-Name}' AND 
      radreplies.priority = (radchecks.credit_counter > 0)::int
ORDER BY radreplies.id

(in other words: if the credit_counter of a user goes below 1, the freeradius system switches over to the shadow profile of that user.)

Questions, suggestions or feedback? Please let me know.

---