greenplum账号和权限管理

1. 关于greenplum权限说明

1. 关于greenplum权限说明

1.1. 注意：

Note: 您必须单独为每个对象授予权限。

例如，授予数据库上ALL权限，并不授予对该数据库中的对象的完全访问权限。它只授予数据库级别的（CONNECT、CREATE、TEMPORARY）到数据库本身的权限。

1.2. 基本概念

1.2.1. 基本概念

1、在GP中，Database（数据库）、Schema（模式）以及Role（角色）三者之间的关系如下所示：
一个数据库下可以有多个模式，一个模式只属于一个数据库。模式在GP中也被称为Namespace，不同数据库之间的模式没有关系，可以重名；
表、视图、索引、序列、函数必须属于一个模式；
一个文件空间可以有多个表空间，一个表空间只属于一个文件空间，文件空间和角色之间没有关系；
表空间和表时一对多的关系，一个模式下的表可以分布在多个表空间下；
除了文件空间之外，其他的权限管理都是通过角色来实现，在这些层次结构中，用户必须对上一层有访问权限才能够访问该层的内容；
2、什么是角色(role)
Role的组成：由用户(User)和组(Group)组成；
跟OS的role没有关系；
User通过Master节点登录和认证的；
Role是定义在GPDB系统级别的；
初始化SUPERUSERROLE：gpadmin。
3、角色与权限安全的最佳实践
保护系统gpadmin的用户；
为每个登录的User分配不同的角色；
使用组来管理权限从而实现管理组；
控制具备SUPERUSER属性的User数量。
GRANT ALL ON TABLE mytable TO admin;
=# GRANT ALL ON SCHEMA myschema TO admin;
=# GRANT ALL ON DATABASE mydb TO admin;

1.2.2. 概述

用户与角色在整个数据库中都是全局性的。
在安装数据库时已指定超级管理员，系统管理员，例如超级管理员:gpadmin
每个数据库的逻辑结构对象都有一个所有者，所有者默认拥有所有的权限，不需要重新赋予。
删除和任意修改它的权利不能赋予别人，为所有者固有，不能被赋予或撤销。
可以把操作该对象的权限赋予别人。
授权和撤销授权用命令GRANT REVOKE

1.2.3. 赋予权限的步骤总结

权限按如下几个层次进行管理

1.首先管理赋予在用户特殊属性上的权限
2.在数据库上的权限
3.在数据库中创建schema的权限
4.在模式中创建数据库对象的权限，表，索引等
5.表的增删改查的权限
6.操作表中某些字段的权限

1.2.4. 管理赋予在用户特殊属性上的权限

1.user的 Superuser与createuser属性不能同时拥有。
2.有superuser属性的用户实际可以创建库和创建用户，且nocreateuser nocreatedb 对superuser属性没有约束。
3.create role创建用户，alter role修改用户属性。删除用户drop role，同理删除数据库是drop database;
4.拥有资源的用户不能被drop，提示错误。但是资源可以被superuser drop掉。
修改用户属性用alter role

1.2.5. 用户对数据库对象操作权限列表

对象类型	权限
表、视图、序列	SELECT/INSERT/UPDATE/DELETE/RULE/ALL
外部表	SELECT/RULE/ALL
数据库	CONNECT/CREATE/TEMPORARY/TEMP/ALL
函数	EXECUTE
过程语言	USAGE
模式	CREATE/USAGE/ALL
自定义协议	SELECT/INSERT/UPDATE/DELETE/RULE/ALL

Note:您必须单独为每个对象授予权限。
例如，授予数据库上ALL权限，并不授予对该数据库中的对象的完全访问权限。它只授予数据库级别的（CONNECT、CREATE、TEMPORARY）到数据库本身的权限。

1.2.6. 用户和角色区别

角色概念把用户(user)和组(group)的概念包括在内。一个角色可能是一个数据库用户、一个组或者两者兼具。角色可以拥有数据库对象（例如表）并且可以那些对象上的特权分配给其他角色来控制对对象的访问。角色可以是其他角色的成员，因此一个成员角色能够继承其父角色的对象特权。

1.在greenplum后续版本中，已经将使用role取代了user，所以创建用户就是create role：
2.role可以分为登录角色和组角色，
3.CREATE ROLE创建的用户默认不带LOGIN属性，而CREATE USER创建的用户默认带有LOGIN属性
1. Greenplum数据库不支持行级访问或行级标记的安全性。可以使用视图来限制所选行的行来模拟行级访问。

