Access Controller

RBAC0

User - Role - Permission

RBAC1

Role Inheritance

access-01

public.actions

column	type	length	constraints
id (pk)	character varying	21	NOT NULL
name	text
description	text
created_at	timestamp with time zone
updated_at	timestamp with time zone

public.permissions

column	type	length	constraints
id (pk)	character varying	21	NOT NULL
action_id	text
resource_id	text
key	text
created_at	timestamp with time zone
updated_at	timestamp with time zone

public.resources

column	type	length	constraints
id (pk)	character varying	21	NOT NULL
name	text
created_at	timestamp with time zone
updated_at	timestamp with time zone

public.role_permission

column	comment	type	length	default	constraints	values
permission_id (pk)		character varying	21		NOT NULL, permission_id
role_id (pk)		character varying	21		NOT NULL, role_id

public.roles

column	type	length	constraints
id (pk)	character varying	21	NOT NULL
parent_id	character varying	21	parent_id
role_name	text
description	text
type	bigint
enabled	boolean
created_at	timestamp with time zone
updated_at	timestamp with time zone

public.user_role

column	comment	type	length	default	constraints	values
role_id (pk)		character varying	21		NOT NULL, role_id
user_id (pk)		character varying	21		NOT NULL, user_id

public.users

column	type	length	constraints
id (pk)	character varying	21	NOT NULL
name	text
enabled	boolean
created_at	timestamp with time zone
updated_at	timestamp with time zone

create table roles
(
	id varchar(21) not null
		constraint roles_pkey
			primary key,
	parent_id varchar(21)
		constraint fk_roles_roles
			references roles,
	role_name text,
	description text,
	type bigint,
	enabled boolean,
	created_at timestamp with time zone,
	updated_at timestamp with time zone
);


create table permissions
(
	id varchar(21) not null
		constraint permissions_pkey
			primary key,
	action_id text,
	resource_id text,
	key text,
	created_at timestamp with time zone,
	updated_at timestamp with time zone
);


create table role_permission
(
	permission_id varchar(21) not null
		constraint fk_role_permission_permission
			references permissions,
	role_id varchar(21) not null
		constraint fk_role_permission_role
			references roles,
	constraint role_permission_pkey
		primary key (permission_id, role_id)
);


create table actions
(
	id varchar(21) not null
		constraint actions_pkey
			primary key,
	name text,
	description text,
	created_at timestamp with time zone,
	updated_at timestamp with time zone
);


create table resources
(
	id varchar(21) not null
		constraint resources_pkey
			primary key,
	name text,
	created_at timestamp with time zone,
	updated_at timestamp with time zone
);


create table users
(
	id varchar(21) not null
		constraint users_pkey
			primary key,
	name text,	
	enabled boolean,
	created_at timestamp with time zone,
	updated_at timestamp with time zone
);


create table user_role
(
	role_id varchar(21) not null
		constraint fk_user_role_role
			references roles,
	user_id varchar(21) not null
		constraint fk_user_role_user
			references users,
	constraint user_role_pkey
		primary key (role_id, user_id)
);



-- data
INSERT INTO public.actions (id, name, description, created_at, updated_at) VALUES ('QPjlpxmEE7fSjeTvavn0C', 'create','create', '2021-01-24T10:24:17+08:00', '2021-01-24T10:24:17+08:00');
INSERT INTO public.actions (id, name, description, created_at, updated_at) VALUES ('2iNamDFktFA6qfz_Tk5nH', 'update','update', '2021-01-24T10:24:17+08:00', '2021-01-24T10:24:17+08:00');
INSERT INTO public.actions (id, name, description, created_at, updated_at) VALUES ('kZBX-FnIYa374wt4D5OlK', 'delete','delete', '2021-01-24T10:24:17+08:00', '2021-01-24T10:24:17+08:00');
INSERT INTO public.actions (id, name, description, created_at, updated_at) VALUES ('ozXzHwl1tDLmgIwCBjQMc', 'read','read', '2021-01-24T10:24:17+08:00', '2021-01-24T10:24:17+08:00');

INSERT INTO public.resources (id, name, created_at, updated_at) VALUES ('k4Wcq9zGqShiSH6-HmR9_', 'rbac', '2021-01-24T10:24:17+08:00', '2021-01-24T10:24:17+08:00');
INSERT INTO public.resources (id, name, created_at, updated_at) VALUES ('AoiiJEVfm3Fe8gtlZvE0_', 'users', '2021-01-24T10:24:17+08:00', '2021-01-24T10:24:17+08:00');
INSERT INTO public.resources (id, name, created_at, updated_at) VALUES ('_-0lB0FJklZQf0wMCOdb3', 'devops', '2021-01-24T10:24:17+08:00', '2021-01-24T10:24:17+08:00');

