Re: Strange security issue with Superuser access

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