数据库内核月报 - 2015 / 11-PgSQL · 答疑解惑 · PostgreSQL 用户组权限管理

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
简介:

背景

RDS上的PG没有开放超级用户,这给很多云上的客户使用PG带来了困难。因此有必要给大家讲讲PG的用户权限管理的一些小知识,它可以很好的帮助用户顺利的从之前的 DB 管理方式过度到云上。

  1. PG 的 superuser 拥有几乎全部的数据库权限,甚至可以直接修改系统表,潜在风险相当大;
  2. RDS PG 使用 superuser 运维 DB,例如管理用、管理流复制、备份等,这些操作用户是不需要关心的,换句话说它应该完全的交给云服务来处理;
  3. 对于用户而言,PG 的普通用户权限是完全够用的。使用普通用户,可以管理自己在云上的数据(对表进行DDL、DML、创建修改和管理其他数据库对象)。

之前的 PG 用户习惯使用 superuser 进行日常运维,一方面 superuser 完全不用做任何的授权,使用方便;另一方面这样的操作带来了潜在的风险,容易误操作。

最近碰到了较多的用户反馈,没有了超级用户,无法使用 RDS 的普通用户,管理其他普通用户创建的对象,经常出现操作对象无权限的问题,尤其存在多用户的情况下。通常情况下:

  1. 一个普通用户只能在自己 owner 的DB下创建 schema;
  2. DB 下的对象有一个所属的 schema,普通用户可以在 public 下创建对象(如 table),在其他 schema 下创建对象需要 schema 的 owner 是当前用户或特别的授权;
  3. 管理一个数据库对象,需要是超级用户、或对象的 owner 是当前用户(在用户组权限之外)。

很多用户,使用了很多个 user 在 public 模式下创建了多张表,但是单个用户却没有权限同时管理他们, 更没有权限切换他们的 owner 到一个统一的用户下做统一处理,十分恼火。

解决方法

在这里,我们提供一种通用的方法,可以使用 PG 的用户组和继承特性,做到使用一个普通用户管理多个其他用户。

  1. 使用 RDS 的根账号创建一个用于管理数据库和用户的管理员账号,他具有创建用户和创建 DB 的权限

     postgres=# create user admin createdb createrole login password 'pgsql';
     CREATE ROLE
     postgres=# \du
                                   List of roles
       Role name  |                   Attributes                   | Member of 
     -------------+------------------------------------------------+-----------
      admin       | Create role, Create DB                         | {}
      test        | Superuser                                      | {}
    
  2. 使用这个 admin 登陆 postgres,创建用于存放数据的DB

     postgres=> create database dbadmin;
     CREATE DATABASE
    
  3. 创建子账户subuser1 subuser2,并把他们的权限给admin

     dbadmin=> create user subuser1 ;
     CREATE ROLE
     dbadmin=> create user subuser2 ;
     CREATE ROLE
     dbadmin=> grant subuser1 to admin;
     GRANT ROLE
     dbadmin=> grant subuser2 to admin;
     GRANT ROLE
    
  4. 我们看到 admin 用户组管理了下列2个用户,也就是admin 拥有 subuser1,subuser2 的所有权限

     dbadmin=> \du
                                        List of roles
       Role name  |                   Attributes                   |      Member of
     -------------+------------------------------------------------+---------------------
      admin       | Create role, Create DB                         | {subuser1,subuser2}
      subuser1    |                                                | {}
      subuser2    |                                                | {}
    
  5. 使用 subuser1 或 subuser2 登陆数据库 dbadmin,public schema上创建 owner 是自己的表对象和其他对象。分别用3个用户创建3张表a b c用作测试,另外也可以使用 admin 用户创建分别属于 subuser1 和 subuser2 的 schema ,再创建 table

     dbadmin=> \d
                List of relations
      Schema |    Name    | Type  |  Owner
     --------+------------+-------+----------
      public | t_admin    | table | admin
      public | t_subuser1 | table | subuser1
      public | t_subuser2 | table | subuser2
    
  6. 使用 admin 用户,可以对这3张表做管理,例如:

     dbadmin=> select user;
      current_user
     --------------
      admin
     (1 row)
     dbadmin=> drop table t_admin,t_subuser1,t_subuser2;
     DROP TABLE
    
  7. 但是 subuser1 无法删除 t_admin 和 t_subuser2,同理 subuser2 也无法删除 t_admin 和 t_subuser1

     dbadmin=> select user;
      current_user
     --------------
      subuser1
     (1 row)
     dbadmin=> drop table t_admin;
     ERROR:  must be owner of relation t_admin
     dbadmin=> drop table t_subuser1;
     DROP TABLE
     dbadmin=> drop table t_subuser2;
     ERROR:  must be owner of relation t_subuser2
    
  8. 同理,可以使用 admin 用户,重置这3张表的 owner 到3个用户中的一个,而使用 subuser1 和 subuser2 则没有权限操作,例如:

     dbadmin=> select user;
      current_user
     --------------
      admin
     (1 row)
     dbadmin=> alter table t_admin owner to subuser1;
     ALTER TABLE
     dbadmin=> alter table t_subuser2 owner to subuser1;
     ALTER TABLE
     dbadmin=> alter table t_subuser1 owner to subuser2;
     ALTER TABLE
    
    
     dbadmin=> select user;
      current_user
     --------------
      subuser1
     (1 row)
     dbadmin=> \d
                List of relations
      Schema |    Name    | Type  |  Owner
     --------+------------+-------+----------
      public | t_admin    | table | subuser1
      public | t_subuser1 | table | subuser2
      public | t_subuser2 | table | subuser1
     (3 rows)
     dbadmin=> alter table t_admin owner to admin;
     ERROR:  must be member of role "admin"
     dbadmin=> alter table t_subuser1 owner to subuser1;
     ERROR:  must be owner of relation t_subuser1
    
  9. 最后,提一个完成上述功能的假设,admin 用户默认具有 INHERIT 权限,INHERIT 权限决定一个角色是否“继承”它所在组的角色的权限。一个带有 INHERIT 属性的角色可以自动使用已经赋与它直接或间接所在组的任何权限。没有 INHERIT,其它角色的成员关系只赋与该角色 SET ROLE 成其它角色的能力;其它角色的权限只是在这么做了之后才能获得。如果没有声明,缺省是 INHERIT。通过系统表, pg_roles 可以获得该用户的权限信息

     dbadmin=> select rolname,rolinherit from pg_roles ;
        rolname   | rolinherit
     -------------+------------
      subuser1    | t
      subuser2    | t
      admin       | t
    

总结

使用 PG 用户组可以做到使用一个 PG 用户组管理所在组内的所有其他普通用户和他们的对象。RDS 用户设置一个用户组,既可以管理多个用户的对象,又可以实现一定程度上的权限隔离,同时权限又不会过大,推荐在云上使用该方式管理自己的数据库。

如果要实现更加细粒度的权限控制,则需要使用 grant 和 revoke 语句,请参考官方sql-createrole 和 sql-grant

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
24天前
|
SQL 关系型数据库 数据库
【赵渝强老师】创建PostgreSQL的数据库
本文介绍了在PostgreSQL中通过SQL命令“create database”创建数据库的方法。首先查询系统目录pg_database以查看现有数据库集合,然后使用“create database”命令创建新数据库,并了解其在$PDATA/base目录下对应的文件夹生成。最后重新查询数据库集合确认创建结果,附带视频讲解便于理解操作步骤及注意事项。
|
6月前
|
存储 关系型数据库 数据库
【赵渝强老师】PostgreSQL的数据库
PostgreSQL的逻辑存储结构涵盖数据库集群、数据库、表、索引、视图等对象,每个对象有唯一的oid标识。数据库集群包含多个数据库,每个数据库又包含多个模式,模式内含表、函数等。通过特定SQL命令可查看和管理这些数据库对象。
103 4
|
2月前
|
关系型数据库 数据库 PostgreSQL
【赵渝强老师】PostgreSQL的模板数据库
在PostgreSQL中,创建新数据库时,默认通过拷贝`template1`实现。`template1`包含标准系统对象,可自定义以影响新数据库内容;而`template0`是纯净模板,仅含预定义对象且不应修改。视频讲解和代码示例展示了如何查看现有数据库信息及标识字段的作用。 ![图示](https://ucc.alicdn.com/pic/developer-ecology/yub6x2mlkqwck_398ed06397a44c2d9bfbb5ae5c90bbc0.png) [视频链接](https://www.bilibili.com/video/BV1szyfY4EQn)
【赵渝强老师】PostgreSQL的模板数据库
|
3月前
|
存储 关系型数据库 数据库
华为数据库openGauss与PostgreSQL使用对比
华为openGauss数据库基于PostgreSQL内核演进,进行了多项增强。密码认证从MD5升级为SHA256;字符串存储中,char(n)、varchar(n)的n由字符改为字节,新增nvarchar2(n)表示字符,最大存储100MB;且将空字符''统一转换为null,提升了数据处理的一致性和安全性。
295 12
|
4月前
|
存储 关系型数据库 分布式数据库
PolarDB PostgreSQL版:商业数据库替换与企业上云首选
PolarDB PostgreSQL版是商业数据库替换与企业上云的首选。其技术架构实现存储计算分离,具备极致弹性和扩展性,支持Serverless、HTAP等特性。产品在弹性、性能、成本优化和多模处理方面有显著提升,如冷热数据自动分层、Ganos多模引擎等。已在汽车、交通、零售等行业成功应用,典型案例包括小鹏汽车、中远海科等,帮助企业大幅降低运维成本并提高业务效率。
95 13
|
6月前
|
关系型数据库 分布式数据库 数据库
PostgreSQL+Citus分布式数据库
PostgreSQL+Citus分布式数据库
150 15
|
6月前
|
存储 关系型数据库 数据库
【赵渝强老师】PostgreSQL的数据库集群
PostgreSQL的逻辑存储结构涵盖了数据库集群、数据库、表、索引、视图等对象,每个对象都有唯一的oid标识。数据库集群是由单个PostgreSQL实例管理的所有数据库集合,共享同一配置和资源。集群的数据存储在一个称为数据目录的单一目录中,可通过-D选项或PGDATA环境变量指定。
102 3
|
6月前
|
存储 关系型数据库 MySQL
MySQL vs. PostgreSQL:选择适合你的开源数据库
在众多开源数据库中,MySQL和PostgreSQL无疑是最受欢迎的两个。它们都有着强大的功能、广泛的社区支持和丰富的生态系统。然而,它们在设计理念、性能特点、功能特性等方面存在着显著的差异。本文将从这三个方面对MySQL和PostgreSQL进行比较,以帮助您选择更适合您需求的开源数据库。
428 4
|
6月前
|
SQL 关系型数据库 数据库
PostgreSQL性能飙升的秘密:这几个调优技巧让你的数据库查询速度翻倍!
【10月更文挑战第25天】本文介绍了几种有效提升 PostgreSQL 数据库查询效率的方法,包括索引优化、查询优化、配置优化和硬件优化。通过合理设计索引、编写高效 SQL 查询、调整配置参数和选择合适硬件,可以显著提高数据库性能。
1006 1
|
7月前
|
SQL 关系型数据库 数据库
使用 PostgreSQL 和 Python 实现数据库操作
【10月更文挑战第2天】使用 PostgreSQL 和 Python 实现数据库操作

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版