PostgreSQL structure for options that permit overriding and precedence

ctedatabase-designpostgresql

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);

Best Answer

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.

Best Answer

Using JSONB and a self-referential table

Related Solutions

PostgreSQL tree structure and recursive CTE optimization

Designing a database structure for Partners and Websites

Related Question