Re: Strange security issue with Superuser access

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:

> On Tue, Mar 10, 2015 at 8:20 AM, Andrzej Pilacik <cypisek77(at)gmail(dot)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(dot)consulting(at)comcast(dot)net
p: 312.241.7800