Биллинговая система Nodeny

root@localhost [nodeny]> SELECT get_ip_by_num(1, 3);
+---------------------+
| get_ip_by_num(1, 3) |
+---------------------+
| 172.16.0.100        |
+---------------------+
1 row in set (0.00 sec)

 modules {
  # Loaded module rlm_sql
  # Loading module "sql" from file /usr/local/etc/raddb/mods-enabled/sql
  sql {
        driver = "rlm_sql_mysql"
        server = "localhost"
        port = 3306
        login = "root"
        password = <<< secret >>>
        radius_db = "nodeny"
        read_groups = yes
        read_profiles = yes
        read_clients = no
        delete_stale_sessions = yes
        sql_user_name = ""
        default_user_profile = ""
        client_query = "SELECT id,nasname,shortname,type,secret FROM nas"
        authorize_check_query = "call radcheck('%{User-Name}', 0)"
        authorize_reply_query = "call radreply('%{User-Name}', '%{User-Password}')"
        safe_characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"
        auto_escape = no
   accounting {
        reference = "%{tolower:type.%{Acct-Status-Type}.query}"
    type {
     accounting-on {
     }
     accounting-off {
     }
     start {
        query = "call radupdate('%{User-Name}','%{Framed-IP-Address}',                        'user=%{Calling-Station-Id};nas=%{NAS-IP-Address};ses=%{Acct-Session-Id}')"
     }
     interim-update {
        query = "call radupdate('%{User-Name}','%{Framed-IP-Address}',                        'user=%{Calling-Station-Id};nas=%{NAS-IP-Address};ses=%{Acct-Session-Id}')"
     }
     stop {
        query = "call radstop('%{User-Name}', '%{Framed-IP-Address}')"
     }
    }
   }
   post-auth {
        reference = ".query"
        query = "call radupdate('%{User-Name}','%{reply:Framed-IP-Address}',                'user=%{Calling-Station-Id};nas=%{NAS-IP-Address};ses=%{Acct-Session-Id}')"
   }
  }
rlm_sql (sql): Driver rlm_sql_mysql (module rlm_sql_mysql) loaded and linked

....................

Ready to process requests
(0) Received Access-Request Id 28 from x.x.x.x:34285 to x.x.x.x:1812 length 134
(0)   Service-Type = Framed-User
(0)   Framed-Protocol = PPP
(0)   NAS-Port = 15728656
(0)   NAS-Port-Type = Ethernet
(0)   User-Name = "vano+0"
(0)   Calling-Station-Id = "00:5A:0A:3D:07:5D"
(0)   Called-Station-Id = "nas"
(0)   NAS-Port-Id = "ether5"
(0)   CHAP-Challenge = 0x6703b3eda9e156c2c155240e7c419743
(0)   CHAP-Password = 0x01c9bc50468195efc3642bcf93f87e055c
(0)   NAS-Identifier = "nas"
(0)   NAS-IP-Address = x.x.x.x
(0) # Executing section authorize from file /usr/local/etc/raddb/sites-enabled/nodeny
(0)   authorize {
rlm_sql (sql): Reserved connection (0)
(0) sql: EXPAND call radcheck('%{User-Name}', 0)
(0) sql:    --> call radcheck('vano=2B0', 0)
(0) sql: Executing select query: call radcheck('vano=2B0', 0)
(0) sql: ERROR: rlm_sql_mysql: ERROR 1318 (Incorrect number of arguments for PROCEDURE nodeny.radcheck; expected 1, got 2): 42000
(0) sql: ERROR: Error getting check attributes
rlm_sql (sql): Released connection (0)
(0)     [sql] = fail
(0)   } # authorize = fail
(0) Invalid user (sql: rlm_sql_mysql: ERROR 1318 (Incorrect number of arguments for PROCEDURE nodeny.radcheck; expected 1, got 2): 42000): [vano+0] (from client nas port 15728656 cli 00:5A:0A:3D:07:5D)
(0) Using Post-Auth-Type Reject
(0) Post-Auth-Type sub-section not found.  Ignoring.
(0) # Executing group from file /usr/local/etc/raddb/sites-enabled/nodeny
(0) Delaying response for 1.000000 seconds
Waking up in 0.3 seconds.
(0) (0) Discarding duplicate request from client nas port 34285 - ID: 28 due to delayed response
Waking up in 0.6 seconds.
(0) (0) Discarding duplicate request from client nas port 34285 - ID: 28 due to delayed response
Waking up in 0.3 seconds.
(0) Sending delayed response
(0) Sent Access-Reject Id 28 from x.x.x.x:1812 to x.x.x.x:34285 length 20
Waking up in 3.9 seconds.
(0) Cleaning up request packet ID 28 with timestamp +13
Ready to process requests

UPDATE ip_pool SET tags=

UPDATE ip_pool SET tags=',0,' WHERE type='static' AND ip='2886729828' AND uid='1'
Строк: 1. Время выполнения sql: 0.0004 сек

unreachable code after return statement nody.js:873:8

UPDATE ip_pool SET tags=',nas1,0,' WHERE type='static' AND ip='2886729828' AND uid='1'
Строк: 1. Время выполнения sql: 0.0003 сек

unreachable code after return statement nody.js:873:8 

a      = 'ajUserSetIpLogin'
  domid  = 'mUser_ip_list'
  ipn    = '...'
  uid    = '1'
  val    = 'цифра'

DROP VIEW IF EXISTS v_ips;
CREATE ALGORITHM=MERGE VIEW v_ips AS
    SELECT
        IF(a.start IS NULL,0,1) AS auth, i.uid, INET_NTOA(i.ip) AS ip, i.ip AS ipn,
        i.type, i.`release`, IF(a.last<a.start, 0, a.last-a.start) AS tm_auth,
        a.start, a.last, a.properties, i.tags
    FROM ip_pool i
    LEFT JOIN auth_now a ON INET_NTOA(i.ip) = a.ip
    WHERE i.uid<>0;

(1) Received Access-Request Id 121 from 192.168.52.101:58802 to 192.168.52.100:1812 length 134
(1)   Service-Type = Framed-User
(1)   Framed-Protocol = PPP
(1)   NAS-Port = 15728682
(1)   NAS-Port-Type = Ethernet
(1)   User-Name = "vano+0"
(1)   Calling-Station-Id = "B0:50:DA:3D:E7:50"
(1)   Called-Station-Id = "irsh"
(1)   NAS-Port-Id = "ether5"
(1)   CHAP-Challenge = 0x7bf934303d6e2f7400715d45bc93ab62
(1)   CHAP-Password = 0x016a8f54b074c530544453e2e605114e04
(1)   NAS-Identifier = "irsh"
(1)   NAS-IP-Address = 192.168.52.102
(1) # Executing section authorize from file /usr/local/etc/raddb/sites-enabled/nodeny
(1)   authorize {
rlm_sql (sql): Reserved connection (1)
(1) sql: EXPAND call radcheck('%{User-Name}')
(1) sql:    --> call radcheck('vano=2B0')
(1) sql: Executing select query: call radcheck('vano=2B0')
(1) sql: WARNING: Cannot do check groups when group_membership_query is not set
rlm_sql (sql): Released connection (1)
(1)     [sql] = notfound
(1) pap: WARNING: No "known good" password found for the user.  Not setting Auth-Type
(1) pap: WARNING: Authentication will fail unless a "known good" password is available
(1)     [pap] = noop
(1)   } # authorize = noop
(1) ERROR: No Auth-Type found: rejecting the user via Post-Auth-Type = Reject
(1) Failed to authenticate the user
(1) Using Post-Auth-Type Reject
(1) Post-Auth-Type sub-section not found.  Ignoring.
(1) # Executing group from file /usr/local/etc/raddb/sites-enabled/nodeny
(1) Delaying response for 1.000000 seconds
Waking up in 0.3 seconds.
(1) (1) Discarding duplicate request from client irsh port 58802 - ID: 121 due to delayed response
Waking up in 0.6 seconds.
(1) (1) Discarding duplicate request from client irsh port 58802 - ID: 121 due to delayed response
Waking up in 0.4 seconds.
(1) Sending delayed response
(1) Sent Access-Reject Id 121 from 192.168.52.100:1812 to 192.168.52.101:58802 length 20
Waking up in 3.9 seconds.
(1) Cleaning up request packet ID 121 with timestamp +25
Ready to process requests

delete_stale_sessions = yes
        sql_user_name = ""
        default_user_profile = ""
        client_query = "SELECT id,nasname,shortname,type,secret FROM nas"
        authorize_check_query = "call radcheck('%{User-Name}', 0)"
        authorize_reply_query = "call radreply('%{User-Name}', '%{User-Password}')"
        safe_characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"
        auto_escape = no
   accounting {
        reference = "%{tolower:type.%{Acct-Status-Type}.query}"
    type {
     accounting-on {
     }
     accounting-off {
     }
     start {
        query = "call radupdate('%{User-Name}','%{Framed-IP-Address}',                        'user=%{Calling-Station-Id};nas=%{NAS-IP-Address};ses=%{Acct-Session-Id}')"
     }
     interim-update {
        query = "call radupdate('%{User-Name}','%{Framed-IP-Address}',                        'user=%{Calling-Station-Id};nas=%{NAS-IP-Address};ses=%{Acct-Session-Id}')"
     }
     stop {
        query = "call radstop('%{User-Name}', '%{Framed-IP-Address}')"
     }
    }
   }
   post-auth {
        reference = ".query"
        query = "call radupdate('%{User-Name}','%{reply:Framed-IP-Address}',                'user=%{Calling-Station-Id};nas=%{NAS-IP-Address};ses=%{Acct-Session-Id}')"
   }
  }
