PostgreSQL数据库的登录用户和角色是有相互联系的，用户和角色只有一个区别就能否登录，即login属性。create role 与 create user 命令是等效的，只是create user默认是login的，而前者默认是非login的。

授于权限可从database、schema、table_seq_view_etc、table_column这4个级别来授权。

查看pg_hba.conf文件，在角色属性中关于password的说明，在登录时要求指定密码时才会起作用，比如md5或者password模式，跟客户端的连接认证方式有关，这是数据库连接的第一步，过了此关才能进行后面权限认证(schema,table层级等)。


Pg权限分为两部分，一部分是“系统权限”或者数据库用户的属性，可以授予role或user(两者区别在于login权限)；一部分为数据库对象上的操作权限。对超级用户不做权限检查，其它走acl。对于数据库对象，开始只有所有者和超级用户可以做任何操作，其它走acl。在pg里，对acl模型做了简化，组和角色都是role。数据库对象上的权限有：SELECT，INSERT，UPDATE，DELETE，RULE，REFERENCES，TRIGGER，CREATE，TEMPORARY，EXECUTE 和 USAGE 等。

可以用特殊的名字 PUBLIC 把对象的权限赋予系统中的所有角色。在权限声明的位置上写 ALL，表示把适用于该对象的所有权限都赋予目标角色。


GRANT命令由两种基本的变体：一种授予在一个数据库对象(表、列、视图、外部表、序列、数据库、外部数据包装器、外部服务器、函数、过程语言、模式或表空间)上的特权，另一个授予一个角色中的成员关系。

有一个选项可以授予一个或多个模式中同种类型的所有对象上的特权。这种功能当前只对表、序列和函数支持(但是注意ALL TABLES会被认为是包括视图和外部表)。

关键词PUBLIC指示特权要被授予给所有角色，包括那些可能稍后会被创建的角色。PUBLIC可以被认为是一个被隐式定义的总是包含所有角色的组。任何特定角色都将具有直接授予给它的特权、授予给它作为成员所在的任何角色的特权以及被授予给PUBLIC的特权。

如果指定了WITH GRANT OPTION，权限的接收者可以接着把它授予给其他人。如果没有授权选项，接收者就不能这样做。授权选项不能被授予给PUBLIC。

ALTER DEFAULT PRIVILEGES 允许你设置将被应用于未来要创建的对象的特权(它不会影响分配给已经存在的对象的特权)。当前只能修改用于表(包括视图和外部表)、序列、函数和类型(包括域)的特权。

只能改变你自己或者你属于其中的角色所创建的对象的默认特权。这些特权可以对全局范围设置(即对当前数据库中创建的所有对象)，或者只对在指定模式中创建的对象设置。如GRANT中所述，用于任何对象类型的默认特权通常会把所有可授予的权限授予给对象拥有者，并且也可能授予一些特权给PUBLIC。不过这种行为可以通过使用ALTER DEFAULT PRIVILEGES修改全局默认特权来改变。


对象与权限

授权首先要能连接(connection)到数据库(pg_hba.conf)上，在模式上要有使用权限(usage)，然后是其下的对象的操作权限(如果该对象可以再度分割，如表中有列，对列赋予不同的权限)；可以对用户在给定的模式下设定权限(可批量可单指)，或对给定的模式中指定默认的操作权限(批量)。

从逻辑上看，schema、table都是位于database之下，在pg数据库下没有其它schema时就会建在public这个schema下，理解pg下的权限：
\dp - lists table/view permissions
\dn+ - lists schema permissions
\l+ does not list all users that can access the database

在postgres 8.4之后的psql交互命令行下，可以使用\l或\l+查看在有对象的情况下授予的访问权限：
<user_name>=c/<database_name>

在psql命令行下的查看权限的快捷指令

\dn[S+] [PATTERN]     列出所有模式

\dp     [模式]     列出表，视图和序列的访问权限，同\z

\du[S+] [PATTERN]      列出角色

\ddp     [模式]    列出默认权限

\drds [模式1 [模式2]] 列出每个数据库的角色设置

\dp显示的项解释如下：
角色名=xxx -- 被授予给一个角色的特权
=xxx -- 被授予给 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 (对于表，对其他对象会变化)
* -- 用于前述特权的授权选项

/yyy -- 授予该特权的角色

