From: | Jerry Sievers <gsievers19(at)comcast(dot)net> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | Andrzej Pilacik <cypisek77(at)gmail(dot)com>, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, Stephen Frost <sfrost(at)snowman(dot)net>, "pgsql-general\(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Strange security issue with Superuser access |
Date: | 2015-03-10 17:46:57 |
Message-ID: | 86fv9chhim.fsf@jerry.enova.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"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
From | Date | Subject | |
---|---|---|---|
Next Message | inspector morse | 2015-03-10 19:56:58 | How to read refcursor column's using string column name? |
Previous Message | Adrian Klaver | 2015-03-10 15:48:30 | Re: FW: Installation |