rlm_sql (sql): Driver rlm_sql_mysql (module rlm_sql_mysql) loaded and linked

server default {
    listen {
        type = auth
        ipaddr = *
        port = 1812
    }
    listen {
        type = acct
        ipaddr = *
        port = 0
    }
    authorize {
        sql
        pap
        chap
        mschap
    }
    authenticate {
        Auth-Type PAP {
            pap
        }
        Auth-Type CHAP {
            chap
        }
        Auth-Type MSCHAP {
            mschap
        }
    }
    preacct {
        acct_unique
        preprocess
    }
    accounting {
        sql
        exec
        if("%{sql:SELECT IF((SELECT 1 FROM v_ips i JOIN users u ON i.uid=u.id WHERE u.name='%{User-Name}' AND i.ip='%{Framed-IP-Address}'),1,0)}" < 1){
            update reply {
                mpd-drop-user = 1
            }
        }
    }
    session {
        radutmp
        sql
    }
    post-auth {
        sql
    }
    Post-Auth-Type REJECT {
        sql
    }
}

authorize_check_query = "call radcheck('%{User-Name}')"
        authorize_reply_query = "call radreply('%{User-Name}')"
        accounting {
            query = "call radupdate('%{User-Name}','%{Framed-IP-Address}',\
                    'user=%{Calling-Station-Id};nas=%{NAS-IP-Address}')"
            type {
                start {
                    query = "call radupdate('%{User-Name}','%{Framed-IP-Address}',\
                            'user=%{Calling-Station-Id};nas=%{NAS-IP-Address}')"
                }
            }
        }
        post-auth {
            query = "call radupdate('%{User-Name}','%{reply:Framed-IP-Address}',\
                'user=%{Calling-Station-Id};nas=%{NAS-IP-Address}')"
        }
}

