[Help-gnu-radius] problem with auth_query

[Дмитрий Торов]

hi all

the problem:
radius server can't understend auth_query var..  it writes  SQL: invalid 
operator:
logs:
Mar 23 13:39:55 Auth.debug: files.c:943:client_lookup_ip: Found secret for 
127.0.0.1/255.255.255.255 (127.0.0.1): secret
Mar 23 13:39:55 Auth.debug: files.c:943:client_lookup_ip: Found secret for 
127.0.0.1/255.255.255.255 (127.0.0.1): secret
Mar 23 13:39:55 Auth.debug: files.c:698:hints_setup: called for `torov'
Mar 23 13:39:55 Auth.debug: files.c:1433:paircmp: returning 0
Mar 23 13:39:55 Auth.debug: files.c:722:hints_setup: matched DEFAULT at 
/usr/local/etc/raddb/hints:4
Mar 23 13:39:55 Auth.debug: files.c:742:hints_setup: new name is `torov'
Mar 23 13:39:55 Auth.debug: files.c:856:huntgroup_access: returning 1
Mar 23 13:39:55 Auth.debug: files.c:324:user_find_sym: looking for torov
Mar 23 13:39:55 Auth.debug: mysql.c:117:rad_mysql_reconnect: connected to 
localhost
Mar 23 13:39:55 Auth.debug: mysql.c:216:rad_mysql_exec: query: SELECT 
attr,value,op FROM attrib WHERE user_name='torov' AND op IS NOT NULL
Mar 23 13:39:55 Auth.debug: mysql.c:57:do_mysql_query: called with SELECT 
attr,value,op FROM attrib WHERE user_name='torov' AND op IS NOT NULL
Mar 23 13:39:55 Auth.debug: mysql.c:67:do_mysql_query: MYSQL query returned 0
Mar 23 13:39:55 Auth.debug: mysql.c:225:rad_mysql_exec: got 2 rows
Mar 23 13:39:55 Auth.notice: SQL: invalid operator:
Mar 23 13:39:55 Auth.debug: check_item: Group = (STRING) *LOCKED_ACCOUNT*
Mar 23 13:39:55 Auth.debug: files.c:1327:paircmp: trying 1
Mar 23 13:39:55 Auth.debug: auth_item: User-Name = (STRING) torov
Mar 23 13:39:55 Auth.debug: mysql.c:216:rad_mysql_exec: query: SELECT 
user_group FROM groups WHERE user_name = 'torov'
Mar 23 13:39:55 Auth.debug: mysql.c:57:do_mysql_query: called with SELECT 
user_group FROM groups WHERE user_name = 'torov'
Mar 23 13:39:55 Auth.debug: mysql.c:67:do_mysql_query: MYSQL query returned 0
Mar 23 13:39:55 Auth.debug: mysql.c:225:rad_mysql_exec: got 1 rows
Mar 23 13:39:55 Auth.debug: files.c:1425:paircmp: compare: 1
Mar 23 13:39:55 Auth.debug: files.c:1433:paircmp: returning -1
Mar 23 13:39:55 Auth.notice: SQL: invalid operator:
Mar 23 13:39:55 Auth.debug: check_item: NAS-Port-Id <= (INTEGER) 400
Mar 23 13:39:55 Auth.debug: files.c:1327:paircmp: trying 1
Mar 23 13:39:55 Auth.debug: files.c:1327:paircmp: trying 4
Mar 23 13:39:55 Auth.debug: files.c:1327:paircmp: trying 2
Mar 23 13:39:55 Auth.debug: files.c:1327:paircmp: trying 1036
Mar 23 13:39:55 Auth.debug: files.c:1327:paircmp: trying 2101
Mar 23 13:39:55 Auth.debug: files.c:1433:paircmp: returning -1
Mar 23 13:39:55 Auth.notice: SQL: invalid operator:
Mar 23 13:39:55 Auth.debug: check_item: NAS-Port-Id <= (INTEGER) 400
Mar 23 13:39:55 Auth.debug: files.c:1327:paircmp: trying 1
Mar 23 13:39:55 Auth.debug: files.c:1327:paircmp: trying 4
Mar 23 13:39:55 Auth.debug: files.c:1327:paircmp: trying 2
Mar 23 13:39:55 Auth.debug: files.c:1327:paircmp: trying 1036
Mar 23 13:39:55 Auth.debug: files.c:1327:paircmp: trying 2101
Mar 23 13:39:55 Auth.debug: files.c:1433:paircmp: returning -1
Mar 23 13:39:55 Auth.debug: files.c:337:user_find_sym: returning 0
Mar 23 13:39:55 Auth.notice: (Access-Request me 14 "torov"): No such user 
[torov]

config:
bash-2.05b# cat users
DEFAULT         Auth-Type = Local, Password-Location = SQL
        Service-Type = Framed-User,
                Framed-Protocol = PPP

