From: | Dwaraka Srinivasan <dwaraka(dot)tx(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | select for share error when inserting data in RDS |
Date: | 2017-04-12 05:31:14 |
Message-ID: | CAFw7q36ZzaUNan6MYWkbUdNQ93DMjuft2AB5GVc2NagWg_X_EQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | r piper | 2017-04-12 14:38:43 | |
Previous Message | Albe Laurenz | 2017-03-28 09:40:31 | Re: How does backend server know which data file and page to read? |