Freeradius+Postfresqk+MAC problem

[Christoffer Dahl Petersen]

Hi!

As I wrote earlier in this list, I'm trying to get Freeradius to authenticate my clients based on theirs NIC's MAC.
This works great as long as I use the "users" file:
DEFAULT Calling-Station-Id == "CLIENT NIC", Auth-Type := Accept
        Filter-ID="profile="">

Now I'm trying to use a Postgresql as backend, but it won't work.
Here is my radiusd.conf (the entire conf file is in the bottom of the mail):
....
$INCLUDE  ${confdir}/postgresql.conf
....
authorize {
        preprocess
        sql
}


Here is my postgresql.conf:
sql {
        driver = "rlm_sql_postgresql"
        server = "localhost"

        login = "radius"
        password = "123456"

        radius_db = "radius"

        acct_table1 = "radacct"
        acct_table2 = "radacct"

        authcheck_table = "radcheck"
        authreply_table = "radreply"

        groupcheck_table = "radgroupcheck"
        groupreply_table = "radgroupreply"

        usergroup_table = "usergroup"

        deletestalesessions = yes

        sqltrace = yes
        sqltracefile = ${logdir}/sqltrace.sql

        num_sql_socks = 5

        sql_user_name = "%{User-Name}"
        SQL_User_Name = "%{User-Name}"

        authorize_check_query = "SELECT id, UserName, Attribute, Value, Op \
FROM ${authcheck_table} WHERE username = '%{SQL-User-Name}' ORDER BY id"

#       authorize_reply_query = "SELECT id, UserName, Attribute, Value, Op \
# FROM ${authreply_table} WHERE username = '%{SQL-User-Name}' ORDER BY id"


#       authenticate_query = "SELECT Value,Attribute FROM ${authcheck_table} \
# WHERE UserName = '%{User-Name}' AND \
# ( Attribute = 'User-Password' OR Attribute = 'Crypt-Password' ) ORDER BY Attribute DESC"

}


Here is a dump of my database:
[EMAIL PROTECTED] 172.16.0.10]# psql -U radius
radius=> select * from radcheck;
id |     username      | attribute      | op |  value
----+-------------------+----------------+----+---------
  1 | 00-04-23-4d-c4-3d | User-Password  | == | 123456
  2 | 00-20-e0-8d-05-94 | User-Password  | == | 123456
(2 rows)


And here is what my log says:
Jul 12 14:39:02 linux radiusd: ^IUser-Name = "00-20-e0-8d-05-94"
Jul 12 14:39:02 linux radiusd: ^IUser-Password = "123456"
Jul 12 14:39:02 linux radiusd: ^INAS-IP-Address = 172.16.0.10
Jul 12 14:39:02 linux radiusd: ^INAS-Port = 0
Jul 12 14:39:02 linux radiusd: rlm_sql (sql): Reserving sql socket id: 3
Jul 12 14:39:02 linux radiusd: rlm_sql_postgresql: query: SELECT id, UserName, Attribute, Value, Op FROM radcheck WHERE username = '00-20-e0-8d-05-94' ORDER BY id
Jul 12 14:39:02 linux postgres[19980]: [5-1] LOG:  00000: duration: 5.637 ms
Jul 12 14:39:02 linux postgres[19980]: [5-2] LOCATION:  exec_simple_query, postgres.c:960
Jul 12 14:39:02 linux postgres[19980]: [6-1] LOG:  00000: duration: 5.637 ms  statement: SELECT id, UserName, Attribute, Value, Op FROM radcheck WHERE username =
Jul 12 14:39:02 linux postgres[19980]: [6-2]  '00-20-e0-8d-05-94' ORDER BY id
Jul 12 14:39:02 linux postgres[19980]: [6-3] LOCATION:  exec_simple_query, postgres.c:974
Jul 12 14:39:02 linux radiusd: rlm_sql_postgresql: Status: PGRES_TUPLES_OK
Jul 12 14:39:02 linux radiusd: rlm_sql_postgresql: affected rows =
Jul 12 14:39:02 linux radiusd: rlm_sql (sql): No matching entry in the database for request from user [00-20-e0-8d-05-94]
Jul 12 14:39:02 linux radiusd: rlm_sql (sql): Released sql socket id: 3
Jul 12 14:39:02 linux radiusd: Login incorrect: [00-20-e0-8d-05-94/123456] (from client testap1 port 0)
Jul 12 14:39:05 linux radiusd: rad_recv: Access-Request packet from host 172.16.0.10:6001, id=63, length=69
Jul 12 14:39:05 linux radiusd: Sending Access-Reject of id 63 to 172.16.0.10:6001


I really don't know what I'm doing wrong - Could anyone of give me a hint?
If you need to see any other configuration files please let me know.

Thanks

Christoffer

