Postgresql 数据库用户权限授权(用户角色分配模式)

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核8GB 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
简介: 为了更方面和安全地管理数据库用户账号权限安全,实现通过用户角色代理的模式,实现用户账号功能授权的模式

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

环境要求

数据库版本: >= pg9.4

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

image.png

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

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';

image.png

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

image.png

新建表自动授权

-- 为后续创建的表赋予相关权限,则必须使用 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';

image.png

-- 正确的查询用户权限的方式是通过用户的代理角色和用户本身查询相应的权限
-- 可参考函数表达式使用方式(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')
 )

image.png

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

----------------------------- 权限删除 ---------------------
-- 回收用户的角色即可回收用户权限
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';
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
存储 安全 Linux
小白带你学习linux数据库—用户权限管理(二十九)
小白带你学习linux数据库—用户权限管理(二十九)
123 0
|
11月前
|
关系型数据库 数据库 数据安全/隐私保护
PostgreSQL基础之教你如何轻松管理用户角色与权限
PostgreSQL基础之教你如何轻松管理用户角色与权限
513 0
|
8月前
|
Oracle 关系型数据库 数据库
【赵渝强老师】PostgreSQL中的模式
在PostgreSQL中,所有数据库对象均隶属于模式,包括表、索引、视图等,每个对象有唯一的oid标识。创建数据库时,默认生成名为“public”的Schema。用户可自定义模式,如通过SQL语句创建名为demo的模式及其下的表。与Oracle不同,PostgreSQL中用户和模式不是一一对应关系。
189 12
【赵渝强老师】PostgreSQL中的模式
|
12月前
|
分布式计算 DataWorks 关系型数据库
DataWorks操作报错合集之使用连接串模式新增PostgreSQL数据源时遇到了报错"not support data sync channel, error code: 0001",该怎么办
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
|
11月前
|
SQL 安全 数据库
"数据库守卫战:揭秘SQL Server中角色与权限的神秘面纱,一键打造坚不可摧的安全堡垒!"
【8月更文挑战第21天】数据库安全性对企业至关重要。SQL Server提供强大机制保障数据安全,包括用户角色管理和权限授权。本文以杂文形式介绍如何创建服务器角色和数据库角色,并通过SQL语句进行权限分配。从创建角色到添加用户、授权和撤销权限,再到最佳实践,帮助读者掌握SQL Server中的角色和权限管理,确保数据安全与完整性。
284 0
|
弹性计算 安全 数据库
云上攻防-云服务篇&弹性计算&云数据库&实例元数据&控制角色&AK控制台接管
云上攻防-云服务篇&弹性计算&云数据库&实例元数据&控制角色&AK控制台接管
249 7
|
Java 数据库连接 数据库
深入探索:Java连接池在数据库性能优化中的角色
【6月更文挑战第24天】Java应用中,数据库连接池如HikariCP提升性能,减少资源消耗。连接池预创建并管理连接,避免频繁创建/关闭。工作流程:申请连接→池中取或新建→使用后归还给池。示例展示了如何配置及使用HikariCP连接池,强调了其在高并发环境中的重要性。选择合适连接池并优化配置对系统性能至关重要。
92 1
|
存储 安全 关系型数据库
深入理解MySQL数据库存储引擎及数据授权
深入理解MySQL数据库存储引擎及数据授权
155 0
|
关系型数据库 MySQL Java
关系型数据库mysql的开源与授权
【6月更文挑战第12天】
448 3
|
12月前
|
Web App开发 缓存 数据管理
数据管理DMS使用问题之执行SHOW CREATE TABLE命令查看表结构时,数据库管理员和普通授权账号看到的为什么不一样
阿里云数据管理DMS提供了全面的数据管理、数据库运维、数据安全、数据迁移与同步等功能,助力企业高效、安全地进行数据库管理和运维工作。以下是DMS产品使用合集的详细介绍。

热门文章

最新文章

推荐镜像

更多