xxoo_db

--创建data、opr用户
create role xxoodataqa      login encrypted password 'xxxooo';
create role xxoodataqa_opr    login encrypted password 'xxxooo';


-- 创建数据库并指定属主
create database xxoo_db owner xxoodataqa;


-- 1.5.1以上版本需执行如下授权
\c xxoo_db
GRANT EXECUTE ON FUNCTION pg_background.pg_background_launch(sql pg_catalog.text,queue_size pg_catalog.int4)  TO public;
GRANT EXECUTE ON FUNCTION pg_background.pg_background_discard_result(pid pg_catalog.int4) TO public;
GRANT EXECUTE ON FUNCTION pg_background.pg_background_result(pid pg_catalog.int4) TO public;
GRANT EXECUTE ON FUNCTION pg_background.pg_background_detach(pid pg_catalog.int4) TO public;
GRANT ALL ON SCHEMA pg_background TO public;

-- opr用户授权public schema权限
\c xxoo_db
create extension pg_stat_statements;

-- 创建backup 模式
create schema if not exists backup AUTHORIZATION xxoodataqa;
grant usage, create on schema backup to xxoodataqa;

-- 多备份用户，授权如下
--grant usage, create on schema backup to xxoodataqa, xxoocardprd[,username];

--- opr用户授权默认权限，注意如应用使用的不是public schema,需将public替换
grant usage on schema public to xxoodataqa_opr;
grant select, insert, update, delete on all tables in schema public to xxoodataqa_opr;
alter default privileges for role xxoodataqa in schema public grant select, insert, update, delete on tables to xxoodataqa_opr;

grant execute on all functions in schema public to xxoodataqa_opr;
alter default privileges for role xxoodataqa in schema public grant execute on functions to xxoodataqa_opr;

grant all on all sequences in schema public to xxoodataqa_opr;
alter default privileges for role xxoodataqa in schema public grant all on sequences to xxoodataqa_opr;


-- 创建开发用户并授权
\c xxoo_db
create role devqry     login encrypted password 'xxxooo';
--devsup用户授权默认权限，注意如应用使用的不是public schema,需将public替换
grant usage on schema public to devqry;
grant select on all tables in schema public to devqry;
alter default privileges for role xxoodataqa in schema public grant select on tables to devqry;