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

Question

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.

Answer

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.

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

Relevanter Inhalt