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...
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.