dbWriteTable permission problem on Mac OSX

postgresqlWriteTable serializes your entire table to disk, and then
asks postgres to read it back.

It looks like the user name running postgres isn't able to read your file.

I'm sure someone on this list will help you solve your permission
issues.  In case they can't you can try my postgres driver (which
writes via binary transfer though the connection).

http://github.com/armstrtw/unifieddbi

-Whit


On Tue, Nov 3, 2009 at 4:35 PM, McGehee, Robert

R-Sig-DB,
I'm getting a 'Permission denied' error when trying to use the
'dbWriteTable' function (RPostgreSQL\DBI package) to access PostgreSQL
on my Mac OSX. I saw that this error appears to have been documented in
the README file as affecting versions of Linux running SELinux. However,
since my OSX Server is not running SELinux, I wanted to see if anyone
else has had success using 'dbWriteTable' on a Mac with RPostgreSQL.

I can update a database using INSERT commands, but am looking for a
faster solution. Also RODBC crashes my R session when executing the
'sqlTables' function--presumably due to an ODBC driver problem--so
switching packages won't easily help.

Thanks, Robert

---------------------

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
[1] FALSE

R.version

? ? ? ? ? ? ? _
platform ? ? ? x86_64-apple-darwin9.5.0
arch ? ? ? ? ? x86_64
os ? ? ? ? ? ? darwin9.5.0
system ? ? ? ? x86_64, darwin9.5.0
status
major ? ? ? ? ?2
minor ? ? ? ? ?9.0
year ? ? ? ? ? 2009
month ? ? ? ? ?04
day ? ? ? ? ? ?17
svn rev ? ? ? ?48333
language ? ? ? R

_______________________________________________
R-sig-DB mailing list -- R Special Interest Group
R-sig-DB at stat.math.ethz.ch
https://stat.ethz.ch/mailman/listinfo/r-sig-db

| I'm getting a 'Permission denied' error when trying to use the
| 'dbWriteTable' function (RPostgreSQL\DBI package) to access PostgreSQL
| on my Mac OSX. I saw that this error appears to have been documented in
| the README file as affecting versions of Linux running SELinux. However,

(That was an educated guess by Sameer at the time. He had other issues too...)

| since my OSX Server is not running SELinux, I wanted to see if anyone
| else has had success using 'dbWriteTable' on a Mac with RPostgreSQL.
| 
| I can update a database using INSERT commands, but am looking for a
| faster solution. Also RODBC crashes my R session when executing the
| 'sqlTables' function--presumably due to an ODBC driver problem--so
| switching packages won't easily help.
| 
| Thanks, Robert
| 
| ---------------------
| > 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.

RPostgreSQL, I should add, is in a state of graceful unmaintainedness.  I
sort-of look after it as I once suggested for R's participation in the Google
Summer of Code as feel attached. But as I don;t currently run much code
against it, so my usage is light at best.  I have Neil helping me OS X
specific issue (ie he repaired/enhanced the build), Joe helps with real Pg
nitty gritty and Jeff offers some general database / DBI smart.

So with that:  if anyone knows and loves Postgresql and wants to help, please
do not hold back. We'd love to have you on the team.

Dirk

Three out of two people have difficulties with fractions.

Hi,

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

| Hi,
|

| > | ---------------------
| > | > 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.

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 at debian.org] 
Sent: Wednesday, November 04, 2009 11:26 PM
To: Prasenjit Kapat
Cc: McGehee, Robert; r-sig-db at stat.math.ethz.ch
Subject: Re: [R-sig-DB] dbWriteTable permission problem on Mac OSX

| Hi,
|

| > | ---------------------
| > | > 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.

| 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.  

Hm, my postgres servers (running versions 8.2 and 8.3, 8.2 has the default
port)  run as user postgres:

edd at ron:~> pstree -u | grep postgres
     |-postgres(postgres)---2*[postgres]
     |-postgres(postgres)---4*[postgres]
edd at ron:> 

Let's look at modes of /tmp, I have

edd at ron:~/src/debian/CRAN> ls -ld /tmp
drwxrwxrwt 43 root root 19456 2009-11-05 08:04 /tmp

So there is a sticky bit set for 'other' (the trailing t).  What do you have?

| 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. 

Yes, it would also be nice to get that ported into RPostgreSQL.

Dirk

Three out of two people have difficulties with fractions.

My /tmp directory also has a sticky bit. 

