Search code examples
rpostgresqlr-dbirpostgresql

Permission denied to write a table with dbWriteTable in RPostgreSQL

I've been trying to solve a problem with the RPostgreSQL package for quite some time. My code:

path_ene <- "C:/Users/omen03/ENE"

nene <- "ene_2010_02_efm.csv"


drv <- dbDriver("PostgreSQL")

con = dbConnect(drv, user="postgres", password="mypassword",
            host="localhost", port=5432, dbname="ENE")

tn = "ene_2010_02_efm";

dbRemoveTable(con,tn);
dbWriteTable(conn = con, name = tn, value = paste0(path_ene,"/",nene),
                                    sep = ",", overwrite = FALSE))

When executing the code described above, I throw the following error:

Error in postgresqlExecStatement (conn, statement, ...):    RS-DBI driver: (could not Retrieve the result: ERROR: Could not open file «C: /Users/omen03/ENE/ene_2010_02_efm.csv» for reading: Permission denied

HINT: COPY FROM tells the PostgreSQL server process to read a file. You may want to use a client-side facility such as psql \ copy. ) In addition: Warning message: In postgresqlImportFile (conn, name, value, ...):    could not load data into table

when I try without specifying the file path, it throws me another error

dbRemoveTable(con,tn);
dbWriteTable(conn = con, name = tn, value = nene),
                                    sep = ",", overwrite = FALSE))

Error in postgresqlExecStatement (conn, statement, ...):    RS-DBI driver: (could not Retrieve the result: ERROR: could not open file  «./ ene_2010_02_efm» for reading: No such file or directory

HINT: COPY FROM tells the PostgreSQL server process to read a file. You may want to use a client-side facility such as psql \ copy. ) In addition: Warning message: In postgresqlImportFile (conn, name, value, ...):    could not load data into table

Update:

Taking into account the error message, I decided to use sql directly to insert the csv values in each of the tables in the database. For that I used a couple of functions

createEmptyTable <- function(con,tn,df) {
  sql <- paste0("create table \"",tn,"\" (",paste0(collapse=',','"',names(df),'" ',sapply(df[0,],postgresqlDataType)),");");
  dbSendQuery(con,sql);
  invisible();
};

Taken from: How to write a table in PostgreSQL from R?

and 

#tn: Table name
#c_names: column names of each table (a list) 
#source: The path to each csv files

insert_data = function(tn, source){
  sql = paste0('COPY ',tn,' FROM \'',paste0(path_ene,'\\',source),'\' DELIMITER \',\' CSV HEADER')
  dbSendQuery(con, sql);
}


insert_data(tn[1], paste0(path_ene, "/",nene[1]))

Anyway, I still get a very similar error.

Error in postgresqlExecStatement (conn, statement, ...):    RS-DBI driver: (could not Retrieve the result: ERROR: Could not open file  «C: /Users/omen03/ENE/ene_2010_02_efm.csv» for reading: Permission denied HINT: COPY FROM tells the PostgreSQL server process to read a file. You may want to use a client-side facility such as psql \ copy. )

My session info:

R version 3.6.1 (2019-07-05) Platform: x86_64-w64-mingw32/x64 (64-bit) Running under: Windows 10 x64 (build 18362)

Matrix products: default

locale: [1] LC_COLLATE=Spanish_Chile.1252 LC_CTYPE=Spanish_Chile.1252 LC_MONETARY=Spanish_Chile.1252 LC_NUMERIC=C [5] LC_TIME=Spanish_Chile.1252

attached base packages: [1] stats graphics grDevices utils
datasets methods base

other attached packages: [1] RPostgreSQL_0.6-2 DBI_1.0.0
foreign_0.8-71 captioner_2.2.3 kableExtra_1.1.0 wordcloud_2.6 tidytext_0.2.2 gridExtra_2.3 [9] gtable_0.3.0
readstata13_0.9.2 RColorBrewer_1.1-2 ggrepel_0.8.1 pbapply_1.4-2 srvyr_0.3.6 data.table_1.12.6 lubridate_1.7.4 [17] stringi_1.4.3 forcats_0.4.0 stringr_1.4.0 dplyr_0.8.3
purrr_0.3.3 readr_1.3.1 tidyr_1.0.0 tibble_2.1.3 [25] ggplot2_3.2.1 tidyverse_1.3.0

loaded via a namespace (and not attached): [1] httr_1.4.1
jsonlite_1.6 viridisLite_0.3.0 splines_3.6.1 modelr_0.1.5
assertthat_0.2.1 cellranger_1.1.0 sessioninfo_1.1.1 [9] pillar_1.4.3 backports_1.1.5 lattice_0.20-38 glue_1.3.1
digest_0.6.23 rvest_0.3.5 colorspace_1.4-1 htmltools_0.4.0 [17] Matrix_1.2-17 survey_3.36 pkgconfig_2.0.3 broom_0.5.2 haven_2.2.0 scales_1.1.0 webshot_0.5.2 generics_0.0.2
[25] withr_2.1.2 lazyeval_0.2.2 cli_2.0.0
survival_2.44-1.1 magrittr_1.5 crayon_1.3.4 readxl_1.3.1
evaluate_0.14 [33] tokenizers_0.2.1 janeaustenr_0.1.5 fs_1.3.1
fansi_0.4.0 nlme_3.1-140 SnowballC_0.6.0 xml2_1.2.2
tools_3.6.1 [41] hms_0.5.2 mitools_2.4
lifecycle_0.1.0 munsell_0.5.0 reprex_0.3.0 compiler_3.6.1
rlang_0.4.2 grid_3.6.1 [49] rstudioapi_0.10
rmarkdown_1.18 R6_2.4.1 knitr_1.26 zeallot_0.1.0
parallel_3.6.1 Rcpp_1.0.3 vctrs_0.2.1 [57] dbplyr_1.4.2 tidyselect_0.2.5 xfun_0.11

Solution

  • Finally, I found the answer by looking for the problem of directly importing data into postgresql. It was enough to change the configuration of the folder containing the .csv files.

    I reproduce this answer that was the one I finally used.

    • Right click the folder containing the data file(s) that permission was denied to and then click Properties.
    • In the Folder's Properties window, select the Security tab.
    • Click the Edit button. In the "Permissions for the folder" window that opened, click the Add... button.
    • Type Everyone into the "Enter the object names to select" text area box.
    • Click OK and the window will close.