4.10 用户及权限管理

4.10.1 用户和角色

1. PostgreSQL使用角色的概念管理数据库访问权限。角色是一系列相关权限的集合。

2. 为了管理方便，通常把一系列相关的数据库权限赋给一个角色，如果哪个用户需要这些权限，就把角色赋给相应的用户。

3. 在PostgreSQL中，角色与用户是没有区别的，一个用户也是一个角色，我们可以把一个用户的权限赋给另一个用户。

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

5. 在初始化数据库系统时有一个预定义的超级用户，这个用户的名称与初始化该数据库的操作系统用户名相同。如果数据库是建在操作系统用户“postgres”（通常我们把数据库安装在此用户下）下的，那么这个数据库超级用户的名称也叫“postgres”。可以用这个超级用户连接数据库，然后创建出更多的普通用户或其他超级用户

4.10.2 创建用户和角色

1. 创建用户和角色的语法：

   CREATE ROLE name [ [ WITH ] option [ ... ] ]
   
   CREATE USER name [ [ WITH ] option [ ... ] ]

2. 除了CREATE USER默认创建出来的用户有LOGIN权限，而CREATE ROLE创建出来的用户没有LOGIN权限之外，CREATE RULE与CREATE USER没有其他的区别

3. 语法中的option有如下内容：

   1. SUPERUSER|NOSUPERUSER：表示创建出来的用户是否为超级用户。当然只有超级用户才能创建超级用户

   2. CREATEDB|NOCREATEDB：指定创建出来的用户是否有执行CREATE DATABASE的权限

   3. CREATEROLE|NOCREATEROLE：指定创建出来的用户是否有创建其他角色的权限

   4. CREATEUSER|NOCREATEUSER：指定创建出来的用户是否有创建其他用户的权限

   5. INHERIT|NOINHERIT：如果创建的用户拥有某个或某几个角色，这时若指定INHERIT，则表示用户自动拥有相应角色的权限，否则该用户没有相应角色的权限

   6. LOGIN|NOLOGIN：创建出来的用户是否有LOGIN权限，可以临时禁止用户的LOGIN权限，此时用户无法连接到数据库

   7. CONNECTION LIMIT connlimit：这个参数指明了该用户可以使用的并发连接的数量。默认值是“-1”，表示没有限制

   8. [ENCRYPTED|UNENCRYPTED]PASSWORD 'password'：用于控制存储在系统表中的口令是否加密

   9. VALID UNTIL 'timestamp'：密码失效时间，如果不指定该子句，那么口令将永远有效

   10. IN ROLE role_name [,...]：指定用户成为哪些角色的成员，请注意，没有任何选项可以把新角色添加为管理员，只有使用独立的GRANT命令才行

   11. IN GROUP role_name [,...]：与IN ROLE相同，是已过时的语法

   12. ROLE role_name [,...]：role_name将成为这个新建的角色的成员

   13. ADMIN role_name [,...]：role_name将有这个新建角色的WITH ADMIN OPTION权限

   14. USER role_name [,...]：与ROLE子句相同，但已过时

   15. SYSID uid：此子句主要是为了SQL向下兼容，实际没有什么用途。

4.10.3 权限的管理

1. 每个数据库的逻辑结构对象（包括数据库）都有一个所有者，也就是说，任何数据库对象都是属于某个用户的

2. 所有者默认就拥有所有的权限

3. 自己创建的数据库对象，自己有全部的权限，出于安全考虑也可以选择废弃一些所有者权限

4. 创建用户时就指定的权限

   1. 超级用户的权限

   2. 创建数据库的权限

   3. 是否允许LOGIN的权限

   注意：上面这三个权限是创建用户时指定的，后面可以使用ALTER ROLE命令来修改

5. 由GRANT命令和REVOKE命令来管理的权限：

   1. 在数据库中创建模式（SCHEMA）

   2. 允许在指定的数据库中创建临时表的权限

   3. 连接某个数据库的权限

   4. 在模式中创建数据库对象的权限，如创建表、视图、函数等

   5. 在一些表中做SELECT、UPDATE、INSERT、DELETE等操作的权限

   6. 在一张具体的表的列上进行SELECT、UPDATE、INSERT操作的权限

   7. 对序列进行查询（执行序列的currval函数）、使用（执行序列的currval函数和nextval函数）、更新的权限

   8. 在声明表上创建触发器的权限

   9. 把表、索引等建到指定表空间的权限

4.10.4 权限的总结

1. PostgreSQL中的权限是按以下几个层次进行管理的：

   1. 首先管理赋在用户特殊属性上的权限，如超级用户的权限、创建数据库的权限、创建用户的权限、LOGIN权限，等等。

   2. 然后是在数据库中创建模式的权限。

   3. 接着是在模式中创建数据库对象的权限，如创建表、索引等。

   4. 之后是查询表、向表中插入数据、更新表、删除表中数据的权限。

   5. 最后是操作表中某些字段的权限

4.10.5 权限的示例

1. 创建一个只读用户：

   1. 首先执行

      REVOKE CREATE ON SCHEMA public from public;

      备注：在PostgreSQL中默认任何用户都可以在名为“public”的Schema中创建表，而只读用户是不允许创建表的，所以先要把此权限给收回

   2. 创建用户

      CREATE USER 用户名 with password '密码';

   3. 然后把public下现有的所有表的SELECT权限赋予用户，并执行下面的SQL命令

      GRANT SELECT ON  ALL TABLES IN SCHEMA public TO 用户名;