FreeRADIUS 3.0.23 custom mysql queries in mysql/queries.conf

[Alan DeKok]

On Oct 1, 2021, at 6:41 PM, Dave Grassi <dgrassi at corp.digitalpath.net> wrote:
> 
> I'm trying to add a custom query for the accounting section under interim-update, but have been somewhat unsuccessful in my goal.

  That depends on what you want to do.  The existing behavior does very specific things, and is well documented.  Complex perhaps, but at least documented.

> I was able to add my custom query (saving and manipulating octets in a new table),  but by doing so it disables the original update/insert queries.
> Disable might be the wrong word, it seems that you can only have 2 queries per section (an update and insert which one should succeed).

  The SQL module runs queries until one succeeds.   This is so that the server can insert / update / whatever the accounting rows, as the row may or may not exist.

> If I move my queries below the original insert/updating accounting queries, those run but now mine do not.

  Yes, if an earlier query works, then the later ones don't run.

> I then tried moving my custom query into its own section interim-update {} however the behavior persists, only the 1st interim-update scope gets processed.

  Yes.  Nothing in the documentation says you can add multiple "interim-update" sections.

> If I move my interim-update scope above the original interim-update scope, my mysql queries execute but the original interm-update is skipped.

  Yes, one one works.

> I also tried creating my own accounting{} scope with type{} then interim-update{} and put my own queries there, but same behavior they are skipped.

  See above.  Nothing in the documentation says that you can add configuration and it will suddenly do what you want.

> Any assistance would be appreciated.

  The solution is simple.  Don't try to run multiple queries.  The SQL module expects to run only one successful query.

  Instead, run the SQL module multiple times.

  See the queries.conf file.  Look for:

accounting {
	reference = "%{tolower:type.%{%{Acct-Status-Type}:-%{Request-Processing-Stage}}.query}"

  This configuration tells the SQL module which query to run for Accounting-Request packets, depending on the Acct-Status-Type.  Each accounting packet normally contains an Acct-Status-Type attribute.  But there's no reason you can't change it.

  You can fix this by editing raddb/dictionary, and adding your own Acct-Status-Type definition:

VALUE	Acct-Status-Type		my-query			123456

   Don't worry too much about the numerical value, it doesn't matter.  It just has to be larger than 256 so it doesn't conflict with the officially allocated ones.

  Now that you have your own Acct-Status-Type, edit queries.conf:

accounting {
	reference = "%{tolower:type.%{%{Acct-Status-Type}:-%{Request-Processing-Stage}}.query}"
	... existing stuff...

	# run my query, too!
	my-query {
		query = "UPDATE whatever"
	}
}

   Now you can run your query when the server gets an Accounting-Request packet with "Acct-Status-Type = my-query".   This doesn't happen normally, but it's trivial to do.

  Edit sites-enabled/default, and look at the "accounting" section.  This what's run when the server receives Accounting-Request packets.

  Keep the existing reference to the "sql" module, as you still want to run the current queries.  Then at the *end* of the "accounting" section, just before the final closing brace, add:

	# run my queries
	update request {
		Acct-Status-Type := my-query
	}
	sql

  This will over-write the previous Acct-Status-Type, and run the "sql" module again.  There's no issue with running the sql module twice, it's fine.

  If you want the query to be run only when the server receives certain accounting packets, then just check for that:

	if (Acct-Status-Type == Interim-Update) {
		update request {
			Acct-Status-Type := my-query
		}
		sql

	}

  This is admittedly not an obvious solution.  But once you know how the server works, it's pretty clear.  The key is understanding that the SQL module really only wants to run one query.  So you can't magically make it run two different queries.  But you can run the SQL module twice!

  Alan DeKok.