延續上一篇文章 Rbac1 Design | KaiChu, 我們透過 Postgres 中的 CTE (common table expression) recursive query 來查詢有繼承 Role 的有對應的權限
user_id permission
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 |
user vs resource permission
read:R, write:C, delete:D, update:U
user_id | devops | rbac | users |
---|---|---|---|
87gb8fKJHGxh2Pz_Gk_R2 | CDRU | CDRU | CDRU |
h8Iqlb8Ixc4IltuOoY5QC | CDRU | NULL | NULL |
SbZeBSpuy2OdJ0WZ2Z_Qo | R | NULL | NULL |
SJ36zw7nRS4lx18dZlCoo | NULL | NULL | CDRU |
透過 postgres 的 Transpose Rows to Columns 可以得到上面一個人眼比較直覺得大表
核心的 sql 就是使用 crosstab
指令, 完整操作請看下方 full sql