sql authorize with password and mac
Marcin
marcin at nicram.net
Mon Apr 20 15:48:21 CEST 2015
Hi,
I've that config for query:
#v+
authorize_check_query = "SELECT \
id, lower(name) as UserName , 'Cleartext-Password' as Attribute
, passwd as Value, ':=' as op \
FROM nodes \
WHERE name = '%{User-Name}'\
UNION \
SELECT n.id, lower(name) as UserName, 'Calling-Station-Id' as
Attribute, upper(mac) as Value, ':=' as op FROM \
nodes n, macs m \
WHERE lower(name) = '%{User-Name}' and access=1 AND n.id = m.nodeid
\
UNION \
SELECT id, lower(name) as UserName , 'Simultaneous-Use' as
Attribute, '1' as Value, ':=' as op \
FROM nodes \
WHERE name = '%{User-Name}'\
;"
#v-
callint station id from database doesn't contain proper mac, but
authorization is accepted
#v+
(0) Received Access-Request Id 100 from 172.21.7.176:43226 to
172.21.7.175:1812 length 145
(0) Service-Type = Framed-User
(0) Framed-Protocol = PPP
(0) NAS-Port = 15728670
(0) NAS-Port-Type = Ethernet
(0) User-Name = 'serwis'
(0) Calling-Station-Id = '08:00:27:D6:04:2D'
(0) Called-Station-Id = 'MtekPPPoE'
(0) NAS-Port-Id = 'bridge1'
(0) CHAP-Challenge = 0xc2d72642e5e288f7079288c64afa13bb
(0) CHAP-Password = 0x0140f664ed644130bfdf53268c444c8382
(0) NAS-Identifier = 'TestowyMT'
(0) NAS-IP-Address = 172.21.7.176
(0) # Executing section authorize from file
/etc/freeradius/sites-enabled/default
(0) authorize {
(0) policy filter_username {
(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 =~ /\.\./ ) {
(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) } # policy filter_username = notfound
(0) [preprocess] = ok
(0) chap: &control:Auth-Type := CHAP
(0) [chap] = ok
(0) [mschap] = noop
(0) [digest] = noop
(0) suffix: Checking for suffix after "@"
(0) suffix: No '@' in User-Name = "serwis", 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) redundant (null) {
(0) sqlms: EXPAND %{User-Name}
(0) sqlms: --> serwis
(0) sqlms: SQL-User-Name set to 'serwis'
rlm_sql (sqlms): 0 of 0 connections in use. You may need to increase
"spare"
rlm_sql (sqlms): Opening additional connection (0), 1 of 10 pending slots
used
rlm_sql_postgresql: Connecting using parameters: dbname='lms'
host='172.21.7.29' port=5432 user='user' password='pass'
Connected to database 'lms' on '172.21.7.29' server version 90401, protocol
version 3, backend PID 17929
rlm_sql (sqlms): Reserved connection (0)
(0) sqlms: EXPAND SELECT id, lower(name) as UserName ,
'Cleartext-Password' as Attribute , passwd as Value, ':=' as op FROM
nodes WHERE name = '%{User-Name}' UNION SELECT n.id, lower(name)
as UserName, 'Calling-Station-Id' as Attribute, upper(mac) as Value, ':='
as op FROM nodes n, macs m WHERE lower(name) = '%{User-Name}' and
access=1 AND n.id = m.nodeid UNION SELECT id, lower(name) as
UserName , 'Simultaneous-Use' as Attribute, '1' as Value, ':=' as
op FROM nodes WHERE name = '%{User-Name}' ;
(0) sqlms: --> SELECT id, lower(name) as UserName ,
'Cleartext-Password' as Attribute , passwd as Value, ':=' as op FROM
nodes WHERE name = 'serwis' UNION SELECT n.id, lower(name) as
UserName, 'Calling-Station-Id' as Attribute, upper(mac) as Value, ':=' as
op FROM nodes n, macs m WHERE lower(name) = 'serwis' and access=1
AND n.id = m.nodeid UNION SELECT id, lower(name) as UserName ,
'Simultaneous-Use' as Attribute, '1' as Value, ':=' as op FROM
nodes WHERE name = 'serwis' ;
(0) sqlms: Executing select query: SELECT id, lower(name) as
UserName , 'Cleartext-Password' as Attribute , passwd as Value, ':=' as
op FROM nodes WHERE name = 'serwis' UNION SELECT n.id,
lower(name) as UserName, 'Calling-Station-Id' as Attribute, upper(mac) as
Value, ':=' as op FROM nodes n, macs m WHERE lower(name) =
'serwis' and access=1 AND n.id = m.nodeid UNION SELECT id,
lower(name) as UserName , 'Simultaneous-Use' as Attribute, '1' as Value,
':=' as op FROM nodes WHERE name = 'serwis' ;
rlm_sql_postgresql: Status: PGRES_TUPLES_OK
rlm_sql_postgresql: query affected rows = 3 , fields = 5
(0) sqlms: User found in radcheck table
(0) sqlms: Conditional check items matched, merging assignment check items
(0) sqlms: Cleartext-Password := 'serwis*'
(0) sqlms: Simultaneous-Use := 1
(0) sqlms: Calling-Station-Id := '00:02:2D:8B:67:C3'
(0) sqlms: EXPAND SELECT id, lower(name) as UserName ,
'Framed-IP-Address' as Attribute, inet_ntoa(ipaddr) as Value, '=' as
op FROM nodes WHERE name = '%{User-Name}' UNION
SELECT id, lower(name) as UserName , 'Acct-Interim-Interval' as Attribute ,
'300' as Value, '=' as op FROM nodes WHERE
name='%{User-Name}';
(0) sqlms: --> SELECT id, lower(name) as UserName ,
'Framed-IP-Address' as Attribute, inet_ntoa(ipaddr) as Value, '=' as
op FROM nodes WHERE name = 'serwis' UNION SELECT id,
lower(name) as UserName , 'Acct-Interim-Interval' as Attribute , '300' as
Value, '=' as op FROM nodes WHERE name='serwis';
(0) sqlms: Executing select query: SELECT id, lower(name) as
UserName , 'Framed-IP-Address' as Attribute, inet_ntoa(ipaddr) as Value,
'=' as op FROM nodes WHERE name = 'serwis' UNION
SELECT id, lower(name) as UserName , 'Acct-Interim-Interval' as Attribute ,
'300' as Value, '=' as op FROM nodes WHERE
name='serwis';
rlm_sql_postgresql: Status: PGRES_TUPLES_OK
rlm_sql_postgresql: query affected rows = 2 , fields = 5
(0) sqlms: User found in radreply table, merging reply items
(0) sqlms: Acct-Interim-Interval = 300
(0) sqlms: Framed-IP-Address = 192.168.168.254
rlm_sql (sqlms): Released connection (0)
rlm_sql (sqlms): 0 of 1 connections in use. Need more spares
rlm_sql (sqlms): Opening additional connection (1), 1 of 9 pending slots
used
rlm_sql_postgresql: Connecting using parameters: dbname='lms'
host='172.21.7.29' port=5432 user='user' password='pass'
Connected to database 'lms' on '172.21.7.29' server version 90401, protocol
version 3, backend PID 17930
(0) [sqlms] = ok
(0) } # redundant (null) = 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 = CHAP
(0) # Executing group from file /etc/freeradius/sites-enabled/default
(0) Auth-Type CHAP {
(0) chap: Comparing with "known good" Cleartext-Password
(0) chap: CHAP user "serwis" authenticated successfully
(0) [chap] = ok
(0) } # Auth-Type CHAP = ok
(0) # Executing section session from file
/etc/freeradius/sites-enabled/default
(0) session {
(0) radutmp: EXPAND /var/log/freeradius/radutmp
(0) radutmp: --> /var/log/freeradius/radutmp
(0) radutmp: EXPAND %{User-Name}
(0) radutmp: --> serwis
(0) [radutmp] = ok
(0) } # session = ok
(0) # Executing section post-auth from file
/etc/freeradius/sites-enabled/default
(0) post-auth {
(0) update {
(0) No attributes updated
(0) } # update = noop
(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 = noop
(0) Sent Access-Accept Id 100 from 172.21.7.175:1812 to 172.21.7.176:43226
length 0
(0) Acct-Interim-Interval = 300
(0) Framed-IP-Address = 192.168.168.254
(0) Finished request
Waking up in 4.9 seconds.
#v-
Why authorization is ok when calling station is different from database?
--
Pozdrawiam
Marcin / nicraM
More information about the Freeradius-Users
mailing list