Postgresql 数据库用户权限授权（用户角色分配模式）

数据库角色授权模式，用户按需分配

环境要求

数据库版本: >= pg9.4

实现案例目标,用户的数据库角色权限分配架构

参考案例-只读角色的建立与授权

pg 为用户创建只读角色，所有需要配置只读账号的只要授予只读角色即可

初始化数据库

-- 创建测试库
CREATE DATABASE testdb;
-- 创建测试表
CREATE TABLE "test_1" ("id" bigserial NOT NULL, "txt" text NOT NULL,   PRIMARY KEY ("id") );

创建角色，给角色授权

-- 一定要切换到 testdb 库执行相关的指令，否则无效
-- \c testdb
-- 创建角色并授予登陆的权限，默认拥有public的部分权限，只能查看数据库名词和表名，其他不能查阅
CREATE ROLE db_role1 WITH 
  LOGIN
  NOSUPERUSER
  NOINHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
  ENCRYPTED PASSWORD '123456';    
-- 添加注释 
COMMENT ON ROLE db_role1 IS '只读用户';

-- 授予只读权限
GRANT select ON all tables in schema public TO db_role1;
-- 查看权限授权作用，会发现目前库里的所有表的权限都已授权，但是重新创建的表，则没有对应的 权限，还需要执行 ALTER...GRANT... 才能动态构建新创建表的权限授权
select * from information_schema.table_privileges where grantee='db_role1';

grant 【命令】之参数组合参考 ，例如： grant select 只读授权
来源: https://www.postgresql.org/docs/14/ddl-priv.html

新建表自动授权

-- 为后续创建的表赋予相关权限，则必须使用 ALTER  ... GRANT ... 的语法授权
-- 函数授权
GRANT USAGE ON SCHEMA public TO db_role1;
-- 每创建一个新表，则赋予对象查询的权限
ALTER DEFAULT PRIVILEGES for role postgres IN SCHEMA public
GRANT select ON TABLES TO db_role1;

角色授权给用户

-- 创建用户，默认可查看到所有的数据库名和表名
CREATE USER pguser_1 WITH PASSWORD '123456';

-- 将角色1授权给用户1
grant db_role1 to pguser_1; 

--- 查看用户的权限，会发现是空的
select * from information_schema.table_privileges where grantee='pguser_1';

-- 正确的查询用户权限的方式是通过用户的代理角色和用户本身查询相应的权限
-- 可参考函数表达式使用方式(pg12的相对写得较全，新版本14的反而比较模糊，可通过顶部去切换当前信息载体的版本，便于对照):
https://www.postgresql.org/docs/12/functions-info.html
-- 直接查询用户权限
 SELECT * from information_schema.table_privileges
 where grantee in (
   select rolname from pg_roles where pg_has_role('pguser_1',oid,'member')
 )

删除用户角色，以此收回权限

----------------------------- 权限删除 ---------------------
-- 回收用户的角色即可回收用户权限
revoke db_role1 from pguser_1; 

接下来我们给其他用户授权，就只需要分配给新用户角色即可快速授权，可以高效地管理数据库权限，以此达到高效的安全管控

    grant 【自定义或系统角色】to  【新用户】; 

其他场景和命令参考

-- 回收所有权限
revoke all on database "testdb" from db_role1;
revoke all on all tables in schema public from db_role1;
revoke all ON SCHEMA public from db_role1;
-- 回收默认权限
alter DEFAULT PRIVILEGES for role postgres IN SCHEMA public
revoke all on tables from db_role1;
drop role db_role1; -- 删除角色
drop user pguser_1; -- 删除用户

-------------------------- 其他权限查询参考 -------------
-- 查看视图权限
select * from information_schema.table_privileges where grantee='db_role1';
-- 查看函数授权
select * from information_schema.usage_privileges where grantee='db_role1';
-- 查看存储过程函数相关权限表
select * from information_schema.routine_privileges where grantee='db_role1';