Here's my guess of the problem. This chunk of code from the
'dbWriteTable' method 'postgresqlWriteTable' specifies different
directories for Linux and non-Linux (e.g. Mac) tempfiles. 
    if(as.character(Sys.info()["sysname"])=="Linux")
        fn <- tempfile("rsdbi","/tmp")
    else
        fn <- tempfile("rsdbi")

This difference is significant because on my Mac, the data file is saved
inside an R tempdir with only user read permissions:
drwx------   2 a347549 pcap       40 Oct 30 14:05 Rtmp8fgNQf/

while on Linux, the file is saved in /tmp, with whatever permissions
that has. So, presumably either patching the code as follows:
-    if(as.character(Sys.info()["sysname"])=="Linux")
+    if(as.character(Sys.info()["sysname"]) %in% c("Linux", "Darwin"))

or finding a way to have R create a tempdir with more generous
permissions would solve the problem. Changing my umask settings didn't
seem to change the permissions of the R tempdir--I'm not sure how that
is specified. Alternatively, I suppose the directory could be an option
to let the user specify somewhere readable by both 'postgres' and the
user's R session.

--Robert

-----Original Message-----
From: Dirk Eddelbuettel [mailto:edd at debian.org] 
Sent: Thursday, November 05, 2009 9:09 AM
To: McGehee, Robert
Cc: Prasenjit Kapat; r-sig-db at stat.math.ethz.ch
Subject: Re: [R-sig-DB] dbWriteTable permission problem on Mac OSX

| 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.  

Hm, my postgres servers (running versions 8.2 and 8.3, 8.2 has the
default
port)  run as user postgres:

edd at ron:~> pstree -u | grep postgres
     |-postgres(postgres)---2*[postgres]
     |-postgres(postgres)---4*[postgres]
edd at ron:> 

Let's look at modes of /tmp, I have

edd at ron:~/src/debian/CRAN> ls -ld /tmp
drwxrwxrwt 43 root root 19456 2009-11-05 08:04 /tmp

So there is a sticky bit set for 'other' (the trailing t).  What do you
have?

| 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. 

Yes, it would also be nice to get that ported into RPostgreSQL.

Dirk

Three out of two people have difficulties with fractions.

| My /tmp directory also has a sticky bit. 
| 
| Here's my guess of the problem. This chunk of code from the
| 'dbWriteTable' method 'postgresqlWriteTable' specifies different
| directories for Linux and non-Linux (e.g. Mac) tempfiles. 
|     if(as.character(Sys.info()["sysname"])=="Linux")
|         fn <- tempfile("rsdbi","/tmp")
|     else
|         fn <- tempfile("rsdbi")

Spot on!  Sameer added this on my recommendation based on the very same /tmp
issue problem.  Too bad I couldn't remember that this week :-/

| This difference is significant because on my Mac, the data file is saved
| inside an R tempdir with only user read permissions:
| drwx------   2 a347549 pcap       40 Oct 30 14:05 Rtmp8fgNQf/
| 
| while on Linux, the file is saved in /tmp, with whatever permissions
| that has. So, presumably either patching the code as follows:
| -    if(as.character(Sys.info()["sysname"])=="Linux")
| +    if(as.character(Sys.info()["sysname"]) %in% c("Linux", "Darwin"))

Agreed.  Explicitly CC'ing Neil for OS X advice -- Darwin is the best test?

| or finding a way to have R create a tempdir with more generous
| permissions would solve the problem. Changing my umask settings didn't
| seem to change the permissions of the R tempdir--I'm not sure how that
| is specified. Alternatively, I suppose the directory could be an option
| to let the user specify somewhere readable by both 'postgres' and the
| user's R session.

I think you can't easily which is why we created the workaround with the
temporary directory directly below /tmp, rather than by default in R's
tempdir /tmp/Rtmp...../

I think we got this bug!

Dirk


| --Robert
| 
| -----Original Message-----
| From: Dirk Eddelbuettel [mailto:edd at debian.org] 
| Sent: Thursday, November 05, 2009 9:09 AM
| To: McGehee, Robert
| Cc: Prasenjit Kapat; r-sig-db at stat.math.ethz.ch
| Subject: Re: [R-sig-DB] dbWriteTable permission problem on Mac OSX
| 
|

