PostgreSQL创建数据迁移专用账号

PostgreSQL创建数据迁移专用账号

一、用超管（postgres或者root）登录

• 创建数据迁移专用账号（common_etl）
  创建用户、给tzq schema授权能够使用、越过rls行级策略。

用超管（postgres、root）登录tzqdb，在tzq schema下，创建查询窗口执行下面语句：

CREATE USER common_etl WITH PASSWORD 'Hw13685@';
grant usage on schema tzq to common_etl;
alter user common_etl with bypassrls;

二、用普通用户（tzq）登录

• 普通用户（tzq）给专号（common_etl）授权
  • 授权tzq schema下所有表的查询权限
  • 在tzq schema里设置所有表的查询权限为默认权限
  • 授权tzq schema下所有序列的查询权限
  • 在tzq schema里设置所有序列的查询权限为默认权限
  • 视图要单独一个个授权，这里授权了一个视图tab_info_v（表信息视图）

用普通用户（tzq）登录tzqdb，在tzq schema下，给专号（common_etl）授权，执行下列语句：

grant select on all tables in schema tzq to common_etl;
alter default privileges in schema tzq grant select on tables to common_etl;
grant select on all sequences in schema tzq to common_etl;
alter default privileges in schema tzq grant select on sequences to common_etl;
grant select on tab_info_v to common_etl;

附件：表信息视图（tab_info_v）

CREATE or replace VIEW tab_info_v AS
 WITH schemainfo AS (
         SELECT pg_namespace.oid,
            pg_namespace.nspname
           FROM pg_namespace
        ), tbinfo AS (
         SELECT pg_class.oid,
            pg_class.relname,
            (col_description(pg_class.oid, 0))::character varying AS comment,
            pg_class.relkind,
            pg_class.relnamespace
           FROM pg_class
        ), colinfo AS (
         SELECT pg_attribute.attrelid,
            pg_attribute.attname,
            pg_attribute.attnum,
            (format_type(pg_attribute.atttypid, pg_attribute.atttypmod))::character varying AS typelen,
            (col_description(pg_attribute.attrelid, (pg_attribute.attnum)::integer))::character varying AS comment,
						CASE
						  when pg_attribute.attnotnull = 't' then 'NOT NULL' ELSE ''
						end AS attnotnull
           FROM pg_attribute
        )
 SELECT schemainfo.nspname AS schema,
    tbinfo.relname AS table_name,
    tbinfo.comment AS table_comment,
    colinfo.attnum AS column_number,
    colinfo.attname AS column_name,
        CASE
            WHEN ((colinfo.typelen)::text = 'bigint'::text) THEN 'INT8'::character varying
            WHEN ((colinfo.typelen)::text = 'smallint'::text) THEN 'INT2'::character varying
            WHEN ((colinfo.typelen)::text = 'integer'::text) THEN 'INT4'::character varying
            WHEN ((colinfo.typelen)::text = 'text'::text) THEN 'TEXT'::character varying
            WHEN ((colinfo.typelen)::text = 'oid'::text) THEN 'OID'::character varying
            WHEN ("left"((colinfo.typelen)::text, 17) = 'character varying'::text) THEN (replace((colinfo.typelen)::text, 'character varying'::text, 'VARCHAR'::text))::character varying
            WHEN ("left"((colinfo.typelen)::text, 9) = 'character'::text) THEN (replace((colinfo.typelen)::text, 'character'::text, 'CHAR'::text))::character varying
            WHEN ((colinfo.typelen)::text = 'timestamp without time zone'::text) THEN 'TIMESTAMP'::character varying
            WHEN ((colinfo.typelen)::text = 'timestamp(6) without time zone'::text) THEN 'TIMESTAMP'::character varying
            WHEN ((colinfo.typelen)::text = 'numeric(38,10)'::text) THEN 'NUMERIC(38,10)'::character varying
            ELSE colinfo.typelen
        END AS column_type,
	          colinfo.attnotnull,
    colinfo.comment AS column_comment
   FROM tbinfo,
    colinfo,
    schemainfo
  WHERE ((tbinfo.oid = colinfo.attrelid) AND (schemainfo.oid = tbinfo.relnamespace) AND (colinfo.attnum > 0) AND (tbinfo.relkind in ('r'::"char",'p'::"char")) AND (tbinfo.relname !~~ 'pg_%'::text) AND (tbinfo.relname !~~ 'sql_%'::text) AND (colinfo.attname !~~ '%.....pg.dropped%'::text))
  ORDER BY schemainfo.nspname, tbinfo.relname, colinfo.attnum
;