PostgreSQL批量修改对象owner

PostgreSQL批量修改对象owner

创建测试数据

psql -U postgres
create user test password 'test';
create database testdb;
\c testdb
CREATE SCHEMA test AUTHORIZATION postgres;

创建对象

export PGPASSWORD=test
psql -U postgres -d testdb
set search_path to test;
-- 创建数据类型
CREATE TYPE human_sex AS ENUM ('male', 'female');
CREATE TYPE test_type AS (f1 int, f2 text);
create domain phone_type as text constraint phone_check check (VALUE ~ '^\d{11}$');
CREATE TYPE float8_range AS RANGE (subtype = float8, subtype_diff = float8mi);
CREATE TYPE box;


-- 创建序列
create sequence employees_s;
create sequence employees_s1;
-- 创建测试表
create table test_t1(id int);
create table test_t2(id int);
create table test_t3(id int);
create table test_t4(id int);
create table employees
    ( employee_id    int8    primary key
    , first_name     varchar(20)
    , last_name      varchar(25)
    , sex            human_sex
    , email          varchar(25)
    , phone_number   phone_type
    , salary         numeric(8,2)
    , last_update_date timestamp
    , constraint     emp_salary_min check (salary > 0) 
    , constraint     emp_email_uk unique (email)
    ) ;
-- 插入数据
insert into employees values(nextval('employees_s'),'King','Johnn','male','johnn@163.com','15145264084',10000,now());
-- 以下两条应该报错
insert into employees values(nextval('employees_s'),'Job','Lucy','female','lucy@163.com','151452640841',10000,now());
insert into employees values(nextval('employees_s'),'Job','Lucy','females','lucy@163.com','15145264084',10000,now());
-- 创建索引
create index idx_name on employees(first_name,last_name);

视图

-- 普通视图
create view emp as select * from employees;
-- 物化视图
create materialized view emp_v as select * from employees;
refresh materialized view emp_v;

创建触发器

-- 普通触发器
CREATE OR REPLACE FUNCTION update_time_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.last_update_date := CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_time_trigger BEFORE UPDATE ON employees FOR EACH ROW EXECUTE FUNCTION update_time_column();

-- 事件触发器
CREATE OR REPLACE FUNCTION DISABLE_DROP_TABLE()
RETURNS event_trigger AS $$
BEGIN
    if tg_tag = 'DROP TABLE'  THEN
        RAISE EXCEPTION 'Command % is disabled.', tg_tag;
    END if;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER DISABLE_DROP_TABLE on ddl_command_start EXECUTE FUNCTION DISABLE_DROP_TABLE();

函数和存储过程

-- 函数
CREATE FUNCTION inc(val integer) RETURNS integer AS $$
BEGIN
  RETURN val + 1;
END;
$$ LANGUAGE PLPGSQL;

CREATE FUNCTION inc(val1 integer,val2 integer) RETURNS integer AS $$
BEGIN
  RETURN val1 + val2;
END;
$$ LANGUAGE PLPGSQL;


-- 存储过程
CREATE OR REPLACE PROCEDURE test_sum(a NUMERIC,b NUMERIC,C NUMERIC)
AS $$
DECLARE
  val int;
BEGIN
  val := a+b+c;
  RAISE NOTICE 'Total is : % !',val;
END;
$$ language plpgsql;


CREATE OR REPLACE FUNCTION sample_function(
    IN IN_param INTEGER, 
    OUT OUT_param INTEGER, 
    INOUT INOUT_param INTEGER
)
AS $$
BEGIN
    OUT_param := IN_param * 2;
    INOUT_param := INOUT_param + 5;
    RAISE NOTICE 'IN_param: %, OUT_param: %, INOUT_param: %', IN_param, OUT_param, INOUT_param;
END;
$$ LANGUAGE plpgsql;



CREATE OR REPLACE PROCEDURE sample_procedure(
    IN IN_param INTEGER, 
    INOUT INOUT_param INTEGER
)
LANGUAGE plpgsql AS $$
BEGIN
    INOUT_param := IN_param + 10;
    RAISE NOTICE 'IN_param: %, INOUT_param: %', IN_param, INOUT_param;
