0

I'm accessing a PostgreSQL database through the R library RPostgreSQL. The following line successfully reads my table into object DF:

DF <- dbReadTable(conn = con, name = c("my_schema","my_table"))

However, attempting to write back into the database with the following line throws ERROR: permission denied for schema my_schema:

dbWriteTable(conn = con, name = c("my_schema", "my_table"), value = DF)

I've discovered from the question Writing to specific schemas with RPostgreSQL that the solution is to SET search_path = my_schema, public;, but I have no idea how to run this from the R Console. I've tried lines such as dbSendQuery(conn = con, statement = "SET search_path = my_schema, public;"), and I recognize that setting permissions is not querying at all, but there's not a dbSetPermissions function in RPostgreSQL.

I'm clearly missing something fundamental since the answer to the aforementioned question satisfied the user who asked it, so I appreciate your patience.

user3765080
  • 21
  • 7
  • Does the user you connected with to Postgres have sufficient permissions to write to the table? I had a similar issue in python that was a db user permission issue. – Hatt Aug 14 '18 at 18:54
  • The same credentials can modify tables in the database through DBeaver, and I *think* that means the user has sufficient permissions. – user3765080 Aug 14 '18 at 19:06
  • Ah wait that's a good point, I can modify *some* tables in the database through DBeaver. I can *dbWriteTable* to the same schemas that I can modify via DBeaver, and I can't DBeaver-modify the tables to which I can't *dbWriteTable* – user3765080 Aug 14 '18 at 19:46
  • That makes sense - I had to go into postgres user and have a look at the permissions. I'm trying to find a good reference for you but am unsuccessful thus far. – Hatt Aug 14 '18 at 19:50
  • I think this should help some - part of my issue as well was working with existing versus new objects. https://dba.stackexchange.com/a/53936/76752 – Hatt Aug 14 '18 at 20:15

0 Answers0