近期公司有新入职小伙伴不小心在生产的用户上直接跑了ETL脚本，对于hawq的用户权限方面控制需要加强，查阅官方文档，发现hawq在用户的权限管理方面做得不是那么那么好，不能对库级别、或者schema级别的表做只读权限，只能单张表授权控制。
没办法，那就写shell脚本统一控制吧。

新建角色

创建访客角色Guest,只具有登陆权限

CREATE ROLE guest WITH LOGIN;
ALTER ROLE guest WITH PASSWORD '*********';

执行select pg_reload_conf()重新加载hawq配置

授权访问

配置pg_hba.conf，使外部client使用该用户登陆,并设置为password/md5的登陆方式

测试连接是否成功

根据元数据信息表编写脚本

--查询所有非系统库的database名称
SELECT ss.datname
FROM (
	SELECT d.datname, d.oid, pg_get_userbyid(d.datdba) AS owner, 
		shobj_description(d.oid, 'pg_database') AS comment, 
		t.spcname, d.datacl, d.datlastsysoid, d.encoding, pg_encoding_to_char(d.encoding) AS encodingname 
	FROM pg_database d LEFT 
	JOIN pg_tablespace t 
	ON d.dattablespace=t.oid
) ss 
WHERE ss.datname not in ('hcatalog','template1','template0','postgres','gpadmin')

--查询所有非系统schema
SELECT ss.Name from (
	SELECT n.nspname AS Name,pg_catalog.pg_get_userbyid(n.nspowner) AS Owner
	FROM pg_catalog.pg_namespace n
	WHERE (n.nspname !~ '^pg_temp_' OR n.nspname = (pg_catalog.current_schemas(true))[1])
	ORDER BY 1
) ss
WHERE ss.Name !='hawq_toolkit' and ss.Name !='information_schema' and ss.Name!~ '^pg_' and ss.Owner!='guest'

--查询当前库所有schema下的用户表和用户视图
SELECT ss.schema || '.' || ss.name as tableName,owner,type
FROM(
    SELECT n.nspname as schema,
    c.relname as name,
    CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as type,
    pg_catalog.pg_get_userbyid(c.relowner) as owner
    FROM pg_catalog.pg_class c
        LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind IN ('r','v','S','')
    AND c.relstorage IN ('h', 'a', 'c', 'p','x','f','v','')
        AND n.nspname <> 'pg_catalog'
        AND n.nspname <> 'information_schema'
        AND n.nspname <> 'hawq_toolkit'
        AND n.nspname !~ '^pg_toast'
    --AND pg_catalog.pg_table_is_visible(c.oid)  --当前模式
    ORDER BY 1,2
) ss
WHERE ss.owner!='guest';
-- 排除掉已经授权过的表
and ss.schema || '.' || ss.name not in (
		select table_schema|| '.' || table_name as tableName from information_schema.role_table_grants where grantee='guest' and privilege_type='SELECT'
	);

有了这3个相关元数据信息的SQL，就可以使用shell拼接grant语句进行遍历授权了
最终脚本就不放了，功能很容易实现
可以使用azkaban配置定时任务，

调用：