guest   Auth-Type = Accept,
                Simultaneous-Use = 10
        Service-Type = Framed-User,
                Framed-Protocol = PPP

sqlserver:
i think problem is here, but where????
                
bash-2.05b# cat sqlserver

interface mysql

server localhost
port 3306
login radius
password radpasswd

keepopen yes

idle_timeout 14400

doauth yes

auth_db radius

group_query SELECT user_group FROM groups WHERE user_name = '%u'
auth_query SELECT password FROM passwd WHERE user_name='%u' AND active='Y'

check_attr_query  SELECT attr,value,op \
                  FROM attrib \
                  WHERE user_name='%u' AND op IS NOT NULL
reply_attr_query  SELECT attr,value \
                  FROM attrib \
                  WHERE user_name='%u' AND op IS NULL
#########
# Accounting-relevant settings

# Enable/Disable Mysql accounting: default no
doacct yes

# accounting database name
acct_db radius

# Query to be used on session start
acct_start_query     INSERT INTO calls \
                     VALUES(%C{Acct-Status-Type},\
                            '%u',\
                            '%C{Realm-Name}',\
                            now(),\
                            '%C{NAS-IP-Address}',\
                            %C{NAS-Port-Id},\
                            '%C{Acct-Session-Id}',\
                            0,\
                            0,\
                            0,\
                            0,\
                            '%C{Framed-IP-Address}',\
                            '%C{Called-Station-Id}',\
                            '%C{Calling-Station-Id}')

# Query to be used on session end
acct_stop_query      UPDATE calls \
                     SET status=%C{Acct-Status-Type},\
                         acct_session_time=%C{Acct-Session-Time},\
                         acct_input_octets=%C{Acct-Input-Octets},\
                         acct_output_octets=%C{Acct-Output-Octets},\
                         connect_term_reason=%C{Acct-Terminate-Cause} \
                     WHERE user_name='%C{User-Name}' \
                       AND status = 1 \
                       AND acct_session_id='%C{Acct-Session-Id}'

# Query to be used on receiving a keepalive record
acct_alive_query     UPDATE calls \
                     SET acct_session_time=%C{Acct-Session-Time},\
                         acct_input_octets=%C{Acct-Input-Octets},\
                         acct_output_octets=%C{Acct-Output-Octets},\
                         framed_ip_address='%C{Framed-IP-Address}' \
                     WHERE user_name='%C{User-Name}' \
                       AND status = 1 \
                       AND acct_session_id='%C{Acct-Session-Id}'

# Query to be used when a NAS goes down, i.e. when it sends
# Accounting-Off packet
acct_nasdown_query   UPDATE calls \
                     SET status=2,\
                        
acct_session_time=unix_timestamp(now())-unix_timestamp(event_date_time) \
                     WHERE status=1 AND nas_ip_address='%C{NAS-IP-Address}'
#SQL92: acct_session_time=extract('epoch' from age(now(),event_date_time))

# Query to be used when a NAS goes up, i.e. when it sends
# Accounting-On packet
acct_nasup_query   UPDATE calls \
                   SET status=3,\
                        
acct_session_time=unix_timestamp(now())-unix_timestamp(event_date_time) \
                   WHERE status=1 AND nas_ip_address='%C{NAS-IP-Address}'


# Multiple Login Checking

mlc_user_query     SELECT user_name,nas_ip_address,nas_port_id,acct_session_id \
                   FROM calls \
                   WHERE user_name='%C{User-Name}' \
                   AND status = 1

mlc_realm_query    SELECT user_name,nas_ip_address,nas_port_id,acct_session_id \
                   FROM calls \
                   WHERE realm_name='%C{Realm-Name}'

mlc_stop_query     UPDATE calls \
                     SET status=4,\
                         
acct_session_time=unix_timestamp(now())-unix_timestamp(event_date_time) \
                     WHERE user_name='%C{User-Name}' \
                       AND status = 1 \
                       AND acct_session_id='%C{Acct-Session-Id}'                


config:
[cut]
auth {
        max-requests 127;
        request-cleanup-delay 2;
        detail yes;
        detail-file-name "=nas_name(request_source_ip()) + \"/detail.auth\"";
        strip-names yes;
        checkrad-assume-logged no;
        listen 172.22.1.155;
};
[cut]

bash-2.05b# radiusd -V
radiusd: GNU Radius version 1.3 (i386-unknown-freebsd4.10)
Compilation platform: FreeBSD
Debugging flags:
Compilation flags: USE_SERVER_GUILE USE_SQL_MYSQL USE_SNMP USE_LOADABLE_MODULES
Compilation defaults:
Ports in use:
 AUTH: 1812
 ACCT: 1813
Paths:
 configuration directory: /usr/local/etc/raddb
 logging directory:       /var/log
 accounting directory:    /var/log/radacct
 pidfile directory:       /var/run

Report bugs to <address@hidden>


any ideas?

-- 
Best regards
Dmitriy