CREATE SUBSCRIPTION fails with long passwords

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

CREATE SUBSCRIPTION fails with long passwords

Mike Lissner
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
Reply | Threaded
Open this post in threaded view
|

Re: CREATE SUBSCRIPTION fails with long passwords

Tomas Vondra-4
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


Reply | Threaded
Open this post in threaded view
|

Re: CREATE SUBSCRIPTION fails with long passwords

Paul Mansueto Namuag
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 <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: CREATE SUBSCRIPTION fails with long passwords

Tom Lane-2
Paul Mansueto Namuag <[hidden email]> 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