Permission errors on insert after schema drop/create (Pg 9.0.23) We have a web application that allows users to modify data through an API. In order to allow people to run tests, we provide a sandbox area called 'daily'. This sandbox gets refreshed every day from the 'live' database.

This works by dropping/creating a 'daily' schema then copying over a subset of tables from the 'live' schema. This is applied by running a simple SQL script via crontab every day (see below).

Issue:


when the web application is started after the daily refresh, clients can insert data into the 'daily' schema as expected
when the web application is started before the daily refresh, we encounter permission errors when trying to insert data, eg

permission denied for relation annotation



Note: restarting the web application allows everything to work fine, but I would prefer to avoid the downtime. 

Question:


is this behaviour expected (Pg 9.0.23)?
is there anything I can do to avoid this problem?


The script we are using to provide the daily refresh:

BEGIN;

drop schema if exists genome3d_data_daily CASCADE;

create schema genome3d_data_daily AUTHORIZATION genome3ddaily;

-- recreate sequence
create sequence genome3d_data_daily.annotation_id_seq;
select setval('genome3d_data_daily.annotation_id_seq', orig.last_value) from genome3d_data.annotation_id_seq orig;

-- create new tables (including constraints, indexes, etc)
create table genome3d_data_daily.annotation_type    (like genome3d_data.annotation_type including all);
create table genome3d_data_daily.annotation_segment (like genome3d_data.annotation_segment including all);
create table genome3d_data_daily.annotation         (like genome3d_data.annotation including all);

grant select on all tables    in schema genome3d_data_daily to PUBLIC;
grant select on all sequences in schema genome3d_data_daily to PUBLIC;

grant all privileges on all tables    in schema genome3d_data_daily to genome3ddaily;
grant all privileges on all sequences in schema genome3d_data_daily to genome3ddaily;

-- by default this will point to the original sequence
alter table genome3d_data_daily.annotation
alter column id set default nextval('genome3d_data_daily.annotation_id_seq');

-- insert data
insert into genome3d_data_daily.annotation_type     select * from genome3d_data.annotation_type;
insert into genome3d_data_daily.annotation_segment  select * from genome3d_data.annotation_segment;
insert into genome3d_data_daily.annotation          select * from genome3d_data.annotation;

END;
Figured out a workaround that involved just deleting tables (and sequences) rather than dropping the schema. Leaving here in case it helps anyone else.

Changed the first few lines:

BEGIN;
drop schema if exists genome3d_data_daily CASCADE;
create schema genome3d_data_daily AUTHORIZATION genome3ddaily;


...to...

BEGIN;
create schema genome3d_data_daily AUTHORIZATION genome3ddaily;
END;

BEGIN;
drop table if exists genome3d_data_daily.annotation_type;
drop table if exists genome3d_data_daily.annotation_segment;
drop table if exists genome3d_data_daily.annotation;
drop sequence if exists genome3d_data_daily.annotation_id_seq;


This no longer disrupts existing database connections.