Hi, I am receiving accounting messages from the network and storing them to the radacct table. Currently in order to filter I have changed the INSERT/UPDATE commands with a where statement to only insert rows for subscribers I am managing. This is working fine but as my network grows it is creating a lot of overhead to execute SQL commands that don’t do anything and so I am trying to move the filtering to the FreeRADIUS code. I have added the preacct to the attr_filter file: attr_filter attr_filter.preacct { key = "%{User-Name}" filename = ${modconfdir}/${.:name}/preacct } And have this preacct file to try and currently filter only 1 number from being inserted to the DB. # # Configuration file for the rlm_attr_filter module. # Please see rlm_attr_filter(5) manpage for more information. # # # This configuration file is used to remove almost all of the attributes # From an Accounting-Response message. The RFC's say that an # Accounting-Response packet can contain only a few attributes. # We enforce that here. # DEFAULT Vendor-Specific =* ANY, Message-Authenticator =* ANY, Error-Cause =* ANY, Proxy-State =* ANY, Calling-Station-Id == ‘XXXXXXXX' However it seems that the filter is being ignored and all lines are inserted to the DB. Can you please assist in understanding why it isn’t filtering? In addition if I have a list of about 100K subscribers that I do want to store their accounting data to the DB is there a way to manage this list without adding it 1 by 1 to the filter file? I am running Freeradius 3.0.13 with PostgreSQL. Thanks Liran Kessel
On Jul 14, 2019, at 2:02 PM, liran kessel <lirankessel@gmail.com> wrote:
I am receiving accounting messages from the network and storing them to the radacct table. Currently in order to filter I have changed the INSERT/UPDATE commands with a where statement to only insert rows for subscribers I am managing.
Hmm... that works, but isn't the most efficient.
This is working fine but as my network grows it is creating a lot of overhead to execute SQL commands that don’t do anything and so I am trying to move the filtering to the FreeRADIUS code.
That's good.
I have added the preacct to the attr_filter file:
I'm not sure why. The attr_filter module will filter (i.e. delete) attributes in a packet. It won't change how many times SQL is being called.
However it seems that the filter is being ignored and all lines are inserted to the DB.
Yes.
Can you please assist in understanding why it isn’t filtering?
Because filter attributes is not the same as skipping SQL.
In addition if I have a list of about 100K subscribers that I do want to store their accounting data to the DB is there a way to manage this list without adding it 1 by 1 to the filter file?
Yes. Presumably there is some check you can do for your own users. Then, do: accounting { ... if (my users) { sql } # else not my users, don't do SQL ... } The only magic here is how to determine what are "my users". Since that's local to your system, we can't help here. Alan DeKok.
Hi, Alan thanks for the explanation. I understand what you proposed but this (my users) is a dynamic list that will grow constantly so the question is what would be the best way to keep it updated within the configuration? As I said in the original mail , we are growing to over 150k unique calling-station-ids that around 80% should be recorded to the DB while 20% shouldn’t. Maybe storing the list in a file that I can reference in the suggested “if” statement? Or maybe there is way to cache the list in memory to speed it up even more? Thanks Liran
On 14 Jul 2019, at 21:27, Alan DeKok <aland@deployingradius.com> wrote:
On Jul 14, 2019, at 2:02 PM, liran kessel <lirankessel@gmail.com> wrote:
I am receiving accounting messages from the network and storing them to the radacct table. Currently in order to filter I have changed the INSERT/UPDATE commands with a where statement to only insert rows for subscribers I am managing.
Hmm... that works, but isn't the most efficient.
This is working fine but as my network grows it is creating a lot of overhead to execute SQL commands that don’t do anything and so I am trying to move the filtering to the FreeRADIUS code.
That's good.
I have added the preacct to the attr_filter file:
I'm not sure why. The attr_filter module will filter (i.e. delete) attributes in a packet. It won't change how many times SQL is being called.
However it seems that the filter is being ignored and all lines are inserted to the DB.
Yes.
Can you please assist in understanding why it isn’t filtering?
Because filter attributes is not the same as skipping SQL.
In addition if I have a list of about 100K subscribers that I do want to store their accounting data to the DB is there a way to manage this list without adding it 1 by 1 to the filter file?
Yes. Presumably there is some check you can do for your own users. Then, do:
accounting { ... if (my users) { sql } # else not my users, don't do SQL ... }
The only magic here is how to determine what are "my users". Since that's local to your system, we can't help here.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Jul 14, 2019, at 10:07 PM, liran kessel <lirankessel@gmail.com> wrote:
I understand what you proposed but this (my users) is a dynamic list that will grow constantly so the question is what would be the best way to keep it updated within the configuration?
This is what databases are for. FreeRADIUS is a RADIUS server. We don't implement internal databases. We just use normal databases.
As I said in the original mail , we are growing to over 150k unique calling-station-ids that around 80% should be recorded to the DB while 20% shouldn’t. Maybe storing the list in a file that I can reference in the suggested “if” statement? Or maybe there is way to cache the list in memory to speed it up even more?
Use Redis. We've tested it at 10K+ complex queries per second with FreeRADIUS. That should be more than sufficient for your needs. Alan DeKok.
Hi, I am working with Postgres so will use it in the if statement to query for my users before executing the sql as you suggested, though this still means I am quering sql for every event. Would using the rlm_cache to store the users be useful in this instance in terms of reducing the sql queries and improving performance? Thanks
On 15 Jul 2019, at 10:11, Alan DeKok <aland@deployingradius.com> wrote:
On Jul 14, 2019, at 10:07 PM, liran kessel <lirankessel@gmail.com> wrote:
I understand what you proposed but this (my users) is a dynamic list that will grow constantly so the question is what would be the best way to keep it updated within the configuration?
This is what databases are for.
FreeRADIUS is a RADIUS server. We don't implement internal databases. We just use normal databases.
As I said in the original mail , we are growing to over 150k unique calling-station-ids that around 80% should be recorded to the DB while 20% shouldn’t. Maybe storing the list in a file that I can reference in the suggested “if” statement? Or maybe there is way to cache the list in memory to speed it up even more?
Use Redis. We've tested it at 10K+ complex queries per second with FreeRADIUS. That should be more than sufficient for your needs.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
I have added the recommended check and the accounting section looks like this accounting { unix if ("%{sql:SELECT coalesce(1,0) FROM [TABLE_NAME] WHERE ne_id='%{Calling-Station-Id}'}" == 1) { sql } exec attr_filter.accounting_response } However when running radiusd it fails with this error # Loading preacct {...} # Loading accounting {...} /etc/raddb/sites-enabled/default[642]: Failed to find "{" as a module or policy. /etc/raddb/sites-enabled/default[642]: Please verify that the configuration exists in /etc/raddb/mods-enabled/{. /etc/raddb/sites-enabled/default[642]: Failed to parse "{" entry. /etc/raddb/sites-enabled/default[608]: Errors parsing accounting section.
On 15 Jul 2019, at 10:11, Alan DeKok <aland@deployingradius.com> wrote:
On Jul 14, 2019, at 10:07 PM, liran kessel <lirankessel@gmail.com> wrote:
I understand what you proposed but this (my users) is a dynamic list that will grow constantly so the question is what would be the best way to keep it updated within the configuration?
This is what databases are for.
FreeRADIUS is a RADIUS server. We don't implement internal databases. We just use normal databases.
As I said in the original mail , we are growing to over 150k unique calling-station-ids that around 80% should be recorded to the DB while 20% shouldn’t. Maybe storing the list in a file that I can reference in the suggested “if” statement? Or maybe there is way to cache the list in memory to speed it up even more?
Use Redis. We've tested it at 10K+ complex queries per second with FreeRADIUS. That should be more than sufficient for your needs.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Mon, 2019-07-15 at 14:54 +0300, liran kessel wrote:
if ("%{sql:SELECT coalesce(1,0) FROM [TABLE_NAME] WHERE ne_id='%{Calling-Station-Id}'}" == 1) { sql }
That's not correct unlang syntax.
However when running radiusd it fails with this error
# Loading preacct {...} # Loading accounting {...} /etc/raddb/sites-enabled/default[642]: Failed to find "{" as a module or policy.
Put the '{' on the same line as the 'if'. -- Matthew
Thanks that solved the problem.
On 15 Jul 2019, at 15:13, Matthew Newton <mcn@freeradius.org> wrote:
On Mon, 2019-07-15 at 14:54 +0300, liran kessel wrote:
if ("%{sql:SELECT coalesce(1,0) FROM [TABLE_NAME] WHERE ne_id='%{Calling-Station-Id}'}" == 1) { sql }
That's not correct unlang syntax.
However when running radiusd it fails with this error
# Loading preacct {...} # Loading accounting {...} /etc/raddb/sites-enabled/default[642]: Failed to find "{" as a module or policy.
Put the '{' on the same line as the 'if'.
-- Matthew
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
participants (3)
-
Alan DeKok -
liran kessel -
Matthew Newton