DROP PROCEDURE IF EXISTS `radcheck`;
DELIMITER $$
CREATE PROCEDURE `radcheck` (IN login VARCHAR(64))
BEGIN
  DECLARE real_login VARCHAR(64) DEFAULT NULL;
  SELECT SUBSTRING_INDEX(login, '-', 1) INTO real_login;
  SELECT id, name, 'Password' AS Attribute, AES_DECRYPT(passwd,'xxxxx') AS Value,'=='
    FROM users WHERE name=real_login;
END$$
DELIMITER ;

(0) # Executing section authorize from file /usr/local/etc/raddb/sites-enabled/nodeny
(0)   authorize {
rlm_sql (sql): Reserved connection (0)
(0) sql: EXPAND call radcheck('%{User-Name}')
(0) sql:    --> call radcheck('vano-1')
(0) sql: Executing select query: call radcheck('vano-1')
(0) sql: ERROR: Failed to create the pair: Invalid vendor name in attribute name "Password"
(0) sql: ERROR: Error parsing user data from database result
(0) sql: ERROR: Error getting check attributes
rlm_sql (sql): Released connection (0)
(0)     [sql] = fail
(0)   } # authorize = fail
(0) Invalid user (sql: Failed to create the pair: Invalid vendor name in attribute name "Password"): [vano-1] (from client irsh port 15728764 cli B0:5A:D0:3D:E7:50)
(0) Using Post-Auth-Type Reject
(0) Post-Auth-Type sub-section not found.  Ignoring.
(0) # Executing group from file /usr/local/etc/raddb/sites-enabled/nodeny
(0) Delaying response for 1.000000 seconds

DROP PROCEDURE IF EXISTS `radcheck`;
DELIMITER $$
CREATE PROCEDURE `radcheck` (IN login VARCHAR(64))
BEGIN
  DECLARE real_login VARCHAR(64) DEFAULT NULL;
  SELECT SUBSTRING_INDEX(login, '-', 1) INTO real_login;
  SELECT id, name, 'Cleartext-Password' AS Attribute, AES_DECRYPT(passwd,'xxxxxxx') AS Value,'=='
    FROM users WHERE name=real_login;
END$$
DELIMITER ;

(0) # Executing section authorize from file /usr/local/etc/raddb/sites-enabled/nodeny
(0)   authorize {
rlm_sql (sql): Reserved connection (0)
(0) sql: EXPAND call radcheck('%{User-Name}')
(0) sql:    --> call radcheck('vano-1')
(0) sql: Executing select query: call radcheck('vano-1')
(0) sql: User found in radcheck table
(0) sql: WARNING: Cannot do check groups when group_membership_query is not set
rlm_sql (sql): Released connection (0)
(0)     [sql] = noop
(0) pap: WARNING: No "known good" password found for the user.  Not setting Auth-Type
(0) pap: WARNING: Authentication will fail unless a "known good" password is available
(0)     [pap] = noop
(0) chap:   &control:Auth-Type := CHAP
(0)     [chap] = ok
(0)     [mschap] = noop
(0)   } # authorize = ok
(0) Found Auth-Type = CHAP
(0) # Executing group from file /usr/local/etc/raddb/sites-enabled/nodeny
(0)   Auth-Type CHAP {
(0) chap: ERROR: &control:Cleartext-Password is required for authentication
(0)     [chap] = fail
(0)   } # Auth-Type CHAP = fail
(0) Failed to authenticate the user
(0) Using Post-Auth-Type Reject
(0) Post-Auth-Type sub-section not found.  Ignoring.
(0) # Executing group from file /usr/local/etc/raddb/sites-enabled/nodeny
(0) Delaying response for 1.000000 seconds
Waking up in 0.3 seconds.

DROP PROCEDURE IF EXISTS `radreply`;
DELIMITER $$
CREATE PROCEDURE `radreply`(IN login VARCHAR(64))
BEGIN
  DECLARE usr_id INT;
  DECLARE ip_index INT;
  DECLARE usr_ip VARCHAR(15) DEFAULT NULL;
  DECLARE real_login VARCHAR(64) DEFAULT NULL;

  SELECT SUBSTRING_INDEX(login, '-', 1) INTO real_login;
  SELECT CAST(REPLACE(login, real_login, '') AS UNSIGNED) INTO ip_index;

  SELECT id INTO usr_id FROM users WHERE name=real_login LIMIT 1;
  SELECT get_ip_by_num(usr_id, ip_index) INTO usr_ip;

  SELECT NULL,login,'Framed-IP-Address',usr_ip,'=';
  SELECT NULL,login,'Framed-IP-Netmask','255.255.255.255','=';
  SELECT NULL,login,'Framed-Protocol','PPP','=';
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `radupdate`;
DELIMITER $$
CREATE PROCEDURE `radupdate`(IN login VARCHAR(64), IN ip VARCHAR(16), IN properties VARCHAR(255))
BEGIN
  DECLARE usr_id INT;
  DECLARE ip_index INT;
  DECLARE usr_ip VARCHAR(15) DEFAULT NULL;
  DECLARE real_login VARCHAR(64) DEFAULT NULL;

  SELECT SUBSTRING_INDEX(login, '-', 1) INTO real_login;
  SELECT CAST(REPLACE(login, real_login, '') AS UNSIGNED) INTO ip_index;

  SELECT id INTO usr_id FROM users WHERE name=real_login LIMIT 1;
  SELECT get_ip_by_num(usr_id, ip_index) INTO usr_ip;
  CALL set_auth(usr_ip, CONCAT('mod=pppoe;',REPLACE(properties,':','')));
END$$
DELIMITER ;

root@localhost [3newnodeny]> call radreply('vano-1');
+------+--------+-------------------+--------------+---+
| NULL | login  | Framed-IP-Address | usr_ip       | = |
+------+--------+-------------------+--------------+---+
| NULL | vano-1 | Framed-IP-Address | 172.16.0.100 | = |
+------+--------+-------------------+--------------+---+
1 row in set (0.01 sec)

