Thread: 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@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


--
PT <wmoran@potentialtech.com>

* Andrzej Pilacik (cypisek77@gmail.com) wrote:
> 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.

Yes.

    Thanks,

        Stephen

On 03/10/2015 05:41 AM, Stephen Frost wrote:
> * Andrzej Pilacik (cypisek77@gmail.com) wrote:
>> 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.
>
> Yes.


This brought back a memory. For the hows and whys see this thread :

http://www.postgresql.org/message-id/4D6BB33F.9010704@lupomesky.cz

>
>     Thanks,
>
>         Stephen
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 03/10/2015 08:20 AM, Andrzej Pilacik wrote:
> I see the issues that this person might be having.  I am not doing a
> restore or working on an existing issue.

I know, but the thread actually covers the same ground. If you want to
learn exactly what happens with FKs and users in Postgres read Tom Lanes
responses in the thread. They will explain what you are seeing.

>
> My setup is very vanilla, anyone can create these tables and test, will
> get the same permission error...  (I did it in a brand new environment)


>
> What I am asking here is why does a SUPERUSER not able to insert the
> data when the constraint is introduced.
>
>
> On Tue, Mar 10, 2015 at 10:16 AM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 03/10/2015 05:41 AM, Stephen Frost wrote:
>
>         * Andrzej Pilacik (cypisek77@gmail.com
>         <mailto:cypisek77@gmail.com>) wrote:
>
>             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.
>
>
>         Yes.
>
>
>
>     This brought back a memory. For the hows and whys see this thread :
>
>     http://www.postgresql.org/__message-id/4D6BB33F.9010704@__lupomesky.cz
>     <http://www.postgresql.org/message-id/4D6BB33F.9010704@lupomesky.cz>
>
>
>                  Thanks,
>
>                          Stephen
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

"David G. Johnston" <david.g.johnston@gmail.com> writes:

> On Tue, Mar 10, 2015 at 8:20 AM, Andrzej Pilacik <cypisek77@gmail.com> wrote:
>
>     I see the issues that this person might be having.  I am not doing a restore or working on an existing issue.
>
>     My setup is very vanilla, anyone can create these tables and test, will get the same permission error...  (I did
itin a brand new environment) 
>
>     What I am asking here is why does a SUPERUSER not able to insert the data when the constraint is introduced.
>
> ​To prevent a super-user from shooting themselves in the
> foot.  Begin allowed to insert otherwise invalid data is not
> something that the database allows a superuser to do.  A
> superuser inserting a NULL into a NOT NULL column will also get an
> error.  This is no different.


No, tha'ts not what this discussion is about...  A superuser is
prohibited even from a valid insert if  table owner is defficient in
whatever grants needed to validate the constraint.

Below demonstrates this issue which is apparently long-standing and well
enough known though I haven't run across it myself so rolled this test
case to have a look-see.


sj$ cat q
-- be a super user here

begin;

select version();

create role foo;
create schema authorization foo;

set role foo;

create table foo.referenced(a int primary key);
create table public.referring(like foo.referenced,
    foreign key (a) references foo.referenced);

reset role;

select rolsuper from pg_authid where rolname = current_user;

insert into foo.referenced select 1;
insert into public.referring select 1;

revoke all on schema foo from foo;

insert into public.referring select 1;
-- FAIL



sj$ psql -ef q
begin;
BEGIN
select version();
                                           version
----------------------------------------------------------------------------------------------
 PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit
(1 row)

create role foo;
CREATE ROLE
create schema authorization foo;
CREATE SCHEMA
set role foo;
SET
create table foo.referenced(a int primary key);
CREATE TABLE
create table public.referring(like foo.referenced,
    foreign key (a) references foo.referenced);
CREATE TABLE
reset role;
RESET
select rolsuper from pg_authid where rolname = current_user;
 rolsuper
----------
 t
(1 row)

insert into foo.referenced select 1;
INSERT 0 1
insert into public.referring select 1;
INSERT 0 1
revoke all on schema foo from foo;
REVOKE
insert into public.referring select 1;
psql:q:23: ERROR:  permission denied for schema foo
LINE 1: SELECT 1 FROM ONLY "foo"."referenced" x WHERE "a" OPERATOR(p...
                           ^
QUERY:  SELECT 1 FROM ONLY "foo"."referenced" x WHERE "a" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x


HTH



>
> David J.
> ​
>  
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800