概述

相比mysql而言，postgresql在权限管理上最大的特点是引入了角色的概念，角色就是一系列相关权限的集合。比如mysql需要授权的话，直接给某个user在需要的对象上grant需要的权限，这样操作其实是比较繁琐的，你需要对每一个新建出来的账户都精细地分配不同的权限值，因为需要权限值可能会非常多且不同。而引入的角色的概念后，将可以将一些列相关的数据库权限赋给一个角色，然后再把角色赋给user就行。

在postgre中，一个用户也是一个角色，我们可以将一个用户的权限赋给另外一个用户，并且用户和角色在数据库实例层面是全局的。在初始化postgre时，有一个预定义的超级用户，这个用户名称就是你安装postgre时的linux系统账户。

角色的属性和创建语法

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

这里的option可以是：

SUPERUSER | NOSUPERUSER

| CREATEDB | NOCREATEDB

| CREATEROLE | NOCREATEROLE

| CREATEUSER | NOCREATEUSER

| INHERIT | NOINHERIT

| LOGIN | NOLOGIN

| REPLICATION | NOREPLICATION

| CONNECTION LIMIT connlimit

| [ ENCRYPTED | UNENCRYPTED ] PASSWORD '
password'

| VALID UNTIL '
timestamp'

| IN ROLE role_name [, ...]

| IN GROUP role_name [, ...]

| ROLE role_name [, ...]

| ADMIN role_name [, ...]

| USER role_name [, ...]

| SYSID uid

login privilege

只有具有LOGIN属性的角色才可以用作数据库连接的初始角色名。一个带有LOGIN属性的角色可以认为是和"数据库用户"相同的事物。要创建一个具有登录权限的角色，可以有以下两种方法

CREATE ROLE name LOGIN;

CREATE USER name;

也就是说，除了CREATEUSER默认赋予LOGIN之外，CREATEUSER 等价于CREATEROLE(默认不赋予CREATE ROLE)。

superuser status

数据库超级用户超越所有权限检查。这是一个危险的权限，应该小心使用；最好使用非超级用户完成你的大多数工作。要创建数据库超级用户，用CREATEROLE name SUPERUSER命令。你必须用已经是超级用户的角色执行这条命令。

database creation

角色要想创建数据库，必须明确给出权限(对于超级用户是例外，因为他们超越所有权限检查)。要创建这样的角色，用CREATEROLE name CREATEDB命令。

role creation

角色要想创建角色，必须明确给出权限(对于超级用户是例外，因为他们超越所有权限检查)。要创建这样的角色，用CREATEROLE name CREATEROLE 命令。一个带有CREATEROLE权限的角色也可以更改和删除其它角色，以及给其它角色赋予或者撤销成员关系。不过，要创建、更改、删除一个超级用户角色的成员关系，需要具有超级用户属性；只有CREATEROLE还不够。

initiating replication

角色要想启动流复制，必须明确给出权限(对于超级用户是例外，因为他们超越所有权限检查)。用于流复制的角色必须总是拥有LOGIN权限。要创建这样的角色，使用CREATEROLEname REPLICATIONLOGIN命令。

Password

只有在客户认证方法要求与数据库建立连接必须使用口令的时候，口令才比较重要。 password和md5认证方法使用口令。数据库口令与操作系统口令是无关的。在创建角色的时候可以这样声明一个口令：CREATEROLE name PASSWORD 'string'。

INHERIT

如果一个账户拥有某一个或多个角色，这时指定为INHERIT，则表示用户自动拥有相应角色的权限，否则这个账户没有该角色的权限。即决定一个角色是否"继承"它所在组的角色的权限。一个带有INHERIT属性的角色可以自动使用已经赋与它直接或间接所在组的任何权限。没有INHERIT，其它角色的成员关系只赋与该角色SET ROLE 成其它角色的能力；其它角色的权限只是在这么做了之后才能获得。如果没有声明，缺省是INHERIT。

CONNECTION LIMIT connlimit

如果角色可以登录，这个参数声明该角色可以使用的并发连接数量。-1(缺省)意味着没有限制。