1.2.7. 用户角色列表

属性	描述
SUPERUSER / NOSUPERUSER	确定角色是否为超级用户。您必须自己是超级用户才能创建新的超级用户。默认值是NOSUPERUSER。
CREATEDB / NOCREATEDB	确定是否允许角色创建数据库。默认值是NOCREATEDB。
CREATEROLE / NOCREATEROLE	确定是否允许角色创建和管理其他角色。默认值是NOCREATEROLE。
INHERIT / NOINHERIT	确定角色是否继承其所属角色的权限。具有INHERIT属性的角色继承可以自动使用已授予其直接或间接成员的所有角色的任何数据库权限。默认值是INHERIT。
LOGIN / NOLOGIN	确定是否允许角色登录。具有该LOGIN属性的角色可以被认为是用户。没有此属性的角色对于管理数据库权限（组）非常有用。默认值是NOLOGIN。
CONNECTION LIMIT connlimit	如果角色可以登录，则指定角色可以使用的并发连接数。默认值-1表示没有限制。
CREATEEXTTABLE / NOCREATEEXTTABLE	确定是否允许角色创建外部表。默认值是NOCREATEEXTTABLE。具有该CREATEEXTTABLE属性的角色，默认外部表类型是可读的，注意使用文件或执行的外部表只能由超级用户创建。
PASSWORD 'password'	设置角色的密码。如果您不打算使用密码身份验证，则可以省略此选项。如果未指定密码，则密码将设置为空，并且该用户的密码验证将始终失败。可以选择将空密码明确写为PASSWORD NULL。
ENCRYPTED / UNENCRYPTED	控制是否将新密码在pg_authid系统目录中存储为哈希字符串。如果没有指定ENCRYPTED，或者指定UNENCRYPTED，则默行为由password_encryption配置参数决定，该参数默认值为on。如果提供password字符串已经是哈希格式，无论是否指定ENCRYPTED或UNENCRYPTED都原样存储。有关保护登录密码的其他信息，参阅保护Greenplum数据库中的密码。
VALID UNTIL 'timestamp'	设置角色密码失效的日期和时间。如果省略，密码将始终有效。
RESOURCE QUEUE queue_name	将角色分配给指定的资源队列以进行工作负载管理。任何角色问题的声明都受资源队列限制的约束。注意RESOURCE QUEUE属性不可继承；必须为每个用户级别（LOGIN）角色分别设置。
DENY	限制时间间隔期间的访问，按日期或日期时间指定。更多信息，参阅基于时间的身份验证。

1.2.8. 角色成员

将用户组合在一起以便于管理对象权限通常很方便：这样，可以将权限授予整个组或从组中撤销。在Greenplum数据库中，通过创建表示组的角色，然后将组角色的成员身份授予单个用户角色来完成的。

使用CREATE ROLE此SQL创建一个新的组角色。例如：

=# CREATE ROLE admin CREATEROLE CREATEDB;

一旦组角色存在后, 可以使用GRANT和REVOKE命令，来添加和删除成员（用户角色）。例如：

=# GRANT admin TO john, sally;
=# REVOKE admin FROM bob;

为了管理对象权限，您只能为组级角色授予适当的权限（参阅Table 2）。然后，成员用户角色将继承组角色的对象权限。例如：

=# GRANT ALL ON TABLE mytable TO admin;
=# GRANT ALL ON SCHEMA myschema TO admin;
=# GRANT ALL ON DATABASE mydb TO admin;

角色属性LOGIN、SUPERUSER、CREATEDB、 CREATEROLE、CREATEEXTTABLE和RESOURCE QUEUE 永远不会像数据库对象上的普通权限那样被继承。用户成员实际上必须SET ROLE具有这些属性的特定角色，才能使用该属性。在上面的例子中，我们给出了CREATEDB和CREATEROLE到了admin角色。如果sally是admin角色的成员，她可以发出以下命令来承担父角色的角色属性：

=> SET ROLE admin;

1.3. 2 权限操作实例

1.3.1. 在用户(USER)特殊属性上的权限

1.3.1.1. 权限说明

1、user的 Superuser与createuser属性不能同时拥有。
2、有superuser属性的用户实际可以创建库和创建用户，且nocreateuser nocreatedb 对superuser属性没有约束。
3、create role创建用户，alter role修改用户属性。删除用户drop role，同理删除数据库是drop database;
4、拥有资源的用户不能被drop，提示错误。但是资源可以被superuser drop掉。 级联删除drop user username cascade;
5、修改用户属性用alter role

