#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的表,这点需要注意。