eap-tls + postgresql
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://stopstopnop.pl/stop_stopnop.pl_o_nas.html Samsung Wave to badziewie ...
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@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.
hi, in the first instance I would advise that you read this mailing list archive (via google searches with the same terms as you are using) - this scenario has been covered many times in the past. cheers alan
A.L.M.Buxey@lboro.ac.uk writes:
hi,
in the first instance I would advise that you read this mailing list archive (via google searches with the same terms as you are using) - this scenario has been covered many times in the past. Hm.
I tried: https://www.google.pl/search?q=freeradius-users+archive+sql+default&btnG=Goo... But with no success, can you give hint for keywords or sth? Moreover it is not clear for me: https://github.com/FreeRADIUS/freeradius-server/blob/v3.0.x/raddb/mods-avail... says --8<---------------cut here---------------start------------->8--- # 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 --8<---------------cut here---------------end--------------->8--- it suggests that if I have "Fall-Through = no" in group radgroupreply , then profiles are not tested. but https://github.com/FreeRADIUS/freeradius-server/blob/v3.0.x/raddb/mods-confi... suggest than default profile is always check What I am missing? KJ -- http://stopstopnop.pl/stop_stopnop.pl_o_nas.html 1.79 x 10^12 furlongs per fortnight -- it's not just a good idea, it's the law!
Naive question: shouldn't definition of sql_fall_through_t (in rlm_sql.h) be like this? --8<---------------cut here---------------start------------->8--- typedef enum { FALL_THROUGH_DEFAULT = -1, FALL_THROUGH_NO, FALL_THROUGH_YES } sql_fall_through_t; --8<---------------cut here---------------end--------------->8--- current seems to be inconsistent with internal dictionary. But it is only quick look of novice :) KJ kjonca@o2.pl (Kamil Jońca) writes:
To be more precise: my users file: skowronek.kjonca DEFAULT Auth-Type := Reject
I tried to play with profiles: 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" [...] [...]
-- http://stopstopnop.pl/stop_stopnop.pl_o_nas.html They finally got King Midas, I hear. Gild by association.
On Nov 14, 2016, at 2:00 PM, Kamil Jońca <kjonca@o2.pl> wrote:
Naive question: shouldn't definition of sql_fall_through_t (in rlm_sql.h) be like this?
--8<---------------cut here---------------start------------->8--- typedef enum { FALL_THROUGH_DEFAULT = -1, FALL_THROUGH_NO, FALL_THROUGH_YES
} sql_fall_through_t; --8<---------------cut here---------------end--------------->8---
current seems to be inconsistent with internal dictionary. But it is only quick look of novice :)
Looks consistent to me. Though technically we should explicitly set FALL_THROUGH_NO = 0 and FALL_THROUGH_YES = 1. FALL_THROUGH_DEFAULT is not intended to be set by the user. -Arran
a.cudbardb@freeradius.org (Arran Cudbard-Bell) writes: [...]
Looks consistent to me.
Though technically we should explicitly set FALL_THROUGH_NO = 0 and FALL_THROUGH_YES = 1.
FALL_THROUGH_DEFAULT is not intended to be set by the user.
Agreed, but, so far was set to 0 and it is used somewhere in code and I suspected that 0 was not distinguishable from "No" so far. Am I wrong? Nevermind. I compiled freeradius with[1]: --8<---------------cut here---------------start------------->8--- typedef enum { FALL_THROUGH_NO = 0, FALL_THROUGH_YES, FALL_THROUGH_DEFAULT /* , without comma here */ } sql_fall_through_t; --8<---------------cut here---------------end--------------->8--- And now it works as I expected, eg. when "Fall-Through = No" comes from groupreply, profiles were not checked. when no "Fall-Through" - profiles were checked. KJ [1]https://github.com/FreeRADIUS/freeradius-server/commit/43d0d1e5b691f7c412e60... -- http://stopstopnop.pl/stop_stopnop.pl_o_nas.html No small art is it to sleep: it is necessary for that purpose to keep awake all day. -- Nietzsche
On Nov 14, 2016, at 4:05 PM, Kamil Jońca <kjonca@o2.pl> wrote:
Nevermind. I compiled freeradius with[1]: --8<---------------cut here---------------start------------->8--- typedef enum { FALL_THROUGH_NO = 0, FALL_THROUGH_YES, FALL_THROUGH_DEFAULT /* , without comma here */ } sql_fall_through_t; --8<---------------cut here---------------end--------------->8---
Please don't use the huge "CUT HERE" lines. That just makes it harder to read your messages.
And now it works as I expected, eg. when "Fall-Through = No" comes from groupreply, profiles were not checked. when no "Fall-Through" - profiles were checked.
I've pushed a fix. Yes, "no" is zero. "yes" is one. Alan DeKok.
participants (4)
-
A.L.M.Buxey＠lboro.ac.uk -
Alan DeKok -
Arran Cudbard-Bell -
kjonca＠o2.pl