1.3.1.2. 权限创建实例

create role user1 with login password '123456';

1.3.2. 总结数据库上权限

1、实际上在数据库方面控制的权限有CREATE,CONNECT,TEMP,即使把这些权限全部取消了，仍可以有CONNECT和创建临时表(TEMP)的权限。

2、属性（nosuperuser,nocreatedb,nocreaterole）的用户默认情况下可以connect数据库。
不可以创建schema。可以创建temporary table ，自动生成临时的schema，在会话结束后自动销毁。可以在public schema中创建表。不能在owner为其他用户的schema下创建表。

3、数据库的CREATE权限，控制是否可以在库中创建schema,以及是否可以在schema下创建表与查询表中的数据。

4、通过身份验证的用户总有CONNECT库的权限。即使是通过REVOKE撤销CONNECT，也能正常连接数据库。

5、用户总有创建TEMP表的权限。即使是通过REVOKE撤销TEMP，也能创建临时表。

1.3.3. 在SCHEMA(模式)上的权限

总结SCHEMA(模式)上的权限汇总

1、如果要在别人的schema中创建自己的表，需要用户对该shema有CREATE，USAGE权限，才可以对表和数据有足够权限。
2、用户默认无法在owner为别个用户的schema中创建表。
3、用户默认无法看到owner为别个用户的schema中的表，注意设置search_path 。（\dt命令查看）。
4、赋予USAGE权限后可以看到owner为别个用户的schema中的表，但无法在里面创建表。
5、赋予CREATE权限后可以在别个用户的schema中创建表，但如果没有USAGE权限，仍无法看到表，无法查询表中的数据，也无法更改表，即使owner也是不行。再赋予USAGE后可以查询自己创建的表，可以更改自己创建的表，但无法查询别人的表。
6、用户user1的schema信息无法分配给user2用户。

1.3.4. 在TABLE(表)上的权限

1.3.4.1. 权限参数详解

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
 [, ...] | ALL [ PRIVILEGES ] }
 ON { [ TABLE ] table_name [, ...]
 | ALL TABLES IN SCHEMA schema_name [, ...] }
 TO role_specification [, ...] [ WITH GRANT OPTION ]
 
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
 [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
 ON [ TABLE ] table_name [, ...]
 TO role_specification [, ...] [ WITH GRANT OPTION ]
 
GRANT { { USAGE | SELECT | UPDATE }
 [, ...] | ALL [ PRIVILEGES ] }
 ON { SEQUENCE sequence_name [, ...]
 | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
 TO role_specification [, ...] [ WITH GRANT OPTION ]
 
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
 ON DATABASE database_name [, ...]
 TO role_specification [, ...] [ WITH GRANT OPTION ]
 
GRANT { USAGE | ALL [ PRIVILEGES ] }
 ON DOMAIN domain_name [, ...]
 TO role_specification [, ...] [ WITH GRANT OPTION ]
 
GRANT { USAGE | ALL [ PRIVILEGES ] }
 ON FOREIGN DATA WRAPPER fdw_name [, ...]
 TO role_specification [, ...] [ WITH GRANT OPTION ]
 
GRANT { USAGE | ALL [ PRIVILEGES ] }
 ON FOREIGN SERVER server_name [, ...]
 TO role_specification [, ...] [ WITH GRANT OPTION ]
 
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
 ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...]
 | ALL FUNCTIONS IN SCHEMA schema_name [, ...] }
 TO role_specification [, ...] [ WITH GRANT OPTION ]
 
GRANT { USAGE | ALL [ PRIVILEGES ] }
 ON LANGUAGE lang_name [, ...]
 TO role_specification [, ...] [ WITH GRANT OPTION ]
 
GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
 ON LARGE OBJECT loid [, ...]
 TO role_specification [, ...] [ WITH GRANT OPTION ]
 
GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
 ON SCHEMA schema_name [, ...]
 TO role_specification [, ...] [ WITH GRANT OPTION ]
 
GRANT { CREATE | ALL [ PRIVILEGES ] }
 ON TABLESPACE tablespace_name [, ...]
 TO role_specification [, ...] [ WITH GRANT OPTION ]
 
GRANT { USAGE | ALL [ PRIVILEGES ] }
 ON TYPE type_name [, ...]
 TO role_specification [, ...] [ WITH GRANT OPTION ]
 
where role_specification can be:
 [ GROUP ] role_name
 | PUBLIC
 | CURRENT_USER
 | SESSION_USER
GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]

1.3.4.2. 用户批量赋权脚本

对单个表授权，可以使用 GRANT ALL ON TABLE {tablename} TO {username}，如果需要批量操作某个schema下所有表请使用以下函数进行赋权
创建函数

create or replace function grant_on_all_tables(schema text, usr text) 
returns setof text as $$
declare
 r record ; 
 grantstmt text; 
begin
 for r in select * from pg_class c, pg_namespace nsp
 where c.relnamespace = nsp.oid AND c.relkind='r' AND nspname = schema
 loop
 grantstmt = 'GRANT SELECT,UPDATE,DELETE,INSERT ON "'|| quote_ident(schema) || '"."' || 
quote_ident(r.relname) || '" to "' || quote_ident(usr) || '"'; 
 EXECUTE grantstmt; 
 return next grantstmt; 
 end loop; 
end;
$$ language plpgsql; 
schema text : 需要授权的schema名称。
usr text : 需要授权的role名称。
然后代码会遍历参数schema下的所有表，轮询的去做授权操作。

调用函数

select grant_on_all_tables('schema_name','user_name');
schema text : 需要授权的schema名称。
usr text : 需要授权的role名称。

1.3.4.3. 创建用户与修改用户密码方法

5.1 使用role方式创建

在管理员用户上创建以下role
create role user1 with login password '123456';

给用户限制连接数
alter user user1 CONNECTION LIMIT 20;

5.2 使用user方式创建

create user user_name;
alter user user_name with password '';
alter user user_namewith CONNECTION LIMIT 20;#连接数限制

1.3.5. 函数权限管理

6.1 通用语句

GRANT { EXECUTE | ALL [ PRIVILEGES ] }
 ON FUNCTION funcname ([type, ...]) [, ...]
 TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]

6.2 赋给用户权限

grant all on FUNCTION ods.sp_t_job_employ_jobinfo_etl(_date text) to

1.3.6. 生成自动赋权的语句

以下语句主要是查询出指定schema下的表在用户rolename没有权限的信息，会生成grant的授权语句，如果想自动赋权请定时刷新即可。
select 'grant select on'||nspname||'.'||relname||' to rolename ;' as grantsql
from pg_class a,pg_namespace b where relname not like '%_1—prt%'
and relkind = 'r' 
and has_table_privilege('rolename',a.oid,'select')='f' and a.relnamespace=b.oid
and nspname in ('pg_catalog','schemaname') and nspname not like '%pg_%' and relname like 'tablename';
或者使用以下语句
select 'grant '||privilege_type||' on schemaname.tablename  to '||grantee||';' from
information_schema.table_privileges
where table_schema='schemaname' and table_name='tablename'
group by grantee,privilege_type;
rolename: 角色的名字
schemaname : 制定需要查看schema的信息
tablename : 表的匹配信息

1.3.7. pg_hba.conf客户端登录权限

通过修改pg_hba.conf文件可以控制客户端登录数据库的权限
vi $MASTER_DATA_DIRECTORY/pg_hba.conf
pg_hba.conf文件配置参数为：
# local DATABASE USER METHOD [OPTIONS]
# host DATABASE USER CIDR-ADDRESS METHOD [OPTIONS]
# hostssl DATABASE USER CIDR-ADDRESS METHOD [OPTIONS]
# hostnossl DATABASE USER CIDR-ADDRESS METHOD [OPTIONS]
例如:
host  all all 0.0.0.0/0 md5
host : 链接host
all : 表示链接所有的数据库
all : 表示链接的所有的用户
0.0.0.0/0 : 允许所有的IP登录数据库
md5 : 允许登录的方式是md5加密方式
例如:
host  test_db all 192.168.253.3/32 md5
表示192.168.253.3地址的所有用户通过md5加密方式登录test_db数据库
使用gpstop -u 生效

1.3.8. 删除集群中赋权的用户

1.3.8.1. 撤销用户在数据库上的权限

-- 移除数据库的权限
revoke all on database databasename from username;
databasename ：数据库的名字
username : 角色的名字

1.3.8.2. 撤销用户在schema上的权限

-- 移除schema的权限
revoke all on schema schema1,schema2 from username;
schema1,schema2 : schema的集合，以逗号分开
username : 角色的名字

1.3.8.3. 撤销用户在table上的权限

select 'revoke all on '||table_schema||'.'||table_name||' from username cascade; ' from 
information_schema.table_privileges
where grantee='username';
username : 角色的名字
用此语句查询出revoke的语句，去执行即可

