Thread: CREATE SUBSCRIPTION fails with long passwords
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
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
On Wed, Apr 24, 2019 at 10:07:45AM -0700, Mike Lissner wrote: > 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. I've tried to reproduce this on a master, and it works for me (with 200 char passwords). Which version have you used? Can you share an actual example of commands creating the role/subscription that triggers the failure, including the password value? regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Possibly the password might have non-ascii or something causing it to unmatched but when stripped off, that non-ascii wasn't included. I'm curious if you try to have passwords Mike with 108 chars for which chars are of [0-9] and [a-zA-Z] strings.
On Thu, Apr 25, 2019 at 5:19 AM Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
On Wed, Apr 24, 2019 at 10:07:45AM -0700, Mike Lissner wrote:
> 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.
I've tried to reproduce this on a master, and it works for me (with 200
char passwords). Which version have you used? Can you share an actual
example of commands creating the role/subscription that triggers the
failure, including the password value?
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Paul Mansueto Namuag <paulnamuag@gmail.com> writes:
> Possibly the password might have non-ascii or something causing it to
> unmatched but when stripped off, that non-ascii wasn't included.
Oh --- another variant of that theory, if there's any non-ASCII in the
password, is that you're getting bit by an encoding conversion issue.
That is, whatever the client is sending is in a different encoding
from the way the password was stored.
regards, tom lane
---one month passes---
Just to close this loop, because I like things closed. I wasn't able to reproduce this in the end. I'll keep trying, but seems like I must have mis-diagnosed the issue, I think.
Thanks for all the suggestions and help here. This kind of help and support is so wonderful and valuable.
Mike
On Thu, Apr 25, 2019 at 6:59 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Paul Mansueto Namuag <paulnamuag@gmail.com> writes:
> Possibly the password might have non-ascii or something causing it to
> unmatched but when stripped off, that non-ascii wasn't included.
Oh --- another variant of that theory, if there's any non-ASCII in the
password, is that you're getting bit by an encoding conversion issue.
That is, whatever the client is sending is in a different encoding
from the way the password was stored.
regards, tom lane