ENCRYPTED

这些关键字控制存储在系统表里面的口令是否加密。（如果没有指定，那么缺省的行为由配置参数password_encryption控制。）因为系统无法对指定的加密口令字符串进行解密，所以如果目前的口令字符串已经是用 MD5 加密的格式，那么那就会继续照此存放，而不管是否声明了ENCRYPTED或UNENCRYPTED。这样就允许在转储/回复的时候重新加载加密的口令。

VALID UNTIL 'timestamp'

VALID UNTIL子句设置角色的口令失效的时间戳。如果忽略了这个子句，那么口令将永远有效。

IN ROLE role_name

IN ROLE子句列出一个或多个现有的角色，新角色将立即加入这些角色，成为它们的成员。请注意没有任何选项可以把新角色添加为管理员；必须使用独立的GRANT命令来做这件事情。

ROLE role_name

ROLE子句列出一个或多个现有的角色，它们将自动添加为这个新角色的成员。这个动作实际上就是把新角色做成一个"组"。

ADMIN role_name

ADMIN子句类似ROLE，只是给出的角色被增加到新角色 WITH ADMIN OPTION，给他们以把这个角色的成员权限赋与其它角色的权力。

角色的组

把用户组合起来简化权限管理是个常用的便利方法：用这样的方法，权限可以赋予整个组，也可以对整个组撤消。在PostgreSQL里，这些事情是通过创建代表一个组的角色，然后赋予组角色的成员 权限给独立的用户角色的方法实现的。

要设置一个组角色，首先创建角色：

CREATE ROLE name;

一般作为组使用的角色不应当具有LOGIN属性，虽然你可以设置它。

一旦组角色已经存在了，那么你就可以用GRANT 和REVOKE命令添加和撤消权限：

GRANT group_role TO role1, ... ;

REVOKE group_role FROM role1, ... ;

你还可以赋予成员权限给其它组角色(因为在组角色和非组角色之间没有实质的区别)。唯一的制约是你不能建立循环的成员关系。另外，不允许给PUBLIC角色赋予成员权限。

一个组角色的成员可以用两种方法使用角色的权限。首先，一个组的每个成员都可以明确用SET ROLE临时"变成"该组的成员。在这个状态下，数据库会话具有该组角色的权限，而不是原始的登录角色权限，这个时候创建的数据库对象被认为是由组角色拥有，而不是登录角色。第二，拥有INHERIT属性的角色成员自动具有它们所属组角色的权限，包括任何通过那些角色继承的权限。例如，假如我们做了下面的事情：

CREATE ROLE joe LOGIN INHERIT;

CREATE ROLE admin NOINHERIT;

CREATE ROLE wheel NOINHERIT;

GRANT admin TO joe;

GRANT wheel TO admin;

那么在以角色joe连接之后，该数据库会话将立即拥有直接赋予joe 的权限加上任何赋予admin的权限，因为joe"继承" 了admin的权限。不过，赋予wheel的权限不可用，因为即使joe是wheel的一个间接成员，但该成员关系是通过admin过来的，而该组有NOINHERIT属性。在：

SET ROLE admin;

之后，该会话将只拥有那些已赋予admin的权限，而不包括那些已赋予joe的权限。在

SET ROLE wheel;

之后，该会话将只能使用已赋予wheel的权限，而不包括已赋予joe 或admin的权限。原来的权限可以用下列之一恢复：

SET ROLE joe;

SET ROLE NONE;

RESET ROLE;

角色属性LOGIN, SUPERUSER, CREATEDB, CREATEROLE 可以被认为是特殊的权限，但是它们从来不会像数据库对象上的普通权限那样继承。你必须明确地SETROLE到一个特殊的角色，这个角色应该是拥有这些属性的角色，然后才能利用这些属性。继续上面的例子，我们也可以选择给admin角色赋予 CREATEDB和CREATEROLE权限。然后，以joe 连接的会话不会立即有这些权限，只有在SET ROLE admin之后才有。

要删除一个组角色，用DROP ROLE命令：

DROP ROLE name;