+------+--------+-------------------+-----------------+---+
| NULL | login  | Framed-IP-Netmask | 255.255.255.255 | = |
+------+--------+-------------------+-----------------+---+
| NULL | vano-1 | Framed-IP-Netmask | 255.255.255.255 | = |
+------+--------+-------------------+-----------------+---+
1 row in set (0.01 sec)

+------+--------+-----------------+-----+---+
| NULL | login  | Framed-Protocol | PPP | = |
+------+--------+-----------------+-----+---+
| NULL | vano-1 | Framed-Protocol | PPP | = |
+------+--------+-----------------+-----+---+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

root@localhost [3newnodeny]> call radreply('vano-0');
+------+--------+-------------------+--------------+---+
| NULL | login  | Framed-IP-Address | usr_ip       | = |
+------+--------+-------------------+--------------+---+
| NULL | vano-0 | Framed-IP-Address | 172.16.0.100 | = |
+------+--------+-------------------+--------------+---+
1 row in set (0.00 sec)

+------+--------+-------------------+-----------------+---+
| NULL | login  | Framed-IP-Netmask | 255.255.255.255 | = |
+------+--------+-------------------+-----------------+---+
| NULL | vano-0 | Framed-IP-Netmask | 255.255.255.255 | = |
+------+--------+-------------------+-----------------+---+
1 row in set (0.00 sec)

+------+--------+-----------------+-----+---+
| NULL | login  | Framed-Protocol | PPP | = |
+------+--------+-----------------+-----+---+
| NULL | vano-0 | Framed-Protocol | PPP | = |
+------+--------+-----------------+-----+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

root@localhost [3newnodeny]> call radreply('vano');
+------+-------+-------------------+--------------+---+
| NULL | login | Framed-IP-Address | usr_ip       | = |
+------+-------+-------------------+--------------+---+
| NULL | vano  | Framed-IP-Address | 172.16.0.100 | = |
+------+-------+-------------------+--------------+---+
1 row in set (0.00 sec)

+------+-------+-------------------+-----------------+---+
| NULL | login | Framed-IP-Netmask | 255.255.255.255 | = |
+------+-------+-------------------+-----------------+---+
| NULL | vano  | Framed-IP-Netmask | 255.255.255.255 | = |
+------+-------+-------------------+-----------------+---+
1 row in set (0.00 sec)

+------+-------+-----------------+-----+---+
| NULL | login | Framed-Protocol | PPP | = |
+------+-------+-----------------+-----+---+
| NULL | vano  | Framed-Protocol | PPP | = |
+------+-------+-----------------+-----+---+
1 row in set (0.00 sec)

root@localhost [3newnodeny]> call radreply('vano-1');
+------+--------+-------------------+--------------+---+
| NULL | login  | Framed-IP-Address | usr_ip       | = |
+------+--------+-------------------+--------------+---+
| NULL | vano-1 | Framed-IP-Address | 172.16.0.100 | = |
+------+--------+-------------------+--------------+---+
1 row in set (0.00 sec)

+------+--------+-------------------+-----------------+---+
| NULL | login  | Framed-IP-Netmask | 255.255.255.255 | = |
+------+--------+-------------------+-----------------+---+
| NULL | vano-1 | Framed-IP-Netmask | 255.255.255.255 | = |
+------+--------+-------------------+-----------------+---+
1 row in set (0.00 sec)

+------+--------+-----------------+-----+---+
| NULL | login  | Framed-Protocol | PPP | = |
+------+--------+-----------------+-----+---+
| NULL | vano-1 | Framed-Protocol | PPP | = |
+------+--------+-----------------+-----+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

root@localhost [3newnodeny]> call radupdate('vano');
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE 3newnodeny.radupdate; expected 3, got 1
root@localhost [3newnodeny]> call radupdate('vano-0');
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE 3newnodeny.radupdate; expected 3, got 1
root@localhost [3newnodeny]> call radupdate('vano-1');
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE 3newnodeny.radupdate; expected 3, got 1

authorize_check_query = "call radcheck('%{User-Name}', 0)"
    authorize_reply_query = "call radreply('%{User-Name}', '%{User-Password}')"
    accounting {
        reference = "%{tolower:type.%{Acct-Status-Type}.query}"
        type {
            start {
                query = "call radupdate('%{User-Name}','%{Framed-IP-Address}',\
                        'user=%{Calling-Station-Id};nas=%{NAS-IP-Address};ses=%{Acct-Session-Id}')"
            }
            interim-update {
                query = "${..start.query}"
            }
            stop {
                query = "call radstop('%{User-Name}', '%{Framed-IP-Address}')"
            }
        }
    }
    post-auth {
        query = "call radupdate('%{User-Name}','%{reply:Framed-IP-Address}',\
                'user=%{Calling-Station-Id};nas=%{NAS-IP-Address};ses=%{Acct-Session-Id}')"
    }
}

(0) # Executing section authorize from file /usr/local/etc/raddb/sites-enabled/nodeny
(0)   authorize {
rlm_sql (sql): Reserved connection (0)
(0) sql: EXPAND call radcheck('%{User-Name}', 0)
(0) sql:    --> call radcheck('vano-1', 0)
(0) sql: Executing select query: call radcheck('vano-1', 0)
(0) sql: ERROR: rlm_sql_mysql: ERROR 1318 (Incorrect number of arguments for PROCEDURE 3newnodeny.radcheck; expected 1, got 2): 42000
(0) sql: ERROR: Error getting check attributes
rlm_sql (sql): Released connection (0)
(0)     [sql] = fail
(0)   } # authorize = fail
(0) Invalid user (sql: rlm_sql_mysql: ERROR 1318 (Incorrect number of arguments for PROCEDURE 3newnodeny.radcheck; expected 1, got 2): 42000): [vano-1] (from client irsh port 15728766 cli B0:50:DA:3D:E7:50)
(0) Using Post-Auth-Type Reject
(0) Post-Auth-Type sub-section not found.  Ignoring.
(0) # Executing group from file /usr/local/etc/raddb/sites-enabled/nodeny
(0) Delaying response for 1.000000 seconds

