From 0b211dae2238184820eb95d683b02c04fd93b74e Mon Sep 17 00:00:00 2001 From: Sam Hartman Date: Wed, 25 Mar 2015 14:21:54 -0400 Subject: [PATCH] Don't include expired keys in the psk_keys table FreeRADIUS directly queries psk_keys. It's important that it not match expired keys. Instead create a table psk_keys_tab and make psk_keys a view that excludes expired keys. --- schema.sql | 5 +++-- 1 file changed, 3 insertions(+), 2 deletions(-) diff --git a/schema.sql b/schema.sql index 1e0a5f9..752fe2c 100644 --- a/schema.sql +++ b/schema.sql @@ -1,7 +1,8 @@ -create table if not exists psk_keys (keyid text primary key, key blob, client_dh_pub raw(20), key_expiration timestamp); +create table if not exists psk_keys_tab(keyid text primary key, key blob, client_dh_pub raw(20), key_expiration timestamp); create table if not exists authorizations( client_dh_pub raw(20), coi string, acceptor_realm string, hostname string, apc string); create index if not exists authorizations_dhpub on authorizations( client_dh_pub); -CREATE VIEW if not exists authorizations_keys as select keyid, authorizations.* from psk_keys join authorizations on psk_keys.client_dh_pub = authorizations.client_dh_pub and datetime(key_expiration) > datetime('now'); +create view if not exists psk_keys as select * from psk_keys_tab where datetime(key_expiration) > datetime('now'); +CREATE VIEW if not exists authorizations_keys as select keyid, authorizations.* from psk_keys join authorizations on psk_keys.client_dh_pub = authorizations.client_dh_pub; .quit -- 2.1.4