1

Our dev ops team have whitelisted my home ip address so that I can connect to our Postgres database on Azure. I am able to connect to our Azure database due to this.

Today I set up a VM in order to run Docker. I am running a container for RStudio which is an app that, among many other things, allows me to connect to our database using ODBC.

After configuring the odbcinst and odbc.ini files I believe that those are configured correctly because when I try to connect I get the following error:

Error: nanodbc/nanodbc.cpp:983: 00000: FATAL: SSL connection is required. Please specify SSL options and retry.

Thus I think that my odbc set up is correct because this error suggests my connection setting are fine, it's just that Azure will not allow it without SSL.

Searching that error message took me to this SO post with the following accepted answer:

By default, Azure Database for PostgreSQL enforces SSL connections between your server and your client applications to protect against MITM (man in the middle) attacks. This is done to make the connection to your server as secure as possible.

Although not recommended, you have the option to disable requiring SSL for connecting to your server if your client application does not support SSL connectivity. Please check How to Configure SSL Connectivity for your Postgres server in Azure for more details. You can disable requiring SSL connections from either the portal or using CLI. Note that Azure does not recommend disabling requiring SSL connections when connecting to your server.

My question is, if I am already able to connect to our database outside of my VM due to my home IP being whitelisted and just using a Postgres Driver with Dbeaver SQL client, is there anything I can do to connect from within my VM?

I can get my VMs ip address but I suspect (am not sure) if sending hat to our developers to whitelist would work?

Is there a prescribed course of action here?

Community
  • 1
  • 1
Doug Fir
  • 19,971
  • 47
  • 169
  • 299
  • It all depends on the way your networks are setup. Is this ipv4 or ipv6? Assuming you have a fairly common connection at home, all of your ip4 traffic is translated to a single public address. It looks like you just need to enable ssl in your client. – Doon Dec 30 '19 at 00:18
  • Hi @Doon, could you expand on that? Outside of my VM, just using a SQL client (DBeaver) I am able to connect with a connection setting of just entering my server host, username and database name. Similar to my odbc connection within the VM, I add pretty much the same details. When you say enable SSL in my client, you mea locally? Is that a flag of some sort? – Doug Fir Dec 30 '19 at 00:22
  • Looking at my connection in Dbeaver (outside the VM), I do not have ssl enabled there yet am able to connect. – Doug Fir Dec 30 '19 at 00:25
  • The difference in behavior is probably due to different drivers which are being used on the host and on the VM guest. Can you tell us how you obtained the drivers in each case? – jjanes Dec 30 '19 at 00:40
  • Hi,through Dbeaver desktop app for Mac I used to GUI to install. It looks like it's a JDBC driver, 'description: PostgreSQL standard driver, website: http://jdbc.postgresql.org/'. On my VM I'm running on Debian with `apt-get install unixodbc unixodbc-dev` and `apt-get install odbc-postgresql`. Is this information useful? Is there any other info I can provide? – Doug Fir Dec 30 '19 at 00:47

1 Answers1

1

I added this parameter to my .odbc.ini file and was able to connect:

sslmode=require

From Azure Postgres documentation, this parameter may take on different permutations depending on the context

"for example "ssl=true" or "sslmode=require" or "sslmode=required" and other variations"

Doug Fir
  • 19,971
  • 47
  • 169
  • 299