Using "sql_session_start" does not insert a record into the radacct table.

[Alan DeKok]

On Feb 9, 2023, at 7:26 AM, Conrad Classen <conrad.classen at gmail.com> wrote:
> No record is being inserted into the radacct table after a successful authentication with the sql_session_start queries enabled and the sites-enabled/default files has the line uncommented.
...
> (9) Received Access-Request Id 55 from jjj.kkk.lll.mmm:13856 to 192.168.0.10:1812 length 360
> 
> (9)   Calling-Station-Id = "27xxxxxxxxx"

  OK.... having the debug output double-spaced doesn't help.  But whatever.
> 

> (9) # Executing section post-auth from file /etc/freeradius/sites-enabled/default
> ...
> (9)     policy sql_session_start.post-auth {

  It's running the sql_session_start policy, which is good.

> (9) sql: EXPAND %{tolower:type.%{%{Acct-Status-Type}:-%{Request-Processing-Stage}}.query}
> (9) sql:    --> type.start.query

  And it's using the "start" query.

> (9) sql: Using query template 'query'
> rlm_sql (sql): Reserved connection (5)
> (9) sql: EXPAND %{%{Stripped-User-Name}:-%{%{User-Name}:-none}}
> (9) sql:    --> 27000000000
> (9) sql: SQL-User-Name set to '27000000000'
> (9) sql: EXPAND UPDATE radacct SET AcctSessionId = '%{Acct-Session-Id}', AcctUniqueId = '%{Acct-Unique-Session-Id}', AcctAuthentic = '%{Acct-Authentic}', ConnectInfo_start = '%{Connect-Info}', ServiceType = '%{Service-Type}', FramedProtocol = '%{Framed-Protocol}', FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, FramedIPv6Address = NULLIF('%{Framed-IPv6-Address}', '')::inet, FramedIPv6Prefix = NULLIF('%{Framed-IPv6-Prefix}', '')::inet, FramedInterfaceId = NULLIF('%{Framed-Interface-Id}', ''), DelegatedIPv6Prefix = NULLIF('%{Delegated-IPv6-Prefix}', '')::inet, AcctStartTime = TO_TIMESTAMP(%{%{integer:Event-Timestamp}:-%l}), AcctUpdateTime = TO_TIMESTAMP(%{%{integer:Event-Timestamp}:-%l}) WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}' AND AcctStopTime IS NULL

  Why is doing an "update" instead of an "insert" ?

  The only reason is that you've edited the queries.conf file, and broken it.  The default configuration has an "insert" as the first query.  It works.

  Why did you change that?

> rlm_sql_postgresql: query affected rows = 0

  Since there's no existing row, the UPDATE doesn't update anything.  This makes sense.

> (9) sql: SQL query returned: success
> (9) sql: 0 record(s) updated
> (9) sql: Trying next query...

  Which is what it's supposed to do.  And why the first query in the default configuration is an INSERT.

> (16) Received Accounting-Request Id 138 from aaa.bbb.ccc.ddd:13216 to 192.168.0.10:1813 length 468

  And then when it receives an accounting request, the same thing happens.

  Why did you edit the default queries and break them?

  If you're editing the queries, you should first understand what they do.  And if you edit them, you shouldn't be surprised at how they work.  You should understand them before making random changes to the configuration.

  Use the default queries in mods-config/sql/main/postgresql/queries.conf.  They work.  The configuration you created doesn't work.

  Alan DeKok.