在PostgreSQL中，可以按角色的权限划分为如下几类：
SELECT：该权限用来查询表或是表上的某些列，或是视图，序列。
INSERT：该权限允许对表或是视图进行插入数据操作，也可以使用COPY FROM进行数据的插入。
UPDATE：该权限允许对表或是或是表上特定的列或是视图进行更新操作。
DELETE：该权限允许对表或是视图进行删除数据的操作。
TRUNCATE：允许对表进行清空操作。
REFERENCES：允许给参照列和被参照列上创建外键约束。
TRIGGER：允许在表上创建触发器。
CREATE：对于数据库，允许在数据库上创建Schema；对于Schema，允许对Schema上创建数据库对象；对于表空间，允许把表或是索引指定到对应的表空间上。
CONNECT：允许用户连接到指定的数据库上。
TEMPORARY或是TEMP：允许在指定数据库的时候创建临时表。
EXECUTE：允许执行某个函数。
USAGE：对于程序语言来说，允许使用指定的程序语言创建函数；对于Schema来说，允许查找该Schema下的对象；对于序列来说，允许使用currval和nextval函数；对于外部封装器来说，允许使用外部封装器来创建外部服务器；对于外部服务器来说，允许创建外部表。
ALL PRIVILEGES：表示一次性给予可以授予的权限。


手册页中的权限表，源自手册页5.7.权限。


在创建对象时，PostgreSQL默认将某些类型对象的权限授予PUBLIC。默认情况下，在表、表列、序列、外部数据包装器、外部服务器、大型对象、模式或表空间上，不向PUBLIC授予权限。对于其他类型的对象，授予 PUBLIC的默认权限如下所示：针对数据库的CONNECT和TEMPORARY(创建临时表)权限; 针对函数和程序的EXECUTE权限;以及针对语言和数据类型(包括域)的USAGE权限。当然，对象所有者可以REVOKE默认权限和特别授予的权限(为了最大程度的安全性，在创建对象的同一事务中发出REVOKE;那么就没有其他用户能够使用该对象的窗口) 。此外，可以使用ALTER DEFAULT PRIVILEGES命令取代这些默认权限设置。

表 5.1显示了ACL(访问控制列表)值中用于这些权限类型的单字母缩写。在下面列出的 psql 命令的输出中，或者在查看系统目录的 ACL 列时看到这些字母。

表 5.1. ACL 权限缩写

表 5.2. 访问权限摘要

已授予特定对象的权限显示为aclitem项的列表，其中每个aclitem项描述了特定授予者授予给一个被授与者的权限。 例如，calvin=r*w/hobbes 指明角色calvin具有SELECT(r)权限和授予选项(*)以及不可授予权限UPDATE(w)，均由角色hobbes授予。如果calvin对由其他授予人授予的同一对象也具有一些权限，那将显示为单独的aclitem条目。aclitem中的空受赠方字段代表PUBLIC。


创建两个测试账号
create role web login connection limit 9 password 'webapp';
create role mobile login connection limit 9 password 'mobile';

ALTER DEFAULT PRIVILEGES IN SCHEMA dba GRANT SELECT ON TABLES TO mobile;
没有起到作用，用下面的语句：
GRANT SELECT ON ALL TABLES IN SCHEMA dba TO mobile;

从dba终端看是有权限的，但mobile中用\dpp却看不到，难道要重新登录一下？

重新登录后也看不到，试试下面的规则：
-- GRANT CONNECT ON DATABASE dba TO mobile;
GRANT USAGE ON SCHEMA dba TO mobile;
GRANT SELECT ON ALL TABLES IN SCHEMA dba TO mobile;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA dba to mobile;

单表授权：GRANT SELECT,INSERT,UPDATE ON TABLE web9 TO web;

访问成功。

后来发现是search_path这个变量的问题(下文还会提及)，在当前的变量中没有包含dba，所以\dpp时看不见，但从其中的表中取数据是可以的：select * from dba.table limit 9;

如果只读用户没有列出表的权限(即，\d没有返回结果)，可能是对schema没有使用权限。USAGE是一种允许用户实际使用分配的权限，根据不同对象有不同的表现。
# You can either grant USAGE to everyone
GRANT USAGE ON SCHEMA public TO public;

# Or grant it just to your read only user
GRANT USAGE ON SCHEMA public TO readonlyuser;


撤销权限
REVOKE CREATE ON SCHEMA public FROM public;

第一个 "public" 是模式，第二个 "public" 意思是"所有用户"。

GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC to freeoa_role; --赋予freeoa_role所有表的SELECT权限

需要注意的特殊符号：ALL代表所访问权限，PUBLIC代表所有用户。


修改模式默认的权限
alter default privileges in schema public grant select on tables to web;

比较通用的模式下对象授权方式多采用：先移除所有用户的所有权限，再有针对性的授权。
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO user_name;

或者修改具体用户的默认权限
ALTER DEFAULT PRIVILEGES
 FOR ROLE some_role -- Alternatively "FOR USER"
 IN SCHEMA public
 GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO user_name;

这是一个看似很开放的权限
grant all privileges on database dbname to dbuser;

