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

[Dirk Eddelbuettel]

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' ?

   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/
   
See if that works.  Sameer was definitely bitten by this on his Fedora Core
machine; it never affected me on Debian or Ubuntu.
 
| I can create tables using dbGetQuery, for example the following works:
| > dbGetQuery(con, "create table tmp_vidtable AS ( select * from other_table )")
|  [1] TRUE
| 
| Is it possible to hold this temporary file "/tmp/rsdbi..." (ie not get
| deleted) and see the permissions on it?

Yes. It appears that SE Linux gets in the way of tempfiles created by you and
to be consumed by the postgres user.
 
| I guess there is some problem in setting up the postgresql
| server/user/roles/.... But I have not faced any such issues when
| working from pgadminIII or directly from psql prompt, so I am not
| exactly sure how to debug.
| 
| Any help is greatly appreciated. I really need to get this working....

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

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