Hi, a patch for adding postgres support to CUI: diff --git a/raddb/mods-available/cui b/raddb/mods-available/cui index 2464613..b1ba225 100644 --- a/raddb/mods-available/cui +++ b/raddb/mods-available/cui @@ -5,10 +5,15 @@ # # Write Chargeable-User-Identity to the database. # -# Schema raddb/sql/mysql/cui.sql -# Queries raddb/sql/mysql/cui.conf +# Schema raddb/sql/DB/cui.sql +# Queries raddb/sql/DB/cui.conf # sql cui { + # + # Set the database to one of: + # + # mysql, postgresql + # database = "mysql" driver = "rlm_sql_${database}" server = "localhost" diff --git a/raddb/sql/postgresql/cui.conf b/raddb/sql/postgresql/cui.conf new file mode 100644 index 0000000..d90eccc --- /dev/null +++ b/raddb/sql/postgresql/cui.conf @@ -0,0 +1,31 @@ +# -*- text -*- + +## +## Queries to update the CUI table. +## +postauth_query = "INSERT INTO ${cui_table} \ + (clientipaddress, callingstationid, username, cui) \ + VALUES \ + ('%{Client-IP-Address}', '%{Calling-Station-Id}', '%{User-Name}', '%{reply:Chargeable-User-Identity}')"; + +accounting_start_query = "UPDATE ${cui_table} \ + SET \ + lastaccounting = now() \ + WHERE clientipaddress = '%{Client-IP-Address}' \ + AND callingstationid = '%{Calling-Station-Id}' \ + AND username = '%{User-Name}' \ + AND cui = '%{Chargeable-User-Identity}'"; + +accounting_update_query = "UPDATE ${cui_table} \ + SET \ + lastaccounting = now() \ + WHERE clientipaddress = '%{Client-IP-Address}' \ + AND callingstationid = '%{Calling-Station-Id}' \ + AND username = '%{User-Name}' \ + AND cui = '%{Chargeable-User-Identity}'"; + +accounting_stop_query = "DELETE FROM ${cui_table} WHERE \ + clientipaddress = '%{Client-IP-Address}' \ + AND callingstationid = '%{Calling-Station-Id}' \ + AND username = '%{User-Name}' \ + AND cui = '%{Chargeable-User-Identity}'"; diff --git a/raddb/sql/postgresql/cui.sql b/raddb/sql/postgresql/cui.sql new file mode 100644 index 0000000..34346da --- /dev/null +++ b/raddb/sql/postgresql/cui.sql @@ -0,0 +1,14 @@ +CREATE TABLE cui ( + clientipaddress INET NOT NULL DEFAULT '0.0.0.0', + callingstationid varchar(50) NOT NULL DEFAULT '', + username varchar(64) NOT NULL DEFAULT '', + cui varchar(32) NOT NULL DEFAULT '', + creationdate TIMESTAMP with time zone NOT NULL default 'now()', + lastaccounting TIMESTAMP with time zone NOT NULL default '-infinity'::timestamp, + PRIMARY KEY (username, clientipaddress, callingstationid) +); + +CREATE RULE postauth_query AS ON INSERT TO cui + WHERE EXISTS(SELECT 1 FROM cui WHERE (username, clientipaddress, callingstationid)=(NEW.username, NEW.clientipaddress, NEW.callingstationid)) + DO INSTEAD UPDATE cui SET lastaccounting ='-infinity'::timestamp with time zone, cui=NEW.cui WHERE (username, clientipaddress, callingstationid)=(NEW.username, NEW.clientipaddress, NEW.callingstationid); +