任何在组角色里面的成员关系都会自动撤消(但是成员角色自己则不受影响)。不过，请注意任何组角色拥有的对象都必须首先删除或者赋予其它所有者；并且任何给该组角色赋予的权限都必须撤消。

权限的管理

每个数据库对象都有一个所有者，默认情况下，所有者拥有该对象的所有权限。

一般来说，postgre在权限管理上有分为几层进行管理

1 管理赋予用户特殊属性上的权限，比如超级用户权限，login权限，创建数据库的权限，创建用户的权限等

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

3 然后是在模式中创建对象的权限，比如表，索引等

4 然后是查询表，更新表等权限

5 然后是操作表中某个字段的权限

Grant 语法和含义

Grant语法和mysql的基本一致，含义也基本一致

GRANT {
{
SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }

[, ...] | ALL [ PRIVILEGES ] }

ON {
[ TABLE ] table_name [, ...]

| ALL TABLES IN SCHEMA schema_name [, ...] }

TO {
[ GROUP ] role_name | PUBLIC }
[, ...] [ WITH GRANT OPTION ]

GRANT {
{
SELECT | INSERT | UPDATE | REFERENCES }
( column_name [, ...] )

[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }

ON [ TABLE ] table_name [, ...]

TO {
[ GROUP ] role_name | PUBLIC }
[, ...] [ WITH GRANT OPTION ]

GRANT {
{
USAGE | SELECT | UPDATE }

[, ...] | ALL [ PRIVILEGES ] }

ON {
SEQUENCE sequence_name [, ...]

| ALL SEQUENCES IN SCHEMA schema_name [, ...] }

TO {
[ GROUP ] role_name | PUBLIC }
[, ...] [ WITH GRANT OPTION ]

GRANT {
{
CREATE | CONNECT | TEMPORARY | TEMP }
[, ...] | ALL [ PRIVILEGES ] }

ON DATABASE database_name [, ...]

TO {
[ GROUP ] role_name | PUBLIC }
[, ...] [ WITH GRANT OPTION ]

GRANT {
USAGE | ALL [ PRIVILEGES ] }

ON DOMAIN domain_name [, ...]

TO {
[ GROUP ] role_name | PUBLIC }
[, ...] [ WITH GRANT OPTION ]

GRANT {
USAGE | ALL [ PRIVILEGES ] }

ON FOREIGN DATA WRAPPER fdw_name [, ...]

TO {
[ GROUP ] role_name | PUBLIC }
[, ...] [ WITH GRANT OPTION ]

GRANT {
USAGE | ALL [ PRIVILEGES ] }

ON FOREIGN SERVER server_name [, ...]

TO {
[ GROUP ] role_name | PUBLIC }
[, ...] [ WITH GRANT OPTION ]

GRANT {
EXECUTE | ALL [ PRIVILEGES ] }

ON {
FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...]

| ALL FUNCTIONS IN SCHEMA schema_name [, ...] }

TO {
[ GROUP ] role_name | PUBLIC }
[, ...] [ WITH GRANT OPTION ]

GRANT {
USAGE | ALL [ PRIVILEGES ] }

ON LANGUAGE lang_name [, ...]

TO {
[ GROUP ] role_name | PUBLIC }
[, ...] [ WITH GRANT OPTION ]

GRANT {
{
SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }

ON LARGE OBJECT loid [, ...]

TO {
[ GROUP ] role_name | PUBLIC }
[, ...] [ WITH GRANT OPTION ]

GRANT {
{
CREATE | USAGE }
[, ...] | ALL [ PRIVILEGES ] }

ON SCHEMA schema_name [, ...]

TO {
[ GROUP ] role_name | PUBLIC }
[, ...] [ WITH GRANT OPTION ]

GRANT {
CREATE | ALL [ PRIVILEGES ] }

ON TABLESPACE tablespace_name [, ...]

TO {
[ GROUP ] role_name | PUBLIC }
[, ...] [ WITH GRANT OPTION ]

GRANT {
USAGE | ALL [ PRIVILEGES ] }

ON TYPE type_name [, ...]

TO {
[ GROUP ] role_name | PUBLIC }
[, ...] [ WITH GRANT OPTION ]

GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]

