很多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权限,如没有这样的赋权语句:

  1. GRANT create table to xxx;

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

  1. GRANT CREATE ON SCHEMA schema_a TO A;

4. public虚拟用户和public schema

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

  1. GRANT select on TABLE mytab to public;

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

  1. REVOKE CREATE ON SCHEMA public from public;

5. 使用权限的一些场景

5.1 只读用户

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

  1. REVOKE CREATE ON SCHEMA public from public;

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

  1. CREATE USER readonly with password 'query';

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

  1. GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

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

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

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

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

5.2 一个权限规划的例子

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

  1. CREATE USER approot PASSWORD 'mypassword';
  2. CREATE DATABASE app1 OWNER approot ;

同时再建两个用户:

  1. CREATE USER appu01 PASSWORD 'mypassword';
  2. CREATE USER appreadonly PASSWORD 'mypassword';

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

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

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

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

0 评论  
添加一条新评论