PG中的用户权限

-- 创建只读角色,名称为readonly_role
CREATE ROLE readonly_role;
-- 赋予readonly_role角色拥有public下所有表的只读权限(SELECT,如果是多个则是:SELECT, INSERT, UPDATE, DELETE)
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_role;
-- 赋予target_username用户拥有readonly_role角色权限
GRANT readonly_role TO target_username;
-- 赋予readonly_role角色的默认权限为public下的所有SELECT权限,以后创建新表时也会自动继承该权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_role;
-- 撤销用户所有权限
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM username;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM username;
REVOKE ALL PRIVILEGES ON SCHEMA public FROM username;
-- 撤销用户角色
REVOKE rolename FROM username;