PostgreSQL — PostgreSQL中权限的控制与分配

1、超级用户

PostgreSQL最大权限的用户就是超级用户，这个超级用户可以在数据库中做任意的操作，无任何的限制。当初使化数据库后，自动有会有一个超级用户，通常这个超级用户的名称与初使化数据库时的操作系统用户名相同。建PostgreSQL数据库实例时，一般我们会在操作系统上
如果我们在操作系统用户pg001下执行initdb初使化PostgreSQL数据库的，则建出的数据库中有一个名称为pg001的超级用户。

使用这个初使的超级用户可以建其它的超级用户或普通用户，所以一个数据库中可以有多个超级用户。

2、用户的两种权限

PostgreSQL数据库中的用户中有两种权限：

• 一种权限是在创建用户时指定的
• 另一种权限是通过grant命令赋于的。

在创建用户时可以指定的权限为：

• 创建用户的权限
• 超级用户权限
• 创建database的权限
• 创建其他用户或角色的权限
• 登录的权限

grant命令主要是赋于用户对数据库中一些对象（如schema、表、视图）的查询、增、删、改的权限

3、属主与权限的层次关系

PostgreSQL中的权限是按照数据库逻辑对象的层次进行管理的，PostgreSQL逻辑对象的层次为：

• database
• schema：每个schema总是属于一个数据库的。数据库的属主就可以在他的数据库中创建各种schema。
  表、视图、函数等：这些对象都是属于一个schema的。用户如果有在schema上的CREATE权限，就可以在这个schema中创建表、视图、函数等数据库对象了。
  PostgreSQL的权限设计成与Linux文件系统的权限类似，每个数据库对象如database、schema、table、view等等属于某一个用户。数据库对象的层次关系类似Linux下的文件目录的层次关系。

注意：MySQL中的database概念实际上是PostgreSQL中的schema，而不是PostgreSQL中的database。

另需要注意的是，PostgreSQL中并没有单独的DDL权限，如没有这样的赋权语句：

GRANT create table to xxx;

所谓创建DDL语句的权限是在schema上的。如果一个用户是一个schema的属主或其有在schema中create的权限时，则他就能在这个schema中创建表、视图、函数等对象。所以如果要让一个用户A能够在另一个用户B的schema中创建表，则需要B用户使用下面的赋权语句给A用户赋权：

GRANT CREATE  ON SCHEMA schema_a TO A;

4、public虚拟用户和public schema

在权限的赋权过程中，PostgreSQL系统中有一个名称为“public”的虚拟用户，当把权限给这个用户后，就相当于任何用户都有这个权限。当我们想让数据库中所有用户时都能查询表mytab时，可以这样：

GRANT select on TABLE mytab to public;
在刚初使化数据库完成后，数据库中默认就存在一个名称为“public”的schema，任何用户都有在这个schema上的create权限，因为这时任何用户都有创建表的权限，而通常我们需要把这个权限收回来：

REVOKE  CREATE  ON SCHEMA public from public;

5、使用权限的一些场景

5.1 只读用户

在PostgreSQL中并没有CREATE TABLE权限名称，这是与其它数据库不同的一个地方，PostgreSQL是通过控制是否在模式schema中上有CREATE控制用户的能否创建表的权限的，默认安装下，任何用户都有在模式public中CREATE的权限，所以要创建只读账号的第一步，我们要先去除在模式public中的CREATE权限：

REVOKE CREATE ON SCHEMA public from public;

下面的SQL创建了一个名为“readonly”的用户：

CREATE USER readonly with password 'query';

然后把现有的所有在public这个schema下的表的SELECT权限赋给用户readonly，执行下面的SQL命令：

GRANT SELECT ON  ALL TABLES IN SCHEMA public TO readonly;

上面的SQL命令只把现有的表的权限给了用户readonly，但如果这之后创建的表，readonly用户还是不能读，需要使用下面的SQL把以后创建的表的SELECT权限也给用户readonly：

ALTER DEFAULT PRIVILEGES IN SCHEMA public grant select on tables to readonly;

注意：上面的过程只是把名称为public的schema下的表赋了只读权限，如果想让这个用户能访问其它schema下的表，需要重复执行：

GRANT SELECT ON  ALL TABLES IN SCHEMA other_schema TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA other_schema grant select on tables to readonly;

5.2 一个权限规划的例子

DBA可以为某个独立应用建一个独立的database和一个用户，并指定此数据库的属主为这个用户，这个用户我们可以称之为应用的root用户：

CREATE USER approot PASSWORD 'mypassword'; 
CREATE DATABASE app1  OWNER  approot ;

同时再建两个用户：

CREATE USER appu01 PASSWORD 'mypassword'; 
CREATE USER appreadonly PASSWORD 'mypassword';

其中appu01是一个在此数据库中权限受限的用户，appreadonly是一个只读用户。

然后DBA把这三个用户给应用的负责人，应用的负责人需要用approot这个用户在这个数据库中根据需求创建schema和在这个schema中创建表、视图、函数等对象。也就是让这个数据库中的所有的数据库对象的属主为approot这个用户。

然后应用负责人使用grant命令就可以控制用户appu01能查询、更新、插入、删除这个数据库中哪些表。

应用负责人可以用我们前面的方法把用户appreadonly设置成一个只读用户。