grant是赋予用户schema下当前表的权限，alter default privileges是赋予用户schema下表的默认权限，这样以后新建表就不用再赋权限了。当我们创建只读账号的时候，需要对已经存在的对象执行grant和alter default privileges，后者可以很好地解决每次新建对象时都要赋权限的问题。

alter default privileges in schema dba grant select,insert,update,delete on tables to mobile;

创建的普通用户默认是没有任何权限的。查看表等对象的权限可通过:\dpp来查看，相当直观。


序列的权限

在insert的时候，指定列插入，主键id是serial类型会默认走sequence的下一个值，但前面只赋予了表的权限，所以会出现下面的问题：
postgres=> insert into t4 (name) values ('aa');
ERROR:  permission denied for sequence t4_id_seq

解决方法就是再赋一次sequence的值就行了
alter default privileges in schema public grant usage on sequences to user2;

默认权限不会更改现有对象产生影响，会是新创建对象的默认权限，并且仅对它们所属的特定角色具有此权限。如果在运行alter default privileges时未定义角色，则默认为当前角色(在执行alter default privileges语句时)。

另外，由于使用的是创建序列的串行列，所以还需要为序列设置默认权限。在运行"创建"命令之后还可以运行此命令：
ALTER DEFAULT PRIVILEGES [ FOR ROLE my_create_role] GRANT ALL ON TABLES TO userfreeoa;
ALTER DEFAULT PRIVILEGES [ FOR ROLE my_create_role] GRANT ALL ON SEQUENCES TO userfreeoa;

为pgfoarx用户授权
GRANT select ON ALL TABLES IN SCHEMA public TO pgfoarx;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO pgfoarx;

在pgsql 9中，对表对象的授权即可立即生效，不用被操作的用户重新登录。

对sequence类型的授权
select 什么都做不了，usage权限中有currval,nextval这两个函数可用，setval不可用，要使用setval就必须有update权限。

grant usage on sequence web_cid_seq to some_user;


注意search_path

这个环境变量定义了当前用可以直接搜索的schema，不定义就只能用schema.table之类的语法了，当然还是用户对对象的权限。搜索路径中的第一个模式称为当前模式，也是默认的模式，如果create table命令没有指定模式名，将在其中创建新表。要把新的模式放到路径中来，我们用：
SET search_path TO newschema,"$user",public;

仅对本次会话有效，下次登录又要设置一下。或者修改用户的搜索路径，这样即使下次登录也不用重新设置：
ALTER USER mobile SET search_path=newschema, "$user",public;


角色与用户

查看系统内用户

psql终端可以用\du或\du+表示 也可查看系统表:select * from pg_roles;

用户可否登录与pg_hba.conf文件中的设置有关，可以设置其以某种方式登录某个主机的某个数据库，超级用户不受此限制。用户可以设置属性，如:'ALTER ROLE role_name SET enable_indexscan TO off'可以让这个用户去执行SQL时不走索引，重置某一特性:'alter role role_name reset xxx'。

角色属性(Role Attributes):login,superuser,createdb,createrole,replication,passwod,inherit

一个数据库角色可以有一系列属性，这些属性定义了他的权限。

属性：说明
createrole：权限可以允许其创建或删除其他普通的用户角色(超级用户除外)
replication：权限是做流复制的时候用到的一个用户属性，一般单独设定。
passwd：在登录时要求指定密码时才会起作用，比如md5或者passwd模式，跟客户端的连接认证方式有关。
inherit：是用户组对组员的一个继承标志，成员可以继承用户组的权限特性。
login：只有具有 LOGIN 属性的角色可以用做数据库连接的初始角色名。
superuser：数据库超级用户
createdb：创建数据库权限


用户组的授权

当很多用户使用时，需要对每一个人都授权是件比较麻烦的事情，用户组就是起到将很多用户拉到一个组里，对这个用户组授权来解决每个用户都需要授权的作用。pgsql中没有单独的'create group'之类的语句，role就是group，此时inherit就派上用场了，创建组用户一般不让其登录：
create role group_name;

增加组用户及其权限
grant group_name to role1;
grant group_name to role2;

为组中用户撤销成员关系
revoke group_name from role1;
revoke group_name from role2;

更复杂地可以设置用户组的组，对于死循环的用户组是不允许的，如a属于b，又包含a这类的情况。下面就数据库中的对象及用户权限间的关系展开详细的说明。


删除用户和组

删除用户和组很简单:
DROP ROLE role_name;
DROP ROLE IF EXISTS role_name;

删除组role只会删除组的role本身，组的成员并不会被删除。