[3newnodeny]> call radupdate('vano');
ERROR 1318 (42000): Incorrect number of arguments for PROCEDURE 3newnodeny.radupdate; expected 3, got 1

CREATE PROCEDURE `radupdate`(IN login VARCHAR(64), IN ip VARCHAR(16), IN properties VARCHAR(255))

        accounting_update_query = "call radupdate('%{User-Name}','%{Framed-IP-Address}',\
                'user=%{Calling-Station-Id};nas=%{NAS-IP-Address}')"

call radupdate('vano');

(0) sql:    --> call radcheck('vano-1', 0)
(0) sql: Executing select query: call radcheck('vano-1', 0)
(0) sql: ERROR: rlm_sql_mysql: ERROR 1318 (Incorrect number of arguments for PROCEDURE 3newnodeny.radcheck; expected 1, got 2): 42000
(0) sql: ERROR: Error getting check attributes
rlm_sql (sql): Released connection (0)
(0)     [sql] = fail
(0)   } # authorize = fail
(0) Invalid user (sql: rlm_sql_mysql: ERROR 1318 (Incorrect number of arguments for PROCEDURE 3newnodeny.radcheck; expected 1, got 2): 42000): [vano-1] (from client irsh port 15728839 cli B0:5A:DA:3D:E7:5D)
(0) Using Post-Auth-Type Reject
(0) Post-Auth-Type sub-section not found.  Ignoring.
(0) # Executing group from file /usr/local/etc/raddb/sites-enabled/nodeny
(0) Delaying response for 1.000000 seconds

root@localhost [3newnodeny]> SELECT get_ip_by_num(1, 1);
+---------------------+
| get_ip_by_num(1, 1) |
+---------------------+
| 172.16.0.100        |
+---------------------+
1 row in set (0.00 sec)

root@localhost [3newnodeny]> SELECT get_ip_by_num(1, 0);
+---------------------+
| get_ip_by_num(1, 0) |
+---------------------+
| 172.16.0.102        |
+---------------------+
1 row in set (0.00 sec)

root@localhost [3newnodeny]> call radcheck('vano-1');
+----+------+--------------------+----------+----+
| id | name | Attribute          | Value    | == |
+----+------+--------------------+----------+----+
|  1 | vano | Cleartext-Password | xxxxxx | == |
+----+------+--------------------+----------+----+
1 row in set (0.00 sec)

root@localhost [3newnodeny]> call radreply('vano-1');
+------+--------+-------------------+--------------+---+
| NULL | login  | Framed-IP-Address | usr_ip       | = |
+------+--------+-------------------+--------------+---+
| NULL | vano-1 | Framed-IP-Address | 172.16.0.102 | = |
+------+--------+-------------------+--------------+---+
1 row in set (0.00 sec)

+------+--------+-------------------+-----------------+---+
| NULL | login  | Framed-IP-Netmask | 255.255.255.255 | = |
+------+--------+-------------------+-----------------+---+
| NULL | vano-1 | Framed-IP-Netmask | 255.255.255.255 | = |
+------+--------+-------------------+-----------------+---+
1 row in set (0.00 sec)

+------+--------+-----------------+-----+---+
| NULL | login  | Framed-Protocol | PPP | = |
+------+--------+-----------------+-----+---+
| NULL | vano-1 | Framed-Protocol | PPP | = |
+------+--------+-----------------+-----+---+
1 row in set (0.00 sec)

root@localhost [3newnodeny]> call radreply('vano-0');
+------+--------+-------------------+--------------+---+
| NULL | login  | Framed-IP-Address | usr_ip       | = |
+------+--------+-------------------+--------------+---+
| NULL | vano-0 | Framed-IP-Address | 172.16.0.102 | = |
+------+--------+-------------------+--------------+---+
1 row in set (0.00 sec)

+------+--------+-------------------+-----------------+---+
| NULL | login  | Framed-IP-Netmask | 255.255.255.255 | = |
+------+--------+-------------------+-----------------+---+
| NULL | vano-0 | Framed-IP-Netmask | 255.255.255.255 | = |
+------+--------+-------------------+-----------------+---+
1 row in set (0.00 sec)

+------+--------+-----------------+-----+---+
| NULL | login  | Framed-Protocol | PPP | = |
+------+--------+-----------------+-----+---+
| NULL | vano-0 | Framed-Protocol | PPP | = |
+------+--------+-----------------+-----+---+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

DROP PROCEDURE IF EXISTS `radcheck`;
DELIMITER $$
CREATE PROCEDURE `radcheck` (IN login VARCHAR(64))
BEGIN
  DECLARE real_login VARCHAR(64) DEFAULT NULL;
  SELECT SUBSTRING_INDEX(login, '-', 1) INTO real_login;
  SELECT id, name, 'Cleartext-Password' AS Attribute, AES_DECRYPT(passwd,'xxxxxxx') AS Value,'=='
    FROM users WHERE name=real_login;
END$$
DELIMITER ;

