Postgresql - 用戶權限數據庫

1、綜述

????????在實際的軟件項目開發過程中，用戶權限控制可以說是所有運營系統中必不可少的一個重點功能，根據業務的復雜度，設計的時候可深可淺，但無論怎么變化，設計的思路基本都是圍繞著用戶、部門、角色、菜單這幾個部分展開。

1.1 數據庫實體

? ? ? ? 1、用戶：用戶名、密碼、頭像、個人簡介、性別、所屬部門以及個人權限

? ? ? ? 2、角色：角色名稱和描述，暫時無用處，只是定義。后期進行擴展

? ? ? ? 3、部門：部門名稱、父級部門以及描述

? ? ? ? 4、菜單：菜單名稱、標識、排序、父級菜單等信息

? ? ? ? 5、權限：個人菜單的權限，暫定不根據角色劃分

1.2 數據庫分析

? ? ? ? 數據庫設計規范，按照3級范式設計

? ? ? ? 1、用戶-部門：M:1,包括用戶表、部門表，用戶包含部門ID

? ? ? ? 2、用戶-角色：N:M,包括用戶表、角色表、用戶角色表，用戶角色表包括用戶ID,角色ID

? ? ? ? 3、用戶-權限：M:1，包括用戶表、權限表、用戶表中包括權限ID

? ? ? ? 4、權限-菜單：M:M,包括權限表、菜單表、權限菜單表，權限菜單表包括權限ID、菜單ID

2、數據庫表設計

2.1 表設計

? ? ? ? 用戶表、部門表、角色表、用戶角色表、權限表、菜單表、權限菜單表

????????

2.2 生成數據庫完整的SQL

/*==============================================================*/
/* DBMS name:      PostgreSQL 9.x                               */
/* Created on:     2024/7/7 17:49:29                            */
/*==============================================================*/drop index  if exists index_7;drop table if exists menu cascade;drop index  if exists index_1;drop table if exists organization cascade;drop index  if exists index_5;drop table if exists permission cascade;drop index  if exists index_6;drop table if exists permission_menu cascade;drop index  if exists index_4;drop table if exists role cascade;drop index  if exists index_2;drop table if exists user_info cascade;drop index  if exists index_3;drop table if exists user_role cascade;/*==============================================================*/
/* Table: menu                                                  */
/*==============================================================*/
create table menu (id                   varchar(128)         not null,name                 varchar(128)         null,parent_id            varchar(128)         null,menu_type            varchar(128)         null,permission           varchar(128)         null,sort                 int4                 null,status               int4                 null,create_user          varchar(128)         null,create_time          timestamp            null,extension            json                 null,constraint pk_menu primary key (id)
);comment on table menu is
'菜單表';comment on column menu.id is
'菜單編號';comment on column menu.name is
'菜單名稱';comment on column menu.parent_id is
'父級編號';comment on column menu.menu_type is
'菜單類別';comment on column menu.permission is
'權限標識';comment on column menu.sort is
'排序';comment on column menu.status is
'狀態';comment on column menu.create_user is
'創建人';comment on column menu.create_time is
'創建時間';comment on column menu.extension is
'擴展信息';/*==============================================================*/
/* Index: index_7                                               */
/*==============================================================*/
create  index index_7 on menu (
parent_id
);/*==============================================================*/
/* Table: organization                                          */
/*==============================================================*/
create table organization (id                   varchar(128)         not null,name                 varchar(128)         null,parent_id            varchar(128)         null,description          varchar(256)         null,create_user          varchar(128)         null,create_time          timestamp            null,extension            json                 null,constraint pk_organization primary key (id)
);comment on table organization is
'組織';comment on column organization.id is
'部門編號';comment on column organization.name is
'部門名稱';comment on column organization.parent_id is
'父級部門';comment on column organization.description is
'部門描述';comment on column organization.create_user is
'創建人';comment on column organization.create_time is
'創建時間';comment on column organization.extension is
'擴展信息';/*==============================================================*/
/* Index: index_1                                               */
/*==============================================================*/
create  index index_1 on organization (
parent_id
);/*==============================================================*/
/* Table: permission                                            */
/*==============================================================*/
create table permission (id                   varchar(128)         not null,name                 varchar(256)         null,description          varchar(256)         null,create_user          varchar(128)         null,create_time          timestamp            null,extension            json                 null,constraint pk_permission primary key (id)
);comment on table permission is
'權限表';comment on column permission.id is
'角色編號';comment on column permission.name is
'角色名稱';comment on column permission.description is
'角色描述';comment on column permission.create_user is
'創建人';comment on column permission.create_time is
'創建時間';comment on column permission.extension is
'擴展信息';/*==============================================================*/
/* Index: index_5                                               */
/*==============================================================*/
create  index index_5 on permission (
name
);/*==============================================================*/
/* Table: permission_menu                                       */
/*==============================================================*/
create table permission_menu (id                   varchar(128)         not null,permission_id        varchar(128)         null,menu_id              varchar(128)         null,constraint pk_permission_menu primary key (id)
);comment on table permission_menu is
'權限菜單表';/*==============================================================*/
/* Index: index_6                                               */
/*==============================================================*/
create  index index_6 on permission_menu (
permission_id,
menu_id
);/*==============================================================*/
/* Table: role                                                  */
/*==============================================================*/
create table role (id                   varchar(128)         not null,name                 varchar(256)         null,description          varchar(256)         null,create_user          varchar(128)         null,create_time          timestamp            null,extension            json                 null,constraint pk_role primary key (id)
);comment on table role is
'角色信息表';comment on column role.id is
'角色編號';comment on column role.name is
'角色名稱';comment on column role.description is
'角色描述';comment on column role.create_user is
'創建人';comment on column role.create_time is
'創建時間';comment on column role.extension is
'擴展信息';/*==============================================================*/
/* Index: index_4                                               */
/*==============================================================*/
create  index index_4 on role (
name
);/*==============================================================*/
/* Table: user_info                                             */
/*==============================================================*/
create table user_info (id                   varchar(128)         not null,username             varchar(128)         null,password             varchar(256)         null,aliasname            varchar(128)         null,phone                varchar(20)          null,face                 varchar(256)         null,profile              varchar(500)         null,sex                  int4                 null,org_id               varchar(128)         null,permission_id        varchar(128)         null,create_user          varchar(128)         null,create_time          timestamp            null,extension            json                 null,constraint pk_user_info primary key (id)
);comment on table user_info is
'用戶信息表';comment on column user_info.id is
'用戶編號';comment on column user_info.username is
'用戶名';comment on column user_info.password is
'用戶密碼';comment on column user_info.aliasname is
'用戶昵稱';comment on column user_info.phone is
'用戶電話';comment on column user_info.face is
'頭像圖片';comment on column user_info.profile is
'個人簡介';comment on column user_info.sex is
'性別';comment on column user_info.org_id is
'所在部門';comment on column user_info.permission_id is
'權限編號';comment on column user_info.create_user is
'創建人';comment on column user_info.create_time is
'創建時間';comment on column user_info.extension is
'擴展信息';/*==============================================================*/
/* Index: index_2                                               */
/*==============================================================*/
create  index index_2 on user_info (
username,
password,
phone
);/*==============================================================*/
/* Table: user_role                                             */
/*==============================================================*/
create table user_role (id                   varchar(128)         not null,user_id              varchar(128)         null,role_id              varchar(128)         null,constraint pk_user_role primary key (id)
);comment on table user_role is
'用戶角色表';comment on column user_role.id is
'編號';comment on column user_role.user_id is
'用戶編號';comment on column user_role.role_id is
'角色編號';/*==============================================================*/
/* Index: index_3                                               */
/*==============================================================*/
create  index index_3 on user_role (
user_id,
role_id
);alter table permission_menuadd constraint fk_permissi_reference_permissi foreign key (permission_id)references permission (id)on delete cascade on update restrict;alter table permission_menuadd constraint fk_permissi_reference_menu foreign key (menu_id)references menu (id)on delete cascade on update restrict;alter table user_infoadd constraint fk_user_inf_reference_organiza foreign key (org_id)references organization (id)on delete cascade on update restrict;alter table user_infoadd constraint fk_user_inf_reference_permissi foreign key (permission_id)references permission (id)on delete set null on update restrict;alter table user_roleadd constraint fk_user_rol_reference_user_inf foreign key (user_id)references user_info (id)on delete cascade on update restrict;alter table user_roleadd constraint fk_user_rol_reference_role foreign key (role_id)references role (id)on delete cascade on update restrict;

