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

[Conrad Classen]

Hi Alan

Thanks

In terms of disabling a query, I was following the instructions to 
implement the change to move the - character from the lower query 
variable to the one above it, i.e. from the 2nd block to the first block 
in sections of the accounting queries.

                        #
                         #  When using "sql_session_start", you should 
comment out
                         #  the previous query, and enable this one.
                         #
                         #  Just change the previous query to "-query",
                         #  and this one to "query".  The previous one
                         #  will be ignored, and this one will be
                         #  enabled.

Thanks, and much appreciated.

Conrad


I will restore the original one and try again to implement the 
"sql_session_start"
On 2023/02/09 16:41, Alan DeKok wrote:
> 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.
>
> -
> List info/subscribe/unsubscribe? Seehttp://www.freeradius.org/list/users.html