postgres权限赋权以及search_path设置

#pg权限列表
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 ]

验证两个问题：

1 使用gpadmin创建的schema，其他用户怎么访问，怎么赋权

2 使用dbuser创建schema，自己怎么查询，是否需要赋权

准备工作：

#pg有两个用户gpadmin,dbuser

#1 创建数据库
postgres=# create database spdb;
CREATE DATABASE

#2 创建dbuser用户

spdb=# CREATE  USER  dbuser  LOGIN  PASSWORD 'dbuser';

# 赋予dbuser操作数据库的权限
spdb=# grant all on database spdb to dbuser;
GRANT

验证问题1：

spdb=# \c
You are now connected to database "spdb" as user "gpadmin".
#1 使用gpadmin创建shema以及表
spdb=# create schema shm1;
CREATE SCHEMA
spdb=# create table shm1.t(id int);

#2 使用gpadmin去查询:可以使用gpadmin
spdb=# select * from shm1.t;
 id 
----
(0 rows)

#3 使用dbuser去查询：没有权限查询
spdb=# \c - dbuser
You are now connected to database "spdb" as user "dbuser".
spdb=> select * from shm1.t;
ERROR:  permission denied for schema shm1

#4 切换回gpadmin授权，必须两次赋权
spdb=> \c - gpadmin
You are now connected to database "spdb" as user "gpadmin".
spdb=# grant all on schema shm1 to dbuser;
GRANT
spdb=# grant all on all tables in  schema shm1 to dbuser;
GRANT

#5 可以查询
spdb=# \c - dbuser
You are now connected to database "spdb" as user "dbuser".
spdb=> select * from shm1.t;
 id 
----
(0 rows)

结论：A用户创建的schema，B用户要访问其下面的表，需要A用户先授权schema给B，再授权schema下的表给B

验证问题2：

spdb=> \c
You are now connected to database "spdb" as user "dbuser".
#1 创建schema
spdb=> create schema shm2;
CREATE SCHEMA
#1 dbuser创建表
spdb=> create table shm2.t(id int);
#2 访问表：可以访问
spdb=> select * from shm2.t;
 id 
----
(0 rows)

结论：A用户创建的schema，自己可以访问(如果不想使用shm2这种前缀访问，需要设置search_path)

设置search_path:

spdb=> alter  role dbuser  set  search_path to "$user",shm2,public;
ALTER ROLE
#重新登陆，才能刷新权限
[gpadmin@mdw ~]$ psql -d spdb -U dbuser
spdb=> select * from t;
 id 
----
(0 rows)

注:search_path会根据设置的路径往下寻找表所在的schema(第一个schema优先),如果不同schema的存在表名相同，会取第一个schema的表，这点需要注意。