Re: Strange security issue with Superuser access

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(at)gmail(dot)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 <wmoran(at)potentialtech(dot)com>