| | 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.  
| 
| Hm, my postgres servers (running versions 8.2 and 8.3, 8.2 has the
| default
| port)  run as user postgres:
| 
| edd at ron:~> pstree -u | grep postgres
|      |-postgres(postgres)---2*[postgres]
|      |-postgres(postgres)---4*[postgres]
| edd at ron:> 
| 
| Let's look at modes of /tmp, I have
| 
| edd at ron:~/src/debian/CRAN> ls -ld /tmp
| drwxrwxrwt 43 root root 19456 2009-11-05 08:04 /tmp
| 
| So there is a sticky bit set for 'other' (the trailing t).  What do you
| have?
| 
| | 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. 
| 
| Yes, it would also be nice to get that ported into RPostgreSQL.
| 
| Dirk
| 
| -- 
| Three out of two people have difficulties with fractions.
|

Three out of two people have difficulties with fractions.

On 5 November 2009 at 10:45, McGehee, Robert wrote:
| My /tmp directory also has a sticky bit.
|
| Here's my guess of the problem. This chunk of code from the
| 'dbWriteTable' method 'postgresqlWriteTable' specifies different
| directories for Linux and non-Linux (e.g. Mac) tempfiles.
|     if(as.character(Sys.info()["sysname"])=="Linux")
|         fn <- tempfile("rsdbi","/tmp")
|     else
|         fn <- tempfile("rsdbi")

Spot on!  Sameer added this on my recommendation based on the very  
same /tmp
issue problem.  Too bad I couldn't remember that this week :-/

| This difference is significant because on my Mac, the data file is  
saved
| inside an R tempdir with only user read permissions:
| drwx------   2 a347549 pcap       40 Oct 30 14:05 Rtmp8fgNQf/
|
| while on Linux, the file is saved in /tmp, with whatever permissions
| that has. So, presumably either patching the code as follows:
| -    if(as.character(Sys.info()["sysname"])=="Linux")
| +    if(as.character(Sys.info()["sysname"]) %in% c("Linux",  
"Darwin"))

Agreed.  Explicitly CC'ing Neil for OS X advice -- Darwin is the  
best test?

Dirk,

"Darwin" is correct for Mac OS X.

The test dbWriteTableTest.R works for me without this change.  Is the  
test not robust enough?

Neil

Neil,

| "Darwin" is correct for Mac OS X.

Cool, thanks.

| The test dbWriteTableTest.R works for me without this change.  Is the  
| test not robust enough?

Can you maybe follow-up with Robert about other possible cause of this?  As I
(as a non-Mac-user) understand it, there are numerous ways to install R (and
presumably Pg) onto OS X and the bug may bite only in some.  His suggested
fix strikes me as correct, though, as $TEMPDIR permissions are a general
Unixy way of doing things.

Dirk

Three out of two people have difficulties with fractions.

I think the running hypothesis is that, for Macs, RPostgreSQL saves its
data into an R temp directory that may or may not be readable by the
account that runs the postgres database. If it is, then the test will
run fine (presumably in Neil's case). If it is not, then you will get a
permission error. I don't think there's anything wrong with the test
case, it just won't work for everyone's configuration. With the 'bug
fix', I believe the test will work for more configurations--though still
not all if either there is a Mac system that does not use /tmp, or if
/tmp is not readable by the Postgres user. In Neil's case, I'll guess
that either he runs Postgres out of his user account, or his R temp
directories are group or world readable--mine are not.

Another gotcha not addressed that I think could bite either a Linux or
Mac users is that the user's umask settings can influence whether the
bulk copy works. For instance, (I'm almost certain that) for anyone that
runs R and postgres from different accounts, the bulk import will fail
if their umask is set to 0077, since R temp files seem to follow umask
settings. 

As mentioned before, a better solution, in theory, will likely involve a
direct binary transfer.
--Robert

-----Original Message-----
From: Dirk Eddelbuettel [mailto:edd at debian.org] 
Sent: Thursday, November 05, 2009 2:43 PM
To: Neil Tiffin
Cc: McGehee, Robert; Prasenjit Kapat; r-sig-db at stat.math.ethz.ch
Subject: Re: [R-sig-DB] dbWriteTable permission problem on Mac OSX


Neil,

| "Darwin" is correct for Mac OS X.

Cool, thanks.

| The test dbWriteTableTest.R works for me without this change.  Is the

| test not robust enough?

Can you maybe follow-up with Robert about other possible cause of this?
As I
(as a non-Mac-user) understand it, there are numerous ways to install R
(and
presumably Pg) onto OS X and the bug may bite only in some.  His
suggested
fix strikes me as correct, though, as $TEMPDIR permissions are a general
Unixy way of doing things.

Dirk

Three out of two people have difficulties with fractions.