eap-tls + postgresql

Kamil Jońca kjonca at o2.pl
Mon Nov 14 09:07:28 CET 2016


To be more precise:
my users file:
--8<---------------cut here---------------start------------->8---
skowronek.kjonca
DEFAULT Auth-Type := Reject
--8<---------------cut here---------------end--------------->8---

I tried to play with profiles:
--8<---------------cut here---------------start------------->8---
sql {
[...]
   # If set to 'yes' (default) we read the group tables unless Fall-Through = no in the reply table.
    # If set to 'no' we do not read the group tables unless Fall-Through = yes in the reply table.
    read_groups = yes

    # If set to 'yes' (default) we read profiles unless Fall-Through = no in the groupreply table.
    # If set to 'no' we do not read profiles unless Fall-Through = yes in the groupreply table.
    read_profiles = yes

    default_user_profile = "DEFAULT"
[...]

}
--8<---------------cut here---------------end--------------->8---

in database

--8<---------------cut here---------------start------------->8---
select * from radcheck where username ='skowronek.kjonca';
id username attribute op value 
-- -------- --------- -- -----
(0 rows)

select * from radreply where username ='skowronek.kjonca';
id username attribute op value 
-- -------- --------- -- -----
(0 rows)

select * from radusergroup where username ='skowronek.kjonca';
id     username     groupname priority 
-- ---------------- --------- --------
 8 skowronek.kjonca wifi             1
(1 row)

 select * from radgroupcheck;
id groupname attribute op value  
-- --------- --------- -- ------
 2 DEFAULT   Auth-Type := Reject
(1 row)

select * from radgroupreply;
id groupname  attribute   op value  
-- --------- ------------ -- ------
 2 DEFAULT   Auth-Type    := Reject
 3 wifi      Fall-Through =  no

select * from radusergroup where username ='DEFAULT';
id username groupname priority 
-- -------- --------- --------
15 DEFAULT  DEFAULT          1

--8<---------------cut here---------------end--------------->8---
no radcheck/radreply for "DEFAULT" user


logs:


