2

How can I connect to an Azure PostgreSQL database, from a remote machine?

Update 2. I can connect to the database from WSL/Ubuntu using sudo psql, but I can't using plain psql. So it's a permissions issue somewhere...

Update. I've discovered I can connect from the remote machine using PgAdmin4, but I can't connect using psql. So I want to know: how should I connect using psql?

Original question. I can connect to it using psql from a VM inside Azure, so I know the database is up and accepting connections. But when I try to connect from my home machine, using exactly the same psql command, it fails:

psql --user=UUU --host=HHH DB

psql: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.

More information... On the Azure database's "Connection Security" blade, I have

  • added a firewall rule with start IP=0.0.0.0 and end IP=255.255.255.255
  • set "enforce SSL connection" to disabled
  • turned on "allow access to Azure services".

My home machine is running Windows+WSL, and I'm trying to connect from WSL / Ubuntu 18.04 using psql version 10.11. I run into the same problem whether I try to connect from home or from work, and I'm not blocking any outgoing ports (that I know of). The database is running PostgreSQL 10. When I connect (successfully) from an Azure VM, using psql 10.10, it looks like this:

psql --user=UUU --host=HHH DB
Password for user UUU:

psql (10.10 (Ubuntu 10.10-0ubuntu0.18.04.1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
DamonJW
  • 3,342
  • 2
  • 24
  • 29
  • Could you please go to "Azure Database for PostgreSQL server - Server logs" blade and check what's happened when you're trying to connect from your WSL host? – Sergey Nov 30 '19 at 22:33
  • When I try to connect from my WSL host, nothing at all is added to the server logs. When I connect from the Azure VM, the normal "connection received, connection authorized" lines appear in the log. – DamonJW Nov 30 '19 at 22:45
  • Have you ever tried using not psql but something like PgAdmin4? If not, could you please try it and share the details? – Sergey Nov 30 '19 at 22:52
  • I just installed PgAdmin4 (for the first time ever) and it worked -- I can connect from my local machine! So it must be something to do with how I'm using psql... – DamonJW Dec 01 '19 at 09:12
  • I just tried connecting from my local machine, using the psql that came with PgAdmin4. It worked fine. – DamonJW Dec 01 '19 at 09:23
  • Network diagram would be useful here I think showing the paths for the hops that succeed and the paths that fail. I have a number of PostGres databases hosted on Azure. Connecting remotely is as simple as taking the URL from the resource Overview and entering it into the PgAdmin4 then connecting. Something is in the way blocking your request clearly, think a network diagram would help diagnose what. Thanks. Scott – scott_lotus Dec 01 '19 at 09:44
  • @scott_lotus I can connect from PgAdmin4, but I can't connect from psql -- from the same machine. So I don't think it's a network issue. – DamonJW Dec 01 '19 at 11:26
  • @DamonJW I see sorry. I missed that aspect in your comment above. – scott_lotus Dec 01 '19 at 18:47
  • @DamonJW So you have determined that it was an error with the earlier version of psql installed on your local host? – Sergey Dec 02 '19 at 08:37
  • @Sergey It works if I run "sudo psql" but not if I run "psql", so it's a permissions error with my system rather than a psql issue. I'm now hunting around for help with openssl / permissions. – DamonJW Dec 02 '19 at 09:12

2 Answers2

2

Maybe your root user uses a different psql binary than your user. ( You can find out using which psql and sudo which psql )

I Ran into the same connection issue. In my case, the base issue was a postgres major version mismatch.

I was connecting to an Azure Postgresql on version 11 with my local psql on version 12. Downgrading my local machine's Postgres version to 11.6 solved this for me.

Maybe your root user is using psql 10 and your default user is using psql 11 or 12. ( You can check this using psql -V and sudo psql -V )

Erik de Groot
  • 21
  • 3
1

I had the same issue. The error message sucks and is of 0 help.

You're probably using a different version of psql than your Azure DB. It needs to match whatever is installed in Azure.

So if you provisioned a version 10 DB in Azure, either you install version 10 for the pqsl tool or do a full Postgre version 10 install instead. The point is the major versions need to match between psql and the target database.

Serban Cezar
  • 507
  • 1
  • 8
  • 19