ETL: From Supabase to RDS using Mage¶

CREATE USER mage WITH PASSWORD 'source_password'  -- replace password
    REPLICATION  -- for change data capture
    BYPASSRLS;  -- to bypass row level security

GRANT pg_read_all_data TO mage;  -- default postgres role for reading all data

GRANT USAGE ON SCHEMA public TO mage;  
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mage;  
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO mage;  

-- Set Default Permissions for New Objects in Public Schema
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO mage;  
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO mage;

SELECT pg_create_logical_replication_slot('mage_slot', 'pgoutput');

CREATE PUBLICATION mage_pub FOR TABLES IN SCHEMA public;

CREATE SCHEMA IF NOT EXISTS new_schema;  -- replace with the proper schema

CREATE USER mage_destination_user WITH PASSWORD 'mage_password';  -- replace with the proper user, for example, use `mage_tools` for the `tools` schema

-- Grant USAGE and CREATE privileges on the schema `new_schema`
GRANT USAGE, CREATE ON SCHEMA new_schema TO mage_destination_user;

-- Grant ALL PRIVILEGES on all tables in the schema `new_schema`
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA new_schema TO mage_destination_user;

-- Grant ALL PRIVILEGES on all sequences in the schema `new_schema`
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA new_schema TO mage_destination_user;

-- Ensure future tables and sequences in the schema `new_schema` have the necessary privileges
ALTER DEFAULT PRIVILEGES IN SCHEMA new_schema GRANT ALL PRIVILEGES ON TABLES TO mage_destination_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA new_schema GRANT ALL PRIVILEGES ON SEQUENCES TO mage_destination_user;

-- Grant CREATE privilege on the database to allow creating schemas
GRANT CREATE ON DATABASE your_database_name TO mage_destination_user;