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

[McGehee, Robert]

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

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

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.

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