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

[McGehee, Robert]

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. 

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.

Thanks to all.

Cheers,
Robert

-----Original Message-----
From: Dirk Eddelbuettel [mailto:edd using debian.org] 
Sent: Wednesday, November 04, 2009 11:26 PM
To: Prasenjit Kapat
Cc: McGehee, Robert; r-sig-db using stat.math.ethz.ch
Subject: Re: [R-sig-DB] dbWriteTable permission problem on Mac OSX


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.