This made me a bit crazy tonight. Steps to reproduce:
1. Create a new role and give it a really long password. Ours was 108 chars.
2. Set up all the necessary replication permissions/roles.
3. From another computer, try to connect as that user using psql — it works!
4. Now try a CREATE SUBSCRIPTION command like:
create subscription my_sub connection 'host=192.168.0.111 port=5432 user=the_new_user password=long_password dbname=my_db sslmode=require' publication new_server;
You'll get the following error:
ERROR: could not connect to the publisher: FATAL: password authentication failed for user "the_new_user"
Now, go back and shorten that password, and try again. You'll get:
create subscription my_sub connection 'host=192.168.0.111 port=5432 user=the_new_user password=long_password dbname=my_db sslmode=require' publication new_server;
NOTICE: created replication slot "my_sub" on publisher
CREATE SUBSCRIPTION
And it'll be off to the races. I watched the logs on both servers during these experiments. Nothing much in there, aside from the logs above.
Is this known functionality? Seems like a nasty bug and it took me a while to figure it out.
Thanks,
Mike