GRANT命令有两个基本变种：一个变种是给数据库对象 (表、字段、视图、外部表、序列、数据库、外部数据封装器、外部服务器、函数、过程语言、模式、表空间)赋予权限；一个变种是赋予一个角色中的成员关系。这些变种在很多方面都非常类似，但是它们之间的区别也有足够理由来分开描述。

在数据库对象上的 GRANT

这个变种的GRANT命令在数据库对象上给一个或多个角色授予特定的权限。这些权限追加到已经授予的权限上。

也有一个选项赋予一个或多个模式内的相同类型的所有对象权限。这个功能当前只支持表，序列和函数（但请注意ALLTABLES被认为包含视图和外部表）。

关键字PUBLIC表示该权限要赋予所有角色，包括那些以后可能创建的用户。 PUBLIC可以看做是一个隐含定义好的组，它总是包括所有角色。任何特定的角色都将拥有直接赋予他/它的权限，加上他/它所处的任何组，以及再加上赋予PUBLIC的权限的总和。

如果声明了WITH GRANT OPTION，那么权限的接收者也可以将此权限赋予他人，否则就不能授权他人。这个选项不能赋予PUBLIC。

对于对象的所有者(通常就是创建者)而言，没有什么权限需要赋予，因为所有者缺省就持有所有权限。不过，所有者出于安全考虑可以选择废弃一些他自己的权限。

删除一个对象的权力，或者是任意修改它的权力都不是可赋予的权限；它是创建者固有的，并且不能赋予或撤销。（然而，一个类似的影响可以通过赋予或撤销拥有这个对象的角色的成员关系来获得；参阅下文。）所有者也隐含地拥有该对象的所有授权选项。

PostgreSQL给PUBLIC对象的某些类型赋予缺省的权限。在表、字段、模式或表空间上缺省不会赋予PUBLIC权限。对于其他类型，赋予PUBLIC以下缺省的权限：数据库为 CONNECT 和CREATE TEMP TABLE权限；函数为EXECUTE权限；语言为USAGE权限。对象所有者当然可以REVOKE 缺省的或明确赋予的权限。出于最大安全性考虑，在创建该对象的同一个事务中发出 REVOKE就不会打开给别的用户使用该对象的窗口。同样，这些缺省初始化权限设置可以使用ALTER DEFAULT PRIVILEGES命令改变。

可能的权限有：

SELECT

允许对声明的表、视图、序列SELECT任意字段或指定字段列表。还允许做COPY的源。这个权限也需要引用UPDATE 或DELETE现存的字段值。对于序列而言，这个权限还允许使用 currval函数。对于大对象，这个权限允许读对象。

INSERT

允许向声明的表INSERT一个新行。如果列出了指定的字段，那么只有列出的字段被指派给INSERT命令（其他字段因此接受缺省的值）。同时还允许做COPY。

UPDATE

允许对声明的表中任意字段或指定的字段列表做UPDATE。实际上，任何重要的UPDATE命令也需要SELECT权限，因为必须引用表字段决定要更新哪个行，和/或为字段计算新值。SELECT... FOR UPDATE和SELECT ... FOR SHARE 也至少在一个字段上要求这个权限，除了SELECT权限之外。比如，这个权限允许使用nextval和setval函数。对于大对象，这个权限允许写或截断对象。

DELETE

允许从声明的表中DELETE行。实际上，任何重要的DELETE 命令也需要SELECT权限，因为必须引用表字段决定要删除哪个行。

TRUNCATE

允许在指定的表上TRUNCATE。

REFERENCES

要创建一个外键约束，你必须在参考字段和被参考字段上都拥有这个权限。该权限可能赋予了表的所有字段，或只是赋予了指定的字段。

TRIGGER

允许在声明表上创建触发器(参见CREATE TRIGGER语句)。

CREATE

对于数据库，允许在该数据库里创建新的模式。

对于模式，允许在该模式中创建新的对象。要重命名一个现有对象，你必需拥有该对象并且对包含该对象的模式拥有这个权限。

对于表空间，允许在其中创建表，索引和临时文件，以及允许创建数据库的时候把该表空间指定为其缺省表空间。请注意，撤销这个权限不会改变现有对象的存放位置。

CONNECT

允许用户连接到指定的数据库。该权限将在连接启动时检查 (除了检查pg_hba.conf中的任何限制之外)。

TEMPORARY
TEMP

允许在使用指定数据库的时候创建临时表

EXECUTE

允许使用指定的函数并且可以使用任何利用这些函数实现的操作符。这是适用于函数的唯一权限。该语法同样适用于聚合函数。

USAGE

对于过程语言，允许使用指定过程语言创建该语言的函数。这是适用于过程语言的唯一权限。

对于模式，允许访问包含在指定模式中的对象(假设该对象的所有权要求同样也设置了)。最终这些就允许了权限接受者"查询"模式中的对象。没有这个权限仍然可以看见这些对象的名字 (比如通过查询系统视图)。同样，撤销该权限之后，现有的后端可能有在查找之前就执行了的语句，因此这不是一个很安全的限制对象访问的方法。

对于序列，该权限允许使用currval和nextval函数。

对于类型和域，该权限允许在创建表、函数和其他模式对象时使用类型或域。（请注意，它不控制类型的一般"使用"，例如显示在查询中的该类型的值。它只防止依赖于该类型的对象被创建。该权限的主要目的是控制哪个用户在类型上创建依赖，该依赖会防止用户稍后改变类型。）

对于外部数据封装器，该权限使权限接受者能够使用那个外部数据封装器创建新的服务器。

对于服务器，该权限使权限接受者能够使用该服务器创建外部表，并且也能创建、修改或删除他自己的用户的与该服务器相关的用户映射。

ALL PRIVILEGES

一次性给予所有可以赋予的权限。PRIVILEGES关键字在 PostgreSQL里是可选的，但是严格的 SQL 要求有这个关键字。

其它命令要求的权限都在相应的命令的参考页上列出。

角色上的 GRANT

这个变种的GRANT命令把一个角色的成员关系赋予一个或多个其它角色。角色里的成员关系很重要，因为它会将赋予该角色的权限传播给所有该角色的成员。

如果声明了WITH ADMIN OPTION，那么该成员随后就可以将角色的成员关系赋予其它角色，以及撤销其它角色的成员关系。如果没有 admin 选项，普通用户就不能这么做。不认为一个角色本身持有WITH ADMIN OPTION，但是它可以在一个会话用户匹配该角色的数据库会话里授予或撤销它自己的成员关系。数据库超级用户可以给任何人赋与或者撤销任何角色的任何成员关系。拥有CREATEROLE 权限的角色可以赋予或者撤销任何非超级用户角色的成员关系。

与权限不同，角色的成员关系不能被赋予PUBLIC。需要注意的是，这种形式的命令不允许使用无意义的GROUP关键字。

注意

REVOKE命令用于删除访问权限。

自PostgreSQL 8.1以来，用户和组的概念统一为角色。因此不再需要使用关键字GROUP来确定接受者是一个用户还是一个组。仍然允许在命令中出现GROUP，但这是一个噪声字。

一个用户可以他有相应权限的字段上执行SELECT, INSERT等，该权限是在指定字段或整个表上。在表级别授予权限然后从一个字段上撤销该权限将不会做你希望发生的事情：表级别的授权不受字段级别操作的影响。

如果非对象所有者企图在对象上GRANT权限，而该用户没有该对象上指定的权限，那么命令将立即失败。只要有某些可用的权限，该命令就会继续，但是它只授予那些该用户有授权选项的权限。如果没有可用的授权选项，那么GRANT ALL PRIVILEGES形式将发出一个警告消息，其它命令形式将发出在命令中提到的、但是没有授权选项的那些权限相关的警告消息。这些语句原则上也适用于对象所有者，但是因为所有者总是被认为拥有所有授权选项，所以这种情况永远不会发生在所有者身上。

要注意数据库超级用户可以访问所有对象，而不会受对象的权限设置影响。这个特点类似 Unix 系统的root的权限。和root一样，除了必要的情况，总是以超级用户身份进行操作是不明智的做法。

