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

[Dirk Eddelbuettel]

On 4 November 2009 at 22:58, Prasenjit Kapat wrote:
| Hi,
| 
| On Tue, Nov 3, 2009 at 5:14 PM, Dirk Eddelbuettel <edd using debian.org> wrote:
| > | ---------------------
| > | > dbWriteTable(pcon, "test", data)
| > | Error in postgresqlExecStatement(conn, statement, ...) :
| > |   RS-DBI driver: (could not Retrieve the result : ERROR:  could not open
| > | file "/tmp/RtmprNh2yy/rsdbi60b7acd9" for reading: Permission denied
| > | )
| > | Warning in postgresqlWriteTable(conn, name, value, ...) :
| > |   could not load data into table
| >
| > That looks straightforward. Me thinks that
| >
| >  a) R creates its tempdir, so R owns /tmp/RtmprNh2yy/
| >
| >  b) we're talking to Pg so the default Pg user will need to read that
| >     file -- my guess right now is that the default postgres user running
| >     the db is simply prohibited from reading inside that directory.
| >
| > This gives you an idea of how to fix it short term -- maybe by using another
| > tempdir, maybe by using another fudge.   Alternatively, dump your data
| > outside of R and use Pg's native bulk copy tools ...   That said, I don't
| > quite recall by this doesn't bite us on Linux.
| 
| I had a similar situation sometime back. And to this day the only
| solution was to give global read/write permissions (on RedHat, SELinux
| was disabled). Giving read perm is fine but not so for write. So:
| 
| If exporting directly from postgres to disk via any query then I use
| /tmp. Otherwise, read them into R's data frame and then write to
| anywhere on the disk from R.

And just to follow-up, now that I am home where Pg runs:

a) dbWriteTable() works for me under Debian

b) the per-R-session tempdir is clearly mode 0700:
drwx------  2 edd  edd     1024 2009-11-04 22:18 RtmpzGf6Ef

c) I am not sure how Pg gets to the data ... but it does for me as the
unittest tests/dbWriteTableTest.R in the sources also attests

Dirk

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