Re: Strange security issue with Superuser access - Mailing list 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
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