[R-sig-DB] dbWriteTable permission errors using RPostgreSQL

[Prasenjit Kapat]

On Thu, Nov 6, 2008 at 4:36 PM, Dirk Eddelbuettel <edd using debian.org> wrote:
>
> On 6 November 2008 at 16:19, Prasenjit Kapat wrote:
> | Hi,
> |
> | I am using the rpostgresql-0.1-2 package with R 2.7.1 on a RedHat EL 5
> | machine. There were no errors/warning while installing it but I get
> | this permission error while using dbWriteTable:
> |
> | >  if (dbExistsTable(con, "tmp_vidtable")) dbRemoveTable(con,"tmp_vidtable")
> | >  mydata2 <- data.frame(x=1:3, y=4:6)
> | >  dbWriteTable(con,"tmp_vidtable",mydata2)
> |  Error in postgresqlExecStatement(conn, statement, ...) :
> |    RS-DBI driver: (could not Retrieve the result : ERROR:  could not
> | open file "/tmp/rsdbi1da2f885" for reading: Permission denied)
> |  [1] FALSE
> |  Warning message:
> |  In postgresqlWriteTable(conn, name, value, ...) :
> |    could not load data into table
>
> Did you see the README section entitled 'Important Information' ?

Well, my bad, should've checked it.

>   Important Information:
>
>   1. dbWriteTable method is found to fail in some flavours of Linux like RedHat
>      distributions because of SELinux. SELinux is preventing PostgreSQL to
>      access data from locations other than it's data folder. So one of the
>      possible quick solutions is to turn off SELinux temporarily while using
>      this method and turn on later.
>
>   ## To turn off the SELinux, type the following at the command prompt
>   $ echo 0 > /selinux/enforce/
>   ## To turn on the SElinux, type the following at the command prompt
>   $ echo 1 > /selinux/enforce/

Now, that is a killer blow! This is a department machine. I am just a
user and I don't think they'll "compromise" the system for me! BTW,
/selinux folder is empty here. Is that equivalent to "echo 1 >
/selinux/enforce/" ?

> See if that works.  Sameer was definitely bitten by this on his Fedora Core
> machine; it never affected me on Debian or Ubuntu.

I just tried it on my home Ubuntu machine; no problems, as you mentioned.

> | But I have not faced any such issues when
> | working from pgadminIII or directly from psql prompt

Now, that I see where the problem is, let me retract on this
statement; I did face this situation. My solution was to create a
specific folder (sort of data dump) and do a "chmod o+r" on all the
files in there, and delete them once done.

> Good luck.  If you find a better / more generic fix, let me know and I update
> the documentation.

I'll talk with sysadm tomorrow for the selinux thing. But I seriously
doubt against it. In which case, dbWriteTable is unuseable. On RedHat
flavored machines, here is my quick-n-dirty solution:

## BEGIN {

TMPTABLE <- "tmptable"
if (dbExistsTable(con, TMPTABLE)) dbRemoveTable(con, TMPTABLE)

(mydata <- data.frame(x=1:3, y=4:6))

TMPDIR <- "/tmp"; FILENAME <- system("mktemp rpsql.XXXXXX", intern=TRUE);
(FULLPATH <- paste(TMPDIR, FILENAME, sep="/"))

write.csv(mydata, file=FULLPATH, row.names=FALSE)

## I am unable to set field.names argument here.. But that is a
different issue, or is it?
dbGetQuery(con, dbBuildTableDefinition(con, TMPTABLE, mydata2,
row.names = FALSE))

## The trivial key is this one:
system(paste("chmod o+r ", FULLPATH))

dbGetQuery(con, paste("COPY ", TMPTABLE," FROM '", FULLPATH,"' WITH
CSV HEADER QUOTE AS '\"'", sep=""))

system(paste("rm -f ", FULLPATH))

## END }

Is this a viable solution to be wrapped inside dbWriteTable() for
RedHat machines? Thanks, for the very prompt response Dirk, much
appreciated.

Regards
--
PK