I have a basic configured FreeRadius in conjunction with Postgresql and Mikrotik. The SQL module is configured correctly and PPPoE authorization through the database works. I need to connect and configure the sqlippool module. At this point, I have problems because there are no detailed instructions on configuring this module on the Internet. Here is a list of what I tried to do:
- Specified the "postgresql" dialect in the sqlippool file
- Connected the module
- Created a radippool table via the PostgreSQL schema
- Added the following lines to the database:
INSERT INTO radippool (pool_name, framedipaddress) VALUES ('main_pool', '10.1.0.5');
INSERT INTO radippool (pool_name, framedipaddress) VALUES ('main_pool', '10.1.0.6');
INSERT INTO radcheck (username, attribute, op, value) VALUES ('tester', 'Cleartext-Password', ':=', 'test');
INSERT INTO radcheck (username, attribute, op, value) VALUES ('tester', 'Pool-Name', ':=', 'main_pool');
Reboot. As a result - nothing. FreeRadius does not issue ip addresses from the pool, and I do not see any errors in the logs. Can you tell me what I did wrong? Do I need to connect something else, or did I make a mistake somewhere? Please tell a detailed sequence of actions for correct configuration of this module. I think this guide will help many beginners, since there is simply no detailed information.
UPDATE. Logs:
Ready to process requests
(0) Received Access-Request Id 19 from 10.1.0.1:53161 to 10.1.0.4:1812 length 198
(0) Service-Type = Framed-User
(0) Framed-Protocol = PPP
(0) NAS-Port = 15728646
(0) NAS-Port-Type = Ethernet
(0) User-Name = "tester"
(0) Calling-Station-Id = "C4:12:F5:D4:85:70"
(0) Called-Station-Id = "myclients"
(0) NAS-Port-Id = "ether3"
(0) Acct-Session-Id = "81a00006"
(0) MS-CHAP-Challenge = 0x97e9a21dabacee5e9c0384f778dc7915
(0) MS-CHAP2-Response = 0x010029335f35cdd38e0fa6eab66e12022f47000000000000000078126d15554660d74add815846c50ab6353c7e0847952eac
(0) NAS-Identifier = "MikroTik"
(0) NAS-IP-Address = 10.1.0.1
(0) # Executing section authorize from file /etc/freeradius/3.0/sites-enabled/default
(0) authorize {
(0) policy filter_username {
(0) if (&User-Name) {
(0) if (&User-Name) -> TRUE
(0) if (&User-Name) {
(0) if (&User-Name =~ / /) {
(0) if (&User-Name =~ / /) -> FALSE
(0) if (&User-Name =~ /@[^@]*@/ ) {
(0) if (&User-Name =~ /@[^@]*@/ ) -> FALSE
(0) if (&User-Name =~ /../ ) {
(0) if (&User-Name =~ /../ ) -> FALSE
(0) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+).(.+)$/)) {
(0) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+).(.+)$/)) -> FALSE
(0) if (&User-Name =~ /.$/) {
(0) if (&User-Name =~ /.$/) -> FALSE
(0) if (&User-Name =~ /@./) {
(0) if (&User-Name =~ /@./) -> FALSE
(0) } # if (&User-Name) = notfound
(0) } # policy filter_username = notfound
(0) [preprocess] = ok
(0) [chap] = noop
(0) mschap: Found MS-CHAP attributes. Setting 'Auth-Type = mschap'
(0) [mschap] = ok
(0) [digest] = noop
(0) suffix: Checking for suffix after "@"
(0) suffix: No '@' in User-Name = "tester", looking up realm NULL
(0) suffix: No such realm "NULL"
(0) [suffix] = noop
(0) eap: No EAP-Message, not doing EAP
(0) [eap] = noop
(0) files: users: Matched entry DEFAULT at line 181
(0) [files] = ok
(0) sql: EXPAND %{User-Name}
(0) sql: --> tester
(0) sql: SQL-User-Name set to 'tester' rlm_sql (sql): Reserved connection (1)
(0) sql: EXPAND SELECT id, UserName, Attribute, Value, Op FROM radcheck WHERE Username = '%{SQL-User-Name}' ORDER BY id
(0) sql: --> SELECT id, UserName, Attribute, Value, Op FROM radcheck WHERE Username = 'tester' ORDER BY id
(0) sql: Executing select query: SELECT id, UserName, Attribute, Value, Op FROM radcheck WHERE Username = 'tester' ORDER BY id rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 2 , fields = 5
(0) sql: User found in radcheck table
(0) sql: Conditional check items matched, merging assignment check items
(0) sql: Cleartext-Password := "test"
(0) sql: Pool-Name := "main_pool"
(0) sql: EXPAND SELECT id, UserName, Attribute, Value, Op FROM radreply WHERE Username = '%{SQL-User-Name}' ORDER BY id
(0) sql: --> SELECT id, UserName, Attribute, Value, Op FROM radreply WHERE Username = 'tester' ORDER BY id
(0) sql: Executing select query: SELECT id, UserName, Attribute, Value, Op FROM radreply WHERE Username = 'tester' ORDER BY id rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 0 , fields = 5
(0) sql: EXPAND SELECT GroupName FROM radusergroup WHERE UserName='%{SQL-User-Name}' ORDER BY priority
(0) sql: --> SELECT GroupName FROM radusergroup WHERE UserName='tester' ORDER BY priority
(0) sql: Executing select query: SELECT GroupName FROM radusergroup WHERE UserName='tester' ORDER BY priority rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 0 , fields = 1
(0) sql: User not found in any groups rlm_sql (sql): Released connection (1) Need 4 more connections to reach 10 spares rlm_sql (sql): Opening additional connection (6), 1 of 26 pending slots used rlm_sql_postgresql: Connecting using parameters: dbname='radius' host='localhost' port=5432 user='radius' password='radpass' Connected to database 'radius' on 'localhost' server version 110007, protocol version 3, backend PID 28624
(0) [sql] = ok
(0) [expiration] = noop
(0) [logintime] = noop
(0) pap: WARNING: Auth-Type already set. Not setting to PAP
(0) [pap] = noop
(0) } # authorize = ok
(0) Found Auth-Type = mschap
(0) # Executing group from file /etc/freeradius/3.0/sites-enabled/default
(0) authenticate {
(0) mschap: Found Cleartext-Password, hashing to create NT-Password
(0) mschap: Found Cleartext-Password, hashing to create LM-Password
(0) mschap: Creating challenge hash with username: tester
(0) mschap: Client is using MS-CHAPv2
(0) mschap: Adding MS-CHAPv2 MPPE keys
(0) [mschap] = ok
(0) } # authenticate = ok
(0) # Executing section post-auth from file /etc/freeradius/3.0/sites-enabled/default
(0) post-auth {
(0) update {
(0) No attributes updated
(0) } # update = noop
(0) sql: EXPAND .query
(0) sql: --> .query
(0) sql: Using query template 'query' rlm_sql (sql): Reserved connection (2)
(0) sql: EXPAND %{User-Name}
(0) sql: --> tester
(0) sql: SQL-User-Name set to 'tester'
(0) sql: EXPAND INSERT INTO radpostauth (username, pass, reply, authdate) VALUES('%{User-Name}', '%{%{User-Password}:-Chap-Password}', '%{reply:Packet-Type}', NOW())
(0) sql: --> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES('tester', 'Chap-Password', 'Access-Accept', NOW())
(0) sql: Executing query: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES('tester', 'Chap-Password', 'Access-Accept', NOW()) rlm_sql_postgresql: Status: PGRES_COMMAND_OK rlm_sql_postgresql: query affected rows = 1
(0) sql: SQL query returned: success
(0) sql: 1 record(s) updated rlm_sql (sql): Released connection (2)
(0) [sql] = ok
(0) [exec] = noop
(0) policy remove_reply_message_if_eap {
(0) if (&reply:EAP-Message && &reply:Reply-Message) {
(0) if (&reply:EAP-Message && &reply:Reply-Message) -> FALSE
(0) else {
(0) [noop] = noop
(0) } # else = noop
(0) } # policy remove_reply_message_if_eap = noop
(0) } # post-auth = ok
(0) Sent Access-Accept Id 19 from 10.1.0.4:1812 to 10.1.0.1:53161 length 0
(0) Framed-Protocol = PPP
(0) Framed-Compression = Van-Jacobson-TCP-IP
(0) MS-CHAP2-Success = 0x01533d42443236373932353546324538304641374430424235463938333445314331454330323244463233
(0) MS-MPPE-Recv-Key = 0xf03b52e2c28c093649b396d6e2b16945
(0) MS-MPPE-Send-Key = 0xb4d2427c437eca3f444403b060bc59f7
(0) MS-MPPE-Encryption-Policy = Encryption-Allowed
(0) MS-MPPE-Encryption-Types = RC4-40or128-bit-Allowed
(0) Finished request Waking up in 4.9 seconds.
(1) Received Accounting-Request Id 20 from 10.1.0.1:48965 to 10.1.0.4:1813 length 146
(1) Service-Type = Framed-User
(1) Framed-Protocol = PPP
(1) NAS-Port = 15728646
(1) NAS-Port-Type = Ethernet
(1) User-Name = "tester"
(1) Calling-Station-Id = "C4:12:F5:D4:85:70"
(1) Called-Station-Id = "myclients"
(1) NAS-Port-Id = "ether3"
(1) Acct-Session-Id = "81a00006"
(1) Framed-IP-Address = 10.1.0.252
(1) Acct-Authentic = RADIUS
(1) Event-Timestamp = "Jul 13 2020 14:57:41 +08"
(1) Acct-Status-Type = Start
(1) NAS-Identifier = "MikroTik"
(1) Acct-Delay-Time = 0
(1) NAS-IP-Address = 10.1.0.1
(1) # Executing section preacct from file /etc/freeradius/3.0/sites-enabled/default
(1) preacct {
(1) [preprocess] = ok
(1) policy acct_unique {
(1) update request {
(1) &Tmp-String-9 := "ai:"
(1) } # update request = noop
(1) if (("%{hex:&Class}" =~ /^%{hex:&Tmp-String-9}/) && ("%{string:&Class}" =~ /^ai:([0-9a-f]{32})/i)) {
(1) EXPAND %{hex:&Class}
(1) -->
(1) EXPAND ^%{hex:&Tmp-String-9}
(1) --> ^61693a
(1) if (("%{hex:&Class}" =~ /^%{hex:&Tmp-String-9}/) && ("%{string:&Class}" =~ /^ai:([0-9a-f]{32})/i)) -> FALSE
(1) else {
(1) update request {
(1) EXPAND %{md5:%{User-Name},%{Acct-Session-ID},%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}},%{NAS-Identifier},%{NAS-Port-ID},%{NAS-Port}}
(1) --> 9b06bc7d71ce816cb361a7625387ab61
(1) &Acct-Unique-Session-Id := 9b06bc7d71ce816cb361a7625387ab61
(1) } # update request = noop
(1) } # else = noop
(1) } # policy acct_unique = noop
(1) suffix: Checking for suffix after "@"
(1) suffix: No '@' in User-Name = "tester", looking up realm NULL
(1) suffix: No such realm "NULL"
(1) [suffix] = noop
(1) [files] = noop
(1) } # preacct = ok
(1) # Executing section accounting from file /etc/freeradius/3.0/sites-enabled/default
(1) accounting {
(1) detail: EXPAND /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d
(1) detail: --> /var/log/freeradius/radacct/10.1.0.1/detail-20200713
(1) detail: /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d expands to /var/log/freeradius/radacct/10.1.0.1/detail-20200713
(1) detail: EXPAND %t
(1) detail: --> Mon Jul 13 14:57:45 2020
(1) [detail] = ok
(1) [unix] = ok
(1) sql: EXPAND %{tolower:type.%{%{Acct-Status-Type}:-none}.query}
(1) sql: --> type.start.query
(1) sql: Using query template 'query' rlm_sql (sql): Reserved connection (3)
(1) sql: EXPAND %{User-Name}
(1) sql: --> tester
(1) sql: SQL-User-Name set to 'tester'
(1) sql: EXPAND 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) VALUES('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', NULLIF('%{Realm}', ''), '%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}}', NULLIF('%{%{NAS-Port-ID}:-%{NAS-Port}}', ''), '%{NAS-Port-Type}', TO_TIMESTAMP(%{integer:Event-Timestamp}), TO_TIMESTAMP(%{integer:Event-Timestamp}), NULL, 0, '%{Acct-Authentic}', '%{Connect-Info}', NULL, 0, 0, '%{Called-Station-Id}', '%{Calling-Station-Id}', NULL, '%{Service-Type}', '%{Framed-Protocol}', NULLIF('%{Framed-IP-Address}', '')::inet)
(1) sql: --> 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) VALUES('81a00006', '9b06bc7d71ce816cb361a7625387ab61', 'tester', NULLIF('', ''), '10.1.0.1', NULLIF('ether3', ''), 'Ethernet', TO_TIMESTAMP(1594623461), TO_TIMESTAMP(1594623461), NULL, 0, 'RADIUS', '', NULL, 0, 0, 'myclients', 'C4:12:F5:D4:85:70', NULL, 'Framed-User', 'PPP', NULLIF('10.1.0.252', '')::inet)
(1) sql: Executing query: 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) VALUES('81a00006', '9b06bc7d71ce816cb361a7625387ab61', 'tester', NULLIF('', ''), '10.1.0.1', NULLIF('ether3', ''), 'Ethernet', TO_TIMESTAMP(1594623461), TO_TIMESTAMP(1594623461), NULL, 0, 'RADIUS', '', NULL, 0, 0, 'myclients', 'C4:12:F5:D4:85:70', NULL, 'Framed-User', 'PPP', NULLIF('10.1.0.252', '')::inet) rlm_sql_postgresql: Status: PGRES_COMMAND_OK rlm_sql_postgresql: query affected rows = 1
(1) sql: SQL query returned: success
(1) sql: 1 record(s) updated rlm_sql (sql): Released connection (3)
(1) [sql] = ok
(1) [exec] = noop
(1) attr_filter.accounting_response: EXPAND %{User-Name}
(1) attr_filter.accounting_response: --> tester
(1) attr_filter.accounting_response: Matched entry DEFAULT at line 12
(1) [attr_filter.accounting_response] = updated
(1) } # accounting = updated
(1) Sent Accounting-Response Id 20 from 10.1.0.4:1813 to 10.1.0.1:48965 length 0
(1) Finished request
(1) Cleaning up request packet ID 20 with timestamp +5 Waking up in 4.9 seconds.
(0) Cleaning up request packet ID 19 with timestamp +5 Ready to process requests
