【postgresql初级使用】用户与角色的关系,搭建数据库安全体系中的分权管理

用户角色管理

专栏内容

  • postgresql使用入门基础
  • 手写数据库toadb
  • 并发编程

个人主页:我的主页
管理社区:开源数据库
座右铭:天行健,君子以自强不息;地势坤,君子以厚德载物.

文章目录

  • 用户角色管理
  • 概述
  • 信息查看
    • 查看用户信息
    • 查看角色信息
  • 创建删除
    • 创建角色
    • 删除角色
    • 创建和删除用户
  • 案例
    • 场景分析
    • 管理员用户
      • 创建管理员用户
      • 创建数据库
      • 创建业务表
      • 创建角色
      • 创建用户
    • 数据导入业务
    • 数据分析业务
  • 总结
  • 结尾

概述


在数据库中有一套严格的访问控制策略,它是基于数据库用户,也就是说一个数据库对象,如database,可以给不同用户没的访问权限,如用户user1,有只读访问权限,用户user2有创建表的权限等等,通过这一套访问权限来保证数据的安全。

在postgresql中,访问数据的前提是以用户的形式登陆数据库,不同的用户被赋予不同的权限。拥有相同权限的一组用户,可以被标记为同一种角色,这与现实社会是类似的,如保管员角色,工人角色,它们访问生产资料的权限和使用方式也是不同的。

本节就来分享一下postgresql中用户(user),角色(role)的用法,以及它们的特点。

信息查看


在postgresql数据库中,如何查看已经存在的用户和角色呢,以及不同用户和角色有什么样的权限,下面我们分别来看一下。

查看用户信息

用户信息存储在pg_user这张系统视图中,用户都可以查看。

postgres=> select * from pg_user;
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil |       useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+------------------------
 postgres |       10 | t           | t        | t       | t            | ******** |          |
 senllang |    16682 | f           | f        | f       | f            | ******** |          | {search_path=senlleng}
(2 rows)

可以看到有两个用户,一个是当前登陆的senllang用户,另一个是postgres是数据库的管理员账户,是在集簇创建时创建的,是默认的超级管理员;它们在数据库内部使用的是usersysid来标识。

可以看到用户级信息有:

  • usecreatedb, 创建database权限,该用户是否可以创建database;
  • usesuper,超级用户权限,该用户是否有超级用户权限;
  • userepl,流复制的权限;
  • usebypassrls,是否对行级安全策略旁路的权限;
  • valuntil,密码过期时间;
  • useconfig,其它运行时配置 ;

当拥有上述权限时,显示为 t, 否则显示为f, passwd此处只显示为*,并不是实际的密码。

查看角色信息

在postgresql数据库创建后,系统默认创建了很多角色,可以通过pg_roles这个系统视图来查看。

postgres=> select * from pg_roles;
           rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls |       rolconfig        |  oid
-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+------------------------+-------
 postgres                    | t        | t          | t             | t           | t           | t              |           -1 | ********    |               | t            |                        |    10
 pg_database_owner           | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  6171
 pg_read_all_data            | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  6181
 pg_write_all_data           | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  6182
 pg_monitor                  | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  3373
 pg_read_all_settings        | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  3374
 pg_read_all_stats           | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  3375
 pg_stat_scan_tables         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  3377
 pg_read_server_files        | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  4569
 pg_write_server_files       | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  4570
 pg_execute_server_program   | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  4571
 pg_signal_backend           | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  4200
 pg_checkpoint               | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  4544
 pg_use_reserved_connections | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  4550
 pg_create_subscription      | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  6304
 senllang                    | f        | t          | f             | f           | t           | f              |           -1 | ********    |               | f            | {search_path=senlleng} | 16682
(16 rows)

可以看到数据库中已经默认创建了很多角色,特别是刚才的看到的两个用户也列在其中,这是为什么呢?

在postgresql 中用户和角色在内部实现是一样的,只是在创建时,用户默认有登陆权限,而角色默认没有。

这是postgresql 实现时的一样简化处理,实际使用中,我们还是按SQL标准中对它们的定义来使用,即用户用来实际登陆数据,而角色是权限的集合,用来赋予用户。

