Last active
October 7, 2022 19:07
-
-
Save rogeruiz/ffe786ddbe2e6ae62db7df03b3e6f47a to your computer and use it in GitHub Desktop.
AWS DMS NonMasterUser documentation revisted
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- ███████╗██████╗ ██████╗ ███╗ ███╗ ████████╗██╗ ██╗███████╗ | |
-- ██╔════╝██╔══██╗██╔═══██╗████╗ ████║ ╚══██╔══╝██║ ██║██╔════╝ | |
-- █████╗ ██████╔╝██║ ██║██╔████╔██║ ██║ ███████║█████╗ | |
-- ██╔══╝ ██╔══██╗██║ ██║██║╚██╔╝██║ ██║ ██╔══██║██╔══╝ | |
-- ██║ ██║ ██║╚██████╔╝██║ ╚═╝ ██║ ██║ ██║ ██║███████╗ | |
-- ╚═╝ ╚═╝ ╚═╝ ╚═════╝ ╚═╝ ╚═╝ ╚═╝ ╚═╝ ╚═╝╚══════╝ | |
-- | |
-- ██████╗ ███████╗███████╗██╗ ██████╗██╗ █████╗ ██╗ █████╗ ██╗ ██╗███████╗ | |
-- ██╔═══██╗██╔════╝██╔════╝██║██╔════╝██║██╔══██╗██║ ██╔══██╗██║ ██║██╔════╝ | |
-- ██║ ██║█████╗ █████╗ ██║██║ ██║███████║██║ ███████║██║ █╗ ██║███████╗ | |
-- ██║ ██║██╔══╝ ██╔══╝ ██║██║ ██║██╔══██║██║ ██╔══██║██║███╗██║╚════██║ | |
-- ╚██████╔╝██║ ██║ ██║╚██████╗██║██║ ██║███████╗ ██║ ██║╚███╔███╔╝███████║ | |
-- ╚═════╝ ╚═╝ ╚═╝ ╚═╝ ╚═════╝╚═╝╚═╝ ╚═╝╚══════╝ ╚═╝ ╚═╝ ╚══╝╚══╝ ╚══════╝ | |
-- | |
-- ██████╗ ██████╗ ██████╗██╗ ██╗███╗ ███╗███████╗███╗ ██╗████████╗ █████╗ ████████╗██╗ ██████╗ ███╗ ██╗ | |
-- ██╔══██╗██╔═══██╗██╔════╝██║ ██║████╗ ████║██╔════╝████╗ ██║╚══██╔══╝██╔══██╗╚══██╔══╝██║██╔═══██╗████╗ ██║ | |
-- ██║ ██║██║ ██║██║ ██║ ██║██╔████╔██║█████╗ ██╔██╗ ██║ ██║ ███████║ ██║ ██║██║ ██║██╔██╗ ██║ | |
-- ██║ ██║██║ ██║██║ ██║ ██║██║╚██╔╝██║██╔══╝ ██║╚██╗██║ ██║ ██╔══██║ ██║ ██║██║ ██║██║╚██╗██║ | |
-- ██████╔╝╚██████╔╝╚██████╗╚██████╔╝██║ ╚═╝ ██║███████╗██║ ╚████║ ██║ ██║ ██║ ██║ ██║╚██████╔╝██║ ╚████║ | |
-- ╚═════╝ ╚═════╝ ╚═════╝ ╚═════╝ ╚═╝ ╚═╝╚══════╝╚═╝ ╚═══╝ ╚═╝ ╚═╝ ╚═╝ ╚═╝ ╚═╝ ╚═════╝ ╚═╝ ╚═══╝ | |
-- | |
-- 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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment