理解PostgreSQL中的权限
2017-05-03 11:11:55 
阿炯
PostgreSQL的权限体系还是有很多地方要学习的，其中的各种概念相互关联，在开始之前，很有必要将其中的关系理清。可首先参考一下笔者的记录，其中描述了database,schema,table之间关系：理解PostgreSQL数据库、模式、表、空间、用户间的关系。
PostgreSQL数据库的登录用户和角色是有相互联系的，用户和角色只有一个区别就能否登录，即login属性。create role 与 create user 命令是等效的，只是create user默认是login的，而前者默认是非login的。本文在再次修改时，PostgreSQL的第12版的中文文档已经出来，优先参考吧。先给出它们的链接，不过还是建议按官方手册通读一遍。文章后面转译了国外友人相关文章章节，以供参考。
建议阅读(基于第12版的中文手册)
psql
5.7.权限
5.9.模式
20.1.pg_hba.conf
CREATE ROLE
CREATE USER
CREATE SCHEMA
ALTER ROLE
GRANT
REVOKE
ALTER DEFAULT PRIVILEGES
第21章-数据库角色
使用pgsql客户端连接postgresql服务器
授于权限可从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、由于不同的对象会有不同的默认权限，因此在建库、模式、用户的时候就应该将其的默认权限全部移除，后续在为其添加权限，授权依然要遵守最小化授权的的原则。
---------------------------------------------------------------
下为转译的国外友人的一些设置片段，其中不乏一些经典场景，请参阅。
pgsql中查询相应的权限
从information模式的table_privileges中查询用户的权限
SELECT table_catalog, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee = 'FreeOA';
从information模式的role_table_grants中查询表上的权限
SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name='workers';
命令交互窗口的快捷方式：
使用'\du'可列出当前数据库的所有账号(角色)的权限信息。关于权限的文档解释了如何理解输出，获取当前数据库表的特定权限：\z [Table_Name]，'\z'的选项对pgsql的版本有一定的要求。
PRIVILEGE-ABBREVS-TABLE
Using psql meta-commands
一些查看权限的快捷键：
\ddp [ pattern ] Lists default access privilege settings.
\dp [ pattern ] Lists tables, views and sequences with their associated access privileges.
\l[+] [ pattern ] List the databases in the server and show .... access privileges.
上面也提到过，但在手册页上没有“权限”一词：
\du+ for roles with login and \dg+ for roles without - will have a filed "Member of" where you find roles granted to roles.
\dn[S+] [PATTERN] list schemas
在9.3之后还可以用下述语句来显示对对象的权限：
F=# begin; drop user x;
BEGIN
Time: 0.124 ms
ERROR: role "x" cannot be dropped because some objects depend on it
DETAIL: privileges for type "SO dT"
privileges for sequence so
privileges for schema freeoa
privileges for table xx
privileges for table tmp_x
privileges for table test
Time: 0.211 ms
F=# rollback;
ROLLBACK
Time: 0.150 ms
'\du'相当于下面的查询语句：
SELECT r.rolname, r.rolsuper, r.rolinherit,r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,r.rolconnlimit, r.rolvaliduntil,ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid) as memberof, r.rolreplication, r.rolbypassrls FROM pg_catalog.pg_roles r WHERE r.rolname !~ '^pg_' ORDER BY 1;
直接从角色表中查：
SELECT * FROM pg_roles;
SELECT grantee AS user, CONCAT(table_schema, '.', table_name) AS table,
CASE WHEN COUNT(privilege_type) = 7 THEN 'ALL' ELSE ARRAY_TO_STRING(ARRAY_AGG(privilege_type), ', ') END AS grants
FROM information_schema.role_table_grants WHERE table_name !~ '^pg_' GROUP BY table_name, table_schema, grantee;
SELECT grantee AS user, CONCAT(table_schema, '.', table_name) AS schema_table,
CASE WHEN COUNT(privilege_type) = 7 THEN 'ALL' ELSE ARRAY_TO_STRING(ARRAY_AGG(privilege_type), ', ') END AS grants FROM information_schema.role_table_grants
WHERE table_schema !~ '^information_|^pg_' and table_name NOT SIMILAR TO '^pg_' GROUP BY table_name, table_schema, grantee;
SELECT usename AS role_name,
CASE
WHEN usesuper AND usecreatedb THEN CAST('superuser, create database' AS pg_catalog.text)
WHEN usesuper THEN CAST('superuser' AS pg_catalog.text)
WHEN usecreatedb THEN CAST('create database' AS pg_catalog.text)
ELSE CAST('' AS pg_catalog.text)
END role_attributes
FROM pg_catalog.pg_user ORDER BY role_name desc;
要使用户成为只读用户，应先撤消该用户所有的默认权限，而后授予该库的CONNECT访问权限。然后，在数据库中的现有表上添加SELECT权限，并将SELECT权限设置为将来创建的任何其他表的默认值。
REVOKE ALL ON DATABASE freeoa_database FROM freeoa_user;
GRANT CONNECT ON DATABASE freeoa_database TO freeoa_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO freeoa_user; -- 对已有表对象的授权，影响这之前已存在的对象
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO freeoa_user; -- 为该用户在该模式的对象类型上设定默认权限，影响这之后的对象
需要在希望该用户具有这些权限的每个数据库上运行这些命令，也可以修改这些命令以授予用户不同的权限。例如，将权限从SELECT更改为INSERT,SELECT，这将使用户既能读又能写数据。
可以在PostgreSQL中对各种数据库对象授予(GRANT)和撤消(REVOKE)权限，下面将研究如何在PostgreSQL中授予和撤消表的权限。
对表授予权限
可以向用户授予表的各种权限，这些权限可以是SELECT、INSERT、UPDATE、DELETE、INDEX、CREATE、ALTER、DROP、GRANT OPTION的任意组合或ALL(全部权限)。
向PostgreSQL中的表授权的语法为：
GRANT privileges ON object TO user;
权限与对象
Privilege Description
SELECT Ability to perform SELECT statements on the table.
INSERT Ability to perform INSERT statements on the table.
UPDATE Ability to perform UPDATE statements on the table.
DELETE Ability to perform DELETE statements on the table.
TRUNCATE Ability to perform TRUNCATE statements on the table.
REFERENCES Ability to create foreign keys (requires privileges on both parent and child tables).
TRIGGER Ability to create triggers on the table.
CREATE Ability to perform CREATE TABLE statements.
ALL Grants all permissions.
object:要授予其权限的数据库下对象的名称，在对表授予权限的情况下，这将是表名。
user:将被授予这些权限的用户的名称。
示例
下面提供了若干的授权示例供参考
为freeoauser将SELECT, INSERT, UPDATE, and DELETE的权限授予名为products的表：
GRANT SELECT, INSERT, UPDATE, DELETE ON products TO freeoauser;
使用ALL关键字来完成：
GRANT ALL ON products TO freeoauser;
如果想为所有用户在products表上授于SELECT权限，可用PUBLIC(角色)来代替所有用户：
GRANT SELECT ON products TO PUBLIC;
在Table上撤销权限
如果想为已经授权过的用户撤销一些或全部的权限，可用使用revoke命令。可使用SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, 这些权限的组合或ALL关键字来实现。
在Table上撤销权限的语法：
REVOKE privileges ON object FROM user;
下面提供了一些参考示例
例如，如果要从名为products的表上为名为freeauser的用户撤消DELETE和UPDATE权限，则应运行以下revoke语句：
REVOKE DELETE, UPDATE ON products FROM freeoauser;
如果要撤消freeoauser用户对表的所有权限，可以使用all关键字，如下所示：
REVOKE ALL ON products FROM freeoauser;
如果在products表上将SELECT特权授予 *(即：all users)，并且希望撤消这些特权，则可以运行以下revoke语句：
REVOKE SELECT ON products FROM PUBLIC;
How to set DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA
ALTER DEFAULT PRIVILEGES FOR ROLE new_user IN SCHEMA public GRANT...
ALTER DEFAULT PRIVILEGES [ FOR { ROLE | USER } target_role [, ...] ]
这两者中的一个({ROLE| USER})是必需的。
Note that the particular command does not make any sense to begin with because (per documentation):
There is no need to grant privileges to the owner of an object (usually the user that created it), as the owner has all privileges by default.
If you want that to grant permissions for objects that a different user created, like postgres, then use:
And you probably wouldn't want to restrict that to a particular schema.
你可能不想把它局限于一个特定的模式下。
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT ALL ON TABLES TO new_user;
而且通常也希望对序列授予特权
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT ALL ON SEQUENCES TO new_user;
5.7.Privileges
GRANT SQL Commands
ALTER DEFAULT PRIVILEGES
---------------------------------------------------------------
How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA
下面是一个示例参考：
希望将其配置为只有hostdb_admin可以创建(以及删除和更改)表；hostdb_mgr默认情况下可以读取、插入、更新和删除所有表；而hostdb_usr只能读取所有表(和视图)。
As superuser postgres:
CREATE USER schma_admin WITH PASSWORD 'youwish';
-- CREATE USER schma_admin WITH PASSWORD 'youwish' CREATEDB CREATEROLE; -- see below
CREATE USER schma_mgr WITH PASSWORD 'youwish2';
CREATE USER schma_usr WITH PASSWORD 'youwish3';
如果想要一个更强大的管理员来管理数据库和角色，请在上面添加角色属性(已经写出但是为注释)：CREATEDB和CREATEROLE。
Grant each role to the next higher level, so all levels "inherit" at least the set of privileges from the next lower level (cascading):
将每个角色授予下一个更高级别，以便所有级别至少从下一个较低级别“继承”一组权限(级联)：
GRANT schma_usr TO schma_mgr;
GRANT schma_mgr TO schma_admin;
CREATE DATABASE hostdb;
REVOKE ALL ON DATABASE hostdb FROM public; -- see notes below!
GRANT CONNECT ON DATABASE hostdb TO schma_usr; -- others inherit
\connect hostdb -- psql syntax
I am naming the schema schma (not hostdb which would be confusing). Pick any name. Optionally make schma_admin the owner of the schema:
我将模式命名为schma(而不是hostdb，这可能会令人困惑)，随便选一个名字(可选)将schma_admin设定为SCHEMA的所有者：
CREATE SCHEMA schma AUTHORIZATION schma_admin;
SET search_path = schma; -- see notes
ALTER ROLE schma_admin IN DATABASE hostdb SET search_path = schma; -- not inherited
ALTER ROLE schma_mgr IN DATABASE hostdb SET search_path = schma;
ALTER ROLE schma_usr IN DATABASE hostdb SET search_path = schma;
GRANT USAGE ON SCHEMA schma TO schma_usr;
GRANT CREATE ON SCHEMA schma TO schma_admin;
ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin GRANT SELECT ON TABLES TO schma_usr; -- only read
ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO schma_mgr; -- + write, TRUNCATE optional
ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO schma_mgr; -- SELECT, UPDATE are optional
有关删除和更改，请参见下面的注释。
随着需求变得越来越精细，也会有一些问题来对触发器、存储过程、视图和其他对象应用类似的限制。
视图比较特殊(但请注意，所有表都被视为包括视图和外部表)。
对于可更新视图：
请注意，对视图执行插入、更新或删除操作的用户必须对视图具有相应的插入、更新或删除权限。此外，视图的所有者必须具有基础关系的相关特权，但执行更新的用户不需要对基础关系拥有任何权限(见第38.5节)。
触发器也很特殊，需要该表的触发器特权，请参见此处(下文有此文的转译)：what-are-the-privileges-required-to-execute-a-trigger-function-in-postgresql-8-4
注意事项
所有权
如果希望允许schma_admin(单独)删除和更改表，请使角色拥有所有对象。参考文档(SQL-GRANT-DESCRIPTION-OBJECTS)：
删除对象或以任何方式更改其定义的权限不被视为可授予的特权；它是所有者所固有的，不能被授予或撤销(但可以通过授予或取消拥有对象的角色的成员资格也可以获得类似的效果，见下文)。所有者也隐式地拥有其对象的所有操作权限。
ALTER TABLE some_tbl OWNER TO schma_admin;
或者一开始就以schma_admin角色创建所有的对象，则无需显式设置所有者。这样还简化了默认权限，只需为一个角色设置这些权限：
已经存在的对象
默认权限仅适用于新创建的对象，并且仅适用于创建对象时使用的特定角色，还需要调整现有对象的权限：
对应的关系(表)上报出权限拒绝
如果创建的对象的角色没有设置默认权限，则同样适用，如超级用户postgres。将所有权重新分配给schma_admin并手动设置权限-或者也为postgres设置默认权限(前提是连接到正确的数据库时):
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT ... -- etc.
默认权限
这是对alter default privileges命令的深入了解。
默认权限仅适用于当前数据库，这样就不会在DB集群中干扰其他数据库。参考文档(SQL-ALTERDEFAULTPRIVILEGES-DESCRIPTION)
对于在当前数据库中创建的所有对象
可能还想为函数和类型(不仅仅是表和序列)设置默认特权，但可能不需要这些特权。
PUBLIC所代表的默认权限
给予PUBLIC的默认权限是基本的，有些是高估了(SQL-GRANT-DESCRIPTION-OBJECTS)。
PostgreSQL grants default privileges on some types of objects to PUBLIC. No privileges are granted to PUBLIC by default on tables, columns, schemas or tablespaces. For other types, the default privileges granted to PUBLIC are as follows: CONNECT and CREATE TEMP TABLE for databases; EXECUTE privilege for functions; and USAGE privilege for languages.
PostgreSQL将某些类型的对象的默认权限授予PUBLIC。默认情况下，不会向PUBLIC授予表、列、模式或表空间的特权。对于其他类型，授予PUBLIC的默认权限如下：为数据库连接并创建临时表；函数的执行权限；语言的使用权限。
通常可以这样来做：
REVOKE ALL ON DATABASE hostdb FROM public;
特别对于新模式，不会向PUBLIC授予默认特权。名为"public"的默认权限模式向PUBLIC(All Users)授予所有权限，这可能会令人困惑。这只是一个便利的特性，可以方便地使用在新创建的数据库权限管理上，且不会以任何方式影响其他模式。可以撤消模板数据库template1中的这些权限，然后此群集中所有新创建的数据库都将不使用这些默认的权限来工作：
\connect template1
REVOKE ALL ON SCHEMA public FROM public;
TEMP权限
由于我们从PUBLIC撤消了hostdb的所有特权，所以除非我们明确允许，否则普通用户无法创建临时表。
GRANT TEMP ON DATABASE hostdb TO schma_mgr;
search_path
别忘了设置搜索路径。如果集群中只有一个数据库，那么可以在中设置全局默认值postgresql.conf，(更有可能)将其设置为数据库的属性，或仅用于相关角色，甚至是两者的组合。更多详情请参考：How does the search_path influence identifier resolution and the "current schema"
如果使用公共模式(public)，可能希望将其设置为schma，public等，或者甚至$user，schma，public ...
另一种选择是使用默认"public"模式，除非您更改了该模式，否则它应该与search_path的默认设置一起工作。在这种情况下，请记住撤销PUBLIC的特权。
相关参考
Grant privileges for a particular database in PostgreSQL
PostgreSQL - DB user should only be allowed to call functions
--------------------------------------------------------------
How to setting search_path
How does the search_path influence identifier resolution and the "current schema"
表通常由非限定名称引用，这些名称仅由表名组成，系统通过搜索路径(即要查找的模式列表)来确定哪个表。
The "current schema" (or "default schema") is, 参考手册(DDL-SCHEMAS-PATH):
搜索路径中的第一个模式称为当前模式，是搜索到的第一个模式，如果create table命令没有指定模式名，它也是将在其中创建新表的模式。
系统模式pg_temp(当前会话的临时对象的模式)和pg_目录自动成为搜索路径的一部分，并按此顺序首先进行搜索。参考手册(DDL-SCHEMAS-CATALOG):
pg_catalog始终是搜索路径的有效组成部分。如果在路径中没有显式命名，则在搜索路径的模式之前隐式搜索它，这确保了内置名称始终是可查找的。但如果希望用户定义的名称覆盖内置名称，则可以将pg_catalog显式地放在搜索路径的末尾。而pg_temp在这之前，除非它被放在不同的位置。
如何设定
有多种方法可以设置运行时变量(search_path)
为所有数据库中的所有角色设置群集范围的默认值，在postgresql.conf中设定(reload数据库实例后生效)，小心点！
search_path = 'freeoa,public'
此设置的默认值为：
search_path = "$user",public
第一个元素指定要搜索与当前用户同名的模式，如果不存在这样的模式，则忽略该条目。
将其设置为数据库的默认值：
ALTER DATABASE test SET search_path = freeoa,public;
将其设置为连接的角色的默认值(有效的群集范围)：
ALTER ROLE foo SET search_path = freeoa,public;
甚至(通常是最好的)作为数据库中角色的默认值：
ALTER ROLE foo IN DATABASE test SET search_path = freeoa,public;
在脚本顶部编写命令或者在数据库会话中执行：
SET search_path = freeoa,public;
为函数的作用域设置特定的搜索路径(以防恶意用户具有足够的权限)，阅读有关在手册中编写安全定义函数的信息(SQL-CREATEFUNCTION-SECURITY)。
CREATE FUNCTION foo() RETURNS void AS
$func$
BEGIN
-- do stuff
END
$func$ LANGUAGE plpgsql SECURITY DEFINER SET search_path=freeoa,public,pg_temp;
手册面中提及了多种方式，例如设置环境变量或使用命令行选项。
查看当前的设置：
SHOW search_path;
将其重置：
RESET search_path;
参考手册页(https://www.postgresql.org/docs/current/sql-reset.html):
The default value is defined as the value that the parameter would have had, if no SET had ever been issued for it in the current session.
默认值被定义为如果在当前会话中没有为其发出设置，则该参数应该具有的值。
--------------------------------------------------------------
来看一个简单的授权教程
一个名为mydb的数据库，一个名为mygrp的用户组(role)，一个名为myusr的用户。
以超级用户身份连接到相关数据库时(postgres)：
REVOKE ALL ON DATABASE mydb FROM public; -- shut out the general public
GRANT CONNECT ON DATABASE mydb TO mygrp; -- since we revoked from public
GRANT USAGE ON SCHEMA public TO mygrp;
为用户分配所有表的所有权限(可能会限制得更严格)：
GRANT ALL ON ALL TABLES IN SCHEMA public TO mygrp;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO mygrp; --这里别忘记了
若要为将来的对象设置默认权限，请为此模式中创建对象的每个角色运行：
ALTER DEFAULT PRIVILEGES FOR ROLE myusr IN SCHEMA public GRANT ALL ON TABLES TO mygrp;
ALTER DEFAULT PRIVILEGES FOR ROLE myusr IN SCHEMA public GRANT ALL ON SEQUENCES TO mygrp;
现在，将组权限授给用户：
GRANT mygrp TO myusr;
允许调用函数
GRANT EXECUTE ON FUNCTION foo() TO mygroup;
PostgreSQL grants default privileges on some types of objects to PUBLIC. No privileges are granted to PUBLIC by default on tables, columns, schemas or tablespaces. For other types, the default privileges granted to PUBLIC are as follows: CONNECT and CREATE TEMP TABLE for databases; EXECUTE privilege for functions; and USAGE privilege for languages. The object owner can, of course, REVOKE both default and expressly granted privileges. (For maximum security, issue the REVOKE in the same transaction that creates the object; then there is no window in which another user can use the object.) Also, these initial default privilege settings can be changed using the ALTER DEFAULT PRIVILEGES command.
PostgreSQL将某些类型的对象的默认权限授予PUBLIC。默认情况下，不会向PUBLIC授予表、列、模式或表空间的权限。对于其他类型，授予PUBLIC的默认权限如下：为数据库连接并创建临时表；函数的执行权限；语言的使用权限。当然，对象所有者可以撤销默认权限和明确授予的权限。(为了最大限度地提高安全性，请在创建对象的同一事务中发出REVOKE；这样就没有其他用户可以使用该对象的时间窗口)。此外，可以使用ALTER default PRIVILEGES命令更改这些初始默认权限设置。
CREATE OR REPLACE FUNCTION foo()
...
SECURITY DEFINER SET search_path = myschema, pg_temp;
ALTER FUNCTION foo() OWNER TO mydaemon;
REVOKE EXECUTE ON FUNCTION foo() FROM public;
GRANT EXECUTE ON FUNCTION foo() TO mydaemon;
GRANT EXECUTE ON FUNCTION foo() TO mygroup;
-- possibly others ..
--------------------------------------------------------------
The privileges required to execute a trigger function in PostgreSQL 8.4
就权限而言，触发器函数的行为与其他函数相同，有一个小的例外：
要在表上创建触发器，用户必须具有该表的触发器权限，用户还必须对触发器函数具有执行权限。
在Postgres Wiki上有所提及：
加强触发权限检查
目前，触发器函数的执行权限只在触发器创建时检查，而不在运行时检查。因此，在触发器函数上取消EXECUTE对触发器不会产生影响。
这不会授予任何操作对象的附加权限。如果调用角色缺少执行函数体(部分)所需的权限，通常的会引发异常。为了解决这个问题，可以让一个特权用户拥有该函数并使用
SECURITY DEFINER
如手册所述，它会以函数以所有者的权限而不是调用者的权限运行(默认值)。如果所有者是超级用户，则需要格外小心，授予谁执行权限以及该函数可以做些什么，以避免滥用。
REVOKE ALL ON FUNCTION foo() FROM public;
可开始设置search_path来使用这些函数，请务必阅读有关安全编写安全定义函数的章节(SQL-CREATEFUNCTION-SECURITY)。
--------------------------------------------------------------
应该知道的PostgreSQL权限与用户管理
PostgreSQL中的用户管理可能有些难度。通常新用户在数据库集群中的几个关键区域内是协同管理的。
用户角色
将学习角色、角色属性、等实践以及常见的角色设置。
pg_hba.conf
用于客户端连接和与服务器的通信的设置。
数据库、表和列级别的权限和限制
想要配置角色以获得最佳性能和使用率？表是否包含只有特权角色才能访问的敏感数据？但是需要允许不同的角色执行有限的工作？这些问题和更多问题将进行分析。
-------------------------------
角色是什么以及如何创建角色
PostgreSQL中的数据库访问权限是用角色的概念来处理的，这类似于用户。角色也可以表示PostgreSQL生态系统中的用户组。
PostgreSQL建立了角色向其拥有的数据库对象分配特权的能力，从而允许对这些对象进行访问和操作，角色可以将成员资格授予其他角色。属性为允许的客户端身份验证提供自定义选项。通过createrole命令的角色属性可以在官方的PostgreSQL文档中找到。
下面是设置新角色时通常会分配的属性，其中大多数都是不言自明的。不过，还是提供了一个简短的描述，以理清混淆以及示例用法。
SUPERUSER(超级用户)-数据库超级用户值得注意，具有此属性的角色可以创建另一个超级用户。事实上，创建另一个超级用户角色需要此属性。由于具有此属性的角色会绕过所有权限检查，请明智地授予此权限。
CREATEDB-允许角色创建数据库。
CREATEROLE-使用此属性，角色可以发出CREATE role命令，即创建其他角色。
LOGIN-启用登录功能，具有此属性的角色名可以在客户端连接命令中使用，即登录属性。
某些属性有一个显式的反向命名命令，通常在未指定时为默认属性：
SUPERUSER | NOSUPERUSER
CREATEROLE |NOCREATEROLE
LOGIN |NOLOGIN
可以通过检查pg_role目录来验证这些集合属性，这两个列是rolcreaterole和rolcreatedb。两者都是布尔型数据类型，因此对于这些属性，它们应该设置为t表示真
# SELECT rolcreaterole, rolcreatedb FROM pg_roles WHERE rolname = 'log_user';
rolcreaterole | rolcreatedb
---------------+-------------
t | t
如何确定数据库中现有的角色？
两种可用的方法：psql>\du命令或从pg_roles中选择。
postgres=> \du
List of roles
Role name | Attributes | Member of
------------+------------------------------------------------------------+-----------
log_user | Create role, Create DB | {}
nolog_user | Cannot login | {}
postgres=> SELECT rolname FROM pg_roles;
rolname
----------------------
nolog_user
log_user
要点
CREATE ROLE及其对应的DROP ROLE是实现创建和删除角色的常用命令。
ALTER ROLE处理更改角色属性的操作。
由于在数据库群集级别定义，角色在所有数据库中都有效(Roles are valid within all databases due to definition at the database cluster level.)。
请记住，创建以特殊字符开头的角色名需要用双引号("")来标记。
角色及其权限是使用属性是在建立时确定的。
要建立默认情况下需要LOGIN属性的角色，createuser是一个可选的命令。它们基本上是相等的，而不是CREATE ROLE username LOGIN。
-------------------------------
使用pg_hba.conf来建立连接
涵盖pg_hba.conf的所有方面一篇长文都不一定能讲清楚，这里的介绍可能遇到的常见问题的解决方法。
成功的连接需要双方整体来实现。在通过pg_hba.conf的设置后，连接到服务器的角色仍必须满足在数据库级别设置的访问限制。找到你的pg_hba.conf文件，在pg_settings视图上发出SELECT查询。必须以超级用户身份登录才能查询此视图。
postgres=# SELECT name, setting FROM pg_settings WHERE name LIKE '%hba%';
name | setting
----------+-------------------------------------
hba_file | /etc/postgresql/10/main/pg_hba.conf
pg_hba.conf文件包含指定给定连接请求的七种可用格式之一的记录。
对于包含多个用户的强化系统，这些设置不是最佳设置。此类型连接的字段包括：
local database user auth-method [auth-options]
各字段意义为：
local - connections are attempted with Unix-domain sockets.(尝试使用Unix域套接字进行连接)
database - Specifies the database(s) named for this record match.(指定为此记录匹配项命名的数据库)
user - The database user name matched for this record. A comma-separated list of multiple users or all is allowed for this field as well.(与此记录匹配的数据库用户名，此字段也允许使用逗号分隔的多个用户或所有用户的列表)
auth-method - Is used when a connection matches this unique record. The possible choices for this field is:(当连接与此唯一记录匹配时使用，此字段的可能选择是)
trust,reject,scram-sha-256,md5,password,gss,sspi,ident,peer,ldap,radius,cert,pam,bsd
为角色nolog_user、log_user两位的可能记录为：
local all nolog_user password
local all log_user password
注意：由于密码以明文形式发送，因此不应在不受信任的网络或不可信环境中使用。
pg_hba_file_rules视图中的三个相关的列，其中包含以下查询。同样需要SUPERUSER属性来查询此视图。
postgres=# SELECT database, user_name, auth_method FROM pg_hba_file_rules WHERE CAST(user_name AS TEXT) LIKE '%log_user%';
database | user_name | auth_method
----------+--------------+-------------
{all} | {nolog_user} | password
{all} | {log_user} | password
psql -U nolog_user -W postgres
Password for user nolog_user:
psql: FATAL: role "nolog_user" is not permitted to log in
psql -U log_user -W postgres
Password for user log_user:
psql (10.1)
Type "help" for help.
这里的关键是，虽然nolog_user和log_user都可以根据pg_hba.conf文件，只有log_用户才允许实际登录。
当log_user通过数据库级访问限制时(通过具有LOGIN属性)，nolog_user没有。
编辑pg_hba.conf文件中的log_user并更改允许此角色访问的数据库名称。这是一个变化，表明log_user现在只能登录到试用数据库。
local trial log_user password
首先，让我们尝试登录到postgres数据库，由于all标志，log_user以前有权访问该数据库。
$ psql -U log_user -W postgres
Password for user log_user:
psql: FATAL: no pg_hba.conf entry for host "[local]", user "log_user", database "postgres", SSL off
现在trial数据库log_user确实有权。
$ psql -U log_user -W trial
Password for user log_user:
psql (10.1)
Type "help" for help.
没有错误的话，trial=>提示符显示当前连接的数据库，一旦建立连接，这些设置也适用于服务器环境。
trial=> \c postgres;
Password for user log_user:
FATAL: no pg_hba.conf entry for host "[local]", user "log_user", database "postgres", SSL off
注意：通常，重新加载pg_hba.conf需要文件才能使更改生效。
使用pg_ctl工具来重载服务器：
pg_ctl reload [-D datadir] [-s]
要知道datadir在哪里，可以查询pg_settings系统视图，如以超级用户身份登录，使用类似的SELECT查询：
postgres=# SELECT setting FROM pg_settings WHERE name = 'data_directory';
setting
-----------------------------
/var/lib/pgsql/10/main
在postgres用户的shell下：
$ /usr/lib/postgresql/10/bin/pg_ctl reload -D /var/lib/pgsql/10/main
server signaled
检查服务器状态
$ /usr/lib/postgresql/10/bin/pg_ctl status -D /var/lib/pgsql/10/main
pg_ctl: server is running (PID: 6543)
/usr/lib/postgresql/10/bin/postgres "-D" "/var/lib/pgsql/10/main" "-c" "config_file=/etc/pgsql/postgresql.conf"
要点
角色必须通过两个条件：pg_hba.conf文件中的配置和数据库级访问权限。
pg_hba.conf对于每个连接请求，从上到下检查文件，因此文件中的顺序非常重要。
-------------------------------
Database, Table, and Column 权限和限制 - 为任务和职责定制合适的角色
为了让角色使用数据库对象(表、视图、列、函数等)，必须授予它们访问权限。 GRANT命令定义了这些基本特权。
创建数据库
因为log_user被授予CREATEDB和CREATEROLE属性，所以他可以使用这个角色来创建一个名为trial的测试数据库。
postgres=> CREATE DATABASE trial:
现创建一个新的ROLE:
postgres=> CREATE ROLE db_user WITH LOGIN PASSWORD 'freeoapwd';
将log_user连接到新的试用数据库：
postgres=> \c trial;
Password for user log_user:
You are now connected to database "trial" as user "log_user".
trial=>
注意：提示符更改为名称'trial'，表示我们已连接到该数据库。
用logunuser创建一个模拟表：
trial=> CREATE TABLE another_workload(
trial(> id INTEGER,
trial(> first_name VARCHAR(20),
trial(> last_name VARCHAR(20),
trial(> sensitive_info TEXT);
log_user用户最近创建了一个助手角色db_user。我们要求db_user对表another_workload具有有限的权限。
毫无疑问，此角色不应访问敏感的“信息”列。此时也不应授予INSERT、UPDATE和DELETE命令，除非db_user遇到一些特殊情况。
但需要db_user发出SELECT查询，如何在另一个another_workload表中限制这些角色的能力？
首先，让我们在表级别检查PostgreSQL GRANT命令文档中的确切语法：
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
接下来，我们用特定的语法实现为db_user角色设置的权限需求。
trial=> GRANT SELECT (id, first_name, last_name) ON TABLE another_workload TO db_user;
GRANT
注意，在SELECT关键字之后，我们列出了db_user可以访问的列。在更改之前，如果db_user尝试在sensitive_info列上选择查询，或任何其它与此相关的命令，这些查询则不会执行。
在db_user登录之后，就可以尝试使用SELECT查询返回表中的所有列和记录。
trial=> SELECT * FROM another_workload;
ERROR: permission denied for relation another_workload
此查询中包含列sensitive_info的信息。因此，不会将记录返回给db_user。 但只查询允许的列时却没有这个问题：
trial=> SELECT id, first_name, last_name FROM another_workload;
id | first_name | last_name
-----+------------+-----------
10 | John | Morris
191 | Jannis | Harper
2 | Remmy | Rosebuilt
可以测试一下INSERT, UPDATE, DELETE 等命令。
trial=> INSERT INTO another_workload(id,first_name,last_name,sensitive_info)
VALUES(17,'Jeremy','Stillman','key code:400Z');
ERROR: permission denied for relation another_workload
trial=> UPDATE another_workload SET id = 101 WHERE id = 10;
ERROR: permission denied for relation another_workload
trial=> DELETE FROM another_workload
trial-> WHERE id = 2;;
ERROR: permission denied for relation another_workload
要点
角色通过GRANT命令获得对数据库对象的访问权限。
针对这些数据库对象对象的命令在PostgreSQL环境中是可配置的。
小结
创建具有特定属性的角色。
设置客户端和服务器之间的可用连接，允许角色登录访问数据库。
通过实现必要的属性，高度定制角色，以满足数据库、表和列级别访问的个性化要求。
-------------------------------
权限之直接授权与默认权限的差别
直接的授权(GRANT)肯定与上面的默认授权(ALTER DEFAULT PRIVILEGES FOR)是有区别的，前者是针对当时的情况，不包括后面所创建的表对象这些；而后者也只有针对当时之后的情况，不包括之前的表等对象的权限。
于是出现了一个疑问：如何知道一个用户在schema下面是有默认的权限还是一次性赋予的权限？
实验：用户scaprw创建一个schema，
CREATE SCHEMA IF NOT EXISTS tsc;
GRANT USAGE ON SCHEMA tsc TO scapro;
GRANT USAGE ON SCHEMA tsc TO scaprx;
在其中创建一表1
CREATE TABLE tsc.tb1(
id serial2,ename varchar(9)
);
insert into tsc.tb1(ename) values ('htsc1') returning *;
其他用户访问时会报错：
错误: 42501: 对表 tb1 权限不够
位置: aclcheck_error, aclchk.c:3488
为用户scaprx赋予默认权限
scaprw>
ALTER DEFAULT PRIVILEGES IN SCHEMA tsc GRANT SELECT,INSERT ON TABLES TO scaprx;
ALTER DEFAULT PRIVILEGES IN SCHEMA tsc GRANT SELECT,USAGE ON SEQUENCES TO scaprx;
scaprx>select * from tsc.tb1;
错误: 42501: 对表 tb1 权限不够
为用户scapro赋予一次性权限
scaprw>
GRANT SELECT,INSERT,UPDATE ON ALL TABLES IN SCHEMA tsc TO scapro;
GRANT SELECT,USAGE,UPDATE ON ALL SEQUENCES IN SCHEMA tsc to scapro;
scapro>select * from tsc.tb1;
id | ename
----+-------
1 | htsc1
scapro>insert into tsc.tb1(ename) values ('htsc2') returning *;
id | ename
----+-------
2 | htsc2
INSERT 0 1
阶段结论：修改默认权限不能对此前的表产生作用，而Grant可以对此前的表产生作用。下面来测试对之后的表的读写情况：
scaprw>CREATE TABLE tsc.tb2(
id serial2,cname varchar(9)
);
scaprw>insert into tsc.tb2(cname) values ('测试1') returning *;
scaprx>select * from tsc.tb2;
id | cname
----+-------
1 | 测试1
当然对tb1依然报了权限访问错误。
scapro>select * from tsc.tb2;
错误: 42501: 对表 tb2 权限不够
位置: aclcheck_error, aclchk.c:3488
当然对tb1依然的可以访问的，更新也是可以的。
scapro>update tsc.tb1 set ename='change2' where id=1 returning *;
id | ename
----+---------
1 | change2
那么回到上面的问题：目前还真没有办法看出对schema下哪些是默认的授权，哪些是一次性授予的权限，不过这不是什么要紧的问题。
-------------------------------
PostgreSQL数据库的登录用户和角色是有相互联系的，用户和角色只有一个区别就能否登录，即login属性。create role 与 create user 命令是等效的，只是create user默认是login的，而前者默认是非login的。本文在再次修改时，PostgreSQL的第12版的中文文档已经出来，优先参考吧。先给出它们的链接，不过还是建议按官方手册通读一遍。文章后面转译了国外友人相关文章章节，以供参考。
建议阅读(基于第12版的中文手册)
psql
5.7.权限
5.9.模式
20.1.pg_hba.conf
CREATE ROLE
CREATE USER
CREATE SCHEMA
ALTER ROLE
GRANT
REVOKE
ALTER DEFAULT PRIVILEGES
第21章-数据库角色
使用pgsql客户端连接postgresql服务器
授于权限可从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 权限缩写
| 权限 | 缩写 | 适用对象类型 |
|---|---|---|
| SELECT | r (“读”) | LARGE OBJECT, SEQUENCE, TABLE (and table-like objects), table column |
| INSERT | a (“增补”) | TABLE, table column |
| UPDATE | w (“写”) | LARGE OBJECT, SEQUENCE, TABLE, table column |
| DELETE | d | TABLE |
| TRUNCATE | D | TABLE |
| REFERENCES | x | TABLE, table column |
| TRIGGER | t | TABLE |
| CREATE | C | DATABASE, SCHEMA, TABLESPACE |
| CONNECT | c | DATABASE |
| TEMPORARY | T | DATABASE |
| EXECUTE | X | FUNCTION, PROCEDURE |
| USAGE | U | DOMAIN, FOREIGN DATA WRAPPER, FOREIGN SERVER, LANGUAGE, SCHEMA, SEQUENCE, TYPE |
表 5.2. 访问权限摘要
| 对象类型 | 所有权限 | 默认 PUBLIC 权限 | psql 命令 |
|---|---|---|---|
| DATABASE | CTc | Tc | \l |
| DOMAIN | U | U | \dD+ |
| FUNCTION or PROCEDURE | X | X | \df+ |
| FOREIGN DATA WRAPPER | U | none | \dew+ |
| FOREIGN SERVER | U | none | \des+ |
| LANGUAGE | U | U | \dL+ |
| LARGE OBJECT | rw | none | |
| SCHEMA | UC | none | \dn+ |
| SEQUENCE | rwU | none | \dp |
| TABLE (and table-like objects) | arwdDxt | none | \dp |
| Table column | arwx | none | \dp |
| TABLESPACE | C | none | \db+ |
| TYPE | U | U | \dT+ |
已授予特定对象的权限显示为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的字段：
名字 | 类型 | 引用 | 描述 |
rolname | name |
| 角色名 |
rolsuper | bool |
| 有超级用户权限的角色 |
rolcreaterole | bool |
| 可以创建更多角色的角色 |
rolcreatedb | bool |
| 可以创建数据库的角色 |
rolcatupdate | bool |
| 可以直接更新系统表的角色(除非这个字段为真，否则超级用户也不能干这个事情。) |
rolcanlogin | bool |
| 可以登录的角色，也就是说，这个角色可以给予初始化会话认证的标识符。 |
rolpassword | text |
| 不是口令(总是 ********) |
rolvaliduntil | timestamptz |
| 口令失效日期(只用于口令认证)；如果没有失效期，为 NULL |
rolconfig | text[] |
| 运行时配置变量的会话缺省 |
为角色成员赋于权限
创建组角色
# 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、由于不同的对象会有不同的默认权限，因此在建库、模式、用户的时候就应该将其的默认权限全部移除，后续在为其添加权限，授权依然要遵守最小化授权的的原则。
---------------------------------------------------------------
下为转译的国外友人的一些设置片段，其中不乏一些经典场景，请参阅。
pgsql中查询相应的权限
从information模式的table_privileges中查询用户的权限
SELECT table_catalog, table_schema, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee = 'FreeOA';
从information模式的role_table_grants中查询表上的权限
SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name='workers';
命令交互窗口的快捷方式：
使用'\du'可列出当前数据库的所有账号(角色)的权限信息。关于权限的文档解释了如何理解输出，获取当前数据库表的特定权限：\z [Table_Name]，'\z'的选项对pgsql的版本有一定的要求。
PRIVILEGE-ABBREVS-TABLE
Using psql meta-commands
一些查看权限的快捷键：
\ddp [ pattern ] Lists default access privilege settings.
\dp [ pattern ] Lists tables, views and sequences with their associated access privileges.
\l[+] [ pattern ] List the databases in the server and show .... access privileges.
上面也提到过，但在手册页上没有“权限”一词：
\du+ for roles with login and \dg+ for roles without - will have a filed "Member of" where you find roles granted to roles.
\dn[S+] [PATTERN] list schemas
在9.3之后还可以用下述语句来显示对对象的权限：
F=# begin; drop user x;
BEGIN
Time: 0.124 ms
ERROR: role "x" cannot be dropped because some objects depend on it
DETAIL: privileges for type "SO dT"
privileges for sequence so
privileges for schema freeoa
privileges for table xx
privileges for table tmp_x
privileges for table test
Time: 0.211 ms
F=# rollback;
ROLLBACK
Time: 0.150 ms
'\du'相当于下面的查询语句：
SELECT r.rolname, r.rolsuper, r.rolinherit,r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,r.rolconnlimit, r.rolvaliduntil,ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid) as memberof, r.rolreplication, r.rolbypassrls FROM pg_catalog.pg_roles r WHERE r.rolname !~ '^pg_' ORDER BY 1;
直接从角色表中查：
SELECT * FROM pg_roles;
SELECT grantee AS user, CONCAT(table_schema, '.', table_name) AS table,
CASE WHEN COUNT(privilege_type) = 7 THEN 'ALL' ELSE ARRAY_TO_STRING(ARRAY_AGG(privilege_type), ', ') END AS grants
FROM information_schema.role_table_grants WHERE table_name !~ '^pg_' GROUP BY table_name, table_schema, grantee;
SELECT grantee AS user, CONCAT(table_schema, '.', table_name) AS schema_table,
CASE WHEN COUNT(privilege_type) = 7 THEN 'ALL' ELSE ARRAY_TO_STRING(ARRAY_AGG(privilege_type), ', ') END AS grants FROM information_schema.role_table_grants
WHERE table_schema !~ '^information_|^pg_' and table_name NOT SIMILAR TO '^pg_' GROUP BY table_name, table_schema, grantee;
SELECT usename AS role_name,
CASE
WHEN usesuper AND usecreatedb THEN CAST('superuser, create database' AS pg_catalog.text)
WHEN usesuper THEN CAST('superuser' AS pg_catalog.text)
WHEN usecreatedb THEN CAST('create database' AS pg_catalog.text)
ELSE CAST('' AS pg_catalog.text)
END role_attributes
FROM pg_catalog.pg_user ORDER BY role_name desc;
要使用户成为只读用户，应先撤消该用户所有的默认权限，而后授予该库的CONNECT访问权限。然后，在数据库中的现有表上添加SELECT权限，并将SELECT权限设置为将来创建的任何其他表的默认值。
REVOKE ALL ON DATABASE freeoa_database FROM freeoa_user;
GRANT CONNECT ON DATABASE freeoa_database TO freeoa_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO freeoa_user; -- 对已有表对象的授权，影响这之前已存在的对象
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO freeoa_user; -- 为该用户在该模式的对象类型上设定默认权限，影响这之后的对象
需要在希望该用户具有这些权限的每个数据库上运行这些命令，也可以修改这些命令以授予用户不同的权限。例如，将权限从SELECT更改为INSERT,SELECT，这将使用户既能读又能写数据。
可以在PostgreSQL中对各种数据库对象授予(GRANT)和撤消(REVOKE)权限，下面将研究如何在PostgreSQL中授予和撤消表的权限。
对表授予权限
可以向用户授予表的各种权限，这些权限可以是SELECT、INSERT、UPDATE、DELETE、INDEX、CREATE、ALTER、DROP、GRANT OPTION的任意组合或ALL(全部权限)。
向PostgreSQL中的表授权的语法为：
GRANT privileges ON object TO user;
权限与对象
Privilege Description
SELECT Ability to perform SELECT statements on the table.
INSERT Ability to perform INSERT statements on the table.
UPDATE Ability to perform UPDATE statements on the table.
DELETE Ability to perform DELETE statements on the table.
TRUNCATE Ability to perform TRUNCATE statements on the table.
REFERENCES Ability to create foreign keys (requires privileges on both parent and child tables).
TRIGGER Ability to create triggers on the table.
CREATE Ability to perform CREATE TABLE statements.
ALL Grants all permissions.
object:要授予其权限的数据库下对象的名称，在对表授予权限的情况下，这将是表名。
user:将被授予这些权限的用户的名称。
示例
下面提供了若干的授权示例供参考
为freeoauser将SELECT, INSERT, UPDATE, and DELETE的权限授予名为products的表：
GRANT SELECT, INSERT, UPDATE, DELETE ON products TO freeoauser;
使用ALL关键字来完成：
GRANT ALL ON products TO freeoauser;
如果想为所有用户在products表上授于SELECT权限，可用PUBLIC(角色)来代替所有用户：
GRANT SELECT ON products TO PUBLIC;
在Table上撤销权限
如果想为已经授权过的用户撤销一些或全部的权限，可用使用revoke命令。可使用SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, 这些权限的组合或ALL关键字来实现。
在Table上撤销权限的语法：
REVOKE privileges ON object FROM user;
下面提供了一些参考示例
例如，如果要从名为products的表上为名为freeauser的用户撤消DELETE和UPDATE权限，则应运行以下revoke语句：
REVOKE DELETE, UPDATE ON products FROM freeoauser;
如果要撤消freeoauser用户对表的所有权限，可以使用all关键字，如下所示：
REVOKE ALL ON products FROM freeoauser;
如果在products表上将SELECT特权授予 *(即：all users)，并且希望撤消这些特权，则可以运行以下revoke语句：
REVOKE SELECT ON products FROM PUBLIC;
How to set DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA
ALTER DEFAULT PRIVILEGES FOR ROLE new_user IN SCHEMA public GRANT...
ALTER DEFAULT PRIVILEGES [ FOR { ROLE | USER } target_role [, ...] ]
这两者中的一个({ROLE| USER})是必需的。
Note that the particular command does not make any sense to begin with because (per documentation):
There is no need to grant privileges to the owner of an object (usually the user that created it), as the owner has all privileges by default.
If you want that to grant permissions for objects that a different user created, like postgres, then use:
And you probably wouldn't want to restrict that to a particular schema.
你可能不想把它局限于一个特定的模式下。
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT ALL ON TABLES TO new_user;
而且通常也希望对序列授予特权
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT ALL ON SEQUENCES TO new_user;
5.7.Privileges
GRANT SQL Commands
ALTER DEFAULT PRIVILEGES
---------------------------------------------------------------
How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA
下面是一个示例参考：
希望将其配置为只有hostdb_admin可以创建(以及删除和更改)表；hostdb_mgr默认情况下可以读取、插入、更新和删除所有表；而hostdb_usr只能读取所有表(和视图)。
As superuser postgres:
CREATE USER schma_admin WITH PASSWORD 'youwish';
-- CREATE USER schma_admin WITH PASSWORD 'youwish' CREATEDB CREATEROLE; -- see below
CREATE USER schma_mgr WITH PASSWORD 'youwish2';
CREATE USER schma_usr WITH PASSWORD 'youwish3';
如果想要一个更强大的管理员来管理数据库和角色，请在上面添加角色属性(已经写出但是为注释)：CREATEDB和CREATEROLE。
Grant each role to the next higher level, so all levels "inherit" at least the set of privileges from the next lower level (cascading):
将每个角色授予下一个更高级别，以便所有级别至少从下一个较低级别“继承”一组权限(级联)：
GRANT schma_usr TO schma_mgr;
GRANT schma_mgr TO schma_admin;
CREATE DATABASE hostdb;
REVOKE ALL ON DATABASE hostdb FROM public; -- see notes below!
GRANT CONNECT ON DATABASE hostdb TO schma_usr; -- others inherit
\connect hostdb -- psql syntax
I am naming the schema schma (not hostdb which would be confusing). Pick any name. Optionally make schma_admin the owner of the schema:
我将模式命名为schma(而不是hostdb，这可能会令人困惑)，随便选一个名字(可选)将schma_admin设定为SCHEMA的所有者：
CREATE SCHEMA schma AUTHORIZATION schma_admin;
SET search_path = schma; -- see notes
ALTER ROLE schma_admin IN DATABASE hostdb SET search_path = schma; -- not inherited
ALTER ROLE schma_mgr IN DATABASE hostdb SET search_path = schma;
ALTER ROLE schma_usr IN DATABASE hostdb SET search_path = schma;
GRANT USAGE ON SCHEMA schma TO schma_usr;
GRANT CREATE ON SCHEMA schma TO schma_admin;
ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin GRANT SELECT ON TABLES TO schma_usr; -- only read
ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO schma_mgr; -- + write, TRUNCATE optional
ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO schma_mgr; -- SELECT, UPDATE are optional
有关删除和更改，请参见下面的注释。
随着需求变得越来越精细，也会有一些问题来对触发器、存储过程、视图和其他对象应用类似的限制。
视图比较特殊(但请注意，所有表都被视为包括视图和外部表)。
对于可更新视图：
请注意，对视图执行插入、更新或删除操作的用户必须对视图具有相应的插入、更新或删除权限。此外，视图的所有者必须具有基础关系的相关特权，但执行更新的用户不需要对基础关系拥有任何权限(见第38.5节)。
触发器也很特殊，需要该表的触发器特权，请参见此处(下文有此文的转译)：what-are-the-privileges-required-to-execute-a-trigger-function-in-postgresql-8-4
注意事项
所有权
如果希望允许schma_admin(单独)删除和更改表，请使角色拥有所有对象。参考文档(SQL-GRANT-DESCRIPTION-OBJECTS)：
删除对象或以任何方式更改其定义的权限不被视为可授予的特权；它是所有者所固有的，不能被授予或撤销(但可以通过授予或取消拥有对象的角色的成员资格也可以获得类似的效果，见下文)。所有者也隐式地拥有其对象的所有操作权限。
ALTER TABLE some_tbl OWNER TO schma_admin;
或者一开始就以schma_admin角色创建所有的对象，则无需显式设置所有者。这样还简化了默认权限，只需为一个角色设置这些权限：
已经存在的对象
默认权限仅适用于新创建的对象，并且仅适用于创建对象时使用的特定角色，还需要调整现有对象的权限：
对应的关系(表)上报出权限拒绝
如果创建的对象的角色没有设置默认权限，则同样适用，如超级用户postgres。将所有权重新分配给schma_admin并手动设置权限-或者也为postgres设置默认权限(前提是连接到正确的数据库时):
ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT ... -- etc.
默认权限
这是对alter default privileges命令的深入了解。
默认权限仅适用于当前数据库，这样就不会在DB集群中干扰其他数据库。参考文档(SQL-ALTERDEFAULTPRIVILEGES-DESCRIPTION)
对于在当前数据库中创建的所有对象
可能还想为函数和类型(不仅仅是表和序列)设置默认特权，但可能不需要这些特权。
PUBLIC所代表的默认权限
给予PUBLIC的默认权限是基本的，有些是高估了(SQL-GRANT-DESCRIPTION-OBJECTS)。
PostgreSQL grants default privileges on some types of objects to PUBLIC. No privileges are granted to PUBLIC by default on tables, columns, schemas or tablespaces. For other types, the default privileges granted to PUBLIC are as follows: CONNECT and CREATE TEMP TABLE for databases; EXECUTE privilege for functions; and USAGE privilege for languages.
PostgreSQL将某些类型的对象的默认权限授予PUBLIC。默认情况下，不会向PUBLIC授予表、列、模式或表空间的特权。对于其他类型，授予PUBLIC的默认权限如下：为数据库连接并创建临时表；函数的执行权限；语言的使用权限。
通常可以这样来做：
REVOKE ALL ON DATABASE hostdb FROM public;
特别对于新模式，不会向PUBLIC授予默认特权。名为"public"的默认权限模式向PUBLIC(All Users)授予所有权限，这可能会令人困惑。这只是一个便利的特性，可以方便地使用在新创建的数据库权限管理上，且不会以任何方式影响其他模式。可以撤消模板数据库template1中的这些权限，然后此群集中所有新创建的数据库都将不使用这些默认的权限来工作：
\connect template1
REVOKE ALL ON SCHEMA public FROM public;
TEMP权限
由于我们从PUBLIC撤消了hostdb的所有特权，所以除非我们明确允许，否则普通用户无法创建临时表。
GRANT TEMP ON DATABASE hostdb TO schma_mgr;
search_path
别忘了设置搜索路径。如果集群中只有一个数据库，那么可以在中设置全局默认值postgresql.conf，(更有可能)将其设置为数据库的属性，或仅用于相关角色，甚至是两者的组合。更多详情请参考：How does the search_path influence identifier resolution and the "current schema"
如果使用公共模式(public)，可能希望将其设置为schma，public等，或者甚至$user，schma，public ...
另一种选择是使用默认"public"模式，除非您更改了该模式，否则它应该与search_path的默认设置一起工作。在这种情况下，请记住撤销PUBLIC的特权。
相关参考
Grant privileges for a particular database in PostgreSQL
PostgreSQL - DB user should only be allowed to call functions
--------------------------------------------------------------
How to setting search_path
How does the search_path influence identifier resolution and the "current schema"
表通常由非限定名称引用，这些名称仅由表名组成，系统通过搜索路径(即要查找的模式列表)来确定哪个表。
The "current schema" (or "default schema") is, 参考手册(DDL-SCHEMAS-PATH):
搜索路径中的第一个模式称为当前模式，是搜索到的第一个模式，如果create table命令没有指定模式名，它也是将在其中创建新表的模式。
系统模式pg_temp(当前会话的临时对象的模式)和pg_目录自动成为搜索路径的一部分，并按此顺序首先进行搜索。参考手册(DDL-SCHEMAS-CATALOG):
pg_catalog始终是搜索路径的有效组成部分。如果在路径中没有显式命名，则在搜索路径的模式之前隐式搜索它，这确保了内置名称始终是可查找的。但如果希望用户定义的名称覆盖内置名称，则可以将pg_catalog显式地放在搜索路径的末尾。而pg_temp在这之前，除非它被放在不同的位置。
如何设定
有多种方法可以设置运行时变量(search_path)
为所有数据库中的所有角色设置群集范围的默认值，在postgresql.conf中设定(reload数据库实例后生效)，小心点！
search_path = 'freeoa,public'
此设置的默认值为：
search_path = "$user",public
第一个元素指定要搜索与当前用户同名的模式，如果不存在这样的模式，则忽略该条目。
将其设置为数据库的默认值：
ALTER DATABASE test SET search_path = freeoa,public;
将其设置为连接的角色的默认值(有效的群集范围)：
ALTER ROLE foo SET search_path = freeoa,public;
甚至(通常是最好的)作为数据库中角色的默认值：
ALTER ROLE foo IN DATABASE test SET search_path = freeoa,public;
在脚本顶部编写命令或者在数据库会话中执行：
SET search_path = freeoa,public;
为函数的作用域设置特定的搜索路径(以防恶意用户具有足够的权限)，阅读有关在手册中编写安全定义函数的信息(SQL-CREATEFUNCTION-SECURITY)。
CREATE FUNCTION foo() RETURNS void AS
$func$
BEGIN
-- do stuff
END
$func$ LANGUAGE plpgsql SECURITY DEFINER SET search_path=freeoa,public,pg_temp;
手册面中提及了多种方式，例如设置环境变量或使用命令行选项。
查看当前的设置：
SHOW search_path;
将其重置：
RESET search_path;
参考手册页(https://www.postgresql.org/docs/current/sql-reset.html):
The default value is defined as the value that the parameter would have had, if no SET had ever been issued for it in the current session.
默认值被定义为如果在当前会话中没有为其发出设置，则该参数应该具有的值。
--------------------------------------------------------------
来看一个简单的授权教程
一个名为mydb的数据库，一个名为mygrp的用户组(role)，一个名为myusr的用户。
以超级用户身份连接到相关数据库时(postgres)：
REVOKE ALL ON DATABASE mydb FROM public; -- shut out the general public
GRANT CONNECT ON DATABASE mydb TO mygrp; -- since we revoked from public
GRANT USAGE ON SCHEMA public TO mygrp;
为用户分配所有表的所有权限(可能会限制得更严格)：
GRANT ALL ON ALL TABLES IN SCHEMA public TO mygrp;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO mygrp; --这里别忘记了
若要为将来的对象设置默认权限，请为此模式中创建对象的每个角色运行：
ALTER DEFAULT PRIVILEGES FOR ROLE myusr IN SCHEMA public GRANT ALL ON TABLES TO mygrp;
ALTER DEFAULT PRIVILEGES FOR ROLE myusr IN SCHEMA public GRANT ALL ON SEQUENCES TO mygrp;
现在，将组权限授给用户：
GRANT mygrp TO myusr;
允许调用函数
GRANT EXECUTE ON FUNCTION foo() TO mygroup;
PostgreSQL grants default privileges on some types of objects to PUBLIC. No privileges are granted to PUBLIC by default on tables, columns, schemas or tablespaces. For other types, the default privileges granted to PUBLIC are as follows: CONNECT and CREATE TEMP TABLE for databases; EXECUTE privilege for functions; and USAGE privilege for languages. The object owner can, of course, REVOKE both default and expressly granted privileges. (For maximum security, issue the REVOKE in the same transaction that creates the object; then there is no window in which another user can use the object.) Also, these initial default privilege settings can be changed using the ALTER DEFAULT PRIVILEGES command.
PostgreSQL将某些类型的对象的默认权限授予PUBLIC。默认情况下，不会向PUBLIC授予表、列、模式或表空间的权限。对于其他类型，授予PUBLIC的默认权限如下：为数据库连接并创建临时表；函数的执行权限；语言的使用权限。当然，对象所有者可以撤销默认权限和明确授予的权限。(为了最大限度地提高安全性，请在创建对象的同一事务中发出REVOKE；这样就没有其他用户可以使用该对象的时间窗口)。此外，可以使用ALTER default PRIVILEGES命令更改这些初始默认权限设置。
CREATE OR REPLACE FUNCTION foo()
...
SECURITY DEFINER SET search_path = myschema, pg_temp;
ALTER FUNCTION foo() OWNER TO mydaemon;
REVOKE EXECUTE ON FUNCTION foo() FROM public;
GRANT EXECUTE ON FUNCTION foo() TO mydaemon;
GRANT EXECUTE ON FUNCTION foo() TO mygroup;
-- possibly others ..
--------------------------------------------------------------
The privileges required to execute a trigger function in PostgreSQL 8.4
就权限而言，触发器函数的行为与其他函数相同，有一个小的例外：
要在表上创建触发器，用户必须具有该表的触发器权限，用户还必须对触发器函数具有执行权限。
在Postgres Wiki上有所提及：
加强触发权限检查
目前，触发器函数的执行权限只在触发器创建时检查，而不在运行时检查。因此，在触发器函数上取消EXECUTE对触发器不会产生影响。
这不会授予任何操作对象的附加权限。如果调用角色缺少执行函数体(部分)所需的权限，通常的会引发异常。为了解决这个问题，可以让一个特权用户拥有该函数并使用
SECURITY DEFINER
如手册所述，它会以函数以所有者的权限而不是调用者的权限运行(默认值)。如果所有者是超级用户，则需要格外小心，授予谁执行权限以及该函数可以做些什么，以避免滥用。
REVOKE ALL ON FUNCTION foo() FROM public;
可开始设置search_path来使用这些函数，请务必阅读有关安全编写安全定义函数的章节(SQL-CREATEFUNCTION-SECURITY)。
--------------------------------------------------------------
应该知道的PostgreSQL权限与用户管理
PostgreSQL中的用户管理可能有些难度。通常新用户在数据库集群中的几个关键区域内是协同管理的。
用户角色
将学习角色、角色属性、等实践以及常见的角色设置。
pg_hba.conf
用于客户端连接和与服务器的通信的设置。
数据库、表和列级别的权限和限制
想要配置角色以获得最佳性能和使用率？表是否包含只有特权角色才能访问的敏感数据？但是需要允许不同的角色执行有限的工作？这些问题和更多问题将进行分析。
-------------------------------
角色是什么以及如何创建角色
PostgreSQL中的数据库访问权限是用角色的概念来处理的，这类似于用户。角色也可以表示PostgreSQL生态系统中的用户组。
PostgreSQL建立了角色向其拥有的数据库对象分配特权的能力，从而允许对这些对象进行访问和操作，角色可以将成员资格授予其他角色。属性为允许的客户端身份验证提供自定义选项。通过createrole命令的角色属性可以在官方的PostgreSQL文档中找到。
下面是设置新角色时通常会分配的属性，其中大多数都是不言自明的。不过，还是提供了一个简短的描述，以理清混淆以及示例用法。
SUPERUSER(超级用户)-数据库超级用户值得注意，具有此属性的角色可以创建另一个超级用户。事实上，创建另一个超级用户角色需要此属性。由于具有此属性的角色会绕过所有权限检查，请明智地授予此权限。
CREATEDB-允许角色创建数据库。
CREATEROLE-使用此属性，角色可以发出CREATE role命令，即创建其他角色。
LOGIN-启用登录功能，具有此属性的角色名可以在客户端连接命令中使用，即登录属性。
某些属性有一个显式的反向命名命令，通常在未指定时为默认属性：
SUPERUSER | NOSUPERUSER
CREATEROLE |NOCREATEROLE
LOGIN |NOLOGIN
可以通过检查pg_role目录来验证这些集合属性，这两个列是rolcreaterole和rolcreatedb。两者都是布尔型数据类型，因此对于这些属性，它们应该设置为t表示真
# SELECT rolcreaterole, rolcreatedb FROM pg_roles WHERE rolname = 'log_user';
rolcreaterole | rolcreatedb
---------------+-------------
t | t
如何确定数据库中现有的角色？
两种可用的方法：psql>\du命令或从pg_roles中选择。
postgres=> \du
List of roles
Role name | Attributes | Member of
------------+------------------------------------------------------------+-----------
log_user | Create role, Create DB | {}
nolog_user | Cannot login | {}
postgres=> SELECT rolname FROM pg_roles;
rolname
----------------------
nolog_user
log_user
要点
CREATE ROLE及其对应的DROP ROLE是实现创建和删除角色的常用命令。
ALTER ROLE处理更改角色属性的操作。
由于在数据库群集级别定义，角色在所有数据库中都有效(Roles are valid within all databases due to definition at the database cluster level.)。
请记住，创建以特殊字符开头的角色名需要用双引号("")来标记。
角色及其权限是使用属性是在建立时确定的。
要建立默认情况下需要LOGIN属性的角色，createuser是一个可选的命令。它们基本上是相等的，而不是CREATE ROLE username LOGIN。
-------------------------------
使用pg_hba.conf来建立连接
涵盖pg_hba.conf的所有方面一篇长文都不一定能讲清楚，这里的介绍可能遇到的常见问题的解决方法。
成功的连接需要双方整体来实现。在通过pg_hba.conf的设置后，连接到服务器的角色仍必须满足在数据库级别设置的访问限制。找到你的pg_hba.conf文件，在pg_settings视图上发出SELECT查询。必须以超级用户身份登录才能查询此视图。
postgres=# SELECT name, setting FROM pg_settings WHERE name LIKE '%hba%';
name | setting
----------+-------------------------------------
hba_file | /etc/postgresql/10/main/pg_hba.conf
pg_hba.conf文件包含指定给定连接请求的七种可用格式之一的记录。
对于包含多个用户的强化系统，这些设置不是最佳设置。此类型连接的字段包括：
local database user auth-method [auth-options]
各字段意义为：
local - connections are attempted with Unix-domain sockets.(尝试使用Unix域套接字进行连接)
database - Specifies the database(s) named for this record match.(指定为此记录匹配项命名的数据库)
user - The database user name matched for this record. A comma-separated list of multiple users or all is allowed for this field as well.(与此记录匹配的数据库用户名，此字段也允许使用逗号分隔的多个用户或所有用户的列表)
auth-method - Is used when a connection matches this unique record. The possible choices for this field is:(当连接与此唯一记录匹配时使用，此字段的可能选择是)
trust,reject,scram-sha-256,md5,password,gss,sspi,ident,peer,ldap,radius,cert,pam,bsd
为角色nolog_user、log_user两位的可能记录为：
local all nolog_user password
local all log_user password
注意：由于密码以明文形式发送，因此不应在不受信任的网络或不可信环境中使用。
pg_hba_file_rules视图中的三个相关的列，其中包含以下查询。同样需要SUPERUSER属性来查询此视图。
postgres=# SELECT database, user_name, auth_method FROM pg_hba_file_rules WHERE CAST(user_name AS TEXT) LIKE '%log_user%';
database | user_name | auth_method
----------+--------------+-------------
{all} | {nolog_user} | password
{all} | {log_user} | password
psql -U nolog_user -W postgres
Password for user nolog_user:
psql: FATAL: role "nolog_user" is not permitted to log in
psql -U log_user -W postgres
Password for user log_user:
psql (10.1)
Type "help" for help.
这里的关键是，虽然nolog_user和log_user都可以根据pg_hba.conf文件，只有log_用户才允许实际登录。
当log_user通过数据库级访问限制时(通过具有LOGIN属性)，nolog_user没有。
编辑pg_hba.conf文件中的log_user并更改允许此角色访问的数据库名称。这是一个变化，表明log_user现在只能登录到试用数据库。
local trial log_user password
首先，让我们尝试登录到postgres数据库，由于all标志，log_user以前有权访问该数据库。
$ psql -U log_user -W postgres
Password for user log_user:
psql: FATAL: no pg_hba.conf entry for host "[local]", user "log_user", database "postgres", SSL off
现在trial数据库log_user确实有权。
$ psql -U log_user -W trial
Password for user log_user:
psql (10.1)
Type "help" for help.
没有错误的话，trial=>提示符显示当前连接的数据库，一旦建立连接，这些设置也适用于服务器环境。
trial=> \c postgres;
Password for user log_user:
FATAL: no pg_hba.conf entry for host "[local]", user "log_user", database "postgres", SSL off
注意：通常，重新加载pg_hba.conf需要文件才能使更改生效。
使用pg_ctl工具来重载服务器：
pg_ctl reload [-D datadir] [-s]
要知道datadir在哪里，可以查询pg_settings系统视图，如以超级用户身份登录，使用类似的SELECT查询：
postgres=# SELECT setting FROM pg_settings WHERE name = 'data_directory';
setting
-----------------------------
/var/lib/pgsql/10/main
在postgres用户的shell下：
$ /usr/lib/postgresql/10/bin/pg_ctl reload -D /var/lib/pgsql/10/main
server signaled
检查服务器状态
$ /usr/lib/postgresql/10/bin/pg_ctl status -D /var/lib/pgsql/10/main
pg_ctl: server is running (PID: 6543)
/usr/lib/postgresql/10/bin/postgres "-D" "/var/lib/pgsql/10/main" "-c" "config_file=/etc/pgsql/postgresql.conf"
要点
角色必须通过两个条件：pg_hba.conf文件中的配置和数据库级访问权限。
pg_hba.conf对于每个连接请求，从上到下检查文件，因此文件中的顺序非常重要。
-------------------------------
Database, Table, and Column 权限和限制 - 为任务和职责定制合适的角色
为了让角色使用数据库对象(表、视图、列、函数等)，必须授予它们访问权限。 GRANT命令定义了这些基本特权。
创建数据库
因为log_user被授予CREATEDB和CREATEROLE属性，所以他可以使用这个角色来创建一个名为trial的测试数据库。
postgres=> CREATE DATABASE trial:
现创建一个新的ROLE:
postgres=> CREATE ROLE db_user WITH LOGIN PASSWORD 'freeoapwd';
将log_user连接到新的试用数据库：
postgres=> \c trial;
Password for user log_user:
You are now connected to database "trial" as user "log_user".
trial=>
注意：提示符更改为名称'trial'，表示我们已连接到该数据库。
用logunuser创建一个模拟表：
trial=> CREATE TABLE another_workload(
trial(> id INTEGER,
trial(> first_name VARCHAR(20),
trial(> last_name VARCHAR(20),
trial(> sensitive_info TEXT);
log_user用户最近创建了一个助手角色db_user。我们要求db_user对表another_workload具有有限的权限。
毫无疑问，此角色不应访问敏感的“信息”列。此时也不应授予INSERT、UPDATE和DELETE命令，除非db_user遇到一些特殊情况。
但需要db_user发出SELECT查询，如何在另一个another_workload表中限制这些角色的能力？
首先，让我们在表级别检查PostgreSQL GRANT命令文档中的确切语法：
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
[, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
ON [ TABLE ] table_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
接下来，我们用特定的语法实现为db_user角色设置的权限需求。
trial=> GRANT SELECT (id, first_name, last_name) ON TABLE another_workload TO db_user;
GRANT
注意，在SELECT关键字之后，我们列出了db_user可以访问的列。在更改之前，如果db_user尝试在sensitive_info列上选择查询，或任何其它与此相关的命令，这些查询则不会执行。
在db_user登录之后，就可以尝试使用SELECT查询返回表中的所有列和记录。
trial=> SELECT * FROM another_workload;
ERROR: permission denied for relation another_workload
此查询中包含列sensitive_info的信息。因此，不会将记录返回给db_user。 但只查询允许的列时却没有这个问题：
trial=> SELECT id, first_name, last_name FROM another_workload;
id | first_name | last_name
-----+------------+-----------
10 | John | Morris
191 | Jannis | Harper
2 | Remmy | Rosebuilt
可以测试一下INSERT, UPDATE, DELETE 等命令。
trial=> INSERT INTO another_workload(id,first_name,last_name,sensitive_info)
VALUES(17,'Jeremy','Stillman','key code:400Z');
ERROR: permission denied for relation another_workload
trial=> UPDATE another_workload SET id = 101 WHERE id = 10;
ERROR: permission denied for relation another_workload
trial=> DELETE FROM another_workload
trial-> WHERE id = 2;;
ERROR: permission denied for relation another_workload
要点
角色通过GRANT命令获得对数据库对象的访问权限。
针对这些数据库对象对象的命令在PostgreSQL环境中是可配置的。
小结
创建具有特定属性的角色。
设置客户端和服务器之间的可用连接，允许角色登录访问数据库。
通过实现必要的属性，高度定制角色，以满足数据库、表和列级别访问的个性化要求。
-------------------------------
权限之直接授权与默认权限的差别
直接的授权(GRANT)肯定与上面的默认授权(ALTER DEFAULT PRIVILEGES FOR)是有区别的，前者是针对当时的情况，不包括后面所创建的表对象这些；而后者也只有针对当时之后的情况，不包括之前的表等对象的权限。
于是出现了一个疑问：如何知道一个用户在schema下面是有默认的权限还是一次性赋予的权限？
实验：用户scaprw创建一个schema，
CREATE SCHEMA IF NOT EXISTS tsc;
GRANT USAGE ON SCHEMA tsc TO scapro;
GRANT USAGE ON SCHEMA tsc TO scaprx;
在其中创建一表1
CREATE TABLE tsc.tb1(
id serial2,ename varchar(9)
);
insert into tsc.tb1(ename) values ('htsc1') returning *;
其他用户访问时会报错：
错误: 42501: 对表 tb1 权限不够
位置: aclcheck_error, aclchk.c:3488
为用户scaprx赋予默认权限
scaprw>
ALTER DEFAULT PRIVILEGES IN SCHEMA tsc GRANT SELECT,INSERT ON TABLES TO scaprx;
ALTER DEFAULT PRIVILEGES IN SCHEMA tsc GRANT SELECT,USAGE ON SEQUENCES TO scaprx;
scaprx>select * from tsc.tb1;
错误: 42501: 对表 tb1 权限不够
为用户scapro赋予一次性权限
scaprw>
GRANT SELECT,INSERT,UPDATE ON ALL TABLES IN SCHEMA tsc TO scapro;
GRANT SELECT,USAGE,UPDATE ON ALL SEQUENCES IN SCHEMA tsc to scapro;
scapro>select * from tsc.tb1;
id | ename
----+-------
1 | htsc1
scapro>insert into tsc.tb1(ename) values ('htsc2') returning *;
id | ename
----+-------
2 | htsc2
INSERT 0 1
阶段结论：修改默认权限不能对此前的表产生作用，而Grant可以对此前的表产生作用。下面来测试对之后的表的读写情况：
scaprw>CREATE TABLE tsc.tb2(
id serial2,cname varchar(9)
);
scaprw>insert into tsc.tb2(cname) values ('测试1') returning *;
scaprx>select * from tsc.tb2;
id | cname
----+-------
1 | 测试1
当然对tb1依然报了权限访问错误。
scapro>select * from tsc.tb2;
错误: 42501: 对表 tb2 权限不够
位置: aclcheck_error, aclchk.c:3488
当然对tb1依然的可以访问的，更新也是可以的。
scapro>update tsc.tb1 set ename='change2' where id=1 returning *;
id | ename
----+---------
1 | change2
那么回到上面的问题：目前还真没有办法看出对schema下哪些是默认的授权，哪些是一次性授予的权限，不过这不是什么要紧的问题。
-------------------------------