INSERT INTO public.permissions (id, action_id, resource_id, created_at, updated_at, key) VALUES ('7BvEdc4TA-gDOF-mfR3Wy', 'ozXzHwl1tDLmgIwCBjQMc', '_-0lB0FJklZQf0wMCOdb3', '2021-01-24T10:24:17+08:00', '2021-01-24T10:24:17+08:00', 'read:devops');
INSERT INTO public.permissions (id, action_id, resource_id, created_at, updated_at, key) VALUES ('4vIzJkqwRPj2vN_w4Oq88', 'QPjlpxmEE7fSjeTvavn0C', 'k4Wcq9zGqShiSH6-HmR9_', '2021-01-24T10:24:17+08:00', '2021-01-24T10:24:17+08:00', 'create:rbac');
INSERT INTO public.permissions (id, action_id, resource_id, created_at, updated_at, key) VALUES ('0rhMOepRtB5GPO_6xDLZ3', 'ozXzHwl1tDLmgIwCBjQMc', 'k4Wcq9zGqShiSH6-HmR9_', '2021-01-24T10:24:17+08:00', '2021-01-24T10:24:17+08:00', 'read:rbac');
INSERT INTO public.permissions (id, action_id, resource_id, created_at, updated_at, key) VALUES ('4P3xauyVkRxinMmKPL6Lh', '2iNamDFktFA6qfz_Tk5nH', 'k4Wcq9zGqShiSH6-HmR9_', '2021-01-24T10:24:17+08:00', '2021-01-24T10:24:17+08:00', 'update:rbac');
INSERT INTO public.permissions (id, action_id, resource_id, created_at, updated_at, key) VALUES ('S8uK_bjaCDPveyyYqUIJc', 'kZBX-FnIYa374wt4D5OlK', 'k4Wcq9zGqShiSH6-HmR9_', '2021-01-24T10:24:17+08:00', '2021-01-24T10:24:17+08:00', 'delete:rbac');
INSERT INTO public.permissions (id, action_id, resource_id, created_at, updated_at, key) VALUES ('G_8IyfomSis4Mbk9hqCt9', 'QPjlpxmEE7fSjeTvavn0C', 'AoiiJEVfm3Fe8gtlZvE0_', '2021-01-24T10:24:17+08:00', '2021-01-24T10:24:17+08:00', 'create:users');
INSERT INTO public.permissions (id, action_id, resource_id, created_at, updated_at, key) VALUES ('6Mh4-cezNheXhIIMmIIjk', 'ozXzHwl1tDLmgIwCBjQMc', 'AoiiJEVfm3Fe8gtlZvE0_', '2021-01-24T10:24:17+08:00', '2021-01-24T10:24:17+08:00', 'read:users');
INSERT INTO public.permissions (id, action_id, resource_id, created_at, updated_at, key) VALUES ('VgoFSnty6aiBPZUtlsWfg', '2iNamDFktFA6qfz_Tk5nH', 'AoiiJEVfm3Fe8gtlZvE0_', '2021-01-24T10:24:17+08:00', '2021-01-24T10:24:17+08:00', 'update:users');
INSERT INTO public.permissions (id, action_id, resource_id, created_at, updated_at, key) VALUES ('l4QFZ7gg4K8EhCj_rvduc', 'kZBX-FnIYa374wt4D5OlK', 'AoiiJEVfm3Fe8gtlZvE0_', '2021-01-24T10:24:17+08:00', '2021-01-24T10:24:17+08:00', 'delete:users');
INSERT INTO public.permissions (id, action_id, resource_id, created_at, updated_at, key) VALUES ('cdemu6I9VAjcYtQURUQJH', 'QPjlpxmEE7fSjeTvavn0C', '_-0lB0FJklZQf0wMCOdb3', '2021-01-24T10:24:17+08:00', '2021-01-24T10:24:17+08:00', 'create:devops');
INSERT INTO public.permissions (id, action_id, resource_id, created_at, updated_at, key) VALUES ('tIUyyl9n6NKFesDzPlbON', '2iNamDFktFA6qfz_Tk5nH', '_-0lB0FJklZQf0wMCOdb3', '2021-01-24T10:24:17+08:00', '2021-01-24T10:24:17+08:00', 'update:devops');
INSERT INTO public.permissions (id, action_id, resource_id, created_at, updated_at, key) VALUES ('JK_oVtMSqFPjHklwCedJJ', 'kZBX-FnIYa374wt4D5OlK', '_-0lB0FJklZQf0wMCOdb3', '2021-01-24T10:24:17+08:00', '2021-01-24T10:24:17+08:00', 'delete:devops');

