问题描述

按照PG14的创建业务用户和业务数据库时提示如下报错：

--登录数据库  注意显示的是PG10.22版本
[root@ecs-c9d0-159 ~]# su - postgres
Last login: Sun Jun 30 16:05:36 CST 2024 on pts/2
-bash-4.2$ psql
psql (10.22)
Type "help" for help.

--创建业务用户
create user drugtopcsp with encrypted password '123456';

--创建业务数据库
postgres=> create database drugtopcsp owner drugtopcsp;
ERROR:  must be member of role "drugtopcsp"

分析过程

创建用户的时候会自动生成一个同名的角色

--查看用户
meatopcsp=# \du+ drugtopcsp
                   List of roles
 Role name  | Attributes | Member of | Description
------------+------------+-----------+-------------
 drugtopcsp | Create DB  | {}        |

解决办法

--将角色授权给postgres用户
grant drugtopcsp to postgres;

--查看
postgres=> \du+ postgres
                                                     List of roles
 Role name |                   Attributes                    |                 Member of                  | Description
-----------+-------------------------------------------------+--------------------------------------------+-------------
 postgres  | Create role, Create DB, Replication, Bypass RLS | {drugtopcsp,cosmtopcsp,meatopcsp,yjsjzhzl} |

 --创建数据库时指定属主  创建成功
 postgres=> create database drugtopcsp owner drugtopcsp;
CREATE DATABASE

补充

PG10创建业务用户和数据库语句

create user drugtopcsp with encrypted password '123456';
grant cosmtopcsp to postgres;
create database drugtopcsp owner drugtopcsp;
grant all privileges on database drugtopcsp to drugtopcsp;
grant all privileges on all tables in schema public to drugtopcsp;

PG14创建业务用户和数据库语句

create user drugtopcsp with encrypted password '123456';
create database drugtopcsp owner drugtopcsp;
grant all privileges on database drugtopcsp to drugtopcsp;
grant all privileges on all tables in schema public to drugtopcsp; 

二者区别在于：创建业务用户和数据库语句时，版本PG10比版本PG14多一条将角色授予创建数据库用户的sql。

参考链接：https://blog.csdn.net/weixin_49756466/article/details/122074430?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522172008658316800197065196%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=172008658316800197065196&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~first_rank_ecpm_v1~rank_v31_ecpm-1-122074430-null-null.142^v100^pc_search_result_base3&utm_term=ERROR%3A%20%20must%20be%20member%20of%20role%20drugtopcsp&spm=1018.2226.3001.4187