END;
$$;

对象信息查询

-- 数据库
select d.datname as "database",
        pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
		d.datacl AS "Access privileges"
FROM pg_catalog.pg_database d where datname = 'testdb';
-- 模式
SELECT n.nspname AS "schema",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner",
  n.nspacl AS "Access privileges"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
-- 域
select t2.nspname,t1.typname,t3.usename as owner from pg_type t1
  join pg_namespace t2 on t1.typnamespace=t2.oid
  join pg_user t3 on t1.typowner=t3.usesysid
where t1.typtype ='d'
    and t2.nspname='test';
-- 数据类型
SELECT t.typname,
       t.typtype,
       t.typowner::regrole as owner,
       t.typnamespace::regnamespace AS schema_name,
       t.typacl AS access_privileges
FROM pg_type t
LEFT JOIN pg_class c ON c.reltype = t.oid
WHERE 1=1
  -- and t.typtype IN ('c', 'e', 'd', 'b')  -- 仅筛选用户自定义类型
  -- AND n.nspname NOT IN ('pg_catalog', 'information_schema','pg_toast')  -- 排除系统命名空间
  and t.typnamespace::regnamespace::text = 'test'
  AND (c.relkind IS NULL or c.relkind = 'c')  -- 排除表、视图和序列
  and left(t.typname,1) <> '_'
;

-- 对象
select t2.nspname as "schema",
       t1.relname as "object_name",
       case when t1.relkind='r' then '普通表'
            when t1.relkind='i' then '索引'
            when t1.relkind='S' then '序列'
            when t1.relkind='v' then '视图'
            when t1.relkind='m' then '物化视图' end
        as "object_type",
			 t3.usename as "owner",
			 t1.relacl
from pg_class t1,pg_namespace t2,pg_user t3
where t1.relnamespace=t2.oid
and t2.nspname = 'test'
and t1.relowner=t3.usesysid;
-- 约束
select t2.nspname as schema,t3.relname as table,t1.conname,
       case when t1.contype='c' then '检查约束'
            when t1.contype='p' then '主键约束'
            when t1.contype='u' then '唯一约束' end as contype,
       t4.relname as index
from pg_constraint t1 join pg_namespace t2 on t1.connamespace = t2.oid
     join pg_class t3 on t1.conrelid = t3.oid
     left join pg_class t4 on t1.conindid = t4.oid
where t2.nspname = 'test';
-- 普通触发器
select t3.nspname as schema,t2.relname,tgname from pg_trigger t1
  join pg_class t2 on t1.tgrelid=t2.oid
  join pg_namespace t3 on t2.relnamespace=t3.oid
 where t3.nspname = 'test';
-- 事件触发器
select t1.evtname,t2.usename from pg_event_trigger t1
  join pg_user t2 on t1.evtowner = t2.usesysid;
-- 函数/存储过程
select t2.nspname as schema,t1.proname,t3.usename as owner,
       case when t1.prokind='f' then '函数'
            when t1.prokind='p' then '存储过程' end as prokind
from pg_proc t1
  join pg_namespace t2 on t1.pronamespace=t2.oid
  join pg_user t3 on t3.usesysid = t1.proowner
where t2.nspname = 'test';

注意：约束、索引、表上的触发器（普通触发器）是和表关联的，只用修改表的属主即可

批量修改对象owner

针对数据库和模式

alter database testdb owner to test;
alter schema test owner to test;

针对其他对象

DO $$ << change_owner >>
DECLARE
    new_owner  text := 'test';   -- 变更后的对象owner
	cur_schema text :='test';    -- 要修改的schema
    table_names text;
    sequence_names text;
    view_names text;
    mview_names text;
    function_names text;
    procedure_names text;
    types_names text;
