Qlik Replicate Aurora PostgreSQL Setup With a Read-Only CDC User

Qlik's help pages give three different answers to what the database user behind an Aurora PostgreSQL source needs for change data capture. The prerequisites page says superuser permissions once Apply Changes or Store Changes is on. The CDC setup page says the master user, or an account with both rds_superuser and rds_replication. A third page describes how to run without rds_superuser at all, by creating Qlik's DDL capture objects yourself.

The data team at a client needed CDC from its production Aurora PostgreSQL cluster into Snowflake through Qlik Replicate, and asked infrastructure for the database side. The installation handoff lists settings that match that third page: Aurora PostgreSQL 15.12, a read-only account with rds_replication, the test_decoding plugin, Qlik's DDL artifacts in a dedicated schema, and SSL mode verify-full with the RDS global certificate bundle. Our part ended when the database side was handed off for installation, so nothing below claims the pipeline is running in production.

Scope the user from a confirmed table list

Our engineer's first question was which databases and tables the replication user had to read, and the data team confirmed the list. He then scoped a production database user read-only to those databases, with the users table explicitly excluded, tested the grants on the dev database, and confirmed that one of the databases holds no PII. That check is the data-classification step our zero trust implementation guide asks for, applied to a single pipeline user.

In PostgreSQL the shortest way to write "everything in this schema except one table" is a grant on all tables followed by a revoke. The sketch below shows that shape plus the grants Qlik's non-superuser procedure adds. The names are made up; this is the pattern, not the engagement's script.

-- Illustrative sketch: acme_app, acme, qlik_ddl and qlik_cdc are made-up names.
-- Once per cluster, as an administrator:
CREATE ROLE qlik_cdc WITH LOGIN PASSWORD 'change-me';
GRANT rds_replication TO qlik_cdc;

-- In each database Qlik reads (acme_app here):
GRANT CONNECT ON DATABASE acme_app TO qlik_cdc;
GRANT USAGE ON SCHEMA acme TO qlik_cdc;
GRANT SELECT ON ALL TABLES IN SCHEMA acme TO qlik_cdc;
REVOKE SELECT ON acme.users FROM qlik_cdc;

-- Qlik's DDL capture objects, kept in their own schema:
GRANT USAGE ON SCHEMA qlik_ddl TO qlik_cdc;
GRANT INSERT, DELETE ON qlik_ddl.attrep_ddl_audit TO qlik_cdc;
GRANT USAGE ON SEQUENCE qlik_ddl.attrep_ddl_audit_c_key_seq TO qlik_cdc;

Testing it on dev takes two queries. Log in as the replication user, select from the excluded table and expect permission denied. Then select from a table on the list and expect rows.

Qlik's DDL objects in a schema of their own

Without rds_superuser, Qlik's procedure has you create the DDL capture objects yourself: a table named attrep_ddl_audit, a function named attrep_intercept_ddl, and an event trigger of the same name on ddl_command_end. The trigger has to be created from an account that holds rds_superuser. Qlik's default schema for all of this is public. When the objects live somewhere else, the schema name goes into the Create DDL artifacts in schema field on the endpoint's Advanced tab.

Read-only here means read-only on the business data. The same procedure grants the replication user INSERT and DELETE on attrep_ddl_audit and USAGE on its sequence, and it holds rds_replication, which Qlik describes as the role that grants permission to manage logical slots and to stream data from them. We would write that into the access review notes. A reviewer who finds write grants on a user labelled read-only will ask, and the answer should already be on file.

One side effect of a dedicated schema shows up at cleanup. Qlik's removal steps drop the event trigger, the function and the audit table, and warn against ever dropping an operational schema such as public. With the artifacts in a schema that holds nothing else, dropping that schema takes nothing else with it.

What the cluster needs before the first task

Logical replication is off by default on Aurora. AWS's setup page has you set rds.logical_replication to 1 in a custom DB cluster parameter group (the default is 0), then reboot the writer instance. Two statements confirm it took:

SHOW rds.logical_replication;  -- expect: on
SHOW wal_level;                -- expect: logical

Slot counts come next. In PostgreSQL each slot streams changes from a single database, and Qlik's endpoint takes one database name while every task creates its own slot. Four databases in scope therefore means at least four tasks and four slots, and max_replication_slots and max_wal_senders in the cluster parameter group have to allow for that.

