4

I'm trying to make a postgresSQL database that models radius templates and profiles.

Each template/profile would have a one (template/profile) to many (radius attribute value) relationship that defined the radius attributes each template/profile had (and what value that attribute had).

The goal I have is to have a tree of templates with the leaf of the tree being a profile (a set of radius attributes and values for a specific radius username). The leaf of the tree (profile) would inherit all the attributes its ancestors would have AND would also allow a child to have its attributes override (even remove entirely) radius attributes it would otherwise inherit.

The end goal would be to be able write an SQL query where I could something like "select radius_attribute_name, radius_attribute_value from where [profile/template].name = 'someusername'. This would allow me to directly connect my radius server to this database without having to write a program to query the database and write out a radius users configuration file for the radius server every minute.

I think I can figure out a way to model this that generally follows the normalization rules I've learned (mostly, don't repeat yourself; data written one place instead of two can't be inconsistent with itself). I think I would be able to write a recursive CTE query to start with a profile/template and find all ancestors, but past that, I'm having a hard time coming up with how you would find the attributes related to each template, but allow attributes from the descendant templates/profiles override (or remove) attributes inherited from parent templates/profiles. This leads me to wonder if my database design is lacking or if it is my recursive CTE query skills that are lacking.

I'm sure this is not a new pattern, many things an inherited tree of configuration that can be overridden. (Think CSS!) This project feels like I've been on the edge of solving it myself a few times, but it it feels just a little larger than what I can cache in my brain all at once (and therefore fully comprehend and solve).

Here's what I have for a database schema so far (granted I've done a few different things but ended up stuck multiple times, so this is just one try of a few):

create table test_radius_attributes
(
attribute_id SERIAL PRIMARY KEY,
attribute_name varchar(64) NOT NULL,
oprtr varchar(3) NOT NULL DEFAULT '=',
quote bool NOT NULL DEFAULT false
);

/* This is the table of profile/template objects that are in a tree */
create table test_radius_templates
(
template_id SERIAL PRIMARY KEY,
template_name varchar(64), -- Set for a template that is not intended to be used as a leaf
username varchar(64) unique, -- Set for a profile that is used only as a leaf
parent_template_id int references test_radius_templates(template_id) NULL,
check ((template_name is NULL and username is not NULL) or (template_name is not NULL and username is NULL))
);

/* These are the actual instances of attributes that are additional attributes added by this template/profile, or overriding previous inherited attributes. Maybe this table should be split into two tables? */

create table test_radius_attribute_instances
(
attribute_instance_id SERIAL PRIMARY KEY,
template_id int references test_radius_templates(template_id) NOT NULL,
value varchar(128) NULL, -- NULL means remove inherited attribute
instance_order real NULL, -- NULL on instances overriding inherited attribute (inherits value from what it overrides)
overridden_attribute_instance int references test_radius_attribute_instances(attribute_instance_id) NULL, -- NULL when it is not an override
attribute_id int references test_radius_attributes(attribute_id) NULL, -- NULL when this is an override
check ((instance_order is NULL and overridden_attribute_instance is NOT NULL and attribute_id is NULL) or (value is NOT NULL and instance_order is not null and overridden_attribute_instance is NULL and attribute_id is NOT NULL))
);

INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (1, 'User-Service-Type', '=', false);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (2, 'Framed-Protocol', '=', false);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (3, 'Framed-Routing', '=', false);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (4, 'Framed-MTU', '=', false);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (5, 'Framed-Compression', '=', false);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (8, 'Framed-Address', '=', false);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (9, 'Framed-Netmask', '=', false);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (10, 'ZZZ not usedFramed-Route', '=', false);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (13, 'Minimum-Channels', '=', false);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (26, 'Login-Service', '=', false);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (27, 'Login-Host', '=', false);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (28, 'Service-Type', '=', false);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (29, 'NAS-IP-Address', '=', false);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (30, 'Port-Limit', '=', false);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (31, 'Session-Timeout', '=', false);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (32, 'Idle-Timeout', '=', false);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (33, 'Framed-Filter-Id', '=', true);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (34, 'Filter-Id', '=', true);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (35, 'Ascend-Data-Filter', '=', true);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (36, 'Profile', '=', true);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (37, 'Accelerator', '=', true);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (38, 'VNC-PPPoE-CBQ-RX', '=', false);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (39, 'VNC-PPPoE-CBQ-TX', '=', false);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (40, 'USR-IP-Input-Filter', '=', false);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (41, 'USR-IP-Output-Filter', '=', false);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (42, 'Acct-Interim-Interval', '=', false);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (43, 'Propel-Accelerate', '=', false);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (44, 'Mikrotik-Rate-Limit', '=', false);
INSERT INTO test_radius_attributes (attribute_id, attribute_name, oprtr, quote) VALUES (45, 'Cleartext-Password', ':=', true);


INSERT INTO test_radius_templates (template_id, template_name, parent_template_id, username) VALUES (1, 'basicPPP', NULL, NULL);
INSERT INTO test_radius_templates (template_id, template_name, parent_template_id, username) VALUES (2, 'basicDialup', 1, NULL);
INSERT INTO test_radius_templates (template_id, template_name, parent_template_id, username) VALUES (3, 'dialup PPP, 6 port, no bess', 2, NULL);
INSERT INTO test_radius_templates (template_id, template_name, parent_template_id, username) VALUES (4, 'Broadband PPPoE', 1, NULL);
INSERT INTO test_radius_templates (template_id, template_name, parent_template_id, username) VALUES (5, 'Employee internet', 4, NULL);
INSERT INTO test_radius_templates (template_id, template_name, parent_template_id, username) VALUES (6, NULL, 5, 'andersenfiber');


INSERT INTO test_radius_attribute_instances (attribute_instance_id, value, instance_order, overridden_attribute_instance, attribute_id, template_id) VALUES (1, 'Framed-User', 50, NULL, 1, 1);
INSERT INTO test_radius_attribute_instances (attribute_instance_id, value, instance_order, overridden_attribute_instance, attribute_id, template_id) VALUES (2, 'PPP', 100, NULL, 2, 1);
INSERT INTO test_radius_attribute_instances (attribute_instance_id, value, instance_order, overridden_attribute_instance, attribute_id, template_id) VALUES (3, 'None', 150, NULL, 3, 1);
INSERT INTO test_radius_attribute_instances (attribute_instance_id, value, instance_order, overridden_attribute_instance, attribute_id, template_id) VALUES (4, 'Van-Jacobsen-TCP-IP', 200, NULL, 5, 1);
INSERT INTO test_radius_attribute_instances (attribute_instance_id, value, instance_order, overridden_attribute_instance, attribute_id, template_id) VALUES (5, '255.255.255.254', 250, NULL, 8, 1);
INSERT INTO test_radius_attribute_instances (attribute_instance_id, value, instance_order, overridden_attribute_instance, attribute_id, template_id) VALUES (6, '255.255.255.255', 300, NULL, 9, 1);
INSERT INTO test_radius_attribute_instances (attribute_instance_id, value, instance_order, overridden_attribute_instance, attribute_id, template_id) VALUES (7, '615600', 350, NULL, 31, 1);
INSERT INTO test_radius_attribute_instances (attribute_instance_id, value, instance_order, overridden_attribute_instance, attribute_id, template_id) VALUES (8, '0', 400, NULL, 32, 1);
INSERT INTO test_radius_attribute_instances (attribute_instance_id, value, instance_order, overridden_attribute_instance, attribute_id, template_id) VALUES (9, '1', 500, NULL, 30, 2);
INSERT INTO test_radius_attribute_instances (attribute_instance_id, value, instance_order, overridden_attribute_instance, attribute_id, template_id) VALUES (10, '1', 550, NULL, 43, 2);
INSERT INTO test_radius_attribute_instances (attribute_instance_id, value, instance_order, overridden_attribute_instance, attribute_id, template_id) VALUES (11, '1500', 600, NULL, 4, 2);
INSERT INTO test_radius_attribute_instances (attribute_instance_id, value, instance_order, overridden_attribute_instance, attribute_id, template_id) VALUES (12, '2400', NULL, 8, NULL, 2);
INSERT INTO test_radius_attribute_instances (attribute_instance_id, value, instance_order, overridden_attribute_instance, attribute_id, template_id) VALUES (13, '43200', NULL, 7, NULL, 2);
INSERT INTO test_radius_attribute_instances (attribute_instance_id, value, instance_order, overridden_attribute_instance, attribute_id, template_id) VALUES (14, '6', NULL, 9, NULL, 3);
INSERT INTO test_radius_attribute_instances (attribute_instance_id, value, instance_order, overridden_attribute_instance, attribute_id, template_id) VALUES (15, '100k/1M', 650, NULL, 44, 4);
INSERT INTO test_radius_attribute_instances (attribute_instance_id, value, instance_order, overridden_attribute_instance, attribute_id, template_id) VALUES (16, '6M/16M', NULL, 15, NULL, 5);
INSERT INTO test_radius_attribute_instances (attribute_instance_id, value, instance_order, overridden_attribute_instance, attribute_id, template_id) VALUES (17, '20M/150M', NULL, 16, NULL, 6);
1

Using JSONB and a self-referential table

You can easily do this with a recursive query in a self-referencing table.

CREATE TABLE foo (
  id        serial  PRIMARY KEY,
  id_parent int     REFERENCES foo,
  data      jsonb
);

INSERT INTO foo ( id, id_parent, data ) VALUES
  ( 1, DEFAULT, '{"foo":"bar"}' ),
  ( 2, 1,       '{"baz":"quz"}' ),
  ( 3, 2,       '{"foo":"OVERRIDE"}' );

Now we need a recursive query.

WITH RECURSIVE t(id_root, level, id, data) AS (
  SELECT id, 0, id, data
  FROM foo
  WHERE id_parent IS NULL
  UNION ALL
    SELECT t.id_root, t.level+1, foo.id, foo.data
    FROM t
    JOIN foo ON t.id = foo.id_parent
)
SELECT *
FROM t;

Now we need to create a jsonb aggregate, for that let's create a simple function, I copied this one from here

CREATE AGGREGATE jsonb_object_agg(jsonb) (  
  SFUNC = 'jsonb_concat',
  STYPE = jsonb,
  INITCOND = '{}'
);

Now, we query with the recursive query..

WITH RECURSIVE t(id_root, level, id, data) AS (
  SELECT id, 0, id, data
  FROM foo
  WHERE id_parent IS NULL
  UNION ALL
    SELECT t.id_root, t.level+1, foo.id, foo.data
    FROM t
    JOIN foo ON t.id = foo.id_parent
)
SELECT id_root, jsonb_object_agg(data ORDER BY level) AS data
FROM t
GROUP BY id_root;
 id_root |               data                
---------+-----------------------------------
       1 | {"baz": "quz", "foo": "OVERRIDE"}

You can do the same thing with an EAV just convert it to a jsonb first.

0

I did end up being able to do this as I envisioned.

I don't think I would call the solution pretty. I suspect it would have not good performance if you tried to make it general so you could have a view that allowed selecting by username, as I think the CTE expressions could collect a huge number of rows if they weren't just working on the inheritance of attributes for just one username / leaf profile.

I couldn't seem to avoid doing more than one CTE to copy the inherited data down one step of the tree at a time. (It would seem PostgreSQL would need some kind of iterated join where a reference/foreign key "pointer" is followed through the tree until the root of the tree is encountered to collect the inherited data from the root, which could be a variable number of steps/"generations"/levels in the tree away, to make this cleaner. But maybe there is a better way that I have not thought of.)

I would love to hear feedback on how this could be done better. (I am not familiar with json from a database as the other answer suggests, I suspect freeradius will not interface well with that kind of response from a query when it is expecting radius values as rows. But it is probably helpful to other people.)

Here's what I came up with ('andersenfiber' is just a placeholder username that would be replaced with the name for whatever profile you were looking up.)

WITH RECURSIVE 

-- Template tree path elements. Will be unique as the path cannot cycle and elements in the tree are unique
 ttpe(tid, tn, tun, tpti) AS (
    SELECT template_id, template_name, username, parent_template_id from test_radius_templates where username = 'andersenfiber'
  UNION ALL
    SELECT template_id, template_name, username, parent_template_id from test_radius_templates tat 
inner join ttpe on tat.template_id = ttpe.tpti
),


-- Ok, lets see if we can annotate children with the inherited values
inherited_props(attribute_instance_id, template_id, value, instance_order, overridden_attribute_instance, attribute_id) as (
select 
attribute_instance_id,
template_id,
value,
instance_order,
overridden_attribute_instance,
attribute_id
from test_radius_attribute_instances
inner join ttpe on test_radius_attribute_instances.template_id = ttpe.tid -- only select attrs on this template path
where overridden_attribute_instance is NULL -- Leave out all attributes that are overriding in the base case as we will get to them eventually
UNION
-- Add direct children, but bring (copy) along inherited data at each step
select
child_instance.attribute_instance_id,
child_instance.template_id,
inherited_props.value as value,
inherited_props.instance_order as instance_order,
child_instance.overridden_attribute_instance,
inherited_props.attribute_id as attribute_id
from test_radius_attribute_instances as child_instance
inner join ttpe on child_instance.template_id = ttpe.tid -- only select attrs on this template path
inner join inherited_props on inherited_props.attribute_instance_id = child_instance.overridden_attribute_instance -- this gets all children overrides for an attribute instance, does not limit to ones on the path
where child_instance.value is not NULL -- leave out all overrides that have NULL value as that flags that the attribute should be suppressed
  )

-- Get all non-masked attribute instances; also exclude value overrides so that we can bring them in with the appropriate attribute_id that they inherited summarized on them 
select
all_attrs.attribute_instance_id, all_attrs.template_id, coalesce(template_name, username) as template_name,
attribute_name, oprtr, quote,
all_attrs.value, 
coalesce(all_attrs.instance_order, inherited_props.instance_order) as instance_order,
all_attrs.overridden_attribute_instance,
coalesce(all_attrs.attribute_id, inherited_props.attribute_id) as attribute_id
from 
(
select attribute_instance_id, template_id, value, instance_order, overridden_attribute_instance, attribute_id
  from test_radius_attribute_instances
  inner join ttpe on test_radius_attribute_instances.template_id = ttpe.tid -- only select attrs on this template path
) as all_attrs -- All attribute instances along path
left join (
select attribute_instance_id, template_id, value, overridden_attribute_instance, attribute_id
  from test_radius_attribute_instances
  inner join ttpe on test_radius_attribute_instances.template_id = ttpe.tid -- only select attrs on this template path
  where overridden_attribute_instance is not NULL
)
as maskers -- attributes that hide other attributes
on maskers.overridden_attribute_instance = all_attrs.attribute_instance_id
left join inherited_props on all_attrs.attribute_instance_id = inherited_props.attribute_instance_id
inner join test_radius_templates on test_radius_templates.template_id = all_attrs.template_id
inner join test_radius_attributes on test_radius_attributes.attribute_id = coalesce(all_attrs.attribute_id, inherited_props.attribute_id) -- join with the coalesce so that we can interpret the inherited attribute_id
where maskers.attribute_instance_id is NULL -- Do a set difference taking out all attributes that will be masked
and 
(all_attrs.overridden_attribute_instance is NULL or all_attrs.value is not NULL) -- Remove all masker attribute instances whose only job is to suppress inherited attribute instances
-- (all_attrs.overridden_attribute_instance is NULL) -- Leave out all values that are masking, we need to bring them in with another query that annonates them with their inherited attribute_id so they can be joined to test_radius_attributes and be useful
order by instance_order

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.