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>

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@potentialtech.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@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 :

/messages/by-id/4D6BB33F.9010704@lupomesky.cz

Thanks,

Stephen

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 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>
wrote:

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

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

David J.
​

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
</messages/by-id/4D6BB33F.9010704@lupomesky.cz&gt;

Thanks,

Stephen

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

​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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general