Greenplum数据库权限管理

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

$ psql -h 192.168.***.55 -U gpmon -d china***
在管理员用户上创建以下role
# create role user1 with login password '123456';
NOTICE: resource queue required -- using default resource queue "pg_default"
CREATE ROLE

$ psql -h 192.168.***.55 -U user1 -d china***
Password for user user1: 
psql (8.3.23)
Type "help" for help.
china***=>

登录到管理员账户删除刚创建的user1
auth_test=# drop role if exists user1;
DROP ROLE

$ psql -h 192.168.***.55 -U user2 -d auth_test
Password for user user2: 
psql: FATAL: password authentication failed for user "user2"

登录到管理员用户执行删除用户，需要把user1下的创建的相关信息全部删除掉才可删除给用户,或者使用cascade强制删除
auth_test=# revoke connect on database auth_test from user1;
REVOKE
auth_test=# drop user user1;
DROP ROLE
错误信息提示
auth_test=# drop user user1;
ERROR: role "user1" cannot be dropped because some objects depend on it
DETAIL: owner of append only file visibility map pg_aoseg.pg_aovisimap_2214714
owner of append only file segment listing pg_aoseg.pg_aoseg_2214714
owner of append only table schema1.test1

$ psql -h 192.168.***.55 -U user1 -d auth_test
Password for user user1: 
psql: FATAL: password authentication failed for user "user1"

gpadmin创建一个数据库，依次授予某用户 CREATE CONNECT TEMPORARY TEMP 的权限。验证各选项的作用。
创建用户user1 ,赋予对auth_test数据库CREATE权限，则可以在auth_test下创建schema;

在管理员的用户下创建以下数据库
# CREATE DATABASE auth_test;

登录到管理员账号把权限赋予user1
$ psql -h 192.168.***.55 -U gpmon -d auth_test
auth_test=# GRANT CREATE ON DATABASE auth_test TO user1;
GRANT

auth_test=> create schema schema1;
CREATE SCHEMA

创建test1并插入数据
auth_test=> create table schema1.test1(id int,name varchar) WITH (appendonly=true, compresstype=zlib, compresslevel=5) DISTRIBUTED BY (id);
CREATE TABLE
插入数据并查看数据
auth_test=> insert into schema1.test1(id,name) values(1,'1');
INSERT 0 1
auth_test=> select * from schema1.test1;
 id | name 
----+------
 1 | 1
(1 row)

权限包括CREATE,CONNECT,TEMP

# revoke connect on database auth_test from user1;
REVOKE

# revoke create on database auth_test from user1;
REVOKE

auth_test=> create schema schema2;
ERROR: permission denied for database auth_test
在以上可以看出用户已失去创建schema的权限，但还有以下的select权限，同理也可以撤销其他的权限。
auth_test=> select * from schema1.test1;
 id | name 
----+------
 1 | 1
(1 row)

# create role user2 with login password '123456';
NOTICE: resource queue required -- using default resource queue "pg_default"
CREATE ROLE

登录到user1账号下并把使用权限赋予user2
# grant usage on schema schema1 to user2;

登录到user2的用户下
$ psql -h 192.168.***.55 -U user2 -d auth_test
auth_test=> select * from schema1.test1;
ERROR: permission denied for relation test1

登录到user1用户下执行以下命令
auth_test=> grant create on schema schema1 to user2;
GRANT

在user2下查询user1的schema下的数据
auth_test=> select * from schema1.test1;
ERROR: permission denied for relation test1

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用户。

登录到管理员用户执行撤销权限
$ psql -h 192.168.***.55 -U gpmon -d auth_test
auth_test=# revoke select,update,delete,insert on schema1.test1 from user1;
REVOKE
auth_test=# revoke usage on schema schema1 from user1;
REVOKE
auth_test=# revoke create on schema schema1 from user1;
REVOKE
撤销时需要先从table到schema
强制删除schema
auth_test=# drop schema schema1 CASCADE;
NOTICE: drop cascades to table schema1.test1
DROP SCHEMA

在user1上验证权限问题
auth_test=> select * from schema1.test1;
ERROR: permission denied for schema schema1
LINE 1: select * from schema1.test1;

alter schema schema_test owner to xiaoxu;
schema_test : schema的名字
xiaoxu : 用户的名字

grant all on schema schema_test to xiaoxu;
schema_test : schema的名字
xiaoxu : 用户的名字

登录到user1用户，把schema1下的test1查询权限赋予user1
$ psql -h 192.168.***.55 -U user1 -d auth_test
auth_test=> grant select on schema1.test1 to user2;

登录到user2用户，查询user1的schema1下的表
$ psql -h 192.168.***.55 -U user2 -d auth_test
auth_test=> select * from schema1.test1;
 id | name 
----+------
 1 | 1
 2 | 2
 2 | 2
(3 rows)

在用户user1下操作一下命令
$ psql -h 192.168.***.55 -U user1 -d auth_test
auth_test=> grant select,update,delete,insert on schema1.test1 to user2;
GRANT

auth_test=> revoke create on schema schema1 from user2;
REVOKE

auth_test=> revoke select,update,delete,insert on schema1.test1 from user2;
REVOKE

登录到user2用户下查询user1下的数据
$ psql -h 192.168.***.55 -U user2 -d auth_test
auth_test=> select * from schema1.test1;
ERROR: permission denied for relation test1

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 ]

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名称。

在管理员用户上创建以下role
# create role user1 with login password '123456';
NOTICE: resource queue required -- using default resource queue "pg_default"
CREATE ROLE
给用户限制连接数
alter user user1 CONNECTION LIMIT 20;
user1:用户信息

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

alter role username with password 'password';
username : 用户信息
'password' : 用户密码

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

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

以下语句主要是查询出指定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 : 表的匹配信息

通过修改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 生效

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

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

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

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

drop role if exists username;
username : 角色的名字