Personnel permission queries with common table expressions (CTEs)

permissions_on_unit.sql

with recursive unit_tree (id, name, parent_id) as (
  select id, name, parent_id
  	from units
  	where id = 4 # Able-1-1
  union all
  select parent.id, parent.name, parent.parent_id
  	from unit_tree as child
  	join units as parent
  		on child.parent_id = parent.id
),
intersecting_assignments (unit_id, name, access_level) as (
  select unit_tree.id as unit_id, unit_tree.name, positions.access_level
  from unit_tree
  inner join assignments on (
    assignments.user_id = 4 # T/5 Dingo
    and assignments.unit_id = unit_tree.id
  )
  inner join positions on (positions.id = assignments.position_id)
)

select distinct permissions.ability
from intersecting_assignments
inner join permissions on (
  permissions.unit_id = intersecting_assignments.unit_id
  and permissions.access_level <= intersecting_assignments.access_level
)

permissions_on_user.sql

with recursive subject_units (id, name, parent_id) as (
  select units.id, units.name, units.parent_id
  from units
  inner join assignments on (
    assignments.user_id = 1 # Pvt. Antelope
    and assignments.unit_id = units.id
  )
),
unit_tree (id, name, parent_id) as (
  select subject_units.id, subject_units.name, subject_units.parent_id
  	from subject_units
  union all
  select parent.id, parent.name, parent.parent_id
  	from unit_tree as child
  	join units as parent
  		on child.parent_id = parent.id
),
intersecting_assignments (unit_id, name, access_level) as (
  select unit_tree.id as unit_id, unit_tree.name, positions.access_level
  from unit_tree
  inner join assignments on (
    assignments.user_id = 2 # Sgt. Baboon
    and assignments.unit_id = unit_tree.id
  )
  inner join positions on (positions.id = assignments.position_id)
)

select distinct permissions.ability
from intersecting_assignments
inner join permissions on (
  permissions.unit_id = intersecting_assignments.unit_id
  and permissions.access_level <= intersecting_assignments.access_level
)

schema.sql

# members
CREATE TABLE members (
  id serial PRIMARY KEY,
  name text
);

INSERT INTO members (id, name) VALUES
  (1, 'Pvt. Antelope'),
  (2, 'Sgt. Baboon'),
  (3, 'Lt. Chicken'),
  (4, 'T/5 Dingo'),
  (5, 'Pvt. Emu');
  
# units
CREATE TABLE units (
  id serial PRIMARY KEY,
  name text,
  parent_id integer
);

INSERT INTO units (id, name, parent_id) VALUES
  (1, 'First Battalion', NULL),
  (2, 'Able Company', 1),
  (3, 'Able Company, 1st Platoon', 2),
  (4, 'Able Company, 1st Platoon, 1st Squad', 3),
  (5, 'Able Company, 1st Platoon, 2nd Squad', 3),
  (6, 'Able Company, 2nd Platoon', 2),
  (7, 'Able Company, 2nd Platoon, 1st Squad', 6),
  (8, 'Able Company, 2nd Platoon, 2nd Squad', 6),
  (9, 'Training Office', 1);

# positions
CREATE TABLE positions (
  id serial PRIMARY KEY,
  name text,
  access_level integer # 1:member, 2:clerk, 3:leader
);

INSERT INTO positions (id, name, access_level) VALUES
  (1, 'Rifleman', 1), # member
  (2, 'Squad Leader', 3), # leader
  (3, 'Platoon Clerk', 2), # clerk
  (4, 'Platoon Leader', 3), # leader
  (5, 'Training Clerk', 2), # clerk
  (6, 'Office Director', 3); # leader

# permissions
CREATE TABLE permissions (
  id serial PRIMARY KEY,
  unit_id integer,
  access_level integer,
  ability text
);

INSERT INTO permissions (unit_id, access_level, ability) VALUES
  (3, 3, 'add_promotion'),	# Able-1, Leader
  (3, 2, 'add_event'), 		# Able-1, Clerk
  (3, 2, 'edit_profile'), 	# Able-1, Clerk
  (3, 1, 'view_event'), 	# Able-1, Member
  
  (4, 1, 'view_event'), 		# Able-1-1, Member
  (4, 3, 'add_qualification'), 	# Able-1-1, Leader
  
  (5, 1, 'view_event'), 		# Able-1-2, Member
  (5, 3, 'add_qualification'), 	# Able-1-2, Leader
  
  (6, 3, 'add_promotion'), 	# Able-2, Leader
  (6, 2, 'add_event'), 		# Able-2, Clerk
  (6, 2, 'edit_profile'), 	# Able-2, Clerk
  (6, 1, 'view_event'), 	# Abl-2, Member
  
  (7, 1, 'view_event'), 		# Able-2-1, Member
  (7, 3, 'add_qualification'), 	# Able-2-1, Leader
  
  (8, 1, 'view_event'), 		# Able-2-2, Member
  (8, 3, 'add_qualification'), 	# Able-2-2, Leader
  
  (9, 2, 'accept_trainee'),	# Training, Clerk
  (9, 3, 'fire_staff'); 	# Training, Leader

# assignments
CREATE TABLE assignments (
  id serial PRIMARY KEY,
  unit_id integer,
  user_id integer,
  position_id integer
);

INSERT INTO assignments (unit_id, user_id, position_id) VALUES
  (4, 1, 1), # Able-1-1, Pvt. Antelope, Rifleman
  (4, 2, 2), # Able-1-1, Sgt. Baboon, Squad Leader
  (3, 3, 4), # Able-1, Lt. Chicken, Platoon Leader
  (4, 4, 1), # Able-1-1, T/5 Dingo, Rifleman
  (3, 4, 3), # Able-1, T/5 Dingo, Platoon Clerk
  (7, 5, 1), # Able-1-2, Pvt. Emu, Rifleman
  (9, 1, 5), # Training, Pvt. Antelope, Training Clerk
  (9, 2, 6), # Training, Sgt. Baboon, Office Director
  (9, 5, 5); # Training, Pvt. Emu, Training Clerk