Strange security issue with Superuser access

From: Andrzej Pilacik <cypisek77(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Strange security issue with Superuser access
Date: 2015-03-09 21:00:14
Message-ID: CAJw8uJTrRqUqQqCfRBsRMV+2Vc87a+VQVDkHRZxZ-BN4nDauLg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message hailong Li 2015-03-10 11:17:22 Re: The slave suddenly stopped with such DB log : "will not overwrite a used ItemId" and "heap_insert_redo: failed to add tuple"
Previous Message Vang P yang 2015-03-09 19:15:15 Column is a computation