"David G. Johnston" <david.g.johnston@xxxxxxxxx> writes:
> On Tue, Mar 10, 2015 at 8:20 AM, Andrzej Pilacik <cypisek77@xxxxxxxxx> 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@xxxxxxxxxxx
p: 312.241.7800
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
