postgresql 表权限查询

2004

　　hankdb_1=> grant usage on schema hank to select_hank;
hankdb_1=> \z tb1
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+------+-------+-------------------+--------------------------
hank | tb1 | table | |
(1 row)
　　hankdb_1=> grant select on all tables in schema hank to select_hank;
GRANT
hankdb_1=> \z tb1
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+------+-------+--------------------+--------------------------
hank | tb1 | table | hank=arwdDxt/hank +|
| | | select_hank=r/hank |


Access privileges 具体含义：
a: insert
r: select
w: update
d: delete
x: references
t: trigger
D: truncate
已经生效：
postgres=# \c hankdb_1 select_hank
hankdb_1=> select * from hank.zhh;
a
---
1
查询视图：
select relname,relacl from pg_class where relname='zhh';
hankdb=> \c hankdb hank
You are now connected to database "hankdb" as user "hank".
hankdb=> select relname,relacl from pg_class where relname='zhh';
relname | relacl
---------+----------------------------------------
zhh | {hank=arwdDxt/hank,select_hank=r/hank}
查看用户权限：
hankdb=> select * from INFORMATION_SCHEMA.role_table_grants where grantee='select_hank';
grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
---------+-------------+---------------+--------------+------------+----------------+--------------+----------------
hank | select_hank | hankdb | hank | tb2 | SELECT | NO | NO
hank | select_hank | hankdb | hank | zhh | SELECT | NO | NO
　　对未来新建表赋予相关权限：
　　hankdb=> \z zhh
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+------+-------+--------------------+--------------------------
hank | zhh | table | hank=arwdDxt/hank +|
| | | select_hank=r/hank |
(1 row)
　　hankdb=> \z tb2
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+------+-------+--------------------+--------------------------
hank | tb2 | table | hank=arwdDxt/hank +|
| | | select_hank=r/hank |
(1 row)
　　hankdb=> create table tb3(a int);
CREATE TABLE
hankdb=> \z tb3
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+------+-------+-------------------+--------------------------
hank | tb3 | table | |
(1 row)
　　hankdb=> alter default privileges in schema hank grant select on tables to select_hank;
ALTER DEFAULT PRIVILEGES
hankdb=> create table tb4(a int);
CREATE TABLE
hankdb=> \z tb4
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+------+-------+--------------------+--------------------------
hank | tb4 | table | hank=arwdDxt/hank +|
| | | select_hank=r/hank |
(1 row)
　　hankdb=> \z tb3
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+------+-------+-------------------+--------------------------
hank | tb3 | table | |
(1 row)
参考：http://www.postgresql.org/docs/9.2/static/sql-grant.html
     http://www.postgresql.org/docs/9.2/static/sql-alterdefaultprivileges.html