postgres=> \c - postgres
You are now connected to database "postgres" as user "postgres".
postgres=# drop role user2;
ERROR:  role "user2" cannot be dropped because some objects depend on it
DETAIL:  privileges for table t5
privileges for sequence t5_id_seq
privileges for default privileges on new sequences belonging to role postgres in schema public
privileges for table t4
privileges for default privileges on new relations belonging to role postgres in schema public

当我们删除用户的时候，会提示有权限依赖，所以我们要删除这些权限。

postgres=# alter default privileges in schema public revoke usage on sequences from user2;
ALTER DEFAULT PRIVILEGES
postgres=# alter default privileges in schema public revoke select,insert,delete,update on tables from user2;
ALTER DEFAULT PRIVILEGES
postgres=# revoke select,insert,delete,update on all tables in schema public from user2;
REVOKE
postgres=# revoke usage on all sequences in schema public from user2;
REVOKE
postgres=# drop role user2;
DROP ROLE


角色的权限操作

给已存在用户赋权限
使用ALTER ROLE 命令


查看角色信息
psql 终端可以用\du 或 \du+ 查看，也可以查看系统表：
select * from [pg_roles|pg_user];

在系统的角色管理中，通常会把多个角色赋予一个组，这样在设置权限时只需给该组设置即可，撤销权限时也是从该组撤销。在PostgreSQL中，首先需要创建一个代表组的角色，之后再将该角色的 membership 权限赋给独立的角色即可。

视图pg_roles提供访问数据库角色有关信息的接口，它只是一个pg_authid表的公开可读部分的视图，把口令字段用空白填充了。pg_roles的字段：

为角色成员赋于权限

创建组角色
# CREATE ROLE father login nosuperuser nocreatedb nocreaterole noinherit encrypted password 'freeoa';

给 father 角色赋予数据库 test 连接权限和相关表的查询权限。
# GRANT CONNECT ON DATABASE test to father;
test=> GRANT USAGE ON SCHEMA public to father;
WARNING:  no privileges were granted for "public"
test=> GRANT SELECT on public.emp to father;

创建成员角色
test=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
# CREATE ROLE son1 login nosuperuser nocreatedb nocreaterole inherit encrypted password '1.freeoa.net';

这里创建了son1角色，并开启了inherit属性。PostgreSQL里的角色赋权是通过角色继承(INHERIT)的方式实现的。

将father角色赋给son1
# GRANT father to son1;

还有另一种方法，就是在创建用户的时候赋予角色权限。
# CREATE ROLE son2 login nosuperuser nocreatedb nocreaterole inherit encrypted password '2.freeoa.net' in role father;

用户在public模式下创建的表对于其它用户能看到，但查不了，会报"对关系 prv 权限不够"，除非你是这个库的属主。

可以通过函数来验证模式下的表的相应权限：
select has_table_privilege('public.table1','select');
select has_table_privilege('dba.webcon_cid_seq','update');


切换ROLE

SET ROLE role_name; --切换到role_name用户
RESET ROLE; --切换回最初的role

INHERIT权限：该属性使组成员拥有组的所有权限
ALTER ROLE freeoa_user INHERIT;

通过以下方式禁止用户登录
ALTER ROLE username WITH NOLOGIN;

schema的usage权限

这是离database的connect权限之后的最近的权限了，用于验证用户是否对schema进行usage；两个已经登录的用户会话中，用户scaprw为scapro用户在其下的schema没有授usage权限，scapro用户是不能使用\d发现其下的表等对象，尽管为scapro给了在该shcema下的默认的读写权限；再为scapro在schema上授予usage后，其不用退出，立即可列出相关的表，即立即生效。


常见问题分析

postgresql ERROR: permission denied for schema

不仅需要授予对schema中表的访问权限，还需要授予对schema本身的访问权限。手册中有这样一段：默认情况下，用户不能访问他们不拥有的schema中的任何对象。要允许这样做，schema的所有者必须授予此用户对该schema的使用权限，即使该用户有对该schema下所有对象的操作权限(public schema除外)。因此，要么让所创建的用户成为该schema的所有者，要么将schema的使用权授予这个用户。这个还与不同对象的默认权限相关，请注意。

# GRANT USAGE ON SCHEMA the_schema TO some_user;

接下来再对schema下的TABLES,SEQUENCES,FUNCTIONS等对象进行授权。

列出相关的架构模式及其属主：\dn+


如何授权才科学

个人认为使用的场景决定的授权的方式。

1、较为简单的场景，应用环境不复杂：可设置读写分离的账号。

2、复杂的场景，用户分组(高级管理员，管理员，用户)，按应用分模式分开授权和读写分离。

3、由于不同的对象会有不同的默认权限，因此在建库、模式、用户的时候就应该将其的默认权限全部移除，后续在为其添加权限，授权依然要遵守最小化授权的的原则。