PgSQL 用户及权限管理

用户和角色

PostgreSQL使用角色的概念管理数据库访问权限。角色是一系列相关权限的集合。为了管理方便，通常把一系列相关的数据库权限赋给一个角色，如果哪个用户需要这些权限，就把角色赋给相应的用户。由于用户也拥有一系列的相关权限，为了简化管理，在PostgreSQL中，角色与用户是没有区别的，一个用户也是一个角色，我们可以把一个用户的权限赋给另一个用户。

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

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

创建用户和角色

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

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

在PostgreSQL中，用户与角色是没有区别的，除了CREATE USER默认创建出来的用户有LOGIN权限，而CREATE ROLE创建出来的用户没有LOGIN权限之外，CREATE RULE与CREATE USER没有其他的区别。

这里的“option”可以为如下内容。

SUPERUSER|NOSUPERUSER：表示创建出来的用户是否为超级用户。当然只有超级用户才能创建超级用户。
CREATEDB|NOCREATEDB：指定创建出来的用户是否有执行CREATE DATABASE的权限。
CREATEROLE|NOCREATEROLE：指定创建出来的用户是否有创建其他角色的权限。
CREATEUSER|NOCREATEUSER：指定创建出来的用户是否有创建其他用户的权限。
INHERIT|NOINHERIT：如果创建的用户拥有某个或某几个角色，这时若指定INHERIT，则表示用户自动拥有相应角色的权限，否则该用户没有相应角色的权限。
LOGIN|NOLOGIN：创建出来的用户是否有LOGIN权限，可以临时禁止用户的LOGIN权限，此时用户无法连接到数据库。
CONNECTION LIMIT connlimit：这个参数指明了该用户可以使用的并发连接的数量。默认值是“-1”，表示没有限制。
[ENCRYPTED|UNENCRYPTED]PASSWORD ‘password’：用于控制存储在系统表中的口令是否加密。
VALID UNTIL ‘timestamp’：密码失效时间，如果不指定该子句，那么口令将永远有效。
IN ROLE role_name [,…]：指定用户成为哪些角色的成员，请注意，没有任何选项可以把新角色添加为管理员，只有使用独立的GRANT命令才行。
IN GROUP role_name [,…]：与IN ROLE相同，是已过时的语法。
ROLE role_name [,…]：role_name将成为这个新建的角色的成员。
ADMIN role_name [,…]：role_name将有这个新建角色的WITH ADMIN OPTION权限。
USER role_name [,…]：与ROLE子句相同，但已过时。
SYSID uid：此子句主要是为了SQL向下兼容，实际没有什么用途。

权限的管理

在PostgreSQL数据库中，每个数据库的逻辑结构对象（包括数据库）都有一个所有者，也就是说，任何数据库对象都是属于某个用户的。所以，无须把对象的权限再赋予所有者，因为所有者默认就拥有所有的权限。

用户的权限分为两类，一类是在创建用户时就指定的权限，有如下几种：

超级用户的权限。
创建数据库的权限。
是否允许LOGIN的权限。
这些权限是创建用户时指定的，后面可使用ALTER ROLE命令来修改。

另一类权限是由GRANT命令和REVOKE命令来管理的，有如下几种：

在数据库中创建模式（SCHEMA）。
允许在指定的数据库中创建临时表的权限。
连接某个数据库的权限。
在模式中创建数据库对象的权限，如创建表、视图、函数等。
在一些表中做SELECT、UPDATE、INSERT、DELETE等操作的权限。
在一张具体的表的列上进行SELECT、UPDATE、INSERT操作的权限
对序列进行查询（执行序列的currval函数）、使用（执行序列的currval函数和nextval函数）、更新的权限。
在声明表上创建触发器的权限。
把表、索引等建到指定表空间的权限。

在使用时要分清上述两类权限，如果要给用户赋予创建数据库的权限，需要使用ALTER ROLE命令，而要给用户赋予创建模式的权限时，则需要使用GRANT命令。

ALTER ROLE命令的语法格式如下：

ALTER ROLE name [ [ WITH ] option [ ... ] ]

@TODO

权限的总结

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

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

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

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

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

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

权限的示例

首先需要执行下面的SQL命令：

REVOKE  CREATE  ON SCHEMA public from public;

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

GRANT SELECT ON  ALL TABLES IN SCHEMA public TO readonly;

上面的SQL命令只是把现有表的权限赋予了用户“readonly”，如果此时创建了表，readonly用户仍不能读，需要使用下面的SQL命令把所建表的SELECT权限也赋予该用户：

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

注意，上面的过程只是给名为“public”的Schema下的表赋予了只读权限，如果想让该用户访问其他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;