Aurora Postgres 13 with IAM Auth - Unable to establish logical replication connection

0

Hello, I'm following the tutorial for using Postgres CDC using logical replication here - https://aws.amazon.com/blogs/database/stream-changes-from-amazon-rds-for-postgresql-using-amazon-kinesis-data-streams-and-aws-lambda/

The DB cluster parameter group has rds.logical_replication enabled, and I've verified that the user I intend to use is capable of IAM auth, and that logical replication slots can be created and queried:

CREATE ROLE replicate LOGIN;
GRANT rds_replication, rds_iam TO replicate;
GRANT SELECT ON ALL TABLES IN SCHEMA myschema TO replicate;

...snip...IAM authenticate as replicate user...

Server: PostgreSQL 13.4
Version: 3.2.0
Home: http://pgcli.com
replicate@dev-db-host:mydb> SELECT pg_create_logical_replication_slot('mydb_replication_slot', 'wal2json');
replication slot "mydb_replication_slot" already exists

Time: 0.016s
replicate@dev-db-host:mydb> SELECT pg_create_logical_replication_slot('test_replication_slot', 'wal2json');
+--------------------------------------+
| pg_create_logical_replication_slot   |
|--------------------------------------|
| (test_replication_slot,0/5005318)    |
+--------------------------------------+
SELECT 1
Time: 0.044s
replicate@dev-db-host:mydb> SELECT * FROM pg_logical_slot_peek_changes('test_replication_slot', null, null);
+-------+-------+--------+
| lsn   | xid   | data   |
|-------+-------+--------|
+-------+-------+--------+
SELECT 0
Time: 0.033s
replicate@dev-db-host:mydb>

However when I attempt create a replication connection using the python psycopg2 code in the blog post, postgres tells me

FATAL: password authentication failed for user "replicate"

Someone else asked the psycopg devs, who've indicated it's an RDS issue: https://github.com/psycopg/psycopg2/issues/1391

Any ideas?

Cheers, Jim

P.S. I've verified that the postgres logical replication connection (either replication=database option on the command line or psycopg2's LogicalReplicationConnection type) is possible when using a plain old password instead of RDS IAM.

Jim
asked 2 years ago243 views
1 Answer
0

Hiya ,

it's likely that there's a problem with the way that psycopg2 is handling IAM authentication.

One possible solution is to use the connection_factory parameter to the psycopg2.connect() function. This parameter allows you to specify a custom connection factory that will be used to create the connection. You can use this to create a connection factory that uses IAM authentication.

Here's an example of how to do this:

Python import psycopg2

def my_connection_factory(dbname, user, password, host, port): connection = psycopg2.connect( dbname=dbname, user=user, password=password, host=host, port=port, connection_factory=psycopg2.extras.LogicalReplicationConnection, ) return connection

conn = psycopg2.connect( dbname="mydb", user="replicate", password="mypassword", host="myhost", port="5432", connection_factory=my_connection_factory, ) Use code with caution. Learn more This code will create a connection to the database using IAM authentication. If the password is correct, then the connection should be successful.

profile picture
answered 8 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions