Search code examples
rdockerodbcunixodbc

Rstudio via docker cannot read /etc/.odbc.ini, only ~/.odbc.ini

When I build and then run a Docker container which runs rstudio on Ubuntu, the odbc connection does not work when I add the odbc.ini file during the build. However, if I leave out the odbc.ini file from the build and instead add it myself from within the running container, the connection does indeed work.

So my problem is that I am trying to get the odbc connection up and running out of the box whenever this image is run, without the additional step of having to login to the ubuntu container instance and add connection details to the odbc.ini file.

Here's what the odbc.ini file looks like, with dummy data:

[PostgreSQL ANSI]
    Driver              = PostgreSQL ANSI
    Database            = GoogleData
    Servername          = somename.postgres.database.azure.com
    UserName            = docker_rstudio@somename
    Password            = abc123abc
    Port                = 5432
    sslmode             = require

I have a copy of this file, odbc.ini, in my repo directory and then include it in the build. My DockerFile.

FROM rocker/tidyverse:3.6.3
ENV ADD=SHINY
ENV ROOT=TRUE
ENV PASSWORD='abc123'
RUN apt-get update && apt-get install -y \
   less \
   vim  \
   unixodbc unixodbc-dev \
   odbc-postgresql
ADD odbc.ini /etc/odbc.ini
ADD install_packages.R /tmp/install_packages.R
RUN Rscript /tmp/install_packages.R && rm -R /tmp/*
ADD flagship_ecommerce /home/rstudio/blah/zprojects/flagship_ecommerce
ADD commission_junction /home/rstudio/blah/zprojects/commission_junction
RUN mkdir /srv/shiny-server; ln -s /home/rstudio/blah/zprojects/ /srv/shiny-server/

If I then login to the instance via rstudio, the connection does not work, I get this error message:

Error: nanodbc/nanodbc.cpp:983: 00000: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

If I take a look at the file with less /etc/odbc.ini I do indeed see the connection details per my top code block.

If I then copy to home with cp /etc/odbc.ini /home/rstudio/.odbc.ini then, after that, my connection does work.

But, even if I amend my dockerfile with ADD odbc.ini /home/rstudio/.odbc.ini, the connection doesn't work. It only works when I manually add to /home/rstudio/.odbc.ini.

So my problem is two fold:

  1. No matter what I try I cannot get /etc/odbc.ini to be detected by ubuntu to use as odbc connection string. Whether via Dockerfile or by manually adding it. I would prefer this since I want to connection to be available to anyone using the container.

  2. I am able to get a connection when I manually copy whats in odbc.ini above to /home/rstudio/.odbc.ini however if I try to do this via the docker build, the connection does not work. I do see the file there. It exists with all the correct data, it is just not detected by odbc.

In case it's relevant:

odbcinst -j
unixODBC 2.3.6
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/rstudio/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
Solution

  • I believe the problem is with the format of your /etc/odbc.ini. I don't have all your scripts, but this is the Dockerfile I used:

    FROM rocker/tidyverse:3.6.3
ENV ADD=SHINY
ENV ROOT=TRUE
ENV PASSWORD='abc123'
RUN apt-get update && apt-get install -y \
   less \
   vim  \
   unixodbc unixodbc-dev \
   odbc-postgresql
RUN Rscript -e 'install.packages(c("DBI","odbc"))'
ADD ./odbc.ini /etc/odbc.ini

    If I use an odbc.ini of this:

    [mydb]
    Driver              = PostgreSQL ANSI
    ServerName          = 127.0.0.1
    UserName            = postgres
    Password            = mysecretpassword
    Port                = 35432

    I see this (docker build and R startup messages truncated):

    $ docker build -t quux2 .
$ docker run --net='host' -it --rm quux2 bash
> con <- DBI::dbConnect(odbc::odbc(), "mydb")
Error: nanodbc/nanodbc.cpp:983: 00000: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

    When I changed the indentation of the file to this:

    [mydb]
Driver              = PostgreSQL ANSI
ServerName          = 127.0.0.1
UserName            = postgres
Password            = mysecretpassword
Port                = 35432

    I see this:

    $ docker build -t quux3 .
$ docker run --net='host' -it --rm quux3 bash
> con <- DBI::dbConnect(odbc::odbc(), "mydb")
> DBI::dbGetQuery(con, "select 1 as a")
  a
1 1

    (For this demonstration, I'm running postgres:11 as another container, but I don't think that that's relevant, it's the indented values.)