Postgresql 数据库用户权限授权(用户角色分配模式)-阿里云开发者社区

开发者社区> 厦门小佳> 正文

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

简介: 为了更方面和安全地管理数据库用户账号权限安全,实现通过用户角色代理的模式,实现用户账号功能授权的模式
+关注继续查看

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

环境要求

数据库版本: >= 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';

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
9584 0
PostgreSQL 数据库实例只读锁定(readonly) - 硬锁定,软锁定,解锁
PostgreSQL 数据库实例只读锁定(readonly) - 硬锁定,软锁定,解锁
561 0
MySQL与 PostgreSQL 数据库功能对比
概述 在几个流行的数据库中,我首先接触到的是MySQL,随着工作发展,接触到越来越多的是PostgreSQL数据库。这两个十分流行的开源数据库。在这之后,我就会经常和一些朋友进行讨论:MySQL和PostgreSQL两者之间到底有什么异同点呢?翻阅资料,今天就来学习一下 ACID的遵从性(ACID...
11588 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
13292 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
11588 0
.net连接PostgreSql数据库
Postgresql与.net .net连接PostgreSql数据库介绍一种方法。 请到http://pgfoundry.org/ 下载驱动。 地址:http://pgfoundry.org/frs/?group_id=1000140&release_id=1434 我下载了2个, Npgsql2.0.6-bin-ms.net.zip和Npgsql2.0.6-bin-ms.net3.5sp1.zip 解压第一个,把bin目录中的两个库文件拷贝到项目bin里。
849 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
7008 0
+关注
9
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载