AWS DMS NonMasterUser documentation revisted

dms_nonmasteruser.sql

-- ███████╗██████╗  ██████╗ ███╗   ███╗    ████████╗██╗  ██╗███████╗
-- ██╔════╝██╔══██╗██╔═══██╗████╗ ████║    ╚══██╔══╝██║  ██║██╔════╝
-- █████╗  ██████╔╝██║   ██║██╔████╔██║       ██║   ███████║█████╗
-- ██╔══╝  ██╔══██╗██║   ██║██║╚██╔╝██║       ██║   ██╔══██║██╔══╝
-- ██║     ██║  ██║╚██████╔╝██║ ╚═╝ ██║       ██║   ██║  ██║███████╗
-- ╚═╝     ╚═╝  ╚═╝ ╚═════╝ ╚═╝     ╚═╝       ╚═╝   ╚═╝  ╚═╝╚══════╝
--
--  ██████╗ ███████╗███████╗██╗ ██████╗██╗ █████╗ ██╗          █████╗ ██╗    ██╗███████╗
-- ██╔═══██╗██╔════╝██╔════╝██║██╔════╝██║██╔══██╗██║         ██╔══██╗██║    ██║██╔════╝
-- ██║   ██║█████╗  █████╗  ██║██║     ██║███████║██║         ███████║██║ █╗ ██║███████╗
-- ██║   ██║██╔══╝  ██╔══╝  ██║██║     ██║██╔══██║██║         ██╔══██║██║███╗██║╚════██║
-- ╚██████╔╝██║     ██║     ██║╚██████╗██║██║  ██║███████╗    ██║  ██║╚███╔███╔╝███████║
--  ╚═════╝ ╚═╝     ╚═╝     ╚═╝ ╚═════╝╚═╝╚═╝  ╚═╝╚══════╝    ╚═╝  ╚═╝ ╚══╝╚══╝ ╚══════╝
--
-- ██████╗  ██████╗  ██████╗██╗   ██╗███╗   ███╗███████╗███╗   ██╗████████╗ █████╗ ████████╗██╗ ██████╗ ███╗   ██╗
-- ██╔══██╗██╔═══██╗██╔════╝██║   ██║████╗ ████║██╔════╝████╗  ██║╚══██╔══╝██╔══██╗╚══██╔══╝██║██╔═══██╗████╗  ██║
-- ██║  ██║██║   ██║██║     ██║   ██║██╔████╔██║█████╗  ██╔██╗ ██║   ██║   ███████║   ██║   ██║██║   ██║██╔██╗ ██║
-- ██║  ██║██║   ██║██║     ██║   ██║██║╚██╔╝██║██╔══╝  ██║╚██╗██║   ██║   ██╔══██║   ██║   ██║██║   ██║██║╚██╗██║
-- ██████╔╝╚██████╔╝╚██████╗╚██████╔╝██║ ╚═╝ ██║███████╗██║ ╚████║   ██║   ██║  ██║   ██║   ██║╚██████╔╝██║ ╚████║
-- ╚═════╝  ╚═════╝  ╚═════╝ ╚═════╝ ╚═╝     ╚═╝╚══════╝╚═╝  ╚═══╝   ╚═╝   ╚═╝  ╚═╝   ╚═╝   ╚═╝ ╚═════╝ ╚═╝  ╚═══╝
--
-- README: Taken from this link here: https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.PostgreSQL.html#CHAP_Source.PostgreSQL.RDSPostgreSQL.NonMasterUser

SET ROLE master;

CREATE TABLE IF NOT EXISTS public.awsdms_ddl_audit
(
  c_key    bigserial primary key,
  c_time   timestamp,    -- Informational
  c_user   varchar(64),  -- Informational: current_user
  c_txn    varchar(16),  -- Informational: current transaction
  c_tag    varchar(24),  -- Either 'CREATE TABLE' or 'ALTER TABLE' or 'DROP TABLE'
  c_oid    integer,      -- For future use - TG_OBJECTID
  c_name   varchar(64),  -- For future use - TG_OBJECTNAME
  c_schema varchar(64),  -- For future use - TG_SCHEMANAME. For now - holds current_schema
  c_ddlqry  text         -- The DDL query associated with the current DDL event
);


CREATE OR REPLACE FUNCTION public.awsdms_intercept_ddl()
  RETURNS event_trigger
LANGUAGE plpgsql
SECURITY DEFINER
  AS $$
  declare _qry text;
BEGIN
  if (tg_tag='CREATE TABLE' or tg_tag='ALTER TABLE' or tg_tag='DROP TABLE') THEN
         SELECT current_query() into _qry;
         INSERT INTO public.awsdms_ddl_audit
         values
         (
         default,current_timestamp,current_user,cast(TXID_CURRENT()as varchar(16)),tg_tag,0,'',current_schema,_qry
         );
         DELETE FROM public.awsdms_ddl_audit;
end if;
END;
$$;

--- This needs to be run as the dms_export
BEGIN;
	DROP EVENT TRIGGER IF EXISTS awsdms_intercept_ddl;

	CREATE EVENT TRIGGER awsdms_intercept_ddl ON ddl_command_end
		EXECUTE PROCEDURE public.awsdms_intercept_ddl();
END;


--- All users and roles need access to the events
GRANT ALL ON public.awsdms_ddl_audit TO public;
GRANT ALL ON public.awsdms_ddl_audit_c_key_seq TO public;

RESET ROLE;