5

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?

6

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;
1

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;  
  • Is this documentend somewhere? This is also need for non onwers users creating data – brauliobo Aug 23 '17 at 8:08
  • this was nowhere documented, so I posted question-answer here, for people to discover fast. Probably we should report bug to Amazon – danbst Aug 23 '17 at 11:44
  • I think this is issue of pg_repack it self. I had to set the onwer to another write-enabled group I use for some users. – brauliobo Aug 23 '17 at 13:33
  • opened an issue: github.com/reorg/pg_repack/issues/146 doing this created other problems – brauliobo Aug 23 '17 at 23:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.