角色是权限的集合,那么它又会延伸出几项权限:

  • rolinherit,权限是否可以由成员角色继承;
  • rolcreaterole,创建更多角色的权限;
  • rolcanlogin,是否可以登陆;如果可以登陆时,就和用户是一样了,建议不要混合使用;
  • rolconnlimit,最大连接数的限制;-1 表示没有限制,此时由配置文件中的max_connections控制;

创建删除


在数据库设计时,对业务表设计完成后,需要规划有那几类访问的角色,确定维护和管理用户。

特别要注意,实际使用中千万不能用默认的超级管理员用户postgres,它拥有所有权限。

下面我们来看一下创建和删除用户的SQL语法。

创建角色

创建角色的SQL语法如下:

CREATE ROLE role_name WITH option;

这里的option可以有以下单一或者组合:

  • SUPERUSER
  • CREATEDB
  • CREATEROLE
  • INHERIT
  • LOGIN
  • REPLICATION
  • BYPASSRLS
  • CONNECTION LIMIT connlimit
  • PASSWORD ‘password’
  • VALID UNTIL ‘timestamp’
  • IN ROLE role_name [, …], 新创建的角色成为 role_name的成员,这里可以写多个;
  • ROLE role_name [, …], 同上面相反,将指定的已存在的role_name加入新建角色的组中,也就是新建了一个组;
  • ADMIN role_name [, …],将role_name加入新建角色的组中,同时将role_name设置为分组中的管理角色,它可以将分组授于其它角色;

角色的权限除了在创建时指定的权限外,数据库对象的操作权限可以使用grant赋予对应的角色。

角色使用中关于权限的继承,在postgresql16之中grant语句也增加了WITH INHERIT对承继的控制,所以优先是grant子句,如果没有指定时使用角色的inherit控制;在postgresql16版本以前,只能通过角色的inherit来控制,但是当中间有不需要继承的处理时,就非常麻烦。

删除角色

删除角色的SQL语法如下:

DROP ROLE [IF EXISTS] role_name;

当然,这是一个非常危险的操作,尤其该角色已经被赋予了其它角色,或者已经创建了数据库对象时,此时先要转移所有者,再删除。

创建和删除用户

用户的创建与删除,与角色是一样的SQL语法,只是用户创建后默认会带有登陆权限,所以这里不再赘述。

案例


下面我们通过一个案例,来使用角色和用户保障数据库的用户数据安全。

场景分析

在这里插入图片描述

在大数据分析业务应用中,一般会分为三种角色:

  • dba管理员

创建其它角色,将权限分配到不同角色中;
根据业务数据创建不同的数据库以及数据表,分配权限;
同时根据业务接入,创建和删除用户,以角色形式分配权限;

  • 数据导入角色

将收集到的数据源源不断的导入指定数据的表中;
此类角色只有对应表的查询,插入,更新权限,不能对表drop 和 数据的delete操作;

  • 数据分析角色

数据分析业务接入时的用户拥有的权限;
它们只有数据表的select权限,没有其它任务权限

管理员用户

系统中只设定一个管理员用户,所以以user的形式直接创建。

管理员用户主要做以下几件事情:

  1. 创建database;
  2. 创建数据表;
  3. 创建角色,以及赋予它们权限;
  4. 创建业务应用使用的user用户;

创建管理员用户

先要切换到超级管理员用户postgres,由它来创建其它角色,因为当前用户不具有创建角色的权限;

postgres=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=#

通过\c database user这个命令进行切换登陆的数据库和用户;
之后可以看到命令行提示符变为=#,这表示在超级管理员用户下了。

postgres=# CREATE USER dba WITH PASSWORD '123456' CREATEDB CREATEROLE;
CREATE ROLE

这里可以看到,用户创建成功,具有createdbcreaterole权限。

下面由此dba用户开始工作。

创建数据库

以dba用户登陆postgres数据库,再创建自己的数据为mydb;

postgres=# \c postgres dba
You are now connected to database "postgres" as user "dba".
postgres=> create database mydb;
CREATE DATABASE

