目錄

Rbac1 Design

權限系統在任何應用程式都是基礎且重要的部份,主要是對不同的人訪問資源進行權限的控制,本篇文章並沒有特別的再說明 Role-Based Access Control, 解釋的文章已經太多了,隨便 Google 都是一堆。最期在公司的專案也是有採用 RBAC 來管控系統的權限,一般的 RBAC0 (最基本的 User - Role - Permission) 的模型大至上可以符合需求,不過在權限配直的時候會有一點重工,所以選擇了有角色繼承的 RBAC1 來進行實作

/posts/rbac1-design/img/rbac1.jpeg
RBAC1
/posts/rbac1-design/img/public.png
Postgres Database Schema

public.actions

columncommenttypelengthdefaultconstraintsvalues
id (pk)character varying21NOT NULL
nametext
descriptiontext
created_attimestamp with time zone
updated_attimestamp with time zone

public.permissions

columncommenttypelengthdefaultconstraintsvalues
id (pk)character varying21NOT NULL
action_idtext
resource_idtext
keytext
created_attimestamp with time zone
updated_attimestamp with time zone

public.resources

columncommenttypelengthdefaultconstraintsvalues
id (pk)character varying21NOT NULL
nametext
created_attimestamp with time zone
updated_attimestamp with time zone

public.role_permission

columncommenttypelengthdefaultconstraintsvalues
permission_id (pk)character varying21NOT NULL, permission_id
role_id (pk)character varying21NOT NULL, role_id

public.roles

columncommenttypelengthdefaultconstraintsvalues
id (pk)character varying21NOT NULL
parent_idcharacter varying21parent_id
role_nametext
descriptiontext
typebigint
enabledboolean
created_attimestamp with time zone
updated_attimestamp with time zone

public.user_role

columncommenttypelengthdefaultconstraintsvalues
role_id (pk)character varying21NOT NULL, role_id
user_id (pk)character varying21NOT NULL, user_id

public.users

columncommenttypelengthdefaultconstraintsvalues
id (pk)character varying21NOT NULL
nametext
enabledboolean
created_attimestamp with time zone
updated_attimestamp with time zone
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
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;

在繼承的關係下,admin-manager 會有以下角色 (user-manager, devops-manager, devops-runner) 所有的權限。那我們怎麼取得 user1, user3, user3, user4 在角色繼承下的權限呢,這時候我們就可以透過 CTE (common table expression) 來詢查,我們進行小的查詢,最後組成一個 view

  1. 1
    2
    3
    4
    5
    6
    
      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_ididparent_idlevel
    87gb8fKJHGxh2Pz_Gk_R2AsaN4Lp62jeT9jaJl1NljNULL1
    SJ36zw7nRS4lx18dZlCoo-Nmq8l1U-gfQtgTvxAXDJAsaN4Lp62jeT9jaJl1Nlj1
    h8Iqlb8Ixc4IltuOoY5QCgXWvkQRzMaE6fZnKsbVPDAsaN4Lp62jeT9jaJl1Nlj1
    SbZeBSpuy2OdJ0WZ2Z_QoEhZ0CHPt5oc5oe9osk0eQgXWvkQRzMaE6fZnKsbVPD1

    查詢 user 對應到 role 預先代入 level 的欄位給 recursive 查詢使用

  2.  1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    
     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_ididparent_idlevel
    87gb8fKJHGxh2Pz_Gk_R2AsaN4Lp62jeT9jaJl1NljNULL1
    SJ36zw7nRS4lx18dZlCoo-Nmq8l1U-gfQtgTvxAXDJAsaN4Lp62jeT9jaJl1Nlj1
    h8Iqlb8Ixc4IltuOoY5QCgXWvkQRzMaE6fZnKsbVPDAsaN4Lp62jeT9jaJl1Nlj1
    SbZeBSpuy2OdJ0WZ2Z_QoEhZ0CHPt5oc5oe9osk0eQgXWvkQRzMaE6fZnKsbVPD1
    87gb8fKJHGxh2Pz_Gk_R2gXWvkQRzMaE6fZnKsbVPDAsaN4Lp62jeT9jaJl1Nlj2
    87gb8fKJHGxh2Pz_Gk_R2-Nmq8l1U-gfQtgTvxAXDJAsaN4Lp62jeT9jaJl1Nlj2
    h8Iqlb8Ixc4IltuOoY5QCEhZ0CHPt5oc5oe9osk0eQgXWvkQRzMaE6fZnKsbVPD2
    87gb8fKJHGxh2Pz_Gk_R2EhZ0CHPt5oc5oe9osk0eQgXWvkQRzMaE6fZnKsbVPD3

    使用 recursive 詢查之後,我們會得到 user 對到的 role, 並有 level 欄位可以看到繼承的深度

  3.  1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    
     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_idkey
    87gb8fKJHGxh2Pz_Gk_R2create:devops
    87gb8fKJHGxh2Pz_Gk_R2create:rbac
    87gb8fKJHGxh2Pz_Gk_R2create:users
    87gb8fKJHGxh2Pz_Gk_R2delete:devops
    87gb8fKJHGxh2Pz_Gk_R2delete:rbac
    87gb8fKJHGxh2Pz_Gk_R2delete:users
    87gb8fKJHGxh2Pz_Gk_R2read:devops
    87gb8fKJHGxh2Pz_Gk_R2read:rbac
    87gb8fKJHGxh2Pz_Gk_R2read:users
    87gb8fKJHGxh2Pz_Gk_R2update:devops
    87gb8fKJHGxh2Pz_Gk_R2update:rbac
    87gb8fKJHGxh2Pz_Gk_R2update:users
    h8Iqlb8Ixc4IltuOoY5QCcreate:devops
    h8Iqlb8Ixc4IltuOoY5QCdelete:devops
    h8Iqlb8Ixc4IltuOoY5QCread:devops
    h8Iqlb8Ixc4IltuOoY5QCupdate:devops
    SbZeBSpuy2OdJ0WZ2Z_Qoread:devops
    SJ36zw7nRS4lx18dZlCoocreate:users
    SJ36zw7nRS4lx18dZlCoodelete:users
    SJ36zw7nRS4lx18dZlCooread:users
    SJ36zw7nRS4lx18dZlCooupdate:users

    最後一部份就是將經過 recursive 查詢後的 user - role 表去 join permission 拿到 user 對應 role 之後的權限表,然後將這一個查詢作成 View 方便後序使用

  4. Demo RBAC1

    /posts/rbac1-design/img/rbac1.png
    Demo RBAC1

DB fiddle Playground

https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/1179