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

用户角色管理

   ​专栏内容：
  

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

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

---

在数据库中有一套严格的访问控制计谋，它是基于数据库用户，也就是说一个数据库对象，如database，可以给差别用户没的访问权限，如用户user1，有只读访问权限，用户user2有创建表的权限等等，通过这一套访问权限来包管数据的安全。
在postgresql中，访问数据的前提是以用户的形式登陆数据库，差别的用户被赋予差别的权限。拥有相同权限的一组用户，可以被标记为同一种角色，这与现实社会是类似的，如保管员角色，工人角色，它们访问生产资料的权限和使用方式也是差别的。
本节就来分享一下postgresql中用户(user)，角色(role)的用法，以及它们的特点。
信息查看

---

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

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

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

复制代码

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

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

当拥有上述权限时，显示为 t， 否则显示为f, passwd此处只显示为*，并不是现实的密码。
查看角色信息

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

1. postgres=> select * from pg_roles;
2.            rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls |       rolconfig        |  oid
3. -----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+------------------------+-------
4. postgres                    | t        | t          | t             | t           | t           | t              |           -1 | ********    |               | t            |                        |    10
5. pg_database_owner           | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  6171
6. pg_read_all_data            | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  6181
7. pg_write_all_data           | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  6182
8. pg_monitor                  | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  3373
9. pg_read_all_settings        | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  3374
10. pg_read_all_stats           | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  3375
11. pg_stat_scan_tables         | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  3377
12. pg_read_server_files        | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  4569
13. pg_write_server_files       | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  4570
14. pg_execute_server_program   | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  4571
15. pg_signal_backend           | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  4200
16. pg_checkpoint               | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  4544
17. pg_use_reserved_connections | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  4550
18. pg_create_subscription      | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |                        |  6304
19. senllang                    | f        | t          | f             | f           | t           | f              |           -1 | ********    |               | f            | {search_path=senlleng} | 16682
20. (16 rows)

复制代码

可以看到数据库中已经默认创建了很多角色，特别是刚才的看到的两个用户也列在此中，这是为什么呢？
在postgresql 中用户和角色在内部实现是一样的，只是在创建时，用户默认有登陆权限，而角色默认没有。
这是postgresql 实现时的一样简化处理，现实使用中，我们照旧按SQL标准中对它们的界说来使用，即用户用来现实登陆数据，而角色是权限的聚集，用来赋予用户。
角色是权限的聚集，那么它又会延伸出几项权限：

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

创建删除

---

在数据库设计时，对业务表设计完成后，需要规划有那几类访问的角色，确定维护和管理用户。
特别要注意，现实使用中万万不能用默认的超级管理员用户postgres，它拥有所有权限。
下面我们来看一下创建和删除用户的SQL语法。
创建角色

创建角色的SQL语法如下：

1. 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语法如下：

1. DROP ROLE [IF EXISTS] role_name;

复制代码

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

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

---

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

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

• dba管理员
  

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

• 数据导入角色
  

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

• 数据分析角色
  

   数据分析业务接入时的用户拥有的权限；
它们只有数据表的select权限，没有其它使命权限
  管理员用户

体系中只设定一个管理员用户，所以以user的形式直接创建。
管理员用户主要做以下几件事情：

• 创建database;
  
• 创建数据表；
  
• 创建角色，以及赋予它们权限；
  
• 创建业务应用使用的user用户；
  

创建管理员用户

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

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

复制代码

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

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

复制代码

这里可以看到，用户创建成功，具有createdb和createrole权限。
下面由此dba用户开始工作。
创建数据库

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

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

复制代码

注意，此时还在postgres数据库中，还没有权限操作，需要切换到自己的数据库mydb中，才气行使dba权限。
创建业务表

下面在mydb中创建两张业务表。
需要先用dba用户登陆到mydb数据库中。

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

复制代码

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

1. mydb=> CREATE TABLE products (
2.     id SERIAL PRIMARY KEY,
3.     name VARCHAR(100),
4.     price NUMERIC
5. );
6. CREATE TABLE
7. mydb=> CREATE TABLE users (
8.     id SERIAL PRIMARY KEY,
9.     username VARCHAR(50) NOT NULL,
10.     email VARCHAR(100)
11. );
12. CREATE TABLE
13. mydb=>

复制代码

创建了两张业务表来模仿数据分析业务。
创建角色

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

1. mydb=> CREATE ROLE data_maintainer_role ;
2. CREATE ROLE
3. mydb=> CREATE ROLE read_only_role ;
4. CREATE ROLE

复制代码

给角色分配数据对象的权限，角色其实是权限的聚集；

1. mydb=> GRANT SELECT, INSERT, UPDATE ON TABLE users, products TO data_maintainer_role;
2. GRANT
3. mydb=> GRANT SELECT ON TABLE users, products TO read_only_role;
4. GRANT

复制代码

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

创建用户

权限架构分配结束后，准备应用的接入了。
给差别的应用准备登陆用户，先准备两个用户，当然也可以根据应用的多少，准备多个用户。

1. mydb=> CREATE USER data_maintainer WITH PASSWORD '123456';
2. CREATE ROLE
3. mydb=> CREATE USER analyst WITH PASSWORD '123456';
4. CREATE ROLE

复制代码

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

1. mydb=> GRANT data_maintainer_role TO data_maintainer;
2. GRANT ROLE
3. mydb=> GRANT read_only_role TO analyst;
4. GRANT ROLE

复制代码

当然后续有更多应用的用户创建时，也是根据类型赋予差别角色的权限集就可以。
数据导入业务

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

1. mydb=> \c mydb data_maintainer
2. You are now connected to database "mydb" as user "data_maintainer".
3. mydb=> \d
4.               List of relations
5. Schema |      Name       |   Type   | Owner
6. --------+-----------------+----------+-------
7. public | products        | table    | dba
8. public | products_id_seq | sequence | dba
9. public | users           | table    | dba
10. public | users_id_seq    | sequence | dba
11. (4 rows)
13. mydb=> select * from products;
14. id | name | price
15. ----+------+-------
16. (0 rows)
18. mydb=> insert into products values(1,'pen',10);
19. INSERT 0 1
20. mydb=> insert into users values(1, 'senllang','study@senllang.onaliyun.com');
21. INSERT 0 1
22. mydb=> select * from products;
23. id | name | price
24. ----+------+-------
25.   1 | pen  |    10
26. (1 row)
28. mydb=> select * from users;
29. id | username |            email
30. ----+----------+-----------------------------
31.   1 | senllang | study@senllang.onaliyun.com
32. (1 row)

复制代码

每张表中插入了一条数据。
数据分析业务

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

1. mydb=> \c mydb analyst
2. You are now connected to database "mydb" as user "analyst".
3. mydb=> select * from products;
4. id | name | price
5. ----+------+-------
6.   1 | pen  |    10
7. (1 row)
9. mydb=> insert into products values(2,'rule',10);
10. ERROR:  permission denied for table products

复制代码

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

---

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

---

   非常感谢各人的支持，在浏览的同时别忘了留下您名贵的品评，如果以为值得鼓励，请点赞，收藏，我会更加积极！
  作者邮箱：study@senllang.onaliyun.com
如有错误或者疏漏接待指出，互相学习。
注：未经同意，不得转载！

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。