dbAppendTable() is incompatible with PostgreSQL row-level security enabled (COPY FROM not supported)

[salim-b]

Consider the following minimal reproducible example:

1. Create a table public.test with row-level security (RLS) enabled and a simple default-allow policy defined:

   CREATE TABLE public.test (
     "id"  integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
     title text
   );
   
   ALTER TABLE public.test ENABLE ROW LEVEL SECURITY;
   CREATE POLICY default_allow ON public.test AS PERMISSIVE FOR ALL TO PUBLIC USING (TRUE);

2. Try to append to this table via DBI::dbAppendTable():

   DBI::dbAppendTable(conn = DBI::dbConnect(drv = RPostgres::Postgres(), ...),
                      name = DBI::Id(schema = "public",
                                     table = "test"),
                      value = tibble::tibble(title = "First one"))

   (Complete the conn arg with valid credentials!)

   It will fail with the following error:

   Error: Failed to initialise COPY : ERROR:  COPY FROM not supported with row-level security
   HINT:  Use INSERT statements instead.
   

   Without RLS enabled, dbAppendTable() succeeds.

I'm unsure whether this issue should be reported here or rather over at the DBI repo. Please let me know if I should move it there.

[krlmlr]

Thanks. Can you please try dbAppendTable(copy = FALSE) ?

[salim-b]

Can you please try dbAppendTable(copy = FALSE) ?

Thank you a lot, that works! I didn't realize I could provide a copy arg.

I think this issue can be closed then. Although the error message could be extended to be more helpful (i.e. mention the copy arg), I guess... or would this be too Postgres-specific?

[krlmlr]

Thanks. No, if we wanted a specific error message for this condition, we'd want to raise it here, but I wouldn't know how to detect it. PR welcome, but then a web search for this error message will also find this issue, so I wonder if it's worth it.