authorize_check_query = "call radcheck('%{User-Name}', 0)"
    authorize_reply_query = "call radreply('%{User-Name}', '%{User-Password}')"
    accounting {
        reference = "%{tolower:type.%{Acct-Status-Type}.query}"
        type {
            start {
                query = "call radupdate('%{User-Name}','%{Framed-IP-Address}',\
                        'user=%{Calling-Station-Id};nas=%{NAS-IP-Address};ses=%{Acct-Session-Id}')"
            }
            interim-update {
                query = "${..start.query}"
            }
            stop {
                query = "call radstop('%{User-Name}', '%{Framed-IP-Address}')"
            }
        }
    }
    post-auth {
        query = "call radupdate('%{User-Name}','%{reply:Framed-IP-Address}',\
                'user=%{Calling-Station-Id};nas=%{NAS-IP-Address};ses=%{Acct-Session-Id}')"
    }
}

authorize_check_query = "call radcheck('%{User-Name}', 0)"

(0) # Executing section authorize from file /usr/local/etc/raddb/sites-enabled/nodeny
(0)   authorize {
rlm_sql (sql): Reserved connection (0)
(0) sql: EXPAND call radcheck('%{User-Name}')
(0) sql:    --> call radcheck('vano-1')
(0) sql: Executing select query: call radcheck('vano-1')
(0) sql: User found in radcheck table
(0) sql: WARNING: Cannot do check groups when group_membership_query is not set
rlm_sql (sql): Released connection (0)
(0)     [sql] = noop
(0) pap: WARNING: No "known good" password found for the user.  Not setting Auth-Type
(0) pap: WARNING: Authentication will fail unless a "known good" password is available
(0)     [pap] = noop
(0) chap:   &control:Auth-Type := CHAP
(0)     [chap] = ok
(0)     [mschap] = noop
(0)   } # authorize = ok
(0) Found Auth-Type = CHAP
(0) # Executing group from file /usr/local/etc/raddb/sites-enabled/nodeny
(0)   Auth-Type CHAP {
(0) chap: ERROR: &control:Cleartext-Password is required for authentication
(0)     [chap] = fail
(0)   } # Auth-Type CHAP = fail
(0) Failed to authenticate the user
(0) Using Post-Auth-Type Reject
(0) Post-Auth-Type sub-section not found.  Ignoring.
(0) # Executing group from file /usr/local/etc/raddb/sites-enabled/nodeny
(0) Delaying response for 1.000000 seconds

DROP PROCEDURE IF EXISTS `radcheck`;
DELIMITER $$
CREATE PROCEDURE `radcheck` (IN login VARCHAR(64))
BEGIN
  SELECT id,name,'Cleartext-Password' AS Attribute,AES_DECRYPT(passwd,'xxxxxxx') AS Value,':='
    FROM users WHERE name=login;
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `radreply`;
DELIMITER $$
CREATE PROCEDURE `radreply`(IN login VARCHAR(64))
BEGIN
  DECLARE usr_id INT;
  DECLARE usr_ip VARCHAR(15) DEFAULT NULL;

  SELECT id INTO usr_id FROM users WHERE name=login LIMIT 1;
  SELECT get_ip(usr_id) INTO usr_ip;

  SELECT NULL,login,'Framed-IP-Address',usr_ip,'=';
  SELECT NULL,login,'Framed-IP-Netmask','255.255.255.255','=';
  SELECT NULL,login,'Framed-Protocol','PPP','=';
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `radupdate`;
DELIMITER $$
CREATE PROCEDURE `radupdate`(IN login VARCHAR(64), IN ip VARCHAR(16), IN properties VARCHAR(255))
BEGIN
  DECLARE usr_id INT;
  DECLARE usr_ip VARCHAR(15) DEFAULT NULL;
  SELECT id INTO usr_id FROM users WHERE name=login LIMIT 1;
  SELECT get_ip(usr_id) INTO usr_ip;
  CALL set_auth(usr_ip, CONCAT('mod=pppoe;',REPLACE(properties,':','')));
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS `radstop`;
DELIMITER $$
CREATE PROCEDURE `radstop`(IN login VARCHAR(64))
BEGIN
  DECLARE usr_id INT;
  SELECT id INTO usr_id FROM users WHERE name=login LIMIT 1;
  DELETE FROM auth_now WHERE ip = get_ip(usr_id) LIMIT 1;
END$$
DELIMITER ;

BEGIN
    SELECT id, name, 'Cleartext-Password' AS Attribute, AES_DECRYPT(passwd, 'xxxxxxx') AS Value,':='
        FROM users u WHERE name=login AND
            IF(only_one_session < 1, true,
                NOT EXISTS (SELECT 1 FROM v_ips WHERE uid=u.id AND auth=1 AND last>(UNIX_TIMESTAMP() - 300) LIMIT 1)
            );
END

BEGIN
    DECLARE usr_id INT;
    DECLARE usr_ip VARCHAR(15) DEFAULT NULL;
    SELECT id INTO usr_id FROM users WHERE name=login AND password=AES_DECRYPT(passwd, 'xxxxxxxx') LIMIT 1;
    IF( usr_id IS NOT NULL ) THEN
        SELECT get_ip(usr_id) INTO usr_ip;
        SELECT NULL,login,'Framed-IP-Address',usr_ip,'=';
        SELECT NULL,login,'Framed-IP-Netmask','255.255.255.255','=';
        SELECT NULL,login,'Framed-Protocol','PPP','=';
    END IF;
END

BEGIN
    DECLARE usr_id INT;
    DECLARE auth_prop VARCHAR(255);
    DECLARE start_auth INT UNSIGNED;
    SELECT u.id, i.properties, i.start INTO usr_id, auth_prop, start_auth
        FROM v_ips i JOIN users u ON i.uid=u.id WHERE u.name=login AND i.ip=usr_ip AND i.auth=1;
    IF( usr_id IS NOT NULL ) THEN
        INSERT INTO auth_log (uid, ip, start, end, properties) VALUES
            (usr_id, INET_ATON(usr_ip), start_auth, UNIX_TIMESTAMP(), auth_prop);
        DELETE FROM auth_now WHERE ip=usr_ip LIMIT 1;
    END IF;
END

