当前位置：首页 > news >正文

Postgresql - 用户权限数据库

news 来源：原创 2024/9/20 17:52:53

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