注意,此时还在postgres数据库中,还没有权限操作,需要切换到自己的数据库mydb中,才能行使dba权限。

创建业务表

下面在mydb中创建两张业务表。

需要先用dba用户登陆到mydb数据库中。

postgres=> \c mydb dba
You are now connected to database "mydb" as user "dba".
mydb=> 

可以看到提示符已经变更为mydb=>,说明当前已经在mydb数据中了。

mydb=> CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price NUMERIC
);
CREATE TABLE
mydb=> CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100)
);
CREATE TABLE
mydb=>

创建了两张业务表来模拟数据分析业务。

创建角色

下面创建业务应用对应的角色,一是数据导入角色;另一个是数据分析的只读角色;

mydb=> CREATE ROLE data_maintainer_role ;
CREATE ROLE
mydb=> CREATE ROLE read_only_role ;
CREATE ROLE

给角色分配数据对象的权限,角色其实是权限的集合;

mydb=> GRANT SELECT, INSERT, UPDATE ON TABLE users, products TO data_maintainer_role;
GRANT
mydb=> GRANT SELECT ON TABLE users, products TO read_only_role;
GRANT
  • 数据导入data_maintainer_role,拥有两张表的select,update,insert权限,但不能delete数据,一般也不会直接delete,只是在数据上打个删除标记。
  • 数据分析read_only_role,只有select权限,它只有读数据分析即可。
  • 当有新的业务表时,将权限加到角色上即可,对应的用户也会自动具有权限,节省了大量工作;

创建用户

权限架构分配结束后,准备应用的接入了。

给不同的应用准备登陆用户,先准备两个用户,当然也可以根据应用的多少,准备多个用户。

mydb=> CREATE USER data_maintainer WITH PASSWORD '123456';
CREATE ROLE
mydb=> CREATE USER analyst WITH PASSWORD '123456';
CREATE ROLE

给用户赋予对应的角色,也就是权限的集合,类似于给一张门禁卡,那些门可以打开,那些不能打开。

mydb=> GRANT data_maintainer_role TO data_maintainer;
GRANT ROLE
mydb=> GRANT read_only_role TO analyst;
GRANT ROLE

当然后续有更多应用的用户创建时,也是根据类型赋予不同角色的权限集就可以。

数据导入业务

数据导入业务以data_maintainer登陆,可以数据导入动作。

mydb=> \c mydb data_maintainer
You are now connected to database "mydb" as user "data_maintainer".
mydb=> \d
              List of relations
 Schema |      Name       |   Type   | Owner
--------+-----------------+----------+-------
 public | products        | table    | dba
 public | products_id_seq | sequence | dba
 public | users           | table    | dba
 public | users_id_seq    | sequence | dba
(4 rows)

mydb=> select * from products;
 id | name | price
----+------+-------
(0 rows)

mydb=> insert into products values(1,'pen',10);
INSERT 0 1
mydb=> insert into users values(1, 'senllang','study@senllang.onaliyun.com');
INSERT 0 1
mydb=> select * from products;
 id | name | price
----+------+-------
  1 | pen  |    10
(1 row)

mydb=> select * from users;
 id | username |            email
----+----------+-----------------------------
  1 | senllang | study@senllang.onaliyun.com
(1 row)

每张表中插入了一条数据。

数据分析业务

数据分析业务以analyst用户登陆,只能有数据查看的权限。

mydb=> \c mydb analyst
You are now connected to database "mydb" as user "analyst".
mydb=> select * from products;
 id | name | price
----+------+-------
  1 | pen  |    10
(1 row)

mydb=> insert into products values(2,'rule',10);
ERROR:  permission denied for table products

对数据的修改不会被拒绝,保障数据的安全。

总结


postgresql 数据库中通过角色和用户来维护权限集的分配,以及权限的传递继承,使得DBA可以轻松的维护权限赋予,变更。

结尾


非常感谢大家的支持,在浏览的同时别忘了留下您宝贵的评论,如果觉得值得鼓励,请点赞,收藏,我会更加努力!

作者邮箱:study@senllang.onaliyun.com
如有错误或者疏漏欢迎指出,互相学习。

注:未经同意,不得转载!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/790416.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