1.3.8.4. 撤销用户在function上的权限

-- 查询该用户的所属的函数
select * from information_schema.routine_privileges where grantee='username';
-- 移除权限
revoke all on function schemaname.functionname from username;
username : 角色的名字
使用第一个语句把该角色关于函数的语句查询出来，使用第二个语句撤销语句即可

1.3.8.5. 删除角色

drop role if exists username;
username : 角色的名字
cascade：强制删除

1.4. 系统模式简介：

pg_catalog模式存储系统日志表、内置类型、函数和运算符。
Information_schema模式由一个标准化视图构成。其中包含DB中对象的信息。
pg_toast模式是存储大对象（系统内部使用）。
pg_bitmapindex模式存储bitmap index对象（系统内部使用）。
pg_aoseg存储append-only表（系统内部使用）。
gp_toolkit是管理用的模式，可以查看和检查系统日志文件和其他系统信息。

1.5. 常用账号权限命令

greenplum 虽然是postgresql的衍生产品
但是语法上略有不同
类似 grant all on all tables in schema schemaname to someone 就不行
报错提示 ERROR: syntax error at or near "all"

注意：用户带中横线的需要加“”双引号

1.5.1. 批量给表授权：

select 'grant all on table ' || schemaname || '."' || tablename || '" to usertest;' from pg_tables
where schemaname = 'public'

1.5.2. 修改表所有者

select 'ALTER TABLE ' || schemaname || '."' || tablename || '" OWNER TO gpadmin;' from pg_tables
where schemaname = 'public'

1.5.3. 批量赋予视图权限

select 'grant all on ' || schemaname || '."' || viewname || '" to usertest;' from pg_views
where schemaname = 'public'

1.5.4. 修改视图所有者

select 'alter table ' || schemaname || '.' || viewname || ' OWNER TO "gkdb-datawarehouse-test";' from pg_views where schemaname = 'public';

部分函数的参数拼接有问题,需要手动处理

1.5.5. 函数授权

WITH tmp AS (SELECT 
"routines".specific_schema,
"routines".routine_name, 
COALESCE("parameters".parameter_name, '') AS parameter_name, -- COALESCE返回参数中的第一个非null的值
COALESCE("parameters".udt_name, '') AS udt_name,
COALESCE("parameters".parameter_name, '') || ' ' || COALESCE("parameters".udt_name, '') AS params,
"parameters".ordinal_position 
FROM "information_schema"."routines"
LEFT JOIN "information_schema"."parameters" ON "routines".specific_name="parameters".specific_name 
WHERE "routines".specific_schema='public' 
ORDER BY 1,2,6) 
SELECT 
specific_schema, 
routine_name, 
string_agg(params, ',') AS params,  
'grant all on FUNCTION '||  '"'||specific_schema||'"."'||routine_name||'"('||string_agg(params, ',')||')' || ' to "gkdb-datawarehouse-bip-rw";'  AS fname 
FROM tmp GROUP BY specific_schema, routine_name;

1.5.6. 修改所有者：

WITH tmp AS (SELECT 
"routines".specific_schema,
"routines".routine_name, 
COALESCE("parameters".parameter_name, '') AS parameter_name, -- COALESCE返回参数中的第一个非null的值
COALESCE("parameters".udt_name, '') AS udt_name,
COALESCE("parameters".parameter_name, '') || ' ' || COALESCE("parameters".udt_name, '') AS params,
"parameters".ordinal_position 
FROM "information_schema"."routines"
LEFT JOIN "information_schema"."parameters" ON "routines".specific_name="parameters".specific_name 
WHERE "routines".specific_schema='public' 
ORDER BY 1,2,6) 
SELECT 
specific_schema, 
routine_name, 
string_agg(params, ',') AS params,  
'ALTER FUNCTION '||  '"'||specific_schema||'"."'||routine_name ||'"('||string_agg(params, ',')||')' || ' owner to "gkdb-datawarehouse-test";'  AS fname 
FROM tmp GROUP BY specific_schema, routine_name;

其他解决方案：
1.创建触发器，或者存储过程，在需要使用的时候定时调度
2.创建角色，让后将角色授予用户，每次只需要更新角色的权限即可

1.6. 案例：

1.6.1. 新建一个超级用户：

create user xulin superuser createdb createrole password 'xulin' login;

修改用户密码：
alter user/role xulin with password 'xulin';

