postgresql|数据库|角色(用户)管理工作---授权和去权以及usage和select两种权限的区别

本文涉及的产品
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
云原生数据库 PolarDB MySQL 版,通用型 2核4GB 50GB
简介: postgresql|数据库|角色(用户)管理工作---授权和去权以及usage和select两种权限的区别

前言:

postgresql做为一个比较复杂的关系型的重型数据库,不管是安装部署,还是后期的运行维护,都还是有比较多的细节问题需要引起关注。

例如,用户权限的合理分配,那么,什么是权限的合理分配呢? 自然是权限的最小化原则,也就是说每个用户能够完成其权限范围内的工作,而不会由于黑客攻击,漏洞等原因造成安全方面的危险。

在写这篇文章之前,仅仅是对于一些普通用户简单的随便赋权就完事了,而这样的管理工作显然是不够的,因此,本文将对postgresql数据库内的用户赋权和去权做一个相对完整的总结,并通过示例说明 usage权限和select权限的不同。

一,

用户的权限有哪些?

SELECT:该权限用来查询表或是表上的某些列,或是视图,序列。
INSERT:该权限允许对表或是视图进行插入数据操作,也可以使用COPY FROM进行数据的插入。
UPDATE:该权限允许对表或是或是表上特定的列或是视图进行更新操作。
DELETE:该权限允许对表或是视图进行删除数据的操作。
TRUNCATE:允许对表进行清空操作。
REFERENCES:允许给参照列和被参照列上创建外键约束。
TRIGGER:允许在表上创建触发器。
CREATE:对于数据库,允许在数据库上创建Schema;对于Schema,允许对Schema上创建数据库对象;对于表空间,允许把表或是索引指定到对应的表空间上。
CONNECT:允许用户连接到指定的数据库上。
TEMPORARY或是TEMP:允许在指定数据库的时候创建临时表。
EXECUTE:允许执行某个函数。
USAGE:对于程序语言来说,允许使用指定的程序语言创建函数;对于Schema来说,允许查找该Schema下的对象(不包括授权后的新建对象);对于序列来说,允许使用currval和nextval函数;对于外部封装器来说,允许使用外部封装器来创建外部服务器;对于外部服务器来说,允许创建外部表。
ALL PRIVILEGES:表示一次性给予可以授予的权限。

OK,增删改查也就是select ,update,insert,delete  和usage应该是可以归于一类的,而select和usage是十分相似的,至少在schema下,两者是基本雷同的,但需要注意的是,授权后的新建对象,比如新建表,usage是无权查询的,而select显然是不存在此类问题的。

二,

正确的只读用户赋权

1,

第一种赋权

usage---使用权+select查询权

先创建相关schema,名为mytest,相关role,名为test

test=# \c test
You are now connected to database "test" as user "postgres".
test=# create schema mytest;
CREATE SCHEMA
test=# \du
                                        List of roles
     Role name      |                         Attributes                         | Member of 
--------------------+------------------------------------------------------------+-----------
 drmc               |                                                            | {}
 pg1                | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 pms30              | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 postgres           | Superuser                                                  | {}
 postgres_exporter  |                                                            | {}
 postgres_exporter1 |                                                            | {}
 power_common       |                                                            | {}
 power_tf           |                                                            | {}
 zsk                |                                                            | {}
test=# create user test with password '123456';
CREATE ROLE

赋权:

test=# grant USAGE on SCHEMA mytest to test;
GRANT
test1=> grant SELECT on ALL tables in schema mytest to test;

测试就不演示了,只是需要注意一点,要赋权两个,usage和select,两者缺一不可,也就是说必须是两个命令!!!!!

OK,以上是用户test赋权select到test数据库下的mytest这个schema,下面为了继续测试,删除test这个用户。

2,

强制删除已赋权过的用户

OK,删除的时候报错了,这就让人比较无语了,报错说的是名为test的数据库有5个对象依赖于用户test,不过还是有解决办法的

postgres=# drop user test;
2023-08-09 01:15:34.031 CST [14975] ERROR:  role "test" cannot be dropped because some objects depend on it
2023-08-09 01:15:34.031 CST [14975] DETAIL:  5 objects in database test
2023-08-09 01:15:34.031 CST [14975] STATEMENT:  drop user test;
ERROR:  role "test" cannot be dropped because some objects depend on it
DETAIL:  5 objects in database test

强制删除:

需要reassign和drop owner by以及drop user  三条命令,缺一不可。

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# \dn
List of schemas
  Name  | Owner 
--------+-------
 mytest | test
 public | pg1
(2 rows)
test=# REASSIGN OWNED BY test TO postgres;
REASSIGN OWNED
test=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 mytest | postgres
 public | pg1
(2 rows)
test=# drop owned BY test cascade;
NOTICE:  drop cascades to 4 other objects
DETAIL:  drop cascades to table mytest.dept
drop cascades to table mytest.emp
drop cascades to table mytest.bonus
drop cascades to table mytest.salgrade
DROP OWNED

OK,查询test这个用户是否删除:

可以看到,确实没有了,有强迫症的人士就非常舒服了。

