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 <cypisek77@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
