select for share error when inserting data in RDS

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

Browse pgsql-novice by date

  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?