Amazon Aurora and RDS

Creating a service account

setup.sql

CREATE USER artie_transfer WITH PASSWORD 'password';

GRANT USAGE ON SCHEMA schema_name TO artie_transfer;
-- Grant access to existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO artie_transfer;
-- Grant read-only access to future tables created by current user
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT SELECT ON TABLES TO artie_transfer;

-- The replication role does not have enough permissions to create publications.
-- So you will need to create this as well.
CREATE PUBLICATION dbz_publication FOR ALL TABLES WITH (publish_via_partition_root = true);

-- Grant replication role
GRANT rds_replication to artie_transfer;

Granting access if another user will run ddl

For all other users that will run CREATE TABLE statements please run

grant.sql

-- Grant read-only access to future tables for given user
ALTER DEFAULT PRIVILEGES FOR ROLE given_user IN SCHEMA schema_name GRANT SELECT ON TABLES TO artie_transfer;

Turning on logical replication

rds.logical_replication is a static parameter which requires a database reboot to take effect.

Select your database instance
Go into Configuration and find your DB instance parameter group (or DB cluster parameter group if you are using Aurora)
If you don’t have one, go to Parameter groups and click Create parameter group
Find and change rds.logical_replication and set it to 1
Associate the parameter group with your database instance