记录由于数据库用户权限导致的一次线上事故

1. 背景

• 11.3 生产环境和用户查询相关的功能，都报错could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
• ELK查询原因，Caused by: org.postgresql.util.PSQLException: 错误: 对表 em_adm_user_group 权限不够
• f lyway新建了V20231031155120__DDL_create_user_group.sql，并且userVo join user_group，所以会查询user_group这张表

2 原因

• 9.4 LGQ定位中毒事件的时候，为了确认是哪个微服务执行的数据库操作（不同的微服务如果使用了不同的用户，可以看到是哪个微服务执行的sql），修改了sith-mes的application-sithMes.yml，将数据库的用户从sith-mes修改成了sith-server，并且赋予了sith-server超级管理员的权限，所以sith-server可以操作sith-mes创建的表。

• 执行create_user_group.sql的时候，因为此时是在sith-mes服务下执行的，所以user_group这张表的owner是sith-server。admin服务此时连接的数据库用户是sith-mes，所以没有操作sith-server的权限

用户sith-server建立的表，用户sith-mes没有权限操作

3. 紧急修复

将新建的user_group 的owner改成了sith-mes

问题：后期如果在sith-mes新建的表，其他微服务要操作的话，依旧会出现没权限问题

4 解决方式

1. 保持所有微服务用户一致，sith-mes改回用户sith-mes，或者其他微服务也改成使用sith-server
2. 将sith-mes也设置成超级管理员，使他可以操作sith-mes数据库的所有表
3. 每个微服务数据库使用自己的用户，flyway基于每个微服务建立，并且需保证服务只操作自己服务的表

5. 数据库新建用户，并赋予权限步骤

场景：用户panyurou创建了一个名为“micro-weather”的database(数据库)，同时在该库下默认的public(shema)创建了一个表city

需求：新建一个用户cityserver，使得这个用户可以操作“micro-weather”的public(shema)下的所有表

5.1 创建用户并设置密码

create user cityserver with PASSWORD 'city-server'

5.2 数据库授权

赋予指定账号，指定数据库的所有权限

1. 以panyurou的身份进入数据库（因为是panyurou创建的数据库和表，所以需要在panyurou的身份下操作）。此时是可以访问city这张表的

   ➜  Desktop psql -U panyurou -d  micro-weather
   
   psql (14.6 (Homebrew))
   Type "help" for help.
   
   micro-weather=# select * from city;
    id | name
   ----+------
   (0 rows)

2. 赋予数据库权限

micro-weather=# grant all privileges on database "micro-weather" to cityserver;
GRANT
micro-weather=# \q

3. 赋予该数据库下所有表权限

micro-weather=# grant all privileges on all tables in schema public to cityserver;
GRANT
micro-weather=# \q

注：当没有赋予权限，并且以新建的用户去访问数据的话，会报错

➜  Desktop psql -U cityserver -d micro-weather

psql (14.6 (Homebrew))
Type "help" for help.

micro-weather=> select * from city;
ERROR:  permission denied for table city

附：数据库用户介绍

执行select * from pg_user;

5.3 常用sql

将用户修改成超级管理员

ALTER USER cityserver WITH SUPERUSER

移除用户超级管理员

ALTER USER cityserver WITH NOSUPERUSER;