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

[Prasenjit Kapat]

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.

-- 
Prasenjit