2、授予该用户管理监控实例的权限(with grant option 允许该用户授予其他用户相同的权限)

grant all on database gpperfmon to xulin with grant option;

让他成为gpmon的一个成员（可选，应为他已经可以操作gpperfmon数据库了）

grant gpmon to xulin;

3、修改配置文件
找到master安装目录下的 pg_hba.conf 文件，目录如下：

/home/gpadmin/gpdata/gpmaster/gpseg-1 

vi pg_hba.conf  
local    gpperfmon         xulin         md5                    //用来管理监控实例
host     all         xulin         127.0.0.1/28    md5          //表示本地登录

注意：在使用windows版本的gpload的时候通常可使用trust的方式或者在windows客户端修改环境变量把gmon的密码写进去，才能保证使用gpload的时候不用输入密码

4、生效
在master 执行gpstop -u重新加载pg_hba.conf文件，并不会shutdown Greenplum系统。

$ gpstop -u

5、验证

$ psql -h 192.168.1.171 -p 5432 -d postgres -U gpadmin -W 
Password for user gpadmin:

或者
psql -h 192.168.1.60 -p 5432 -d postgres -U testusr -W

1.6.2. 创建一个非超级用户

例2：创建一个只可以查看testdb数据库下的testtb表的一个用户testusr.

create user testusr password 'testusr';

2、授予他管理test数据库下的testtb表的权限（表、视图，函数，触发器、序列、存储过程这些全部都需要一个个单独授权，值得吐槽）

grant all on table testtb to testusr;
grant all on table testtb to testusr;

3、修改配置文件

vi pg_hba.conf
gpstop -u

5、要达到密码登录需要再使用：
alter role/user testuser with password 'testusr'修改之后才能成功使用密码登录

1.6.3. 创建一个组

创建一个组，并授权查询表和视图权限，然后将这个组的权限授权给用户
首先创建webreaders组，然后授予该组对pages的select权限，对viewlog的insert权限，最后把tim，bob加入到webreaders组后，tim和bob就都拥有pages的select权限和viewlog的insert权限。
修改webreaders组对组内成员的权限设定都有效。

GRANT INSERT, UPDATE, DELETE ON comments TO webreaders;

CREATE GROUP webreaders;
GRANT SELECT ON pages TO webreaders;
GRANT INSERT ON viewlog TO webreaders;
GRANT webreaders TO tim, bob;

授予用户对SCHEMA 的权限

GRANT SELECT ON ALL TABLES IN SCHEMA staging TO bob;

1.7. 使用schema来管理用户的权限：

这个和oracle schema一样的用途，用来逻辑组织数据库对象的存放容器，不同schema不共享命名空间。
schema public是在创建数据库之后就会默认创建的，每个用户都有权限在这个schema创建对象，
如果不指定schema那么就会默认创建到这里。创建schema后最好需要修改默认的搜索路径，否则每次登陆都必须指定路径才能使用创建的schema。

1、创建schema并指定他的所有者

create schema test authorization test;

2、修改用户的搜索路径：

alter role zhangsan set search_path=test;

3、授予用户对schema下表的查看和usage的权限

grant all on schema test to zhangsan;

4、查看用户当前搜索路径和当前schema

show search_paht;
select current_schema();

注意两者并非同一个概念
必须要两个都设置才能查看到对应模式下的表的信息

5、在对应的模式下创建表

create table test.t(id int);

1.8. 推荐权限管理

业务账号创建：业务账号推荐直接将角色gpadmin的权限全部授予，这样账号可以自动获取到数据库所有权限

CREATE ROLE "gkdb-datawarehouse-bip-rw" SUPERUSER LOGIN;
UPDATE pg_authid SET rolcatupdate=TRUE WHERE rolname ="gkdb-datawarehouse-bip-rw";
GRANT "gpadmin" TO "gkdb-datawarehouse-bip-rw";

个人账号：

create user "gk-yyh-test-r" password 'YYHsfh2022';
grant all on database datawarehouse_test to "gkdb-datawarehouse-test-rw" ;

推荐创建read_only 和read_write组，每次授权只需要为这两个组授权即可

CREATE GROUP read_only;
CREATE GROUP read_write;

GRANT SELECT ON pages TO read_only;
GRANT SELECT/INSERT/UPDATE/DELETE/RULE/ALL ON table TO read_write;

GRANT webreaders TO tim, bob;

posted @ 2023-06-21 14:11 数据库小白（专注）阅读(1443) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

数据库小白（专注）