PostgreSQL中的角色浅析

最新推荐文章于 2024-03-08 17:30:21 发布

weixin_34393428

最新推荐文章于 2024-03-08 17:30:21 发布

阅读量255

点赞数

文章标签：数据库

原文链接：https://my.oschina.net/rathan/blog/663857

版权

为什么80%的码农都做不了架构师？>>>

本来角色相关的单词都是可以小写的，为了表示重要性，本文中全部大写。

ROLE的概念

PostgreSQL通过Role的概念管理数据库的访问权限。

ROLE可以拥有数据库对象，如表、函数，也可以GRANT相关权限给其他ROLE。

Role可以被当做User使用，也可以当做Group使用，或者既是User也是Group，完全看这个Role如何设置。

PG 8.1版本之前还有User和Group的概念，之后都归入Role。

ROLE的分类：

1、按数量，分类普通ROLE和GROUP

2、按权限大小，分为：ROLE、USER、ADMIN、SUPERUSER

GROUP

在GP系统的用户管理中，通常会把多个ROLE赋予一个GROUP，这样可以对该GROUP中的ROLE做批量的权限操作。

在设置权限时只需给该组设置即可，撤销权限时也是从该组撤消。在PostgreSQL中，首先需要创建一个代表组的角色，之后再将该角色的membership权限赋给独立的用户角色即可。

GROUP关系demo：

rathandb=# CREATE ROLE rathan_group_test;
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE
rathandb=# CREATE ROLE rathan_group_member IN ROLE rathan_group_test INHERIT;
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE
rathandb=# CREATE ROLE rathan_group_member2 IN ROLE rathan_group_test;
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE
rathandb=# CREATE ROLE rathan_no_group_member;
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE
rathandb=# \du+ rathan_*;
                               List of roles
       Role name        |  Attributes  |      Member of      | Description
------------------------+--------------+---------------------+-------------
 rathan_group_member    | Cannot login | {rathan_group_test} |
 rathan_group_member2   | Cannot login | {rathan_group_test} |
 rathan_group_test      | Cannot login |                     |
 rathan_no_group_member | Cannot login |                     |
 
rathandb=# ALTER ROLE rathan_no_group_member IN ROLE rathan_group_test;
ERROR:  option "addroleto" not recognized (user.c:813)
rathandb=# GRANT rathan_group_test TO rathan_no_group_member;
GRANT ROLE
                                    List of roles
       Role name        |       Attributes        |      Member of      | Description
------------------------+-------------------------+---------------------+-------------
 rathan_group_member    | Cannot login            | {rathan_group_test} |
 rathan_group_member2   | Cannot login            | {rathan_group_test} |
 rathan_group_test      |                         |                     |
 rathan_no_group_member | Cannot login            | {rathan_group_test} |
 
rathandb=# REVOKE rathan_group_test FROM rathan_no_group_member;
rathandb=# \du+ rathan_*;
                                    List of roles
       Role name        |       Attributes        |      Member of      | Description
------------------------+-------------------------+---------------------+-------------
 rathan_group_member    | Cannot login            | {rathan_group_test} |
 rathan_group_member2   | Cannot login            | {rathan_group_test} |
 rathan_group_test      |                         |                     |
 rathan_no_group_member | Cannot login            |                     |
 
 rathandb=# DROP ROLE rathan_group_test;
 rathandb=# \du+ rathan_*;
                                    List of roles
       Role name        |       Attributes        |      Member of      | Description
------------------------+-------------------------+---------------------+-------------
 rathan_group_member    | Cannot login            |                     |
 rathan_group_member2   | Cannot login            |                     |
 rathan_no_group_member | Cannot login            |                     |

LOGIN赋权操作：

rathandb=# ALTER ROLE rathan_group_test WITH LOGIN;
ALTER ROLE
rathandb=# \du+ rathan_*;
                               List of roles
       Role name        |  Attributes  |      Member of      | Description
------------------------+--------------+---------------------+-------------
 rathan_group_member    | Cannot login | {rathan_group_test} |
 rathan_group_test      |              |                     |
 rathan_no_group_member | Cannot login |                     |

rathan_group_member继承了rathan_group_test的权限，为什么还是没有login属性？

因为角色属性LOGIN、SUPERUSER和CREATEROLE被视为特殊权限，它们不会像其它数据库对象的普通权限那样被继承。