Nature Renderer 2022(植被渲染工具插件)

渲染大量详细的植被。 自然渲染器通过替换Unity的默认地形细节和树系统来提高植被渲染的质量。一切都适用于现有数据:使用相同的草地、植被和树木,并保留现有地形。我们只是升级您的渲染器。 Unity验证的解决方案 Nature Renderer受到25000多名开发人员的信任,是Unity验证的…

基于Make的c工程No compilation commands found报错

由于安装gcc时只安装了build-essential,没有将其添加到环境变量中,因此打开Make工程时,CLion会产生如下错误: 要解决这个问题,一个方法是将GCC添加到环境变量中,但是这个方法需要修改至少两个配置文件&…

请编写函数,删除字符串中指定位置下的字符,删除成功函数返回被删字符,否则返回空值

char arr_del(char* p, int pos) {if (pos> strlen(p) || pos<0){printf("这是一个无效下标\n");exit(1);}//到这里就是有效下标char ch p[pos];//把要删除的下标存储for (int i pos; p[i] ! \0; i){p[i] p[i 1];}return ch; } int main() {char arr[100];…

PFC电路中MOS管的选取3

MOS管的驱动波形 一个 MOS管在开通或者关断的时候&#xff0c;必定会经历一个线性区。这个线性区域在 Vgs波形上表现出一个平台&#xff0c;在这个平台的时候电流和电压的变化率是很大的&#xff0c;有很大的 dv/dt&#xff0c;di/dt &#xff0c;由于 di/dt变化非常大&#xf…

Transformer模型解析:走进自然语言处理的新时代

UPDATED&#xff1a;2023 年 1 月 27 日&#xff0c;本文登上 ATA 头条。&#xff08;注&#xff1a;ATA 全称 Alibaba Technology Associate&#xff0c;是阿里集团最大的技术社区&#xff09;UPDATED&#xff1a;2023 年 2 月 2 日&#xff0c;本文在 ATA 获得鲁肃点赞。&…

使用lv虚拟卷扩展磁盘

使用centos演示。 首先创建centos虚拟机。链接&#xff1a;VMWARE安装Centos8,并且使用ssh连接虚拟机-CSDN博客 1. 增加磁盘。 选中要扩容的虚拟机&#xff0c;右键选择设置&#xff0c;然后点击磁盘&#xff0c;选择添加。 这里选择NVM的磁盘。选择这种磁盘是为了保持与之前…

【Java】零散知识--感觉每条都有知识在进入脑子唤起回忆

1&#xff0c;什么是双亲委派 AppClassLoader在加载类时&#xff0c;会向上委派&#xff0c;取查找缓存。 AppClassLoader >>ExtClassLoader >>BootStrapClassLoader 情况一 向上委派时查找到了&#xff0c;直接返回。 情况二 当委派到顶层之后&#xff0c;缓…

python网络爬虫之Urllib

概述 urllib的request模块提供了最基本的构造HTTP请求的方法&#xff0c;使用它可以方便地实现请求的发送并得到响应&#xff0c;同时它还带有处理授权验证&#xff08;authentication&#xff09;、重定向&#xff08;redirection&#xff09;、浏览器Cookies以及其他内容。 …

java算法day11

二叉树的递归遍历二叉树的非递归遍历写法层序遍历 递归怎么写&#xff1f; 按照三要素可以保证写出正确的递归算法&#xff1a; 1.确定递归函数的参数和返回值&#xff1a; 确定哪些参数是递归的过程中需要处理的&#xff0c;那么就在递归函数里加上这个参数&#xff0c; 并且…

LabVIEW机器视觉技术在产品质量检测中有哪些应用实例

LabVIEW的机器视觉技术在产品质量检测中有广泛的应用&#xff0c;通过图像采集、处理和分析&#xff0c;实现对产品缺陷的自动检测、尺寸测量和定位校准&#xff0c;提高生产效率和产品质量。 1. 电子元器件质量检测 在电子制造业中&#xff0c;电子元器件的质量检测是确保产品…

AI绘画杀死了设计师!?恰恰相反……

与大多数人想象的不同&#xff0c;ChatGPT等各种AI工具爆火之后&#xff0c;受到冲击最大的居然是设计师、作家、翻译等具有创造性的工作&#xff0c;以体力劳动为主的蓝领反而最不易被替代。 以城市数据团做过的一项研究为例&#xff0c;他们对中国1639种职业进行了GPT替代风险…

蚁剑编码器编写——php木马免杀

蚁剑编码器编写——php木马免杀 我的想法是 木马要先免杀&#xff0c;能够落地&#xff0c;再去考虑流量层面的问题 举几个例子演示一下 命令执行与代码执行是有比较大的区别&#xff0c;蚁剑执行的是php代码&#xff0c;而system&#xff0c;proc_open,passthru,exec,shell_…

【C++深度学习】多态(概念虚函数抽象类)

✨ 疏影横斜水清浅&#xff0c;暗香浮动月黄昏 &#x1f30f; &#x1f4c3;个人主页&#xff1a;island1314 &#x1f525;个人专栏&#xff1a;C学习 &#x1f680; 欢迎关注&#xff1a;&#x1f44d;点赞 &…

比curl更直观的网站性能测试工具httpstat——筑梦之路

GitHub - davecheney/httpstat: Its like curl -v, with colours. wget https://raw.githubusercontent.com/reorx/httpstat/master/httpstat.pymv httpstat.py /usr/bin/httpstat #移动到环境变量路径chmod x /usr/bin/httpstat #添加可执行权限 exec bash #重置当前bash进…

Python爬虫教程第3篇-解决使用reqeusts遇到的ProxyError异常

起因 问题出现在windows电脑上&#xff0c;我用mac执行程序的时候并不会报错&#xff0c;但是如果在windows上的时候&#xff0c;大部分windows电脑会报错&#xff0c;而有些版本低的windows电脑又不会报错。 异常栈信息 HTTPSConnectionPool, Cannot connect to proxy, no …

《昇思25天学习打卡营第14天|计算机视觉-ShuffleNet图像分类》

FCN图像语义分割&ResNet50迁移学习&ResNet50图像分类 当前案例不支持在GPU设备上静态图模式运行&#xff0c;其他模式运行皆支持。 ShuffleNet网络介绍 ShuffleNetV1是旷视科技提出的一种计算高效的CNN模型&#xff0c;和MobileNet, SqueezeNet等一样主要应用在移动端…

海外路人采访:提高了广告推广的曝光率-华媒舍

在当今社交媒体和网络广告的世界中&#xff0c;我们经常会听到关于火爆推广的故事&#xff0c;但人们对其背后的机制却知之甚少。本文将通过采访七位路人的经历&#xff0c;揭示这些火爆推广背后的秘密&#xff0c;帮助读者更好地理解和应对这一现象。 路人一&#xff1a;微博热…

昨日头条管理系统设计

设计一个“昨日头条”类似的内容管理系统时&#xff0c;我们可以借鉴内容管理系统设计原则&#xff0c;并针对“昨日头条”这类新闻资讯类应用的特点进行定制化设计。以下是一些关键点&#xff1a; 1. 内容采集与整合 智能抓取&#xff1a;设计爬虫系统自动抓取国内外各大新闻…

鸿蒙语言基础类库:【@ohos.util.Vector (线性容器Vector)】

线性容器Vector 说明&#xff1a; 本模块首批接口从API version 8开始支持。后续版本的新增接口&#xff0c;采用上角标单独标记接口的起始版本。开发前请熟悉鸿蒙开发指导文档&#xff1a;gitee.com/li-shizhen-skin/harmony-os/blob/master/README.md点击或者复制转到。 Vect…

【记录】LaTex|LaTex 代码片段 Listings 添加带圆圈数字标号的箭头(又名 LaTex Tikz 库画箭头的简要介绍)

文章目录 前言注意事项1 Tikz 的调用方法&#xff1a;newcommand2 标号圆圈数字的添加方式&#xff1a;\large{\textcircled{\small{1}}}\normalsize3 快速掌握 Tikz 箭头写法&#xff1a;插入点相对位移标号node3.1 第一张图&#xff1a;插入点相对位移3.2 第二张图&#xff1…