BEGIN
    IF( SELECT 1 FROM v_ips i JOIN users u ON i.uid=u.id WHERE u.name=login AND i.ip=usr_ip ) THEN
        CALL set_auth(usr_ip, CONCAT('mod=pppoe;',REPLACE(properties,':','')));
    END IF;
    UPDATE users SET name=login WHERE name=login LIMIT 1;
END

# Для некоторых насов нужно поменять разделитель с  '+'  на '=2B'


DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `radcheck`(IN `login` VARCHAR(64))
BEGIN
  DECLARE real_login VARCHAR(64) DEFAULT NULL;
  SELECT SUBSTRING_INDEX(login, '+', 1) INTO real_login;
  SELECT id, name, 'Cleartext-Password' AS Attribute, AES_DECRYPT(passwd,'xxxxxxxxxx') AS Value,':='
    FROM users WHERE name=real_login;
END$$
DELIMITER ;


DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `radreply`(IN `login` VARCHAR(64))
BEGIN
  DECLARE usr_id INT;
  DECLARE ip_index INT ;
  DECLARE usr_ip VARCHAR(15) DEFAULT NULL;
  DECLARE real_login VARCHAR(64) DEFAULT NULL;

  SELECT SUBSTRING_INDEX(login, '+', 1) INTO real_login;
  
  IF login REGEXP '[+][0-9]+$' THEN
  	SELECT CAST(REPLACE(REPLACE(login, real_login, ''),'+','') AS UNSIGNED) INTO ip_index;
  ELSE SET ip_index=0;END IF;

  SELECT id INTO usr_id FROM users WHERE name=real_login LIMIT 1;
  SELECT get_ip_by_num(usr_id, ip_index) INTO usr_ip;

  SELECT NULL,login,'Framed-IP-Address',usr_ip,'=';
  SELECT NULL,login,'Framed-IP-Netmask','255.255.255.255','=';
  SELECT NULL,login,'Framed-Protocol','PPP','=';
END$$
DELIMITER ;

DROP FUNCTION IF EXISTS `get_ip_by_num`;
DELIMITER $$
CREATE FUNCTION `get_ip_by_num` ( user_id INTEGER UNSIGNED, num INTEGER )
    RETURNS VARCHAR(15) NO SQL
BEGIN
    DECLARE user_ip VARCHAR(15);
    DECLARE real_ip VARCHAR(15);

    SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
        WHERE uid = user_id AND type = 'static'
            AND tags LIKE CONCAT('%,', num, ',%') LIMIT 1;
    IF( user_ip IS NOT NULL ) THEN RETURN user_ip; END IF;

    SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
        WHERE uid = user_id AND type='static' LIMIT 1;
    IF( user_ip IS NOT NULL ) THEN RETURN user_ip; END IF;

    SELECT 1 INTO real_ip FROM users_services WHERE uid = user_id AND tags LIKE '%,realip,%';
    UPDATE ip_pool SET uid = user_id, `release` = UNIX_TIMESTAMP() + 300
        WHERE id = (SELECT id FROM (
            (
              SELECT id, uid FROM ip_pool 
              WHERE uid = 0 AND type = 'dynamic' AND realip = IF(real_ip>0,1,0)
              LIMIT 1
            ) UNION (
              SELECT id, uid FROM ip_pool
              WHERE uid = user_id AND type = 'dynamic' AND realip = IF(real_ip>0,1,0)
              LIMIT 1
            )
        ) AS tbl ORDER BY uid DESC LIMIT 1);

    SELECT INET_NTOA(ip) INTO user_ip FROM ip_pool
        WHERE uid = user_id LIMIT 1;
    RETURN user_ip;
END$$
DELIMITER ; 


DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `radupdate`(IN `login` VARCHAR(64), IN `ip` VARCHAR(16), IN `properties` VARCHAR(255))
BEGIN
  DECLARE usr_id INT;
  DECLARE usr_ip VARCHAR(15) DEFAULT NULL;
  DECLARE real_login VARCHAR(64) DEFAULT NULL;
  DECLARE ip_index INT ;
  
  SELECT SUBSTRING_INDEX(login, '+', 1) INTO real_login;
  IF login REGEXP '[+][0-9]+$' THEN
  SELECT CAST(REPLACE(REPLACE(login, real_login, ''),'+','') AS UNSIGNED) INTO ip_index;
  ELSE SET ip_index=0;END IF;
 
  SELECT id INTO usr_id FROM users WHERE name=real_login LIMIT 1;
  SELECT get_ip_by_num(usr_id, ip_index) INTO usr_ip;
  CALL set_auth(ip, CONCAT('mod=pppoe;',REPLACE(properties,':','')));
END$$
DELIMITER ;

(0) # Executing section authorize from file /usr/local/etc/raddb/sites-enabled/nodeny
(0)   authorize {
rlm_sql (sql): Reserved connection (0)
(0) sql: EXPAND call radcheck('%{User-Name}')
(0) sql:    --> call radcheck('vano=2B1')
(0) sql: Executing select query: call radcheck('vano=2B1')
(0) sql: WARNING: Cannot do check groups when group_membership_query is not set
rlm_sql (sql): Released connection (0)
(0)     [sql] = notfound
(0) pap: WARNING: No "known good" password found for the user.  Not setting Auth-Type
(0) pap: WARNING: Authentication will fail unless a "known good" password is available
(0)     [pap] = noop
(0) chap:   &control:Auth-Type := CHAP
(0)     [chap] = ok
(0)     [mschap] = noop
(0)   } # authorize = ok
(0) Found Auth-Type = CHAP
(0) # Executing group from file /usr/local/etc/raddb/sites-enabled/nodeny
(0)   Auth-Type CHAP {
(0) chap: ERROR: &control:Cleartext-Password is required for authentication
(0)     [chap] = fail
(0)   } # Auth-Type CHAP = fail
(0) Failed to authenticate the user
(0) Using Post-Auth-Type Reject
(0) Post-Auth-Type sub-section not found.  Ignoring.
(0) # Executing group from file /usr/local/etc/raddb/sites-enabled/nodeny
(0) Delaying response for 1.000000 seconds