3、數據庫部署

3.1 docker部署數據庫

? ? ? ? 1、創建部署文件

????????Docker Compose 簡化了對整個應用程序堆棧的控制，使得在一個易于理解的 YAML 配置文件中輕松管理服務、網絡和數據卷。要使用 Docker Compose 部署 PostgreSQL，首先需創建一個docker-compose.yml文件，如下所示：

version: '3'
services:postgres:image: postgres:13restart: alwaysports:- 5432:5432environment:POSTGRES_USER: postgresPOSTGRES_PASSWORD: postgresvolumes:- /home/pg/data:/var/lib/postgresql/datapgadmin:image: dpage/pgadmin4restart: alwaysports:- 5050:80environment:- PGADMIN_DEFAULT_EMAIL=admin@pgadmin.com- PGADMIN_DEFAULT_PASSWORD=adminvolumes:- /home/pg/admin:/var/lib/pgadmin

image：指定了要使用的 Docker 鏡像及其版本。在這里，我們使用了官方的 PostgreSQL 13?版本鏡像。為了確保系統的穩定性和兼容性，推薦使用 PostgreSQL 官方鏡像的一個穩定版本而不是最新版（latest）。通常來說，生產環境中應該避免使用 latest 標簽，因為它指向最新的版本，而最新版本可能包含未經充分測試的特性或變化，這可能會影響到生產環境的穩定性。
environment：設置環境變量。我們為 PostgreSQL 數據庫設置了密碼 root。請將其更改為更安全的密碼。這是postgres默認管理員賬戶的密碼。由于這個值是必需的，如果沒有設置，容器將無法啟動。
ports：用來將容器的端口映射到宿主機的端口，使得宿主機能夠與集群進行通信。通常，只有服務需要直接從宿主機的網絡訪問時，我們才會映射端口。將容器的 5432 端口映射到宿主機的 5432 端口，使外部可訪問 PostgreSQL。
volumes：實現數據持久化的關鍵部分。PostgreSQL 存儲數據在 /var/lib/postgresql/data 路徑，日志存儲在 /var/log/postgresql 路徑。postgres_db 服務將這兩個路徑映射到宿主機的數據卷的 data 和 log 的數據卷上。這意味著即使容器被刪除，存儲在這兩個數據卷上的數據也不會丟失，實現了數據的持久化。配置日志數據卷是一個好的實踐，它可以幫助你更好地管理和保存日志文件，以便于問題診斷和性能監控。

? ? ? ? ?2、啟動服務：docker compose up -d