BEGIN
    -- 修改表的owner
    FOR table_names IN SELECT table_name FROM information_schema.tables WHERE table_schema = cur_schema AND table_type = 'BASE TABLE' LOOP
        EXECUTE 'ALTER TABLE '|| cur_schema || '.' || table_names || ' OWNER TO '|| new_owner;
    END LOOP;
    -- 修改序列的owner
    FOR sequence_names IN SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema = cur_schema LOOP
        EXECUTE 'ALTER SEQUENCE '|| cur_schema || '.' || sequence_names || ' OWNER TO '|| new_owner;
    END LOOP;
    -- 修改视图的owner
    FOR view_names IN SELECT table_name FROM information_schema.views WHERE table_schema = cur_schema LOOP
        EXECUTE 'ALTER VIEW '|| cur_schema || '.' || view_names || ' OWNER TO '|| new_owner;
    END LOOP;
    -- 修改物化视图的owner
    FOR mview_names IN select matviewname from pg_matviews where schemaname = cur_schema LOOP
        EXECUTE 'ALTER MATERIALIZED VIEW '|| cur_schema || '.' || mview_names || ' OWNER TO '|| new_owner;
    END LOOP;
    -- 修改数据类型的owner
    FOR types_names in SELECT t.typname FROM pg_type t LEFT JOIN pg_class c ON c.reltype = t.oid where t.typnamespace::regnamespace::text = cur_schema AND (c.relkind IS NULL or c.relkind = 'c') and left(t.typname,1) <> '_'
 loop
        EXECUTE 'ALTER type '|| cur_schema || '.' || types_names || ' OWNER TO '|| new_owner;
	END LOOP;
END change_owner$$;

针对函数和存储过程

/* 【PostgreSQL】PostgreSQL生成批量修改函数或存储过程owner的脚本 */
with cons as (
  -- 修改下面的 “schema名字”、“修改为的owner名字” 即可
  select 'schema名字' as schemaname, '修改为的owner名字' as ownername
)
select 'alter ' || 
       case t.prokind
         when 'f' then 'function'
         when 'p' then 'procedure'
       end 
       || ' ' || t.proname || '(' || 
       (select string_agg(s1, ', ') 
          from (SELECT a || ' ' || b as s1
                  FROM unnest(t.proargmodes, 
                              t.proallargtypes) AS t(a, b)) tmp2) || 
       ') owner to ' || (select ownername from cons) || ';' as alter_function_owner_scripts
      ,t.proname
      ,t.proallargtypes as argtypes
      ,t.proargmodes
  from (select proname
              ,prokind
              ,(SELECT array(SELECT format_type(val, NULL)
                               FROM unnest(proallargtypes) as val)) as proallargtypes
              ,(SELECT array(SELECT case val
                                      when 'i' then 'IN'
                                      WHEN 'o' then 'OUT'
                                      WHEN 'b' then 'INOUT'
                                      WHEN 'v' then 'VARIADIC'
                                      WHEN 't' then 'TABLE' else '未知' 
                                    end 
                               FROM unnest(proargmodes) as val)) as proargmodes
          from pg_proc 
         where pronamespace::REGNAMESPACE::text = (select schemaname from cons)
           and prokind in ('f','p')
           and proname !~ '^dblink'
           and proallargtypes is not null) t
union ALL
select 'alter ' || 
       case t2.prokind
         when 'f' then 'function'
         when 'p' then 'procedure'
       end 
       || ' ' || t2.proname || '(' || 
       case 
         when (select string_agg(s1, ', ') 
                 from (SELECT a as s1
                         FROM unnest(t2.pro_arg_type) AS t(a)) tmp2) is null
           then ''
         else (select string_agg(s1, ', ')
                 from (SELECT a as s1
                         FROM unnest(t2.pro_arg_type) AS t(a)) tmp2)
       end || 
       ') owner to ' || (select ownername from cons) || ';' as s34
      ,t2.proname
      ,t2.pro_arg_type as argtypes
      ,null
  from (select proname
              ,prokind   
              ,proargtypes   
              ,(SELECT array(SELECT format_type(val, NULL)
                               FROM unnest(proargtypes) as val)) as pro_arg_type
          from pg_proc 
         where pronamespace::REGNAMESPACE::text = (select schemaname from cons)
           and prokind in ('f','p')
           and proname !~ '^dblink'
           and proallargtypes is null) t2
;