0) # Executing section authorize from file /usr/local/etc/raddb/sites-enabled/nodeny
(0)   authorize {
rlm_sql (sql): Reserved connection (0)
(0) sql: EXPAND call radcheck('%{User-Name}')
(0) sql:    --> call radcheck('vano=2B1')
(0) sql: Executing select query: call radcheck('vano=2B1')
(0) sql: User found in radcheck table
(0) sql: Conditional check items matched, merging assignment check items
(0) sql:   Cleartext-Password := "1111111"
(0) sql: EXPAND call radreply('%{User-Name}','%{User-Password}')
(0) sql:    --> call radreply('vano=2B1','')
(0) sql: Executing select query: call radreply('vano=2B1','')
(0) sql: ERROR: rlm_sql_mysql: ERROR 1318 (Incorrect number of arguments for PROCEDURE 3newnodeny.radreply; expected 1, got 2): 42000
(0) sql: ERROR: SQL query error getting reply attributes
rlm_sql (sql): Released connection (0)
(0)     [sql] = fail
(0)   } # authorize = fail
(0) Invalid user (sql: rlm_sql_mysql: ERROR 1318 (Incorrect number of arguments for PROCEDURE 3newnodeny.radreply; expected 1, got 2): 42000): [vano+1] (from client irsh port 15728865 cli B0:5A:DA:3D:E7:5D)
(0) Using Post-Auth-Type Reject
(0) Post-Auth-Type sub-section not found.  Ignoring.
(0) # Executing group from file /usr/local/etc/raddb/sites-enabled/nodeny

С такой процедурой

DROP PROCEDURE IF EXISTS `radreply`;
DELIMITER $$
CREATE PROCEDURE `radreply`(IN login VARCHAR(64))
BEGIN
  DECLARE usr_id INT;
  DECLARE ip_index INT ;
  DECLARE usr_ip VARCHAR(15) DEFAULT NULL;
  DECLARE real_login VARCHAR(64) DEFAULT NULL;

  SELECT SUBSTRING_INDEX(login, '=2B', 1) INTO real_login;
  
  IF login REGEXP '[=2B][0-9]+$' THEN
  	SELECT CAST(REPLACE(REPLACE(login, real_login, ''),'=2B','') AS UNSIGNED) INTO ip_index;
  ELSE SET ip_index=0;END IF;

  SELECT id INTO usr_id FROM users WHERE name=real_login LIMIT 1;
  SELECT get_ip_by_num(usr_id, ip_index) INTO usr_ip;

  SELECT NULL,login,'Framed-IP-Address',usr_ip,'=';
  SELECT NULL,login,'Framed-IP-Netmask','255.255.255.255','=';
  SELECT NULL,login,'Framed-Protocol','PPP','=';
END$$
DELIMITER ;

0) # Executing section authorize from file /usr/local/etc/raddb/sites-enabled/nodeny
(0)   authorize {
rlm_sql (sql): Reserved connection (0)
(0) sql: EXPAND call radcheck('%{User-Name}')
(0) sql:    --> call radcheck('vano-1')
(0) sql: Executing select query: call radcheck('vano-1')
(0) sql: User found in radcheck table
(0) sql: Conditional check items matched, merging assignment check items
(0) sql:   Cleartext-Password := "111111"
(0) sql: EXPAND call radreply('%{User-Name}','%{User-Password}')
(0) sql:    --> call radreply('vano-1','')
(0) sql: Executing select query: call radreply('vano-1','')
(0) sql: ERROR: rlm_sql_mysql: ERROR 1318 (Incorrect number of arguments for PROCEDURE 3newnodeny.radreply; expected 1, got 2): 42000
(0) sql: ERROR: SQL query error getting reply attributes
rlm_sql (sql): Released connection (0)
(0)     [sql] = fail
(0)   } # authorize = fail
(0) Invalid user (sql: rlm_sql_mysql: ERROR 1318 (Incorrect number of arguments for PROCEDURE 3newnodeny.radreply; expected 1, got 2): 42000): [vano-1] (from client irsh port 15728866 cli B0:5A:DA:3D:E7:5D)
(0) Using Post-Auth-Type Reject
(0) Post-Auth-Type sub-section not found.  Ignoring.
(0) # Executing group from file /usr/local/etc/raddb/sites-enabled/nodeny
(0) Delaying response for 1.000000 seconds

С такой процедурой

DROP PROCEDURE IF EXISTS `radreply`;
DELIMITER $$
CREATE PROCEDURE `radreply`(IN login VARCHAR(64))
BEGIN
  DECLARE usr_id INT;
  DECLARE ip_index INT ;
  DECLARE usr_ip VARCHAR(15) DEFAULT NULL;
  DECLARE real_login VARCHAR(64) DEFAULT NULL;

  SELECT SUBSTRING_INDEX(login, '-', 1) INTO real_login;
  
  IF login REGEXP '[-][0-9]+$' THEN
  	SELECT CAST(REPLACE(REPLACE(login, real_login, ''),'-','') AS UNSIGNED) INTO ip_index;
  ELSE SET ip_index=0;END IF;

  SELECT id INTO usr_id FROM users WHERE name=real_login LIMIT 1;
  SELECT get_ip_by_num(usr_id, ip_index) INTO usr_ip;

  SELECT NULL,login,'Framed-IP-Address',usr_ip,'=';
  SELECT NULL,login,'Framed-IP-Netmask','255.255.255.255','=';
  SELECT NULL,login,'Framed-Protocol','PPP','=';
END$$
DELIMITER ;