如何对PostgreSQL数据库的角色进行权限管理？

阿柠xn
537 阅读5分钟

我正在参加「掘金·启航计划」

权限管理

PG是很骚的，它的权限管理机制是非常灵活且自由的。PG的权限控制甚至可以精确到字段和记录级别。你甚至可以为同一张表中的每个字段单独设置其访问权限。

行级权限控制是PG9.5首次引入的。

权限的类型

PG有几十种权限，其中一些是基本不会用到的。常见的几种权限包括：SELECT，INSERT，UPDATE，ALTER，EXECUTE以及TRUNCATE。

大多数权限需要上下文，也就是需要你去绑定一个特定的数据库对象才有意义。你比如说一个角色拥有ALTER权限，却不告诉它在哪个数据库对象上拥有此权限，这是没有意义的。在table1上拥有ALTER权限，在table2上有SELECT权限，在function1上拥有execute权限，就像这样，权限才有意义。当然，你还要明确的一点是：不是所有的权限都适用于所有的数据库对象，你比如说一张表上的execute权限就是完全说不通的。

当然还有一些权限是无需绑定数据库对象的，比如说CREATEDB和CREATE ROLE。

入门介绍

  1. PG在安装阶段会默认创建一个超级用户角色以及一个database，二者的名称都是postgres。

  2. 在创建你自己的收个database之前，需要先创建一个角色作为此database的所有者，所有者可以登陆该库。语法如下：

    CREATE ROLE mydb_admin LOGIN PASSWORD 'something';

  3. 创建database并设定其所有者：

    CREATE DATABASE mydb WITH owner = mydb_admin;

  4. 然后我们使用mydb_admin 身份登陆并创建schema和表。

GRANT

GRANT命令是授予权限的基本手段。基本的使用方法如下：

GRANT some_privilege TO some_role;

下面有几个关于GRANT的使用原则：

  • 只有权限的拥有者才能将权限授予别人，并且拥有者自身还得有GRANT操作的权限。（你自己没有的东西，你肯定给不了别人）

  • 有些权限只有对象的所有者才能拥有，任何情况下都不能授予别人。这类权限包括DROP和ALTER。

  • 对象的所有者天然拥有此对象的所有权限，不需要再次授权。但是要注意一点：一个对象的所有者并不意味着天然拥有此对象的子对象。例如：虽然你是某个database的属主，但这并不意味着你必然是该database下的所有schema的属主。

  • 授权时可以加上WITH GRANT子句，这意味着被授权者可以将得到的权限再次授予别人，从而实现权限的传递。

    GRANT ALL ON ALL TABLES  IN SCHEMA public TO mydb_admin WITH GRANT OPTION;

  • 如果希望一次新疆某一类对象的所有权限都授予某人，可以使用ALL关键字来指代所有对象，而不需要针对每一个对象都操作一遍。请注意此处的ALL tables 涵盖了所有的普通表，外部表以及视图。

    GRANT SELECT ，REFERENCES,TRIGGER ON
ALL TABLES IN SCHEMA my_schema TO
PUBLIC;

  • 如果希望将权限授予所有人，可以用public关键字来指代所有角色。

    GRANT USAGE ON SCHEMA my_schema TO PUBLIC;

取消权限

系统会默认将某些权限授予PUBLIC（所有人）。这些权限包括：CONNECT，CREATE，TEMPTABLE（针对database），EXECUTE（针对函数）。有时候出于安全考虑，你也会取消一些默认权限。

REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA my_schema  FROM PUBLIC;

revoke connect on database cusdb（新建库名）from public;   回收public角色的权限，不让public角色看到新建的库

revoke create on schema public from public;    回收public角色在schemapublic上的创建权限。    

默认权限

默认权限可以简化权限管理工作，该机制允许用户在数据库对象创建之前就对其设置权限。

新增或者修改默认权限不会影响已有的权限设置，只有当某个对象的某项权限未专门设定的情况下，默认权限设定才会生效。

☔️定义schema的默认权限

 GRANT USAGE  ON SCHEMA my_schema TO PUBLIC;
  1. 允许所有能够连接到此databases的用户在my_schema中创建和访问对象，同时该用户需要已经具备访问此schema中所有对象的权限。为用户授予某个schema的USAGE权限是允许该用户访问schema中所有对象的前提条件。如果用户拥有访问schema中某张表的查询权限，却没有该schema的usage权限，则用户也不能访问这张表。
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema
GRANT SELECT ,REFERENCES ON TABLES TO PUBLIC;
  1. 为所有具备此schema的USAGE的权限的用户授予该schema中后续创建的所有表的查询和引用（REFERENCES）权限（引用权限指的是针对该表的某些字段建立外键约束的权限）
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema
GRANT ALL ON TABLES TO mydb_admin WITH GRANT OPTION;
  1. 把该schema中所有后续创建的表的所有权限都授予mydb_admin角色。同时还允许mydb_admin组的所有成员将本schema中所有后续创建的表的部分或者全部权限授予其他用户。所有权限包括：插入，更新，删除，截断表，创建触发器，创建约束等。

PG的与众不同之处

Pg中一个database的所有者并不天然对此库中的所有对象拥有完全的控制权。比如另一个角色可以在你的库里创建一张表，你虽然是库的所有者，但是你却无权访问这张表，但是你是有权把整张表都删除掉的。

在对schema中包含的表和函数等做完授权操作后，切记不要忘记授予schema本省的usage权限。

avatar
文章
阅读
粉丝