Hello,
I'd appreciate your help in resolving the following error. I am getting a 'permission denied for schema phoenix; Query: Select 1 from only phoenix.test1 x where id operator(pg_catalog.=) $1 for key share of x'. This happens when I try to insert data into another table that has a FK reference to the table test1. It is the same owner for both tables and this error is happening in RDS, not in my local. I apologize for the lengthy email but I figure more info is better,
I am migrating a postgresql 9.4 instance from EC2 to RDS. I have a database called 'phoenix' created in RDS by the 'postgres' master user. I have a Group Role called 'phoenix-rw' which was created as follows
CREATE ROLE "phoenix-rw"
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
I also have some LOGIN roles
CREATE ROLE phoenix LOGIN
NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;
GRANT "phoenix-rw" TO phoenix;
CREATE ROLE bamboo LOGIN
NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;
CREATE ROLE ds001 LOGIN
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE REPLICATION;
grant "phoenix-rw" to ds001;
CREATE ROLE ds002 LOGIN
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE REPLICATION;
grant "phoenix-rw" to ds002
As the 'postgres' user I created a schema 'phoenix' in the database 'phoenix' -
CREATE SCHEMA phoenix
AUTHORIZATION phoenix;
GRANT ALL ON SCHEMA phoenix TO phoenix;
GRANT ALL ON SCHEMA phoenix TO "phoenix-rw";
As 'ds001' I created a table
create table phoenix.test1(id serial, name text)
ALTER TABLE phoenix.test1
ADD CONSTRAINT pk_test1 PRIMARY KEY(id);
grant all on phoenix.test1 to "bamboo"
grant all on phoenix.test1 to "phoenix-rw"
grant all on phoenix.test1_id_seq to "phoenix-rw"
alter table phoenix.test1 owner to bamboo
I also create a second table as ds001
create table phoenix.test2 (id serial, test1_id int)
ALTER TABLE phoenix.test2
ADD CONSTRAINT pk_test2 PRIMARY KEY(id);
grant all on phoenix.test2 to "bamboo"
grant all on phoenix.test2 to "phoenix-rw"
alter table phoenix.test2
add constraint fk_21 foreign key(test1_id) references phoenix.test1(id) match simple
on update no action on delete no action;
grant all on phoenix.test2_id_seq to "phoenix-rw"
alter table phoenix.test2 owner to bamboo
I log back in as ds002 and insert data into test1 successfully. The one row in this table is (2,'test'). I try to insert (1,2) into the table test2 and I get the error 'permission denied for schema phoenix. Query select 1 ...OPERATOR(pg.=) $1 for key share of ...
I tried creating the table as postgres, granting the database role to ds002, inserting the data as 'postgres', etc. always getting the same error. Thanks for your help.
D