The default postgres database is not one of the four. The scope was corrected to leave it out, because on production it holds only pg_cron bookkeeping. That is where pg_cron lives on Aurora unless someone moved it: AWS's pg_cron guide says the scheduler is set in the default database named postgres, its objects are created there, and all scheduling actions run there. Its cron.job_run_details table holds job history that AWS says can become very large over time. Keeping postgres out also means one slot fewer holding WAL on the cluster.

Point the endpoint at the writer. Qlik's limitations list says the Aurora read replica is not supported as a source. The same list rules out capturing TRUNCATE operations and replicating materialized views, and both are worth checking against the table list.

The plugin in the handoff is test_decoding, the one Qlik's PostgreSQL prerequisites name. Once tasks are running, the plugin column of pg_replication_slots shows which plugin each slot uses.

TLS with verify-full and the RDS global bundle

In Qlik's connection settings, verify-full checks the certificate authority and then compares the host name Replicate connects with against the Common Name in the server certificate. A mismatch fails the connection. AWS puts the DB cluster endpoint in that Common Name. So the Server name field gets the cluster endpoint name, and an IP address or a custom DNS alias will fail the check.

That matters for the network side, which our engineer also reviewed: an HA VPN for production plus the Qlik Replicate instance, with one tunnel pair for now to cut cost. With verify-full in place, the Qlik host has to resolve the cluster endpoint name across that VPN.

The trust file is global-bundle.pem, which AWS's certificate page lists for any commercial Region. It carries the root CAs rds-ca-rsa2048-g1, rds-ca-rsa4096-g1 and rds-ca-ecc384-g1. AWS's advice is to register only root certificates in the trust store, because intermediates can break connections when RDS rotates the server certificate.

On 15.12 the client setting carries the weight. The same Aurora security page says rds.force_ssl defaults to 0 on Aurora PostgreSQL 16 and older and to 1 from 17, and that a major upgrade across that line flips the default. Unless someone has set it to 1, verify-full on the Qlik side is what keeps this connection encrypted and pinned to the right host.

Failure modes written into the handoff

The handoff lists two known failure modes and says where the password lives. The first is permission denied on one specific table. The second is that IAM database authentication is unusable for CDC, so the endpoint authenticates with a password.

The handoff does not say why IAM authentication is unusable here, so we will not guess at a mechanism. Two facts from AWS's IAM database authentication page belong next to that line in any runbook. Each token has a lifetime of 15 minutes. On PostgreSQL, once rds_iam is granted to a user, IAM authentication takes precedence over password authentication and the user must log in as an IAM user. Keep rds_iam off the replication user, or its password login stops working.

Slots keep WAL after a task stops

This last part comes from the vendor docs rather than the engagement. It is what we would have in place before the first task runs. Qlik's PostgreSQL prerequisites say slots open as soon as a task starts and remain open even when the task is no longer running, and that open slots have to be deleted by hand. PostgreSQL's logical decoding docs spell out the cost: a slot prevents removal of the WAL and catalog rows it needs even with no connection using it, and in extreme cases the database could shut down to prevent transaction ID wraparound. AWS's setup page adds that an inactive slot stops vacuum from removing obsolete tuples.

A task that runs while its tables sit idle has a quieter version of the same problem. Qlik's WAL heartbeat option periodically commits pseudo transactions to an attrep_wal_heartbeat table so the slot's position keeps advancing. Put that table in the same dedicated schema.

For alarms, Aurora publishes TransactionLogsDiskUsage, ReplicationSlotDiskUsage and OldestReplicationSlotLag in CloudWatch; the first is generated only when the cluster uses logical replication. For the per-slot view:

SELECT slot_name, plugin, database, active, restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots
WHERE slot_type = 'logical';

Slots also shape the next upgrade. An Aurora PostgreSQL major version upgrade cannot proceed while the cluster has logical replication slots, so moving this cluster off 15 has to be planned with the data team and their Qlik tasks. Our post on RDS PostgreSQL 14 extended support walks through the upgrade paths for that conversation.

For a CDC pipeline out of Aurora PostgreSQL, send us the table list and the endpoint settings. We would start by logging in as the replication user and testing the grants against that list, then check that something alarms on slot disk usage before the first Qlik task starts.