[R-sig-DB] dbWriteTable permission problem on Mac OSX

[Dirk Eddelbuettel]

On 5 November 2009 at 10:45, McGehee, Robert wrote:
| My /tmp directory also has a sticky bit. 
| 
| Here's my guess of the problem. This chunk of code from the
| 'dbWriteTable' method 'postgresqlWriteTable' specifies different
| directories for Linux and non-Linux (e.g. Mac) tempfiles. 
|     if(as.character(Sys.info()["sysname"])=="Linux")
|         fn <- tempfile("rsdbi","/tmp")
|     else
|         fn <- tempfile("rsdbi")

Spot on!  Sameer added this on my recommendation based on the very same /tmp
issue problem.  Too bad I couldn't remember that this week :-/

| This difference is significant because on my Mac, the data file is saved
| inside an R tempdir with only user read permissions:
| drwx------   2 a347549 pcap       40 Oct 30 14:05 Rtmp8fgNQf/
| 
| while on Linux, the file is saved in /tmp, with whatever permissions
| that has. So, presumably either patching the code as follows:
| -    if(as.character(Sys.info()["sysname"])=="Linux")
| +    if(as.character(Sys.info()["sysname"]) %in% c("Linux", "Darwin"))

Agreed.  Explicitly CC'ing Neil for OS X advice -- Darwin is the best test?

| or finding a way to have R create a tempdir with more generous
| permissions would solve the problem. Changing my umask settings didn't
| seem to change the permissions of the R tempdir--I'm not sure how that
| is specified. Alternatively, I suppose the directory could be an option
| to let the user specify somewhere readable by both 'postgres' and the
| user's R session.

I think you can't easily which is why we created the workaround with the
temporary directory directly below /tmp, rather than by default in R's
tempdir /tmp/Rtmp...../

I think we got this bug!

Dirk


| --Robert
| 
| -----Original Message-----
| From: Dirk Eddelbuettel [mailto:edd using debian.org] 
| Sent: Thursday, November 05, 2009 9:09 AM
| To: McGehee, Robert
| Cc: Prasenjit Kapat; r-sig-db using stat.math.ethz.ch
| Subject: Re: [R-sig-DB] dbWriteTable permission problem on Mac OSX
| 
| 
| On 5 November 2009 at 08:39, McGehee, Robert wrote:
| | So following the spirit of Dirk's advice, I resolved the problem by
| running
| | the PostgreSQL database from my user account, rather than the
| 'postgres' user
| | I had been using. Imports happened smoothly afterwards. Presumably
| 'postgres'
| | couldn't see the temp files that my user was creating.  
| 
| Hm, my postgres servers (running versions 8.2 and 8.3, 8.2 has the
| default
| port)  run as user postgres:
| 
| edd using ron:~> pstree -u | grep postgres
|      |-postgres(postgres)---2*[postgres]
|      |-postgres(postgres)---4*[postgres]
| edd using ron:> 
| 
| Let's look at modes of /tmp, I have
| 
| edd using ron:~/src/debian/CRAN> ls -ld /tmp
| drwxrwxrwt 43 root root 19456 2009-11-05 08:04 /tmp
| 
| So there is a sticky bit set for 'other' (the trailing t).  What do you
| have?
| 
| | Also on this note, the first error I encountered with 'dbWriteTable'
| when
| | running from my user account and pg from the 'postgres' account
| informed me
| | that the '\copy' command required superuser access. Granting my pg
| account
| | Postgres superuser abilities fixed that, but I think it should be
| noted
| | that the current implementation seems to require a wide-open set-up to
| use
| | bulk copy. 
| | 
| | I'm still keen to try out Whit's solution as well as a direct binary
| | connection may get around the permission problems and has the
| potential to
| | be faster. I'll report back if I discover anything interesting for the
| | group. 
| 
| Yes, it would also be nice to get that ported into RPostgreSQL.
| 
| Dirk
| 
| -- 
| Three out of two people have difficulties with fractions.
| 

-- 
Three out of two people have difficulties with fractions.