INSERT INTO public.roles (id, parent_id, role_name, type, description, enabled,  created_at, updated_at) VALUES ('AsaN4Lp62jeT9jaJl1Nlj', null, 'admin-manager', 0, 'admin-manager', true, '2021-01-24T10:24:17+08:00', '2021-01-24T10:24:17+08:00');
INSERT INTO public.roles (id, parent_id, role_name, type, description, enabled,  created_at, updated_at) VALUES ('-Nmq8l1U-gfQtgTvxAXDJ', 'AsaN4Lp62jeT9jaJl1Nlj', 'users-manager', 0, 'users-manager', true, '2021-01-24T10:24:17+08:00', '2021-01-24T10:24:17+08:00');
INSERT INTO public.roles (id, parent_id, role_name, type, description, enabled,  created_at, updated_at) VALUES ('gXWvkQRzMaE6fZnKsbVPD', 'AsaN4Lp62jeT9jaJl1Nlj', 'devops-manager', 0, 'devops-manager', true, '2021-01-24T10:24:17+08:00', '2021-01-24T10:24:17+08:00');
INSERT INTO public.roles (id, parent_id, role_name, type, description, enabled,  created_at, updated_at) VALUES ('EhZ0CHPt5oc5oe9osk0eQ', 'gXWvkQRzMaE6fZnKsbVPD', 'devops-runner', 0, 'devops-runner', true, '2021-01-24T10:24:17+08:00', '2021-01-24T10:24:17+08:00');

INSERT INTO public.role_permission (role_id, permission_id) VALUES ('EhZ0CHPt5oc5oe9osk0eQ', '7BvEdc4TA-gDOF-mfR3Wy');
INSERT INTO public.role_permission (role_id, permission_id) VALUES ('AsaN4Lp62jeT9jaJl1Nlj', '4vIzJkqwRPj2vN_w4Oq88');
INSERT INTO public.role_permission (role_id, permission_id) VALUES ('AsaN4Lp62jeT9jaJl1Nlj', '0rhMOepRtB5GPO_6xDLZ3');
INSERT INTO public.role_permission (role_id, permission_id) VALUES ('AsaN4Lp62jeT9jaJl1Nlj', '4P3xauyVkRxinMmKPL6Lh');
INSERT INTO public.role_permission (role_id, permission_id) VALUES ('AsaN4Lp62jeT9jaJl1Nlj', 'S8uK_bjaCDPveyyYqUIJc');
INSERT INTO public.role_permission (role_id, permission_id) VALUES ('-Nmq8l1U-gfQtgTvxAXDJ', 'G_8IyfomSis4Mbk9hqCt9');
INSERT INTO public.role_permission (role_id, permission_id) VALUES ('-Nmq8l1U-gfQtgTvxAXDJ', '6Mh4-cezNheXhIIMmIIjk');
INSERT INTO public.role_permission (role_id, permission_id) VALUES ('-Nmq8l1U-gfQtgTvxAXDJ', 'VgoFSnty6aiBPZUtlsWfg');
INSERT INTO public.role_permission (role_id, permission_id) VALUES ('-Nmq8l1U-gfQtgTvxAXDJ', 'l4QFZ7gg4K8EhCj_rvduc');
INSERT INTO public.role_permission (role_id, permission_id) VALUES ('gXWvkQRzMaE6fZnKsbVPD', 'cdemu6I9VAjcYtQURUQJH');
INSERT INTO public.role_permission (role_id, permission_id) VALUES ('gXWvkQRzMaE6fZnKsbVPD', 'tIUyyl9n6NKFesDzPlbON');
INSERT INTO public.role_permission (role_id, permission_id) VALUES ('gXWvkQRzMaE6fZnKsbVPD', 'JK_oVtMSqFPjHklwCedJJ');

