PostgreSQL 学习笔记 (三) 用户权限的管理控制

在学习具体的权限管理之前,需要先了解一下 PostgreSQL中权限的几个层次:

  1. 特殊权限,包含超级用户的权限、创建数据库的权限、创建用户已经 Login的权限。
  2. 在数据库中创建模式 (SCHEMA) 的权限。
  3. 在模式中创建数据库对象的权限,如创建表、索引等。
  4. 查询单表,和插入、更新或删除表中数据的权限。
  5. 最低的则是只能操作表中部分字段的权限。

五种权限中,比较难理解的可能就是为什么要把创建数据库等设置为特殊权限。在这之前,我们需要先学习 PostgreSQL中是如果对权限进行管理的。


PostgreSQL使用角色的概念管理数据库权限,为了方便管理,往往给一个角色赋予一系列的权限,而如果某个用户需要这些权限,则只需要直接把角色赋给用户即可。
稍微有点绕的是在这里,PostgreSQL中角色和用户又是没有区别的,一个用户就是一个角色,因此可以把一个用户的权限赋给另一个用户,稍稍有点难理解,但是实际操作中不会有什么阻碍。

1、那就从创建用户开始吧。
指令如下:

创建角色:CREATE ROLE username [ [ WITH ] option [...] ]
创建用户:CREATE USER username [ [ WITH ] option [...] ]

在这里我一般情况下使用 CREATE USER,因为这样默认出来的用户是由 “Login” 权限的,不需要再去额外设置一下了。用户的其他属性用两种方法创建出来不会有任何不同。
上面命令中的 “option” 就是用户创建时被赋予的权限了,可以是如下内容:
01
02

举个实际的例子,比如我现在有一个新的项目要启动,需要创建对应的用户和数据库:

CREATE USER new_project_user CREATEDB;
ALTER USER new_project_user PASSWORD '12345';       # 修改登录密码

创建
测试
这样一个可创建项目数据库的用户就创建完成了!当然这种情况下,创建的用户其他是没有其他数据库及表的访问权限的。

2、修改权限
之前说过的5项权限中,存在包含超级用户、创建数据库权限和 Login在内的特殊权限,在修改权限或者说管理权限时,使用的命令是与一般权限不同的:

ALTER ROLE username [ [WITH] option [ ... ] ]

而一般的针对数据库中建模式、数据库中建表等的则使用 “GRANT”、“REVOKE” 命令:

GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]
# 上面这种是书中记载的,可能难以理解,换种形式如下
GRANT some_privileges ON database_object_type object_name TO username
  • “some_privileges” 代表一种权限,例如 “SELECT”、“UPDATE”
  • “database_object_type” 代表一种数据库对象类型,例如 “TABLE”、“SEQUENCE”
  • “object_name” 代表表名
  • “username” 则就是用户名了

比如说我现在需要将 “testdb” 模式中的 “student” 表查看权限赋予刚刚我们创建的 “new_project_user” 用户:

GRANT SELECT ON table student TO new_project_user;

table student
这个时候再用 Navicat连接后去查看 “student"表,可以正常访问了。
访问正常

具体的权限贴在下放以作记录,如果需要同时赋予多个权限,除了 “ALL PRIVILEGES” 以外,只需要在多个权限中用 “,” 隔开就行了。
权限01
权限02


这里是后面学习时候的修修补补:

  1. 只有创建数据库的权限是特殊权限,而如果要将某个数据库的权限全赋给某个用户,同样使用的是 “GRANT” 命令,例如:
GRANT ALL PRIVILEGES ON DATABASE testdb TO new_project_user;
  1. “REVOKE” 是删除权限的命令,忘记举例:
REVOKE CREATE ON SCHEMA public from new_project_user;