Can't connect to PostgreSQL database

[subzerocloud]

Can't connect to PostgreSQL database

[steve-chavez]

Which version of PostgreSQL are you using? OS?

Also check your postgres connection uri(postgres://user:pass@host:port) if it contains reserved characters(like @, # in pass) you might need to percent encode them.

[kishyr]

Hi,

I'm using PostgreSQL 9.6 on Ubuntu 14.04 LTS.

I initially did have a problem where Postgres on Azure automatically appends @my-server-name to each username created on Postgres when trying to authenticate, and got a different error message for that (Invalid '@' character) but after URL-encoding the @ character, this is the only error I get now. There doesn't seem to be anything else that needs to be encoded. There are -s in the username, would that also need to be URL-encoded?

[kishyr]

Correction: Postgres is version 9.6, but since it's hosted as an Azure service, I'm not sure what OS it runs on. My docker master is running Ubuntu 14.04 and I'm running pg-amqp-bridge as a docker instance.

[steve-chavez]

The - chars should be safe, when the connection fails do you get retrying? Which pg-amqp-bridge version are you using? if it's latest(0.0.6) at startup there should be a message like:

Attempting to connect to PostgreSQL..
<Error here>
Retrying the PostgreSQL connection in 2 seconds..
<Error here>
Retrying the PostgreSQL connection in 4 seconds..
......

[kishyr]

After upgrading to latest / 0.0.6 from Docker Hub, I now get the following:

Attempting to connect to PostgreSQL..
Error(Io(Error { repr: Custom(Custom { kind: InvalidInput, error: StringError("invalid message length") }) }))
Retrying the PostgreSQL connection in 1 seconds..
Error(Io(Error { repr: Custom(Custom { kind: InvalidInput, error: StringError("invalid message length") }) }))
Retrying the PostgreSQL connection in 2 seconds..
Error(Io(Error { repr: Custom(Custom { kind: InvalidInput, error: StringError("invalid message length") }) }))
Retrying the PostgreSQL connection in 4 seconds..
Error(Io(Error { repr: Custom(Custom { kind: InvalidInput, error: StringError("invalid message length") }) }))
Retrying the PostgreSQL connection in 8 seconds..
Error(Io(Error { repr: Custom(Custom { kind: InvalidInput, error: StringError("invalid message length") }) }))
Retrying the PostgreSQL connection in 16 seconds..

[kishyr]

I think I've narrowed down this issue. My PostgreSQL server requires connection over SSL. What should I specify in POSTGRESQL_URI to be able to connect over SSL?

[steve-chavez]

Right now there's no SSL connection support, see this line https://github.com/subzerocloud/pg-amqp-bridge/blob/master/src/main.rs#L52 that has TlsMode::None, that would have to be changed to SslMode:Require as described in this issue sfackler/r2d2-postgres#7.

[steve-chavez]

Also you could try temporarily disabling SSL on your Azure potsgres instance with https://docs.microsoft.com/en-us/azure/postgresql/concepts-ssl-connection-security#using-the-azure-portal and see if the connection now works to see if that's indeed the problem.

Reason for double checking is that I've tried to connect to an SSL only Postgres RDS instance and the error message is more clear: no pg_hba.conf entry for host \"xx.xx.xx.xx.xx\", user \"admin\", database \"test\", SSL off maybe something else is going on in Azure.

[kishyr]

It was pretty strange. At first I received the invalid message length error, but after restarting the server I started to received an SSL-related error (not the one you mentioned above, but similar). I think it was Azure's custom SSL-must-be-on error message.

Everything started working when I turned off SSL on the Azure instance. It's not ideal though, but I'm limiting access to the server from a specific IP range so it's OK for now. Would you be able to add SSL support to your feature-list?

[steve-chavez]

The ideal support for this would be to specify the sslmode in the POSTGRESQL_URI like postgres://...?sslmode=verify-full libpq style, so the feature would be a better fit for rust-postgres.

[kishyr]

I see there it's been discussed on that project since 2015 but nothing has come of it. Thanks for all your help. I'm going to close this issue now.