--8<---------------cut here---------------start------------->8---
Mon Nov 14 07:08:16 2016 : Debug: attribute --> User-Name
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: EXPAND %{User-Name}
Mon Nov 14 07:08:16 2016 : Debug: (115) sql:    --> skowronek.kjonca
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: SQL-User-Name set to 'skowronek.kjonca'
Mon Nov 14 07:08:16 2016 : Debug: rlm_sql (sql): Reserved connection (46)
Mon Nov 14 07:08:16 2016 : Debug: SELECT id, UserName, Attribute, Value, Op FROM radcheck WHERE Username = '%{SQL-User-Name}' ORDER BY id
Mon Nov 14 07:08:16 2016 : Debug: Parsed xlat tree:
Mon Nov 14 07:08:16 2016 : Debug: literal --> SELECT id, UserName, Attribute, Value, Op FROM radcheck WHERE Username = '
Mon Nov 14 07:08:16 2016 : Debug: attribute --> SQL-User-Name
Mon Nov 14 07:08:16 2016 : Debug: literal --> ' ORDER BY id
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: EXPAND SELECT id, UserName, Attribute, Value, Op FROM radcheck WHERE Username = '%{SQL-User-Name}' ORDER BY id
Mon Nov 14 07:08:16 2016 : Debug: (115) sql:    --> SELECT id, UserName, Attribute, Value, Op FROM radcheck WHERE Username = 'skowronek.kjonca' ORDER BY id
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: Executing select query: SELECT id, UserName, Attribute, Value, Op FROM radcheck WHERE Username = 'skowronek.kjonca' ORDER BY id
Mon Nov 14 07:08:16 2016 : Debug: rlm_sql_postgresql: Status: PGRES_TUPLES_OK
Mon Nov 14 07:08:16 2016 : Debug: rlm_sql_postgresql: query affected rows = 0 , fields = 5
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: ... falling-through to group processing
Mon Nov 14 07:08:16 2016 : Debug: SELECT GroupName FROM radusergroup WHERE UserName='%{SQL-User-Name}' ORDER BY priority
Mon Nov 14 07:08:16 2016 : Debug: Parsed xlat tree:
Mon Nov 14 07:08:16 2016 : Debug: literal --> SELECT GroupName FROM radusergroup WHERE UserName='
Mon Nov 14 07:08:16 2016 : Debug: attribute --> SQL-User-Name
Mon Nov 14 07:08:16 2016 : Debug: literal --> ' ORDER BY priority
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: EXPAND SELECT GroupName FROM radusergroup WHERE UserName='%{SQL-User-Name}' ORDER BY priority
Mon Nov 14 07:08:16 2016 : Debug: (115) sql:    --> SELECT GroupName FROM radusergroup WHERE UserName='skowronek.kjonca' ORDER BY priority
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: Executing select query: SELECT GroupName FROM radusergroup WHERE UserName='skowronek.kjonca' ORDER BY priority
Mon Nov 14 07:08:16 2016 : Debug: rlm_sql_postgresql: Status: PGRES_TUPLES_OK
Mon Nov 14 07:08:16 2016 : Debug: rlm_sql_postgresql: query affected rows = 1 , fields = 1
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: User found in the group table
Mon Nov 14 07:08:16 2016 : Debug: SELECT id, GroupName, Attribute, Value, op FROM radgroupcheck WHERE GroupName = '%{SQL-Group}' ORDER BY id
Mon Nov 14 07:08:16 2016 : Debug: Parsed xlat tree:
Mon Nov 14 07:08:16 2016 : Debug: literal --> SELECT id, GroupName, Attribute, Value, op FROM radgroupcheck WHERE GroupName = '
Mon Nov 14 07:08:16 2016 : Debug: attribute --> SQL-Group
Mon Nov 14 07:08:16 2016 : Debug: literal --> ' ORDER BY id
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: EXPAND SELECT id, GroupName, Attribute, Value, op FROM radgroupcheck WHERE GroupName = '%{SQL-Group}' ORDER BY id
Mon Nov 14 07:08:16 2016 : Debug: (115) sql:    --> SELECT id, GroupName, Attribute, Value, op FROM radgroupcheck WHERE GroupName = 'wifi' ORDER BY id
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: Executing select query: SELECT id, GroupName, Attribute, Value, op FROM radgroupcheck WHERE GroupName = 'wifi' ORDER BY id
Mon Nov 14 07:08:16 2016 : Debug: rlm_sql_postgresql: Status: PGRES_TUPLES_OK
Mon Nov 14 07:08:16 2016 : Debug: rlm_sql_postgresql: query affected rows = 0 , fields = 5
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: Group "wifi": Conditional check items matched
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: Group "wifi": Merging assignment check items
Mon Nov 14 07:08:16 2016 : Debug: SELECT id, GroupName, Attribute, Value, op FROM radgroupreply WHERE GroupName = '%{SQL-Group}' ORDER BY id
Mon Nov 14 07:08:16 2016 : Debug: Parsed xlat tree:
Mon Nov 14 07:08:16 2016 : Debug: literal --> SELECT id, GroupName, Attribute, Value, op FROM radgroupreply WHERE GroupName = '
Mon Nov 14 07:08:16 2016 : Debug: attribute --> SQL-Group
Mon Nov 14 07:08:16 2016 : Debug: literal --> ' ORDER BY id
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: EXPAND SELECT id, GroupName, Attribute, Value, op FROM radgroupreply WHERE GroupName = '%{SQL-Group}' ORDER BY id
Mon Nov 14 07:08:16 2016 : Debug: (115) sql:    --> SELECT id, GroupName, Attribute, Value, op FROM radgroupreply WHERE GroupName = 'wifi' ORDER BY id
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: Executing select query: SELECT id, GroupName, Attribute, Value, op FROM radgroupreply WHERE GroupName = 'wifi' ORDER BY id
Mon Nov 14 07:08:16 2016 : Debug: rlm_sql_postgresql: Status: PGRES_TUPLES_OK
Mon Nov 14 07:08:16 2016 : Debug: rlm_sql_postgresql: query affected rows = 1 , fields = 5
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: Group "wifi": Merging reply items
Mon Nov 14 07:08:16 2016 : Debug: (115) sql:   Fall-Through = No
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: ... falling-through to profile processing
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: Checking profile DEFAULT
Mon Nov 14 07:08:16 2016 : Debug: DEFAULT
Mon Nov 14 07:08:16 2016 : Debug: Parsed xlat tree:
Mon Nov 14 07:08:16 2016 : Debug: literal --> DEFAULT
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: EXPAND DEFAULT
Mon Nov 14 07:08:16 2016 : Debug: (115) sql:    --> DEFAULT
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: SQL-User-Name set to 'DEFAULT'
Mon Nov 14 07:08:16 2016 : Debug: SELECT GroupName FROM radusergroup WHERE UserName='%{SQL-User-Name}' ORDER BY priority
Mon Nov 14 07:08:16 2016 : Debug: Parsed xlat tree:
Mon Nov 14 07:08:16 2016 : Debug: literal --> SELECT GroupName FROM radusergroup WHERE UserName='
Mon Nov 14 07:08:16 2016 : Debug: attribute --> SQL-User-Name
Mon Nov 14 07:08:16 2016 : Debug: literal --> ' ORDER BY priority
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: EXPAND SELECT GroupName FROM radusergroup WHERE UserName='%{SQL-User-Name}' ORDER BY priority
Mon Nov 14 07:08:16 2016 : Debug: (115) sql:    --> SELECT GroupName FROM radusergroup WHERE UserName='DEFAULT' ORDER BY priority
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: Executing select query: SELECT GroupName FROM radusergroup WHERE UserName='DEFAULT' ORDER BY priority
Mon Nov 14 07:08:16 2016 : Debug: rlm_sql_postgresql: Status: PGRES_TUPLES_OK
Mon Nov 14 07:08:16 2016 : Debug: rlm_sql_postgresql: query affected rows = 1 , fields = 1
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: User found in the group table
Mon Nov 14 07:08:16 2016 : Debug: SELECT id, GroupName, Attribute, Value, op FROM radgroupcheck WHERE GroupName = '%{SQL-Group}' ORDER BY id
Mon Nov 14 07:08:16 2016 : Debug: Parsed xlat tree:
Mon Nov 14 07:08:16 2016 : Debug: literal --> SELECT id, GroupName, Attribute, Value, op FROM radgroupcheck WHERE GroupName = '
Mon Nov 14 07:08:16 2016 : Debug: attribute --> SQL-Group
Mon Nov 14 07:08:16 2016 : Debug: literal --> ' ORDER BY id
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: EXPAND SELECT id, GroupName, Attribute, Value, op FROM radgroupcheck WHERE GroupName = '%{SQL-Group}' ORDER BY id
Mon Nov 14 07:08:16 2016 : Debug: (115) sql:    --> SELECT id, GroupName, Attribute, Value, op FROM radgroupcheck WHERE GroupName = 'DEFAULT' ORDER BY id
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: Executing select query: SELECT id, GroupName, Attribute, Value, op FROM radgroupcheck WHERE GroupName = 'DEFAULT' ORDER BY id
Mon Nov 14 07:08:16 2016 : Debug: rlm_sql_postgresql: Status: PGRES_TUPLES_OK
Mon Nov 14 07:08:16 2016 : Debug: rlm_sql_postgresql: query affected rows = 1 , fields = 5
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: Group "DEFAULT": Conditional check items matched
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: Group "DEFAULT": Merging assignment check items
Mon Nov 14 07:08:16 2016 : Debug: (115) sql:   Auth-Type := Reject
Mon Nov 14 07:08:16 2016 : Debug: SELECT id, GroupName, Attribute, Value, op FROM radgroupreply WHERE GroupName = '%{SQL-Group}' ORDER BY id
Mon Nov 14 07:08:16 2016 : Debug: Parsed xlat tree:
Mon Nov 14 07:08:16 2016 : Debug: literal --> SELECT id, GroupName, Attribute, Value, op FROM radgroupreply WHERE GroupName = '
Mon Nov 14 07:08:16 2016 : Debug: attribute --> SQL-Group
Mon Nov 14 07:08:16 2016 : Debug: literal --> ' ORDER BY id
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: EXPAND SELECT id, GroupName, Attribute, Value, op FROM radgroupreply WHERE GroupName = '%{SQL-Group}' ORDER BY id
Mon Nov 14 07:08:16 2016 : Debug: (115) sql:    --> SELECT id, GroupName, Attribute, Value, op FROM radgroupreply WHERE GroupName = 'DEFAULT' ORDER BY id
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: Executing select query: SELECT id, GroupName, Attribute, Value, op FROM radgroupreply WHERE GroupName = 'DEFAULT' ORDER BY id
Mon Nov 14 07:08:16 2016 : Debug: rlm_sql_postgresql: Status: PGRES_TUPLES_OK
Mon Nov 14 07:08:16 2016 : Debug: rlm_sql_postgresql: query affected rows = 1 , fields = 5
Mon Nov 14 07:08:16 2016 : Debug: (115) sql: Group "DEFAULT": Merging reply items
Mon Nov 14 07:08:16 2016 : Debug: (115) sql:   Auth-Type := Reject
--8<---------------cut here---------------end--------------->8---

So it seems, that DEFAULT user is always check regardless of
 "Fall-Through = No" for wifi group.


 kjonca at o2.pl (Kamil Jońca) writes:

> I have some devices/users authenticated with eap-tls.
> I have "users" file and everything work. (ie. user which are in "users"
> file got authenticated, rest - not)
> I want to migrate to sql database, and I probably missed something -
> every example tells about
> user,atribute,op,value in radcheck/radreply - but in my users file I
> have no attributes (except DEFAUL)
>
> I tried to play with radusergroup but with no success - it is completely
> ignored and regardless of presence user name access is granted only on
> certificate.
>
> I think I missing something.
>
> KJ

-- 
http://wolnelektury.pl/wesprzyj/teraz/
Always try to do things in chronological order; it's less confusing that way.



More information about the Freeradius-Users mailing list