INSERT INTO public.users (id, name, enabled, created_at, updated_at) VALUES ('87gb8fKJHGxh2Pz_Gk_R2', 'User1', true, '2020-09-17 07:56:09.000000', '2021-01-21 15:00:00.000000');
INSERT INTO public.users (id, name, enabled, created_at, updated_at) VALUES ('SJ36zw7nRS4lx18dZlCoo', 'User2', true, '2021-01-21 15:00:00.000000', '2021-01-21 15:00:00.000000');
INSERT INTO public.users (id, name, enabled, created_at, updated_at) VALUES ('h8Iqlb8Ixc4IltuOoY5QC', 'User3', true, '2021-01-21 15:00:00.000000', '2021-01-21 15:00:00.000000');
INSERT INTO public.users (id, name, enabled, created_at, updated_at) VALUES ('SbZeBSpuy2OdJ0WZ2Z_Qo', 'User4', true, '2021-01-21 15:00:00.000000', '2021-01-21 15:00:00.000000');

INSERT INTO public.user_role (user_id, role_id) VALUES ('87gb8fKJHGxh2Pz_Gk_R2','AsaN4Lp62jeT9jaJl1Nlj');
INSERT INTO public.user_role (user_id, role_id) VALUES ('SJ36zw7nRS4lx18dZlCoo','-Nmq8l1U-gfQtgTvxAXDJ');
INSERT INTO public.user_role (user_id, role_id) VALUES ('h8Iqlb8Ixc4IltuOoY5QC','gXWvkQRzMaE6fZnKsbVPD');
INSERT INTO public.user_role (user_id, role_id) VALUES ('SbZeBSpuy2OdJ0WZ2Z_Qo','EhZ0CHPt5oc5oe9osk0eQ');


SELECT DISTINCT gpd.user_id,
                p.key
FROM (SELECT gdr.user_id,
             gdr.role_id,
             gdr.role_parent_id,
             rp.permission_id
      FROM (WITH RECURSIVE deep_roles AS (
          SELECT ur.user_id,
                 rr.id,
                 rr.parent_id,
                 1 AS level
          FROM roles rr
                   JOIN user_role ur ON rr.id::text = ur.role_id::text
          WHERE rr.enabled = true
          UNION ALL
          SELECT deep_roles_1.user_id,
                 rr.id,
                 rr.parent_id,
                 deep_roles_1.level + 1 AS level
          FROM roles rr
                   JOIN deep_roles deep_roles_1 ON rr.parent_id::text = deep_roles_1.id::text
          WHERE rr.enabled = true
      )
            SELECT deep_roles.user_id,
                   deep_roles.id        AS role_id,
                   deep_roles.parent_id AS role_parent_id,
                   deep_roles.level
            FROM deep_roles) gdr
               JOIN role_permission rp ON rp.role_id::text = gdr.role_id::text) gpd
         JOIN permissions p ON p.id::text = gpd.permission_id::text;

Under the inheritance relationship, admin-manager will have all the permissions of the following roles (user-manager, devops-manager, devops-runner). Then how do we get user1, user3, user3, user4 permissions under role inheritance? At this time, we can query through CTE (common table expression), we make a small query, and finally compose a view:

  SELECT ur.user_id,
              rr.id,
              rr.parent_id,
              1 AS level
       FROM roles rr
                JOIN user_role ur ON rr.id::text = ur.role_id::text

user_id	id	parent_id	level
87gb8fKJHGxh2Pz_Gk_R2	AsaN4Lp62jeT9jaJl1Nlj	NULL	1
SJ36zw7nRS4lx18dZlCoo	-Nmq8l1U-gfQtgTvxAXDJ	AsaN4Lp62jeT9jaJl1Nlj	1
h8Iqlb8Ixc4IltuOoY5QC	gXWvkQRzMaE6fZnKsbVPD	AsaN4Lp62jeT9jaJl1Nlj	1
SbZeBSpuy2OdJ0WZ2Z_Qo	EhZ0CHPt5oc5oe9osk0eQ	gXWvkQRzMaE6fZnKsbVPD	1

Query the user corresponding to the role and pre-substitute the field of level for recursive query.

 WITH RECURSIVE deep_roles AS (
       SELECT ur.user_id,
              rr.id,
              rr.parent_id,
              1 AS level
       FROM roles rr
                JOIN user_role ur ON rr.id::text = ur.role_id::text
       WHERE rr.enabled = true
       UNION ALL
       SELECT deep_roles_1.user_id,
              rr.id,
              rr.parent_id,
              deep_roles_1.level + 1 AS level
       FROM roles rr
                JOIN deep_roles deep_roles_1 ON rr.parent_id::text = deep_roles_1.id::text
       WHERE rr.enabled = true
   ) select * from deep_roles

