Yes, it was a typo by me, the second insert should read: > --- Insert as superuser > delete from ap.table1 > Insert into ap.table1 values (12) --- permission issue inserting into table1 > , (SUPERUSER can't access schema ap)
On Tue, Mar 10, 2015 at 8:18 AM, PT <wmo...@potentialtech.com> wrote: > > I've read this email over multiple times, and I don't understand > what your question is. I don't see what it is that you think is > working in an unexpected way, all the situations I see described > you claim work. Did you possible forget to put something in the > email or am I just a poor reader? > > On Mon, 9 Mar 2015 17:00:14 -0400 > Andrzej Pilacik <cypise...@gmail.com> wrote: > > > I ran into this yesterday and I wanted to post this to see if this is > > working as expected or it is a bug. > > > > By creating 2 tables and creating a FK between them and then changing the > > owner of the tables to a group, I lost the ability to insert into the > first > > table executing as SUPERUSER. > > I thought that SUPERUSER does not check any permissions... > > > > Scenario: > > create role rs; > > create schema ap authorization postgres; > > > > create table ap.table1 (a int) > > alter table ap.table1 owner to rs; -- this is a group with nobody in it > > > > create table ap.tablefk (b INT) > > alter table ap.tablefk owner to rs; > > insert into ap.tablefk values (12) > > > > select * from ap.tablefk > > select * from ap.table1 > > > > alter table ap.table1 add constraint apk1 primary key (a) > > alter table ap.tablefk add constraint apkfk1 primary key (b) > > > > --- Insert as superuser > > Insert into ap.table1 values (12) --- works without an issue > > > > > > -- create a foreign key to second table > > ALTER TABLE ap.table1 > > ADD CONSTRAINT id_fk FOREIGN KEY (a) > > REFERENCES ap.tablefk (b) MATCH SIMPLE > > ON UPDATE NO ACTION ON DELETE NO ACTION; > > > > --- Insert as superuser > > delete from ap.table1 > > Insert into ap.table1 values (12) --- works without an issue > > > > /* > > It seems like even though you execute the insert as a superuser, the > > constraint check executes as the owner of the object (table) and > therefore > > needs usage permission on the ap schema > > I thought that superuser privs are "god" like and always work regardless > of > > the scenario > > */ > > > > --- TO FIX > > grant usage on schema ap to rs; > > Insert into ap.table1 values (12) > > > > > > Can anyone explain how the FK constraint function works? Is it executed > as > > the owner of the object. That is the only thing that would make sense > for > > me. > > > > Thank you. > > > > Andrzej > > > -- > PT <wmo...@potentialtech.com> >
