公司选用了阿里云的PolarDB做数据库，这个东西其实就是Postgres增加了外挂，可以支持Oracle语法。

没办法，得仔细研究一下Postgres的用户管理了。

PostgreSQL权限架构是宝塔形结构

最上层是实例

实例中允许创建多个数据库

每个数据库中可以创建多个schema，

每个schema下面可以创建多个对象。

对象包括表、物化视图、操作符、索引、视图、序列、函数、… 等等。

image-20240205103517867

上面schema中有个奇怪的东西，public，注意：是小写。

先总结：PUBLIC是缺省的权限，代表所有人的意思（是个角色）。

默认情况下，在创建数据库之后，允许PUBLIC角色(大写)连接，即允许任何人连接。

默认情况下，数据库在创建后，不允许除了超级用户和owner所有者之外的任何人在数据库中创建schema。

默认情况下，在创建数据库之后，会自动创建名为 public 的schema，这个schema的all权限已经赋予给PUBLIC角色（注意是大写），即允许任何人在里面创建对象。

schema级别的权限，包括允许查看schema中的对象(USAGE)，以及允许在schema中创建对象(CREATE)。

默认情况下新建的schema的权限不会赋予给PUBLIC角色，因此除了超级用户和owner，任何人都没有权限查看schema中的对象或者在schema中新建对象。

举例来说，建了个库，又建了用户，没给这个用户赋予任何权限。缺省他就从PUBLIC角色继承了对库里的schema.pulic权限，可以连接到这个schema.pulic，并且在这里建临时表、建表、view等等对象，但是没办法建立其它schema。

PostgreSQL的模式（SCHEMA）可以看作是一个表的集合。

一个模式可以包含视图、索引、数据类型、函数和操作符等。

再来说角色：

在数据库中所有的权限都和角色挂钩。

角色和用户的唯一区别在于，角色是nologin的，而用户允许login，仅此而已。

而"PUBLIC"是一个特殊的角色，代表着所有人。

那又有一个问题：

每个PostgreSQL对象都有一个名为“所有者”的特殊角色。只有所有者才能执行某些操作，如 ALTER TABLE ，而你不能将这样的权限授予非所有者。

那不可能只有一个人可以alter表结构吧，我们可以使用角色继承来解决此问题。创建 table_owner 角色并且 GRANT table_owner TO user1, user2 （user1和user2继承table_owner)，然后赋权 ALTER TABLE my_table OWNER TO table_owner 赋予table_owner所有者角色。现在表的所有者是 table_owner 了，但是因为 user1 和 user2 是该角色的成员，所以他们也具有继承权限来运行 ALTER TABLE了，如下图 。

image-20240205113508881

啰嗦了这么多，除了修改pg_hba.conf，赋权的命令就两条，grant和revoke，看下图：

image-20240205103610366

进入实战，首先从上到下都看一看，有什么库，有什么schema，有什么对象，有什么表，有什么索引，有什么角色：

#推荐用psql来进行管理，有很多快捷键，navicat里面是没有的
#没有的话就装一个
yum install postgresql.x86_64

psql -h 10.8.2.61 -U admin 

#必用快捷键
#列出所有的库
\l

#列出所有的schema
\dn

#看schema.public权限
\dn+ public

#列出所有的对象（table, view, sequences）
\d

#列出所有的角色
\du

#列出所有表
\dt

#列出所有索引
\di


#同样的sql
select * from pg_roles;

#看看能登录的用户有哪些
select * from pg_user;

那自顶向下开始：

#建立boms_admin角色：非超级用户+可以建立db+可以建立role+可以把权限继承给别人
create role boms_admin nosuperuser createdb createrole inherit;

#建立用户，继承bomsuser的权限，可以登录，有密码
CREATE role bomsuser in role boms_admin login password ‘password’;

#建立库，owner是boms_admin
create database bomsdb owner boms_admin;

#用新用户连接新的DB，当然也可以用超级用户连接，这两人都有权限
\c bomsdb bomsuser;
\c bomsdb superuser;

#先建立个schema，并且在schema里建个表
create schema zrr;
create table zrr.test (x integer);
insert into zrr.test values (1),(2),(3);
select * from zrr.test;