user_id	id	parent_id	level
87gb8fKJHGxh2Pz_Gk_R2	AsaN4Lp62jeT9jaJl1Nlj	NULL	1
SJ36zw7nRS4lx18dZlCoo	-Nmq8l1U-gfQtgTvxAXDJ	AsaN4Lp62jeT9jaJl1Nlj	1
h8Iqlb8Ixc4IltuOoY5QC	gXWvkQRzMaE6fZnKsbVPD	AsaN4Lp62jeT9jaJl1Nlj	1
SbZeBSpuy2OdJ0WZ2Z_Qo	EhZ0CHPt5oc5oe9osk0eQ	gXWvkQRzMaE6fZnKsbVPD	1
87gb8fKJHGxh2Pz_Gk_R2	gXWvkQRzMaE6fZnKsbVPD	AsaN4Lp62jeT9jaJl1Nlj	2
87gb8fKJHGxh2Pz_Gk_R2	-Nmq8l1U-gfQtgTvxAXDJ	AsaN4Lp62jeT9jaJl1Nlj	2
h8Iqlb8Ixc4IltuOoY5QC	EhZ0CHPt5oc5oe9osk0eQ	gXWvkQRzMaE6fZnKsbVPD	2
87gb8fKJHGxh2Pz_Gk_R2	EhZ0CHPt5oc5oe9osk0eQ	gXWvkQRzMaE6fZnKsbVPD	3

After using recursive query, we will get the role that the user corresponds to, and there is a level column to see the depth of inheritance

 SELECT DISTINCT gpd.user_id,
             p.key
 FROM (SELECT gdr.user_id,
             gdr.role_id,
             gdr.role_parent_id,
             rp.permission_id
       FROM (WITH RECURSIVE deep_roles AS (
           SELECT ur.user_id,
                 rr.id,
                 rr.parent_id,
                 1 AS level
           FROM roles rr
                   JOIN user_role ur ON rr.id::text = ur.role_id::text
           WHERE rr.enabled = true
           UNION ALL
           SELECT deep_roles_1.user_id,
                 rr.id,
                 rr.parent_id,
                 deep_roles_1.level + 1 AS level
           FROM roles rr
                   JOIN deep_roles deep_roles_1 ON rr.parent_id::text = deep_roles_1.id::text
           WHERE rr.enabled = true
       )
             SELECT deep_roles.user_id,
                   deep_roles.id        AS role_id,
                   deep_roles.parent_id AS role_parent_id,
                   deep_roles.level
             FROM deep_roles) gdr
               JOIN role_permission rp ON rp.role_id::text = gdr.role_id::text) gpd
         JOIN permissions p ON p.id::text = gpd.permission_id::text;

user_id	key
87gb8fKJHGxh2Pz_Gk_R2	create:devops
87gb8fKJHGxh2Pz_Gk_R2	create:rbac
87gb8fKJHGxh2Pz_Gk_R2	create:users
87gb8fKJHGxh2Pz_Gk_R2	delete:devops
87gb8fKJHGxh2Pz_Gk_R2	delete:rbac
87gb8fKJHGxh2Pz_Gk_R2	delete:users
87gb8fKJHGxh2Pz_Gk_R2	read:devops
87gb8fKJHGxh2Pz_Gk_R2	read:rbac
87gb8fKJHGxh2Pz_Gk_R2	read:users
87gb8fKJHGxh2Pz_Gk_R2	update:devops
87gb8fKJHGxh2Pz_Gk_R2	update:rbac
87gb8fKJHGxh2Pz_Gk_R2	update:users
h8Iqlb8Ixc4IltuOoY5QC	create:devops
h8Iqlb8Ixc4IltuOoY5QC	delete:devops
h8Iqlb8Ixc4IltuOoY5QC	read:devops
h8Iqlb8Ixc4IltuOoY5QC	update:devops
SbZeBSpuy2OdJ0WZ2Z_Qo	read:devops
SJ36zw7nRS4lx18dZlCoo	create:users
SJ36zw7nRS4lx18dZlCoo	delete:users
SJ36zw7nRS4lx18dZlCoo	read:users
SJ36zw7nRS4lx18dZlCoo	update:users

The last part is to join the user-role table after the recursive query to get the permission table after the user corresponds to the role, and then make this query into a View for later use.

RBAC0​

RBAC1​

public.actions​

public.permissions​

public.resources​

public.role_permission​

public.roles​

public.user_role​

public.users​