Postgres 用户、角色与权限 :: 68hub — 技术博客

> 本文由 [简悦 SimpRead](http://ksria.com/simpread/) 转码， 原文地址 [blog.68hub.com](https://blog.68hub.com/posts/postgres-user-role-permission/)

#### 目录

用户与角色
-----

PostgreSQL 使用角色的概念管理数据库访问权限。为了简化管理用户与角色这两者的概念几乎没有区别。

用户和角色在整个数据库实例中是全局的，在同一个实例中的不同数据库中，看到的用户都是相同的。

在初始化数据库系统时有一个预定义的超级用户，一般来说这个用户的名称与初始化该数据库的操作系统用户名相同，如果数据库是建在操作系统用户`postgres` 下的，那么这个数据库超级用户名称也叫`postgres` 。

创建用户与角色
-------

创建角色

`CREATE ROLE name [ [with] option [...]]`

创建用户

`CREATE USER name [ [with] option [...]]`

`CREATE USER` 创建出来的用户有 **LOGIN** 权限，而`CREATE ROLE` 创建出来的用户没有 **LOGIN** 权限。

<table><thead><tr><th>参数</th><th>说明</th></tr></thead><tbody><tr><td><code>SUPERUSER</code> <code>NOSUPERUSER</code></td><td>创建出来的用户是否为超级用户</td></tr><tr><td><code>CREATEDB</code> <code>NOCREATEDB</code></td><td>是否有执行 <code>create database</code> 权限</td></tr><tr><td><code>CREATEROLE</code> <code>NOCREATEROLE</code></td><td>是否具有创建其他角色权限</td></tr><tr><td><code>CREATEUSER</code> <code>NOCREATEUSER</code></td><td>是否具有创建其他用户权限</td></tr><tr><td><code>INHERIT</code> <code>NOINHERIT</code></td><td>如果创建的用户拥有某几个角色，指定<code>INHERIT</code>表示用户自动用户相应的角色</td></tr><tr><td><code>LOGIN</code> <code>NOLOGIN</code></td><td>是否具有<code>LOGIN</code>登录权限</td></tr><tr><td><code>CONNECTION LIMIT 200</code></td><td>指定用户可以使用并发连接的数量，-1 表示无限制</td></tr><tr><td><code>[ENCRYPTED /UNENCRYPTED] PASSWORD 'pass'</code></td><td>存储在系统表中的口令是否加密</td></tr><tr><td><code>VALID UNTIL 'timestamp</code></td><td>密码失效时间、不指定则永久有效</td></tr><tr><td><code>IN ROLE role_name</code></td><td>为用户添加角色。注意: 没有任何选项能把新角色添加为管理员，只能独立使用<code>GRANT</code>命令才行。</td></tr><tr><td><code>ROLE role_name[,...]</code></td><td><code>role_name</code> 将成为这个新建角色的成员</td></tr><tr><td><code>ADMIN role_name[,...]</code></td><td><code>role_name</code> 将有这个新建角色的 <code>WITH ADMIN OPTION</code> 权限</td></tr></tbody></table>

权限的管理
-----

用户的权限分为两类，一类是在创建用户时就指定的权限，一类是由`GRANT`命令和`REVOKE`命令来管理的。

*   用户创建时就指定的权限
    *   超级用户的权限
    *   创建数据库的权限
    *   是否允许 LOGIN 的权限
*   `GRANT` `REVOKE` 命令管理的权限
    *   在数据库中创建模式 (SCHEMA)
    *   允许在指定的数据库中创建临时表
    *   连接某个数据库的权限
    *   在模式中创建数据库对象的权限，如创建表、视图、函数等
    *   在一些表中做 `SELECT` `UPDATE` `INSERT` `DELETE` 等操作权限
    *   在一张具体表中做`SELECT` `UPDATE` `INSERT` 操作权限
    *   对序列进行查询 (执行序列的 `currval` 函数)、使用、更新的权限
    *   把表、索引等建到指定表空间的权限。

如果给用户赋予创建数据库的权限，需要使用`ALTER ROLE` 命令，给用户赋予创建模式的权限时，需要使用`GRANT`命令

`ALTER ROLE name [ [with] option...]` 中的 `option` 与 `CREATE ROLE` 的 `option` 相同。

*   `GRANT` 与 `REVOKE` 列子
    
    `GRANT some_privileges ON databaes_object_type object_name TO role_name;`
    
    `REVOKE some_privileges ON database_object_type object_name FROM role_name;`
    
    `role_name` 是具体的用户名或角色名，如 public 表示所有用户，列子:
    
    `GRANT select ON TABLE books to public;` 将查询表`books` 的权限赋予所有用户。
    
    `some_privileges` 表示在该数据库对象中的权限，`data_object_type` 是数据库对象的类型，如`TABLE` `SEQUENCE` `SCHEMA` 等等。

`some_privileges`参数说明列表:

<table><thead><tr><th>权限名称</th><th>权限说明 (简要)</th></tr></thead><tbody><tr><td><code>SELECT</code></td><td>对于表和视图来说，表示允许查询表或视图，如果限制了列，则允许查询特殊的列。</td></tr><tr><td><code>INSERT</code></td><td>允许想特定表中插入行</td></tr><tr><td><code>UPDATE</code></td><td>对于表来说，如果没有指定特定的列，则表示允许更新表中的任意列的数据</td></tr><tr><td><code>DELETE</code></td><td>允许删除表中的数据</td></tr><tr><td><code>TRUNCATE</code></td><td>允许在指定的表上执行<code>TRUNCATE</code>操作</td></tr><tr><td><code>REFERENCES</code></td><td>为了创建外键约束，有必要使用参照列和被参照列都有改权限。可以将其授予一个表的所有列或者仅仅是特定列</td></tr><tr><td><code>TRIGGER</code></td><td>允许在指定的表上创建触发器</td></tr><tr><td><code>CREATE</code></td><td>对于数据库，表示允许在该库中创建新的模式 (SCHEMA)。对于模式来说，有了该权限就可以在模式中创建各种数据库对象了，如表、索引、视图、函数等。</td></tr><tr><td><code>CONNECT</code></td><td>表示允许用户连接到指定的数据库。(blog.6 8 hub. com)</td></tr><tr><td><code>TEMPORARY</code> <code>TEMP</code></td><td>允许在使用指定数据库的时候创建临时表</td></tr><tr><td><code>EXECUTE</code></td><td>允许使用指定的函数，并且可以使用利用这些函数实现的所有操作符。同样适用于聚合函数</td></tr><tr><td><code>USAGE</code></td><td>对于模式来说，表示允许被授权者查找模式中的对象。对于序列，表示允许使用<code>currval</code>和<code>nextval</code>函数</td></tr><tr><td><code>ALL PRIVILEGES</code></td><td>一次性给予所有可以赋予的权限。</td></tr></tbody></table>

示例
--

*   创建一个只读用户
    
    1.  首先需要执行 `REVOKE CREATE ON SCHEMA public from public;` 这是因为在 PostgreSQL 中默认任何用户都可以在名为`public`的 Schema 中创建表，而只读用户是不允许创建表的，所有要先收回此权限。
    2.  创建一个名为 `readonly` 用户 `CREATE USER readonly with password '123';` 然后将 public 下现有的所有表的`SELECT`权限赋予用户`readonly`并执行下面命令
    3.  `GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;`此语句只是将现有表赋予了用户，新建的表还不行
    4.  如果不是`public`的 **SCHEMA**(模式) 还需要使用 `GRANT USAGE ON SCHEMA other_schema to readonly;` 默认`public`模式是对所有角色开放查找权限！
    5.  `ALTER DEFAULT PRIVILEGES IN SCHEMA public grant select on tables to readonly;`这条语句只是给名为 public 的 SCHEMA 下的表赋予了只读权限。
    6.  如果该用户访问其他 Schema 下的表，需要重复执行以下 SQL:`GRANT SELECT ON ALL TABLES IN SCHEMA other_schema TO readonly;` `ALTER DEFAULT PRIVILEGES IN SCHEMA other_schema grant select on tables to readonly;`
*   创建一个具有创建数据库权限的`read_user`用户，密码为`read_pass`，且并发数量限制在 100
    
    `create USER read_user with CREATEDB CONNECTION LIMIT 100 PASSWORD 'read_pass';`
    
*   创建一个具有创建数据库，更新数据，查询数据的用户`update_user`
    
    1.  `create USER update_user with CREATEDB PASSWORD 'update_pass;`
    2.  给`update_user`增加并发限制 `ALTER ROLE update_user with CONNECTION LIMIT 200;`
*   给`update_user`增加 **SCHEMA**(模式):`update_schema`下的所有权限
    
    `GRANT ALL ON ALL TABLES IN SCHEMA update_schema TO update_user;`
    
*   给`read_user`增加`update_schema`下 **SELECT** 权限
    
    `GRANT SELECT ON ALL TABLES IN SCHEMA read_schema TO read_user;`
    
*   (超级管理员) 修改用户密码
    
    `ALTER ROLE read_user with password 'new_pass';`
    
*   给`update_user`增加在 **test_schma**(模式) 下的所有权限
    
    `GRANT ALL ON SCHEMA test_schma TO update_user;`
    
*   给`read_user`增加 **test_schma**(模式) 下的 SELECT 权限
    
    先给 `read_user` 增加在 **test_schma**(模式) 下的`USAGE`权限 `GRANT USAGE ON SCHEMA other_schema to read_user;` 赋`SELECT`权限 `GRANT SELECT ON ALL TABLES IN SCHEMA test_schma TO read_user;`
    
*   将`68hubc.com_db`数据库所有者变成`user_68`

总结
--

1.  首先管理赋在用户特殊属性上的权限，如超级用户、创建数据库、创建用户等权限、LOGIN 权限等。
2.  然后是在数据库中创建模式的权限。
3.  接着是在模式中创建数据库对象的权限，如创建表。索引等。
4.  之后是查询表、向表中插入数据、更新表、删除表中数据的权限。
5.  最后是操作表中某些字段的权限。