Repacking RDS database with pg_repack isn't online

Question

While running pg_repack on a RDS Postgresql 9.6.3, got this failure for one of writers:

ProgrammingError: permission denied for relation log_24034858
CONTEXT:  SQL statement "INSERT INTO repack.log_24034858(pk, row) 
VALUES(CASE WHEN $1 IS NULL THEN NULL ELSE (ROW($1.id)::repack.pk_24034858) END, $2)"

But pg_repack should allow writes to repacked tables, why did it fail here?

Answer 1

We found a similar issue. The issue appears to be that log tables created in the repack schema don't have the correct permissions. Our solution was to use ALTER DEFAULT PRIVALEGES to fix this:

CREATE EXTENSION pg_repack;
ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT INSERT ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA repack GRANT USAGE, SELECT ON SEQUENCES TO PUBLIC;

Answer 2

According to Amazon you have to create pg_repack extension from your superuser account. But the extension is created with rds_superuser role instead. And all other objects are created with that role as owner, for example:

=> \dnS+ repack                                                                                 │ 
                     List of schemas                                                                    │ subsidy | birth_month | birth_year
  Name  |     Owner     | Access privileges | Description                                               │---------+-------------+------------
--------+---------------+-------------------+-------------                                              │       1 |           7 |       1952
 repack | rds_superuser |                   |                                                           │       1 |           7 |       1952
(1 row)  

You shouldn't change owner of schema. And you probably don't want to REASSIGN OWNED .... It is sufficient to change owner for each function in repack namespace to fix permission problems:

create extension pg_repack;
ALTER FUNCTION repack.version() OWNER TO postgres;                                                                                                                                                             
ALTER FUNCTION repack.version_sql() OWNER TO postgres;                                                
ALTER FUNCTION repack.array_accum(anyelement) OWNER TO postgres;                                      
ALTER FUNCTION repack.oid2text(oid) OWNER TO postgres;                                                
ALTER FUNCTION repack.get_index_columns(oid,text) OWNER TO postgres;                                  
ALTER FUNCTION repack.get_order_by(oid,oid) OWNER TO postgres;                                        
ALTER FUNCTION repack.get_create_index_type(oid,name) OWNER TO postgres;                              
ALTER FUNCTION repack.get_create_trigger(oid,oid) OWNER TO postgres;                                  
ALTER FUNCTION repack.get_enable_trigger(oid) OWNER TO postgres;                                      
ALTER FUNCTION repack.get_assign(oid,text) OWNER TO postgres;                                         
ALTER FUNCTION repack.get_compare_pkey(oid,text) OWNER TO postgres;                                   
ALTER FUNCTION repack.get_columns_for_create_as(oid) OWNER TO postgres;                               
ALTER FUNCTION repack.get_drop_columns(oid,text) OWNER TO postgres;                                   
ALTER FUNCTION repack.get_storage_param(oid) OWNER TO postgres;                                       
ALTER FUNCTION repack.get_alter_col_storage(oid) OWNER TO postgres;                                   
ALTER FUNCTION repack.repack_indexdef(oid,oid,name,boolean) OWNER TO postgres;                        
ALTER FUNCTION repack.repack_trigger() OWNER TO postgres;                                             
ALTER FUNCTION repack.conflicted_triggers(oid) OWNER TO postgres;                                     
ALTER FUNCTION repack.disable_autovacuum(regclass) OWNER TO postgres;                                 
ALTER FUNCTION repack.repack_apply(cstring,cstring,cstring,cstring,cstring,integer) OWNER TO postgres;
ALTER FUNCTION repack.repack_swap(oid) OWNER TO postgres;                                             
ALTER FUNCTION repack.repack_drop(oid,integer) OWNER TO postgres;                                     
ALTER FUNCTION repack.repack_index_swap(oid) OWNER TO postgres;                                       
ALTER FUNCTION repack.get_table_and_inheritors(regclass) OWNER TO postgres;