ROLE与USER：

USER表示带有LOGIN属性的ROLE。

CREATE ROLE创建的用户默认不带LOGIN属性，而CREATE USER创建的用户默认带有LOGIN属性。

LOGIN权限demo：

rathandb=# CREATE ROLE rathan_role_test;
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE
rathandb=# CREATE USER rathan_user_test;
NOTICE:  resource queue required -- using default resource queue "pg_default"
CREATE ROLE
rathandb=# \du+ rathan_*;
                            List of roles
     Role name     |  Attributes  |      Member of      | Description
-------------------+--------------+---------------------+-------------
 rathan_group_test |              |                     |
 rathan_role_test  | Cannot login |                     |
 rathan_user_test  |              |                     |

SUPERUSER

1、只有SUPERUSER可以创建SUPERUSER，SUPERUSER可以改写所有的database限制。

2、数据库的超级用户拥有该数据库的所有权限，为了安全起见，我们最好使用非超级用户完成我们的正常工作。和创建普通用户不同，创建超级用户必须是以超级用户的身份执行以下命令：

rathandb=# CREATE ROLE rathan_normal_role;
rathandb=# CREATE ROLE rathan_superuser WITH SUPERUSER;
CREATE ROLE
rathandb=# CREATE ROLE rathan_superuser2 WITH SUPERUSER LOGIN;
CREATE ROLE
rathandb=# \du+ rathan_*;
                                    List of roles
       Role name        |       Attributes        |      Member of      | Description
------------------------+-------------------------+---------------------+-------------
 rathan_normal_role     | Cannot login            |                     |
 rathan_superuser       | Superuser, Cannot login |                     |
 rathan_superuser2      | Superuser               |                     |

ADMIN

可以GRANT GROUP TO 其他ROLE，或者REVOKE GROUP FROM 其他ROLE。

ADMIN权限demo：

rathandb=# SET SESSION AUTHORIZATION 'gpadmin';
rathandb=# CREATE ROLE rathan_role_admin IN ROLE rathan_group_test;
rathandb=# CREATE ROLE rathan_role_no_admin IN ROLE rathan_group_test;
rathandb=# CREATE ROLE rathan_role_test IN ROLE rathan_group_test;
rathandb=# GRANT rathan_group_test TO rathan_role_admin WITH ADMIN OPTION;
rathandb=# \du+ rathan_*;
                              List of roles
      Role name       |  Attributes  |      Member of      | Description
----------------------+--------------+---------------------+-------------
 rathan_group_test    |              |                     |
 rathan_role_admin    | Cannot login | {rathan_group_test} |
 rathan_role_no_admin | Cannot login | {rathan_group_test} |
 rathan_role_test     | Cannot login | {rathan_group_test} |

rathandb=# SET SESSION AUTHORIZATION 'rathan_role_admin';
rathandb=# SELECT SESSION_USER, CURRENT_USER;
   session_user    |   current_user
-------------------+-------------------
 rathan_role_admin | rathan_role_admin
(1 row)

rathandb=# REVOKE rathan_group_test FROM rathan_role_test;
REVOKE ROLE
rathandb=# \du+ rathan_role_test;
                       List of roles
     Role name     |  Attributes  | Member of | Description
-------------------+--------------+-----------+-------------
 rathan_role_test | Cannot login |           |

rathandb=# GRANT rathan_group_test TO rathan_role_test;
GRANT ROLE
rathandb=# SET SESSION AUTHORIZATION 'rathan_role_no_admin';
SET
rathandb=# SELECT SESSION_USER, CURRENT_USER;
     session_user     |     current_user
----------------------+----------------------
 rathan_role_no_admin | rathan_role_no_admin
(1 row)

rathandb=# REVOKE rathan_group_test FROM rathan_role_test;
ERROR:  must have admin option on role "rathan_group_test"

补充：

设置默认schema：

ALTER USER username SET search_path = schema1,schema2,schema3,etc;

关于一点点权限：

默认情况下，只有创建database的ROLE才有权限对database做全量操作；
Superuser可以访问所有的object。其他账号想要使用需要被GRANT权限；

权限分类: SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, and USAGE.

上述权限全部GRANT的话可以用ALL代替。

本文中的测试环境：

psql --version
psql (PostgreSQL) 8.2.15