Big warning on using SQL queries in your config: the database schema CAN CHANGE. If ClearPass ships updates these SQL queries might break. Use at your own risk.
Database schema, remote access
You can access the ClearPass database with the "appexternal" account (you can set this password under "cluster wide parameters" in the server configuration). Then use a program like pgAdmin (postgres admin) to create a connection.
MAC caching: bind guest user to endpoint
If you want to use MAC caching and bind the endpoint directly to the guest account follow these steps. This means when the guest account is disabled or expired, the MAC authentication will fail as well.
1) Create new Authentication Source, Name = MAC_caching, Type = Generic SQL DB, Server = localhost, database = tipsdb, login = appadmin, driver = postgres
2) Add new filter in authentication source:
- Filter name: Authentication
- Filter query:
SELECT mac_address AS User_Password,
CASE WHEN tips_guest_users.enabled = FALSE THEN 225
WHEN ((tips_guest_users.start_time > now()) OR ((tips_guest_users.expire_time is not null) AND (tips_guest_users.expire_time <= now()))) THEN 226
WHEN tips_guest_users.approval_status != 'Approved' THEN 227
ELSE 0
END AS Account_Status, tips_guest_users.sponsor_name,
CAST(EXTRACT(epoch FROM (tips_guest_users.expire_time - NOW())) AS INTEGER) AS remaining_expiration
FROM tips_endpoints_attr_view INNER JOIN tips_guest_users ON tips_endpoints_attr_view.tag_value=tips_guest_users.user_id
WHERE tips_endpoints_attr_view.mac_address = LOWER('%{Connection:Client-Mac-Address-NoDelim}')
Attributes:
- Name: remaining_expiration
- Alias: remaining_expiration
- Data type: Integer
3) Create a MAC authenitcation service where the above authentication source is used as the authentication source
4) In the enforcement policy you can have a generic accept policy (like day of the week), make sure you have a enforcement profile in place that will return remaining_expiration in the RADIUS - IETF - Session-Timeout attribute. Use %{Authentication:MAC_cache:remaining_expiration} as the value for this.
5) For the captive portal service make sure you have a post_authentication enforcement profile in place which will update the endpoint with the guest username during captive portal login
Dynamic expire time update
If you want to update the expire-time during each login you can do this by creating a new authentication source (same method as described above). Use this authentication source as an authorization source in your service. See attached screenshot for the setings. SQL queries for this:
SELECT NOW() + INTERVAL '90 days' as new_expire_time;
update tips_guest_users set expire_time = NOW() + INTERVAL '90 days' where user_id = '%{Authentication:Username}'
Please note above will update the expire time based on the username, this will only work if you know the username during authentication (thus for captive portal login or 802.1X).
If you want to update the expire time based on the related MAC adres you can use this query:
UPDATE tips_guest_users SET expire_time = NOW() + INTERVAL '90 days'
FROM tips_endpoints_attr_view WHERE tips_endpoints_attr_view.tag_value=tips_guest_users.user_id AND tips_endpoints_attr_view.mac_address = LOWER('%{Connection:Client-Mac-Address-NoDelim}');