#召回PUBLIC可以在数据库级别中database test中连接、使用临时表的权限
revoke connect, temporary on database bomsdb from public;

#召回PUBLIC可以在schema级别中可以USAGE使用和CREATE建立的权限，注意，你现在连接的是哪个库，召回的就是哪个库的public
#这里连的是bomsdb库，召回的就是这个库里的schema.public
REVOKE USAGE, CREATE ON SCHEMA public FROM public;

#这样操作之后只有owner和superuser可以用这个database了
#我们必须显式赋权才可以连上并且建库了

#建个新用户
create user testuser login password ‘password';

#赋连接权限，赋予一个表的只读权限
grant connect on database bomsdb to testuser;
grant usage on schema zrr to testuser;
grant select on table zrr.test to testuser;

#连接上去，测一下
\c bomsdb testuser;
select * from zrr.test;

两个脚本：

A、看用户test1都有啥表权限

SELECT grantee,table_schema,table_name, string_agg( privilege_type,', ' ) as privilege_type 
FROM information_schema.role_table_grants 
where grantee='test1' 
group by table_name,table_schema,grantee;

B、看表xxx都是什么用户才有权限

SELECT grantee,table_schema,table_name,string_agg( privilege_type,', ' ) as privilege_type
FROM information_schema.role_table_grants
WHERE table_name='xxx'
group by grantee,table_schema,table_name;

提示无权限的时候，可以用以下函数来检查

select has_database_privilege(user, database, privilege);

select has_schema_privilege(user, schema, privilege);

select has_table_privilege(user, table, privilege);

太费劲了，花了两天时间才算大概搞明白。

再给个初始的直白解释脚本：

概念神：

一、权限管理在 PostgreSQL 中是递归和分层的，因此需要分别管理1、数据库、2、模式，3、以及模式中的具体对象（如表）。三种级别中的权限。
二、PostgreSQL 的权限模型是累加的。
#首先用admin登录

#建立一个库
CREATE DATABASE test;

#这时候schema.public直接就被建立了，而且PUBLIC直接赋权了
#这时候去\dn+ public
\dn+ public
                        List of schemas
  Name  |  Owner  | Access privileges  |      Description
--------+---------+--------------------+------------------------
 public | dbadmin | dbadmin=UC/dbadmin+| standard public schema
        |         | =UC/dbadmin        |
#上面命令可以看到只有dbadmin是Owner，且权限是UC
#但实际从PGadmin里面看，还有个PUBLIC UC（USAGE+CREATE)的权限，缺省就赋予了


#下面一句虽然召回了public对库的权限，但是，建库就缺省有的PUBLIC对schema的权限不会消失
#因为postgres的权限机制是累加机制
#因此，你撤销了数据库层面的权限，但没有触及 public schema 本身的权限。
REVOKE ALL ON DATABASE test FROM public;

#建立角色组，并且可以连接到数据库，在库级别赋予所有权限
#依然只会叠加，不会影响到public schema的权限
CREATE ROLE test_project;
GRANT CONNECT ON DATABASE test TO test_project;
GRANT ALL PRIVILEGES ON DATABASE test TO test_project;

#建立角色，把角色组的权限给角色，就搞定了
CREATE ROLE test LOGIN PASSWORD 'lankutest';
GRANT test_project TO test;

补充：

  • PUBLIC 和 public（未加引号）是等价的，在 PostgreSQL 上大小写不敏感。
  • "PUBLIC"（加双引号）会被看作是一个字符串，而非 PostgreSQL 预定义的 PUBLIC 角色。
  • PUBLIC 是 PostgreSQL 的特殊保留角色，代表所有用户，不能直接创建或管理，也不会出现在 pg_roles 中。

再补充一下，如果就是建一个用户，然后对一个库有权限，简单了

# 生成密码
tr -cd '[:alnum:]' < /dev/urandom | fold -w "21" | head -n 1

# 建用户和库
CREATE USER "work-booking" WITH PASSWORD 'xxxxxxxx';
CREATE DATABASE "work-booking" OWNER "work-booking";