Can't INSERT while repacking

[ghost]

PGSQL VERSION: 9.6.6 UBUNTU VERSION: 16.04.3 LTS

I tried repacking a table: sudo pg_repack -h db.db.com -U my_role -k mydb -t myschema.mytable

And everything is working: [INFO] repacking myschema.mytable

But while the table is repacking I can't INSERT into the table. Any solutions?

[dvarrazzo]

Yes you can. Can you provide a failing test?

[ghost]

@dvarrazzo `ERROR: permission denied for relation log_17441 CONTEXT: SQL statement "INSERT INTO repack.log_17441(pk, row) VALUES( CASE WHEN $1 IS NULL THEN NULL ELSE (ROW($1.eventtime, $1.orderid, $1.eventtype, $1.typename, $1.sku)::repack.pk_17441) END, $2)" Error

ERROR: permission denied for relation log_17441 SQL state: 42501 Context: SQL statement "INSERT INTO repack.log_17441(pk, row) VALUES( CASE WHEN $1 IS NULL THEN NULL ELSE (ROW($1.eventtime, $1.orderid, $1.eventtype, $1.typename, $1.sku)::repack.pk_17441) END, $2)"`

[andy-plutoflume]

I am also seeing this issue - an application attempting to insert in to a table that is doing a repack is getting this error.

This does not happen consistently - some instances appear to be able to insert, but others do not.

This is on pg_repack 1.4.3.

2019-02-24 10:34:59 UTC:10.0.0.29(37352):admin@masterdb:[123]:ERROR: permission denied for relation log_551734232
2019-02-24 10:34:59 UTC:10.0.0.29(37352):admin@masterdb:[123]:CONTEXT: SQL statement "INSERT INTO repack.log_551734232(pk, row) VALUES( 

Even after a GRANT I see this error:

masterdb=> grant all on log_551734232 to admin;                                                                                                                                                                                                                 
GRANT

[andy-plutoflume]

Some further online info on this:

https://engineering.ometria.com/2018/01/29/using-pg_repack-with-aws-postgres-rds/

https://dba.stackexchange.com/questions/182840/repacking-rds-database-with-pg-repack-isnt-online

FWIW - granting to public worked for us as a temporary measure.

[MichaelDBA]

Yes I would like to see this fixed too

[xzilla]

I believe the correct fix is to modify the repack trigger to be created with security definer, although granting permission to public can work as a work-around, provided you remember to do it :-(

[dvarrazzo]

Does this only happen on RDS?

[xzilla]

I believe this can happen in any case where you have a non superuser who has insert permissions on a table that is owned by a different role. I think the problem is more common in RDS since the permissions system pushes people to use more non-super user accounts. (I certainly see no reason why this wouldn't have the same problem on other cloud postgres solutions, but haven't tested it)