Hi Derin.
Still not working
------------------------------------------------
For "MAC or credentials unicity", I have this filter which works well:
select
CASE
WHEN mac ='%{Connection:Client-Mac-Address-NoDelim}' THEN 'true'
WHEN mac IS NULL THEN 'true'
ELSE 'false'
END as Device_Allowed
from auth where username='%{Authentication:Username}' and error_code=0 and timestamp between (now() - interval '10 years') and now() order by timestamp limit 1;
---------------------------------------------------
for TLS device cert unicity, I tried some of the possibilities you envisage, like:
select
CASE
as Device_Allowed
WHEN username='%{Certificate:Subject-CN}' and error_code=0 and timestamp between (now() - interval '10 years') and now() order by timestamp limit 1 THEN 'true'
ELSE 'false';
Not working.
My CPPM is not joined to any domain.
Pls any idea?
Thanks a lot.
Luigi
------------------------------
Luigi Panico
------------------------------
Original Message:
Sent: May 27, 2021 07:50 AM
From: Derin Mellor
Subject: EAP-TLS device authentication with unicity certificate check by CPPM
You need to explicitly look at the AccessTracker-->Input-->Computed Attributes and pick out the best Certificate details. You will then use this direct is the SQL.
I quickly looked in my environment and the user login reports the Certificate:Subject-CN=Users,<username>
Looks like you really need the Certificate:Subject-emailAddress=<username>@<domain>. Hence to use this in the SQL I'd use username=%{Certificate:Subject-emailAddress}
However, I notice that with my machine login there is no Certificate:Subject-emailAddress or even Certificate:Subject-CN. The best my environment has is the Certificate:Subject-AltName-DNS=<hostname>.<domain>. But hopefully this is important.
------------------------------
Derin Mellor
Original Message:
Sent: May 27, 2021 05:29 AM
From: Luigi Panico
Subject: EAP-TLS device authentication with unicity certificate check by CPPM
Thanks Darin,
- let us assume TLS auth devices be wired only, at this time.
- on the period, you are right. It should be (for example)
timestamp between (now() - interval '12 hours') and now() order by timestamp limit 1;
My concern is on the syntax of the sql query to do the check on the subject-CN
Luigi
------------------------------
Luigi Panico
Original Message:
Sent: May 27, 2021 04:45 AM
From: Derin Mellor
Subject: EAP-TLS device authentication with unicity certificate check by CPPM
I think you're trying to identify that a certificate is only being used by one device?
If this is the case then you are highly likely to have issues with devices that have both wired and wireless interfaces doing TLS.
Irrespective, looking at your SQL the bit I don't understand is the "and updated_at > now()" that scenario is never going to occur. I think you want to backdate by a period of time, ie updated_at > (now() - interval '1' month).
------------------------------
Derin Mellor
Original Message:
Sent: May 20, 2021 02:42 PM
From: Luigi Panico
Subject: EAP-TLS device authentication with unicity certificate check by CPPM
Hi,
I am phasing with a design making use of EAP-TLS.
Devices are off-line certified, not by the Clearpass.
I wish to configure an Insight SQL filter query suitable to check the unicity of the device's certificate by means of the CN.
The behaviour should be: if the 'CN' of the authenticating device still exist in the Insight, then deny access.
Can, please anybody support or share his/her experience?
Thanks in advance
Luigi
PS: I tried with this filter query:
select count(distinct calling_station_id) as active_sessions from radius_acct where end_time is null and username = '%{Authentication:CN}' and calling_station_id != '%{Connection:Client-Mac-Address-NoDelim}' and updated_at > now()
Not working
------------------------------
Luigi Panico
------------------------------