Me entire radiusd.conf:
prefix = /usr
exec_prefix = /usr
sysconfdir = /etc
localstatedir = /var
sbindir = /usr/sbin
logdir = ${localstatedir}/log/radius
raddbdir = ${sysconfdir}/raddb
radacctdir = ${logdir}/radacct
confdir = ${raddbdir}
run_dir = ${localstatedir}/run/radiusd
log_file = ${logdir}/radius.log
libdir = /usr/lib
pidfile = ${run_dir}/radiusd.pid

user = radiusd
group = radiusd

max_request_time = 30

delete_blocked_requests = no

cleanup_delay = 5

max_requests = 1024

bind_address = *

port = 0

hostname_lookups = no

allow_core_dumps = yes

regular_expressions     = yes
extended_expressions    = yes

log_stripped_names = no

log_auth = yes

log_auth_badpass = yes
log_auth_goodpass = yes

usercollide = no

lower_user = no
lower_pass = no

nospace_user = no
nospace_pass = no

checkrad = ${sbindir}/checkrad

security {
        max_attributes = 200
        reject_delay = 1
        status_server = no
}

proxy_requests  = yes
$INCLUDE  ${confdir}/proxy.conf

$INCLUDE  ${confdir}/clients.conf

thread pool {
        start_servers = 5
        max_servers = 32
        min_spare_servers = 3
        max_spare_servers = 10
        max_requests_per_server = 0
}

modules {
        pap {
                encryption_scheme = crypt
        }
        chap {
                authtype = CHAP
        }
        pam {
                pam_auth = radiusd
        }
        unix {
                cache = no
                cache_reload = 600
                shadow = /etc/shadow
                radwtmp = ${logdir}/radwtmp
        }
       eap {
                default_eap_type = md5
                timer_expire     = 60
                md5 {
                }
                leap {
                }
        }

        mschap {
                authtype = MS-CHAP
        }
        realm realmslash {
                format = prefix
                delimiter = "/"
        }
        realm suffix {
                format = suffix
                delimiter = "@"
        }
        realm realmpercent {
                format = suffix
                delimiter = "%"
        }
        preprocess {
                huntgroups = ${confdir}/huntgroups
                hints = ${confdir}/hints
                with_ascend_hack = no
                ascend_channels_per_line = 23
                with_ntdomain_hack = no
                with_specialix_jetstream_hack = no
                with_cisco_vsa_hack = no
        }
        files {
                usersfile = ${confdir}/users
                acctusersfile = ${confdir}/acct_users
                compat = no
        }
        detail {
                detailfile = ${radacctdir}/%{Client-IP-Address}/detail-%Y%m%d
                detailperm = 0600
        }
         detail auth_log {
                detailfile = ${radacctdir}/%{Client-IP-Address}/auth-detail-%Y%m%d
         }
         detail reply_log {
                 detailfile = ${radacctdir}/%{Client-IP-Address}/reply-detail-%Y%m%d
         }
        acct_unique {
                key = "User-Name, Acct-Session-Id, NAS-IP-Address, Client-IP-Address, NAS-Port-Id"
        }

        $INCLUDE  ${confdir}/postgresql.conf


        radutmp {
                filename = ${logdir}/radutmp
                username = %{User-Name}
                case_sensitive = yes
                check_with_nas = yes
                perm = 0600
                callerid = "yes"
        }

        radutmp sradutmp {
                filename = ${logdir}/sradutmp
                perm = 0644
                callerid = "no"
        }

        attr_filter {
                attrsfile = ${confdir}/attrs
        }

        counter daily {
                filename = ${raddbdir}/db.daily
                key = User-Name
                count-attribute = Acct-Session-Time
                reset = daily
                counter-name = Daily-Session-Time
                check-name = Max-Daily-Session
                allowed-servicetype = Framed-User
                cache-size = 5000
        }
        always fail {
                rcode = fail
        }
        always reject {
                rcode = reject
        }
        always ok {
                rcode = ok
                simulcount = 0
                mpp = no
        }

        expr {
        }

        digest {
        }

        exec {
                wait = yes
                input_pairs = request
        }

        exec echo {
                wait = yes
                program = "/bin/echo %{User-Name}"
                input_pairs = request
                output_pairs = reply
        }

        ippool main_pool {
                range-start = 172.16.20.1
                range-stop = 172.16.20.254
                netmask = 255.255.0.0
                cache-size = 800
                session-db = ${raddbdir}/db.ippool
                ip-index = ${raddbdir}/db.ipindex
                override = no
        }

}

instantiate {
        expr
}

authorize {
        preprocess
        sql
}


authenticate {
        Auth-Type PAP {
                pap
        }
        Auth-Type CHAP {
                chap
        }
        Auth-Type MS-CHAP {
                mschap
        }
        unix
        eap
}

preacct {
        preprocess
        suffix
        files
}

accounting {
        acct_unique
        detail
        unix            # wtmp file
        radutmp
}


session {
        radutmp
}


post-auth {
        reply_log
}

pre-proxy {
}

post-proxy {
        eap
}