PostgreSQL flexible with Microsoft Entra authentication enabled - Logical Replication issue

Question

Hi

My application is using logical replication with PostgreSQL. Im trying to implement it using PostgreSQL flexible server with Microsoft Entra authentication only enabled.

Im creating the admin and im able to connect to the database. Im altering my user with role REPLICATION and see it attached. But when im adding ?replication=database parameter to my connection string im unable to connect:

this works:
psql postgresql://user:<token>@host:5432/test

test=> \du+
                                       List of roles
   Role name    |                         Attributes                         | Description
----------------+------------------------------------------------------------+-------------
 user   		| Create role, Create DB, Replication                        |
 azure_pg_admin | Cannot login                                               |
 azuresu        | Superuser, Create role, Create DB, Replication, Bypass RLS |
 replication    | Replication                                                |

This doesnt work:

psql postgresql://user:<token>@host:5432/test?replication=database

psql: error: connection to server at "host" (ip), port 5432 failed: FATAL:  password authentication failed for user "user"

Server log:

2024-07-29 08:29:55 UTC-66a75301.8deb-FATAL:  password authentication failed for user \"user\"","detail_log": "User \"user\" has no password assigned.\nConnection matched pg_hba.conf line 26: \"hostssl all all  0.0.0.0/0 md5\"

Any clue on it?

Answer 1

Hi Ilya Dvorkin •,

Welcome to Microsoft Q&A forum.

As I understand, you are getting error: psql: error: connection to server at "host" (ip), port 5432 failed: FATAL: password authentication failed for user "user".

Could you please refer to the prerequisites mentioned here and see if works for you:

https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/concepts-logical#prerequisites-for-logical-replication-and-logical-decoding

Note

It's always a good practice to separate your replication user from regular admin account.

Let us know your output.

Thanks