但特别需要注意,该强制删除用户因为是级联删除,因此,很大概率会把依赖的schema和table都删除,所以此方式强制删除用户需要提前备份,防止发生不测

test1=# \du+
                                               List of roles
     Role name      |                         Attributes                         | Member of | Description 
--------------------+------------------------------------------------------------+-----------+-------------
 drmc               |                                                            | {}        | 
 pg1                | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        | 
 pms30              | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        | 
 postgres           | Superuser                                                  | {}        | 
 postgres_exporter  |                                                            | {}        | 
 postgres_exporter1 |                                                            | {}        | 
 power_common       |                                                            | {}        | 
 power_tf           |                                                            | {}        | 
 zsk                |                                                            | {}        | 

3,

第二种赋权

grant select+ owner

test=# create user test with password '123456';
CREATE ROLE
test=# \c
You are now connected to database "test" as user "postgres".
test=# grant SELECT on ALL tables in schema mytest to test;
GRANT
test=# set search_path to mytest ;
SET
test=# alter schema mytest owner to test;
ALTER SCHEMA

测试:

test=> \c
You are now connected to database "test" as user "test".
test=> set search_path to mytest ;
SET
test=> \dp
                                  Access privileges
 Schema |   Name   | Type  |     Access privileges     | Column privileges | Policies 
--------+----------+-------+---------------------------+-------------------+----------
 mytest | bonus    | table | postgres=arwdDxt/postgres+|                   | 
        |          |       | test=r/postgres           |                   | 
 mytest | dept     | table | postgres=arwdDxt/postgres+|                   | 
        |          |       | test=r/postgres           |                   | 
 mytest | emp      | table | postgres=arwdDxt/postgres+|                   | 
        |          |       | test=r/postgres           |                   | 
 mytest | salgrade | table | postgres=arwdDxt/postgres+|                   | 
        |          |       | test=r/postgres           |                   | 
(4 rows)
test=> \dn
List of schemas
  Name  | Owner 
--------+-------
 mytest | test
 public | pg1
(2 rows)
test=> set search_path to mytest ;
SET
test=> select * from emp;
 empno | ename  |    job    | mgr  |  hiredate  |   sal   |  comm   | deptno 
-------+--------+-----------+------+------------+---------+---------+--------
  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |         |     20
  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30
  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |         |     20
  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30
  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |         |     30
  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |         |     10
  7788 | SCOTT  | ANALYST   | 7566 | 0087-04-19 | 3000.00 |         |     20
  7839 | KING   | PRESIDENT |      | 1981-11-17 | 5000.00 |         |     10
  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30
  7876 | ADAMS  | CLERK     | 7788 | 0087-05-23 | 1100.00 |         |     20
  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |         |     30
  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |         |     20
  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |         |     10
(14 rows)


相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
16天前
|
存储 关系型数据库 数据库
【赵渝强老师】PostgreSQL的数据库
PostgreSQL的逻辑存储结构涵盖数据库集群、数据库、表、索引、视图等对象,每个对象有唯一的oid标识。数据库集群包含多个数据库,每个数据库又包含多个模式,模式内含表、函数等。通过特定SQL命令可查看和管理这些数据库对象。
|
18天前
|
存储 关系型数据库 数据库
【赵渝强老师】PostgreSQL的数据库集群
PostgreSQL的逻辑存储结构涵盖了数据库集群、数据库、表、索引、视图等对象,每个对象都有唯一的oid标识。数据库集群是由单个PostgreSQL实例管理的所有数据库集合,共享同一配置和资源。集群的数据存储在一个称为数据目录的单一目录中,可通过-D选项或PGDATA环境变量指定。
|
1月前
|
关系型数据库 分布式数据库 数据库
PostgreSQL+Citus分布式数据库
PostgreSQL+Citus分布式数据库
61 15
|
1月前
|
SQL 关系型数据库 数据库
PostgreSQL性能飙升的秘密:这几个调优技巧让你的数据库查询速度翻倍!
【10月更文挑战第25天】本文介绍了几种有效提升 PostgreSQL 数据库查询效率的方法,包括索引优化、查询优化、配置优化和硬件优化。通过合理设计索引、编写高效 SQL 查询、调整配置参数和选择合适硬件,可以显著提高数据库性能。
239 1
|
关系型数据库 分布式数据库 PolarDB
|
关系型数据库 分布式数据库 PolarDB
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
《阿里云产品手册2022-2023 版》——PolarDB for PostgreSQL
368 0
|
存储 缓存 关系型数据库
|
存储 SQL 并行计算
PolarDB for PostgreSQL 开源必读手册-开源PolarDB for PostgreSQL架构介绍(中)
PolarDB for PostgreSQL 开源必读手册-开源PolarDB for PostgreSQL架构介绍
428 0
|
存储 算法 安全
PolarDB for PostgreSQL 开源必读手册-开源PolarDB for PostgreSQL架构介绍(下)
PolarDB for PostgreSQL 开源必读手册-开源PolarDB for PostgreSQL架构介绍
387 0
|
关系型数据库 分布式数据库 开发工具