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

Question

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:

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.
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

Is it a permissions thing? Perhaps https://stackoverflow.com/q/25293266/3358272 helps. If not that, check file ownership of both `/etc/odbc.ini` and `~/.odbc.ini`, make sure it is owned by the currently running user (can be defined by the `Dockerfile`) and/or mode 644. — r2evans, Jun 05 '20 at 04:54

score 3 · Accepted Answer · answered Jun 05 '20 at 23:21

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.)

Thank you very much! I don't understand how on earth you were able to figure out that it was the indentation, but this has indeed solved my problem. — Doug Fir, Jun 06 '20 at 02:52
BTW: have you considered *not* baking your `odbc.ini` into the container, instead mounting it as a volume? My first few tests (not shown here) included `-v ./odbc.ini:/etc/odbc.ini` and they had the same success. Baking a password into a container is questionable at best. — r2evans, Jun 06 '20 at 03:05
Thanks for the suggestion. I will ask our dev ops team about this. We do have a volume with this container — Doug Fir, Jun 06 '20 at 03:55
(I'm with you on this error message, though ... if you're going to give mis-labeled errors, at least make the error vague. Something that specifically says *"no such file or directory"* is just a red herring.) — r2evans, Jun 06 '20 at 04:30

score 0 · Answer 2 · answered May 31 '20 at 07:34

I am no expert in docker, and have failed to find the specific documentation for this. But from experience it seems that every time you add a new layer (eg. using RUN) any copy from previous layers are "forgotten" (Note: this might be completely wrong, if so please someone correct me and specify the documentation).

So I would try to combine your RUN arguments and add every file right before the RUN statement they're needed. This has the added benefit of reducing the final image size, because of the way layers are created and kept.

FROM rocker/tidyverse:3.6.3
ENV ADD=SHINY
ENV ROOT=TRUE
ENV PASSWORD='abc123'
#add files (could also combine them into a single tar file and add it. Or add it via git, which is often used)
ADD odbc.ini /etc/odbc.ini
ADD install_packages.R /tmp/install_packages.R
ADD flagship_ecommerce /home/rstudio/blah/zprojects/flagship_ecommerce
ADD commission_junction /home/rstudio/blah/zprojects/commission_junction
#Combine all runs into a single statement
RUN apt-get update && apt-get install -y \
   less \
   vim  \
   unixodbc unixodbc-dev \
   odbc-postgresql \
 && Rscript /tmp/install_packages.R \
 && rm -R /tmp/* \
 && mkdir /srv/shiny-server \
 && ln -s /home/rstudio/blah/zprojects/ /srv/shiny-server/

Note that now add technically comes right before the statement where it is used.

Hi, thank you for your suggestion. I gave that a try just now but got the same outcome, the error message above about no such file or directory. Thanks all the same. — Doug Fir, May 31 '20 at 15:36

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

2 Answers2