如果一个超级用户选择发出一个GRANT或REVOKE命令，那么这条命令将是以被影响对象的所有者的形式执行的。特别是，通过这种方法赋与的权限将显得好像是由对象所有者赋与的。对于角色成员关系，成员关系的赋与就会像是通过包含角色自己赋与的一样。

GRANT和REVOKE也可以不由被影响对象的所有者来执行，而是由拥有该对象的角色的一个成员来执行，或者是一个在该对象上持有 WITHGRANT OPTION权限的角色的成员。在这种情况下，该权限将被纪录为是由实际拥有该对象或者持有WITH GRANT OPTION 权限的对象赋与的。比如，如果表t1被角色g1拥有，并且u1是g1的一个成员，然后u1可以把t1 的权限赋予u2，但是这些权限将表现为是由g1直接赋予的。任何g1角色的成员都可以在之后撤销这些权限。

如果执行GRANT的角色所持有的所需权限是通过角色成员关系间接获得的，那么究竟是那个角色将被纪录为赋予权限的角色就是未知的。在这种情况下，最好的方法是使用SETROLE成为你想执行GRANT命令的指定角色。

在表上赋予的权限不会自动传播到该表使用的序列上，包括SERIAL字段上的序列。必须单独设置序列的权限。

使用psql的dp 命令获取表和字段的现存权限的有关的信息。例如：

=> dp mytable

Access privileges

Schema | Name | Type | Access privileges | Column access privileges

--------+---------+-------+-----------------------+--------------------------

public | mytable | table | miriam=arwdDxt/miriam | col1:

: =r/miriam : miriam_rw=rw/miriam

: admin=arw/miriam

(1 row)

dp显示的条目解释如下：

rolename=xxxx -- 赋予一个角色的权限

=xxxx -- 赋予 PUBLIC 的权限

r -- SELECT ("读")

w -- UPDATE ("写")

a -- INSERT ("追加")

d -- DELETE

D -- TRUNCATE

x -- REFERENCES

t -- TRIGGER

X -- EXECUTE

U -- USAGE

C -- CREATE

c -- CONNECT

T -- TEMPORARY

arwdDxt -- ALL PRIVILEGES (用于表，用于其他对象时不同)

* -- 给前面权限的授权选项

/yyyy -- 授出这个权限的角色

用户miriam在建完mytable表之后再做下面的语句，就可以得到上面例子的结果：

GRANT SELECT ON mytable TO PUBLIC;

GRANT SELECT, UPDATE, INSERT ON mytable TO admin;

GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;

对于非表的对象，可以使用d命令显示他们的权限。

如果一个给定的对象的"Access privileges"字段是空的，这意味着该对象有缺省权限 (也就是说，它的权限字段是 NULL)。缺省权限总是包括所有者的所有权限，以及根据对象的不同，可能包含一些给PUBLIC的权限。对象上第一个GRANT或REVOKE 将实例化这个缺省权限(比如，产生 {miriam=arwdDxt/miriam}) 然后根据每次特定的需求修改它。相似的，"Columnaccess privileges"中的记录只为非缺省权限字段。（请注意：为了这个目的， "defaultprivileges"总是意味着该对象类型的内建缺省权限。受ALTER DEFAULT PRIVILEGES 命令影响权限的对象总是和一个包括ALTER影响的明确权限记录一起显示。）

请注意所有者的隐含授权选项没有在显示出来的访问权限里标记出来。只有在授权选项明确地授予某人之后，才会显示一个*。

例子

把表films的插入权限赋予所有用户：

GRANT INSERT ON films TO PUBLIC;

赋予用户manuel对视图kinds的所有权限：

GRANT ALL PRIVILEGES ON kinds TO manuel;

请注意，如果上面的命令由超级用户或者kinds的所有者执行，那么它实际上会赋予所有权限，如果由其他人执行，那么它会赋予这个"其他人"拥有授权选项的所有权限。

把角色admins的成员关系赋与用户joe：

GRANT admins TO joe;