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
3.2 创建数据库表
1、登录数据库
我的地址：http://192.168.0.21:5050/browser/
2、创建数据库
3、运行数据库sql
