multiple freeradius instances with sqlippool, is it safe?
Hello, I'm would like to have multiple freeradius instances (processes on different servers) all pointing to the same postgres database. (My goal is to have a freeradius service redundant, I have a postgresql cluster with patroni/haproxy and each radius server point to all of them via a dsn multiple host connection definition and obviously it's connected to the master) I would like to use the sqlipool module. Is it safe use it in this scenario or does exists some side effects (I have some ideas of what could be wrong but I would like a your opinion...)? Do you have suggestions to what to pay attention to in this scenario? Thanks M.
On 22/03/2022, at 9:43 PM, Matteo Sgalaberni <sgala@sgala.com> wrote:
Hello,
I'm would like to have multiple freeradius instances (processes on different servers) all pointing to the same postgres database. (My goal is to have a freeradius service redundant, I have a postgresql cluster with patroni/haproxy and each radius server point to all of them via a dsn multiple host connection definition and obviously it's connected to the master)
I would like to use the sqlipool module. Is it safe use it in this scenario or does exists some side effects (I have some ideas of what could be wrong but I would like a your opinion...)?
Do you have suggestions to what to pay attention to in this scenario?
Yes - provided you use the queries which ship with FreeRADIUS, this should be perfectly safe. I use this in a few networks (not small networks, either) and it works great. I’ve not used the multiple host definition thing you talk about - I have used pgpool as a proxy which proxies to the master. If you want some extra confidence, you may note that a single FreeRADIUS server establishes multiple connections to the database server and runs transactions in parallel - database connections from different RADIUS servers is no different in terms of concurrency in the database than multiple connections from one server. Note there has been work in the past several years on those sqlippool queries to improve performance and so on - you should make sure you’re running the latest code. -- Nathan Ward
As Nathan says, it is worth looking at the most recent queries in FreeRADIUS version 3.0.25 - significant optimisation has been done on the default IP pool queries of late. The key is that appropriate record locking is done during the allocation of addresses. With PostgreSQL, the UPDATE ... RETURNING ... syntax can be used to reduce the the number of round trips to the database, also reducing the time that records are locked. Presuming you are using a PostgreSQL version >= 9.5, ensure you include SKIP LOCKED, which makes a huge difference to the query performance. Nick On 22/03/2022 09:00, Nathan Ward wrote:
On 22/03/2022, at 9:43 PM, Matteo Sgalaberni <sgala@sgala.com> wrote:
Hello,
I'm would like to have multiple freeradius instances (processes on different servers) all pointing to the same postgres database. (My goal is to have a freeradius service redundant, I have a postgresql cluster with patroni/haproxy and each radius server point to all of them via a dsn multiple host connection definition and obviously it's connected to the master)
I would like to use the sqlipool module. Is it safe use it in this scenario or does exists some side effects (I have some ideas of what could be wrong but I would like a your opinion...)?
Do you have suggestions to what to pay attention to in this scenario? Yes - provided you use the queries which ship with FreeRADIUS, this should be perfectly safe. I use this in a few networks (not small networks, either) and it works great. I’ve not used the multiple host definition thing you talk about - I have used pgpool as a proxy which proxies to the master.
If you want some extra confidence, you may note that a single FreeRADIUS server establishes multiple connections to the database server and runs transactions in parallel - database connections from different RADIUS servers is no different in terms of concurrency in the database than multiple connections from one server.
Note there has been work in the past several years on those sqlippool queries to improve performance and so on - you should make sure you’re running the latest code.
-- Nathan Ward
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-- Nick Porter Porter Computing Ltd Registered in England No 12659380
On Mar 22, 2022, at 5:13 AM, Nick Porter <nick@portercomputing.co.uk> wrote:
As Nathan says, it is worth looking at the most recent queries in FreeRADIUS version 3.0.25 - significant optimisation has been done on the default IP pool queries of late.
The key is that appropriate record locking is done during the allocation of addresses.
With PostgreSQL, the UPDATE ... RETURNING ... syntax can be used to reduce the the number of round trips to the database, also reducing the time that records are locked.
Presuming you are using a PostgreSQL version >= 9.5, ensure you include SKIP LOCKED, which makes a huge difference to the query performance.
I'll also add that it doesn't usually make sense to have multiple RADIUS servers use one database. Many databases "max out" at very low numbers of IP allocations per second. Where FreeRADIUS can easily handle tens of thousands of packets per second. That being said, if you're using the 3.0.25 queries, you should be safe up to a few thousand packets/s. For higher performance, you'll have to move to something else like Redis. Alan DeKok.
----- On 22 Mar, 2022, at 10:00, Nathan Ward lists+freeradius@daork.net wrote:
If you want some extra confidence, you may note that a single FreeRADIUS server establishes multiple connections to the database server and runs transactions in parallel - database connections from different RADIUS servers is no different in terms of concurrency in the database than multiple connections from one server.
Note there has been work in the past several years on those sqlippool queries to improve performance and so on - you should make sure you’re running the latest code.
Ok thanks Nathan for your feedback! In my tests seems that some weird events happen. See this log: Mar 22 10:25:01 HOST patroni[2142563]: 2022-03-22 10:25:01 CET 623995bc.20b163 pg_user_radius1 X.X.X.X(36340)LOG: statement: UPDATE radippool SET nasipaddress = 'Y.Y.Y.Y', pool_key = '28905475', callingstationid = '', username = 'USER1', expiry_time = 'now'::timestamp(0) + '3600 second'::interval WHERE framedipaddress = 'Z.Z.Z.Z' Mar 22 10:25:01 HOST patroni[2130290]: 2022-03-22 10:25:01 CET 62397479.208172 pg_user_radius2 X.X.X.X(38266)LOG: statement: UPDATE radacct SET AcctStopTime = TO_TIMESTAMP(1647941081), AcctUpdateTime = TO_TIMESTAMP(1647941081), AcctSessionTime = COALESCE(26332, (1647941081 - EXTRACT(EPOCH FROM(AcctStartTime)))), AcctInputOctets = (('0'::bigint << 32) + '39282628'::bigint), AcctOutputOctets = (('0'::bigint << 32) + '603682375'::bigint), AcctTerminateCause = 'Port-Error', FramedIPAddress = NULLIF('Z.Z.Z.Z', '')::inet, FramedIPv6Address = NULLIF('', '')::inet, FramedIPv6Prefix = NULLIF('', '')::inet, FramedInterfaceId = NULLIF('', ''), DelegatedIPv6Prefix = NULLIF('', '')::inet, ConnectInfo_stop = '' WHERE AcctUniqueId = '355a47bc7d94803b22248312771a6d18' AND AcctStopTime IS NULL Mar 22 10:25:01 HOST patroni[2130291]: 2022-03-22 10:25:01 CET 6239747a.208173 pg_user_radius2 X.X.X.X(38268)LOG: statement: UPDATE radippool SET nasipaddress = '', pool_key = 0, callingstationid = '', expiry_time = 'now'::timestamp(0) - '1 second'::interval WHERE nasipaddress = 'Y.Y.Y.Y' AND pool_key = '28905475' AND username = 'USER1' AND callingstationid = '' AND framedipaddress = 'Z.Z.Z.Z' Mar 22 10:25:21 HOST patroni[2130288]: 2022-03-22 10:25:21 CET 62397479.208170 pg_user_radius2 X.X.X.X(38262)LOG: statement: INSERT INTO radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctUpdateTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_Stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIpAddress, FramedIpv6Address, FramedIpv6Prefix, FramedInterfaceId, DelegatedIpv6Prefix) VALUES('0/0/1/2961.3_0180D016', 'e417ebdf297f3734c255d14089716c22', 'USER1', NULLIF('', ''), 'Y.Y.Y.Y', NULLIF('0/0/1/2961.3', ''), 'Ethernet', TO_TIMESTAMP(1647941101), TO_TIMESTAMP(1647941101), NULL, 0, 'RADIUS', '', NULL, 0, 0, '', '', NULL, 'Framed-User', 'PPP', NULLIF('Z.Z.Z.Z', '')::inet, NULLIF('', '')::inet, NULLIF('', '')::inet, NULLIF('', ''), NULLIF('', '')::inet) ON CONFLICT (AcctUniqueId) DO UPDATE SET AcctStartTime = TO_TIMESTAMP(1647941101), AcctUpdateTime = TO_TIMESTAMP(1647941101), ConnectInfo_start = '' WHERE radacct.AcctUniqueId = 'e417ebdf297f3734c255d14089716c22' AND radacct.AcctStopTime IS NULL This timeline happened: - radius1 received the auth packet and sqlippool assigned the ip (correct information on radippool) - radius2 received the accounting stop and sqlippool freed the ip (destroying the information on radippool of that ip address previously assigned) - radius2 received the accounting start and do nothing on So at the end of this timeline I have a wrong entry on the table (the user is actually connected but not for the radipoool): id | pool_name | framedipaddress | nasipaddress | pool_key | calledstationid | callingstationid | expiry_time | username ------+-----------------+-----------------+--------------+----------+-----------------+------------------+---------------------+--------------- 1686 | dyn_ipv4 | Z.Z.Z.Z | | 0 | | | 2022-03-22 10:25:01 | USER1 Seems to be a concurrency issue occurred because the BRAS (Cisco ASR1001X) sent the auth packet and the acct packets to different radius servers and executed the queries in a logical wrong order. Can this scenario happen also in a single server setup? Eg: the accounting stop packet is processed after the authentication packet. My impression is that the BRAS should send the auth and acct to the same server. Also the radius should process the packets in the right order to prevent this issue. Can you share with me your BRAS "aaa *" configuration? Thanks Matteo
On 22/03/2022, at 11:24 PM, Matteo Sgalaberni <sgala@sgala.com> wrote:
Seems to be a concurrency issue occurred because the BRAS (Cisco ASR1001X) sent the auth packet and the acct packets to different radius servers and executed the queries in a logical wrong order.
Can this scenario happen also in a single server setup? Eg: the accounting stop packet is processed after the authentication packet.
Sure - if the BRAS sends them in different orders, or if one is delayed by the network or all sorts of different reasons.
My impression is that the BRAS should send the auth and acct to the same server. Also the radius should process the packets in the right order to prevent this issue.
No - there’s no rules about that. It’s not really clear exactly what you mean by this situation - why is the BRAS sending auth then stop then start? You should post the full debug per the list instructions so it’s possible to see what the BRAS is sending. Please don’t obscure the IP addresses and things either - it just makes it difficult to debug.
Can you share with me your BRAS "aaa *" configuration?
Not really sorry - for starters I don’t run Cisco BNGs on any networks that I look after at present, and if I did it wouldn’t be relevant to your network. My guess, though I would need to confirm with proper debug, is that your BNG is rejecting the address and letting the customer online some other way. Maybe your RADIUS servers are configured differently. -- Nathan Ward
On Mar 22, 2022, at 6:24 AM, Matteo Sgalaberni <sgala@sgala.com> wrote:
This timeline happened: - radius1 received the auth packet and sqlippool assigned the ip (correct information on radippool) - radius2 received the accounting stop and sqlippool freed the ip (destroying the information on radippool of that ip address previously assigned) - radius2 received the accounting start and do nothing on
That's how RADIUS works. The packets are processed in the order that they are received. And network delays / dropped packets can cause packets to appear "out of order". The packets also contain time stamps and/or delays. So the server can tell (roughly) when an event occurred. The queries use that information to ignore "old" stop/start packets. So what's happening here is that the NAS is sending the "stop" packet *after* it sends the auth request. Which is fine, and the server *should* delete the IP address assignments. Find out why the NAS is sending the stop/start packets with the wrong timestamps.
Seems to be a concurrency issue occurred because the BRAS (Cisco ASR1001X) sent the auth packet and the acct packets to different radius servers and executed the queries in a logical wrong order.
Please try to figure out how to make sure that the queries are run in the "right" logical order. Use only information which is in the RADIUS packets. It's impossible.
Can this scenario happen also in a single server setup? Eg: the accounting stop packet is processed after the authentication packet.
Yes. UDP packets are not ordered, especially to different destinations.
My impression is that the BRAS should send the auth and acct to the same server.
No. Or, only if you configure it to do that.
Also the radius should process the packets in the right order to prevent this issue.
No. This is *impossible* to do. Alan DeKok.
participants (4)
-
Alan DeKok -
Matteo Sgalaberni -
Nathan Ward -
Nick Porter